Dexieish

A minimal Dexie-like API on top of SQLite using val.town/x/std/sqlite2.

Provides val-scoped data storage using sqlite.

import { Dexieish } from "https://esm.town/v/maxm/dexieish/main.ts";

const db = new Dexieish();

// Define schema
db.version(1).stores({
  users: "++id, name, email",
});

await db.ready();

const db = new Dexieish();

db.version(1).stores({
  users: "++id, name, &email, age", // auto-increment id, unique email
});

await db.ready();
const users = db.table("users")!;

// Create
const userId = await users.add({
  name: "Alice",
  email: "alice@example.com",
  age: 25,
});

// Read
const user = await users.get(userId);
const allUsers = await users.toArray();

// Update
await users.update(userId, { age: 26 });

// Delete
await users.delete(userId);

// Auto-increment primary key
"++id, name, email";

// Unique constraint
"++id, name, &email"; // unique email

// Compound index
"++id, name, email, [name+age]"; // index on name+age combination

// Unique compound index
"++id, name, email, &[category+slug]"; // unique category+slug

const posts = db.table("posts")!;

// Exact match
const published = await posts.where("status").equals("published").toArray();

// Range queries
const recent = await posts.where("createdAt").above("2024-01-01").toArray();
const range = await posts.where("score").between(80, 100).toArray();

// Multiple values
const categories = await posts.where("category").anyOf(["tech", "news"])
  .toArray();

// String prefix matching
const drafts = await posts.where("title").startsWith("Draft").toArray();

// Ordering and pagination
const topPosts = await posts
  .orderBy("score")
  .limit(10)
  .offset(20)
  .toArray();

// Get first result
const latestPost = await posts
  .orderBy("createdAt")
  .first();

// Count results
const draftCount = await posts.where("status").equals("draft").count();

interface BlogSchema {
  users: { id?: number; name: string; email: string };
  posts: { id?: number; userId: string; title: string; slug: string };
  comments: { id?: number; postId: string; userId: string; content: string };
  tags: { id?: number; name: string };
  post_tags: { id?: number; postId: string; tagId: string };
}

const db = new Dexieish<BlogSchema>();

db.version(1).stores({
  users: "++id, &email, name",
  posts: "++id, userId, title, &slug, createdAt",
  comments: "++id, postId, userId, createdAt",
  tags: "++id, &name",
  post_tags: "++id, postId, tagId, [postId+tagId]",
});

await db.ready();

// Create relationships
const userId = await db.table("users")!.add({
  name: "Bob",
  email: "bob@example.com",
});

const postId = await db.table("posts")!.add({
  userId,
  title: "My First Post",
  slug: "my-first-post",
  createdAt: new Date().toISOString(),
});

await db.table("comments")!.add({
  postId,
  userId,
  content: "Great post!",
});

// Query relationships
const userPosts = await db.table("posts")!
  .where("userId").equals(userId)
  .toArray();

const postComments = await db.table("comments")!
  .where("postId").equals(postId)
  .toArray();

const users = db.table("users")!;

// Bulk insert
const newUsers = [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
  { name: "Charlie", email: "charlie@example.com" },
];

const count = await users.bulkAdd(newUsers);
console.log(`Added ${count} users`);

const db = new Dexieish();

// Version 1: Initial schema
db.version(1).stores({
  users: "++id, name, age",
});

// Version 2: Add email field and posts table
db.version(2).stores({
  users: "++id, name, age, email", // Added email
  posts: "++id, userId, title", // New table
});

// Version 3: Make email unique, add indexes
db.version(3).stores({
  users: "++id, name, age, &email", // Made email unique
  posts: "++id, userId, &title, content", // Made title unique, added content
});

await db.ready(); // Automatically migrates to latest version

On Val Town VALTOWN_ENTRYPOINT is used to come up with a per-val prefix. You can override it if you'd like.

// Use table prefixes to isolate test data.
const testDb = new Dexieish({
  tablePrefix: "test_",
});

db.version(1).stores({
  users: "++id, name", // Creates table "test_users"
});

await testDb.ready();

// Clean up all tables when done
await testDb.cleanup();

interface User {
  id?: number;
  name: string;
  email: string;
  age?: number;
}

interface AppSchema {
  users: User;
  posts: Post;
}

const db = new Dexieish<AppSchema>();
const users = db.table("users")!; // Fully typed table operations