One table → REST and MCP CRUD.

Install #

Add the data package (and Drizzle) alongside core:

npm i @apex-stack/data drizzle-orm

Postgres/PGlite drivers load on demand — install only the one you use (below).

Define once #

A schema, a database handle, and a resource — three small files:

db/schema.ts

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'

export const todos = sqliteTable('todos', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  text: text('text').notNull(),
  done: integer('done', { mode: 'boolean' }).notNull().default(false),
})

db/index.ts — pick your database in one line

import { fileURLToPath } from 'node:url'
import { applyMigrations, createDb } from '@apex-stack/data'
import * as schema from './schema.js'

const handle = await createDb('data.db')   // local SQLite (libSQL)
await applyMigrations(handle, fileURLToPath(new URL('./migrations', import.meta.url)))

export const db = handle.db
export { schema }

server/api/todos.ts

import { defineResource } from '@apex-stack/data'
import { z } from 'zod'
import { db, schema } from '../../db/index.js'

export default defineResource('todos', {
  db,
  table: schema.todos,
  insert: { text: z.string(), done: z.boolean().optional() },
})

The surfaces you get #

From that single defineResource call:

REST and MCP run the same handlers over the same database. An AI that creates a todo via todos_create and a browser that lists via GET /api/todos see the same rows. The insert shape validates creates; updates accept the same fields, all optional, keyed by id.

Databases & drivers #

createDb is driver-agnostic — every driver uses Drizzle's async API, so a defineResource written once runs unchanged across all of them:

await createDb('data.db')                                       // local SQLite (libSQL)
await createDb({ driver: 'libsql', url: process.env.TURSO_URL })  // Turso (edge)
await createDb({ driver: 'postgres', url: process.env.DATABASE_URL }) // Supabase / Neon
await createDb({ driver: 'pglite' })                             // embedded Postgres (tests)

SQLite and Turso share a dialect; Supabase, Neon, and PGlite are all Postgres. Edge targets can't run native SQLite — pair them with Turso or Supabase/Neon, whose drivers are fetch-based.

Migrations #

Write plain SQL files in db/migrations/. applyMigrations runs each file once in filename order, tracked in an _apex_migrations table — applied on boot in dev, or explicitly as a deploy step with apex migrate (idempotent).

db/migrations/0001_init.sql

CREATE TABLE IF NOT EXISTS todos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  text TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0
);

apex migrate                                   # default: data.db (sqlite)
apex migrate --driver postgres --url $DATABASE_URL

Beyond CRUD #

Need something a table can't express — a search, an aggregate, a domain action? Write a plain typed route with defineApexRoute right next to your resources. It's REST + MCP too, so your custom endpoints join the same AI-callable surface.