Database

Kit stores data in SQLite and queries it through Kysely, a typed SQL query builder. One shared connection serves the whole process. During development, tests and CLI commands the connection comes from better-sqlite3 on Node; in production it comes from the engine’s own SQLite module. Your code uses the same ajo-kit/database API in both places.

Connect

Describe your tables once, connect at module load, and export a typed accessor. This mirrors the official starter’s src/database.ts.

import { connect, db as database, type Generated, type Selectable } from 'ajo-kit/database'
import { env } from 'ajo-kit/platform'

interface Notes {
  id: Generated<number>
  title: string
  body: string
  pinned: Generated<number>
  created: Generated<string>
}

export interface Database {
  notes: Notes
}

export type Note = Pick<Selectable<Notes>, 'id' | 'title' | 'created'>

connect(env('DATABASE_PATH') ?? './database.sqlite')

export const db = () => database<Database>()

Generated marks columns the database fills in, so inserts may omit them. env() reads the environment on both hosts. This module must never reach the browser, so add it to the client guard in vite.config.ts:

plugins: [...kit({ guard: [/\/src\/database/] })]

Query

Loaders read, actions write. Name the columns you need with select([...]) rather than selectAll(): the route payload is sent to the browser, and an explicit list keeps new or private columns from leaking into it. Bound lists with limit().

import type { Request } from 'ajo-kit'
import { db } from '../database'

export async function page(req: Request) {
  req.track?.('notes')
  const notes = await db()
    .selectFrom('notes')
    .select(['id', 'title', 'created'])
    .orderBy('id', 'desc')
    .limit(50)
    .execute()

  return { notes }
}

When one logical change takes several statements, run them in a transaction so they land together or not at all. Emit topics only after it commits, so live pages never reload data that might still roll back.

import type { ActionContext, Request, Response } from 'ajo-kit'
import { number, object, parse } from 'ajo-kit/validate'
import { db } from '../database'

const Pin = object({ id: number() })

export const actions = {
  async pin(req: Request, _res: Response, action: ActionContext) {
    const { id } = parse(Pin, req.body)

    await db().transaction().execute(async trx => {
      await trx.updateTable('notes').set({ pinned: 0 }).where('pinned', '=', 1).execute()
      await trx.updateTable('notes').set({ pinned: 1 }).where('id', '=', id).execute()
    })

    action.emit('notes')
    return { ok: true }
  },
}

Connection settings

Kit configures every connection when it opens. The two hosts differ in one durability setting.

Migrations

Schema changes live in numbered files under db/migrations. Each one exports up() and down().

1. Create the next file. Kit picks the number and turns the name into lowercase words joined by underscores.

   pnpm kit migrate create notes

2. Write both directions.

   import { sql, type Kysely } from 'ajo-kit/database'
   
   export async function up(db: Kysely<any>): Promise<void> {
     await db.schema
       .createTable('notes')
       .addColumn('id', 'integer', column => column.primaryKey())
       .addColumn('title', 'text', column => column.notNull())
       .addColumn('body', 'text', column => column.notNull().defaultTo(''))
       .addColumn('pinned', 'integer', column => column.notNull().defaultTo(0))
       .addColumn('created', 'text', column => column.notNull().defaultTo(sql`CURRENT_TIMESTAMP`))
       .execute()
   }
   
   export async function down(db: Kysely<any>): Promise<void> {
     await db.schema.dropTable('notes').execute()
   }

3. Apply it, and check what has run.

   pnpm kit migrate up
   pnpm kit migrate status

up, down and status use ./database.sqlite unless you pass -d or --database. They do not read DATABASE_PATH, so if your application uses another file, pass the same path: pnpm kit migrate up -d ./notes.sqlite.

• File names are a four-digit number and an underscore, then lowercase letters or digits in words joined by underscores, with a .ts, .js, .mts, .mjs, .cts or .cjs extension.
• The CLI runs TypeScript migrations with Node’s built-in type stripping, so they must use erasable syntax only: no enum, namespace or parameter properties.
• A gap in the numbering, a duplicate name, an invalid file name or a missing up() or down() stops the command before any migration runs.
• Treat a migration as immutable once it has run anywhere that matters. Change the schema by adding the next one.

Sources and identities

Kit combines your migrations with those of installed plugins. A plugin is an ajo-* package whose package.json has a kit.migrations folder; plugin discovery covers the rules. Each folder numbers its own files from 0001 without gaps, so a plugin and your application may both have a 0001_initial. The history stores qualified names:

All sources share one Kysely migrator and one history table. Pending migrations run in order of their qualified names, so on a fresh database plugin migrations run before yours, and your tables can reference theirs. A plugin may later add its next migration after one of yours has run. status fails when the history names a migration no source provides any longer, rather than guessing.

✓ plugin/ajo-kit-auth/0001_initial
✓ plugin/ajo-kit-auth/0002_passkeys
○ project/0001_notes

Seeds

kit seed fills a development database with sample data. It imports every .ts file in db/seeds in file-name order and calls its exported seed(db) with the Kysely instance. Like the migration commands, it takes -d and defaults to ./database.sqlite.

import type { Kysely } from 'ajo-kit/database'
import type { Database } from '../../src/database'

export async function seed(db: Kysely<Database>) {
  await db.deleteFrom('notes').execute()
  await db.insertInto('notes').values([
    { title: 'Groceries', body: 'Garlic, bread, olive oil.' },
    { title: 'Ideas', body: 'A journal that updates across tabs.' },
  ]).execute()
}

Seeds run on Node only and are not part of the production build. Import application types with import type, as above: a value import would run the connect() call in src/database.ts, which throws when it names a different file than -d.

Data on disk in production

The supported production topology is one engine process with one SQLite file on persistent local disk. The engine confines filesystem and SQLite access to the roots the application declares, so declare the data directory in package.json:

{
  "kit": {
    "engine": {
      "fs": { "roots": ["/ajo/data"] }
    }
  }
}

Create that writable directory in the runtime, then start the engine with AJO_DATA=/ajo/data and a relative DATABASE_PATH such as notes.sqlite. The file then lives at /ajo/data/notes.sqlite.

• The data path must sit inside a declared root. Setting AJO_DATA alone grants nothing.
• On the engine, DATABASE_PATH resolves beneath AJO_DATA. Absolute paths and .. segments are rejected, and a file database without a data root fails. :memory: is accepted as is.
• When the build uses the database, the artifact requires a data root, and the engine refuses to start without an available AJO_DATA.
• When the application uses the database, the engine connects to DATABASE_PATH (default ./database.sqlite) at startup and runs the compiled migrations before it runs the bootstrap hook or accepts a request. Read the same variable in your own connect() call, because connecting to a different path throws.

Build and configuration lists every kit.engine field, and Seal and run shows the full engine command.

Backups

• Live updatesEmit topics after commits so pages refresh.
• Build and configurationDeclare engine authority and plugins.
• Seal and runRun the sealed application with its data root.

Source of truth: ajo-kit README