theokit-database

TheoKit Database (Drizzle + SQLite)

Schema Definition

// server/db/schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

export const tasks = sqliteTable('tasks', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  done: integer('done', { mode: 'boolean' }).notNull().default(false),
  createdAt: text('created_at')
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
})

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
})

DB Connection

// server/db/index.ts
import { drizzle } from 'drizzle-orm/better-sqlite3'
import Database from 'better-sqlite3'
import * as schema from './schema.js'

const sqlite = new Database('data/dev.db')
export const db = drizzle(sqlite, { schema })

Common Queries

import { db } from '@/server/db'
import { tasks } from '@/server/db/schema'
import { eq } from 'drizzle-orm'

// Select all
db.select().from(tasks).all()

// Select by ID
db.select().from(tasks).where(eq(tasks.id, 1)).get()

// Insert + return
db.insert(tasks).values({ title: 'New task' }).returning().get()

// Update
db.update(tasks).set({ done: true }).where(eq(tasks.id, 1)).run()

// Delete
db.delete(tasks).where(eq(tasks.id, 1)).run()

Seeds

// server/db/seed.ts
import { db } from './index.js'
import { tasks } from './schema.js'

await db
  .insert(tasks)
  .values([
    { title: 'Learn TheoKit', done: false },
    { title: 'Build an agent', done: false },
  ])
  .run()

console.log('Seeded database')

Run: npm run seed or npx tsx server/db/seed.ts

CLI Commands

npx drizzle-kit push    # Apply schema changes to dev DB (no migration files)
npx drizzle-kit generate # Generate SQL migration files (for production)
npm run seed             # Run seed script

Scaffolding Resources