Add a database

Add a second SQLite database, tasks, to a slate created from the starter. You will declare it, create a table with a Kysely migration, generate TypeScript query types, and query it from the app. The starter’s existing notes database remains in place. Each database has a slug and a matching databases/<slug> directory. See Databases for how storage, resource ownership, and migrations fit together. Start pnpm dev from the checkout root before editing the declarations and keep it running while you work. The migration and type-generation commands use the running local storage, which persists in the checkout’s ignored .contextlayer/databases directory. Those commands read your current declarations; you will restart the app after generating types so its requests can use the new database too.

Declare the database

Add tasks under databases in public/slate.json. Preserve existing connections, databases, and secrets; this example shows the starter’s notes database alongside the new entry:

public/slate.json

{
  "connections": {},
  "databases": {
    "notes": { "mode": "owner" },
    "tasks": { "mode": "owner" }
  },
  "secrets": {}
}

The mode selects whose database the hosted slate uses. In owner mode the slate’s Owner has one database that every user shares. In viewer mode each user has their own database for this slate.An agent that needs this data must also declare tasks in its own agents/<name>/slate.json. Matching slugs must declare the same mode and select the same stored database when they resolve to the same Owner. This example uses owner mode, so the app and agent share the slate Owner’s tasks database.

Add its Kysely configuration

Create databases/tasks/ and add its native Kysely CLI configuration:

databases/tasks/kysely.config.ts

import { databaseDialect } from "@usecontextlayer/sdk/database"
import { defineConfig } from "kysely-ctl"

export default defineConfig({
	dialect: databaseDialect("tasks"),
	migrations: { migrationFolder: "./migrations" },
})

The configuration selects only the slug. ContextLayer supplies the connection behind it: local storage during development, the hosted database during deployment.

Create a migration

pnpm exec kysely --config databases/tasks/kysely.config.ts migrate make create_tasks

Edit the generated migration to create a tasks table with an integer ID and required text title. up() applies the change; an optional down() defines its reverse operation. ContextLayer does not run that reverse operation automatically on a failed deployment.

import type { Kysely } from "kysely"

export async function up(db: Kysely<unknown>) {
	await db.schema
		.createTable("tasks")
		.addColumn("id", "integer", (column) => column.primaryKey().autoIncrement().notNull())
		.addColumn("title", "text", (column) => column.notNull())
		.execute()
}

export async function down(db: Kysely<unknown>) {
	await db.schema.dropTable("tasks").execute()
}

Kysely owns migration naming, ordering and the record of applied migrations. pnpm exec kysely --help lists its other operations.

Apply it and generate the query types

pnpm exec kysely --config databases/tasks/kysely.config.ts migrate latest
pnpm db.codegen tasks

db.codegen writes databases/tasks/types.ts from the migrated local database. Applying migrations and generating types are separate operations: the migration creates the table, then generation describes that table for TypeScript. Do not edit the generated types by hand.The generated file includes a Tasks row type and a DB mapping for the table. This excerpt omits its generated helper definitions:

export interface Tasks {
  id: Generated<number>;
  title: string;
}

export interface DB {
  tasks: Tasks;
}

Kysely does not run a migration again once it has been applied, so change the schema with a new migration rather than by editing an applied one.

Restart the development server

Restart pnpm dev. The app picks up declarations when the server starts, and startup applies each declared database’s pending migrations before the app can query.

Query it from a loader or action

Import the generated DB type and ask the SDK for a Kysely instance of tasks. This loader excerpt demonstrates the query. Merge it into your route’s data flow; do not replace the starter route’s component or existing notes data with the excerpt alone:

app/routes/home.tsx

import { database, databasesContext } from "@usecontextlayer/sdk"
import type { DB } from "@/databases/tasks/types"
import type { Route } from "./+types/home"

export async function loader({ context }: Route.LoaderArgs) {
	const db = database<DB>(context.get(databasesContext), "tasks")
	return db.selectFrom("tasks").selectAll().orderBy("id", "desc").execute()
}

The "tasks" argument to database() selects the database; the "tasks" argument to selectFrom() selects the table inside it. A new table returns an empty array until rows are inserted.Mutations return Kysely’s affected-row counts and SQLite insert IDs; use .returning() to read the written rows. SQL transactions are not supported. The starter’s app/routes/home.tsx shows how a loader and a form action read and write the existing notes table.

Commit and push

Commit the declaration, Kysely configuration, migration, generated query types, and any app changes together, then push main. See Deploy.After deployment, the hosted slate has its own tasks database with the migrated schema. It does not contain rows from your local database.

What the push does

Pushing main builds the commit, runs its migrations with the native Kysely CLI in an isolated sandbox, then deploys it. The migrations run against the slate’s owner-mode databases and every existing member’s viewer-mode databases. When someone becomes a member, the same migrations run for their viewer-mode databases in the background.

A failed migration blocks the deployment. It is not retried automatically, and nothing is rolled back: SQL statements that ran before the failure can remain applied.

Migrations run while the previous deployment is still serving, so pushed migrations must stay compatible with it until the new deployment completes.

Getting started

Core concepts

How-to

Reference

Declare the database

Add its Kysely configuration

Create a migration

Apply it and generate the query types

Restart the development server

Query it from a loader or action

Commit and push

What the push does

Declare the database

Add its Kysely configuration

Create a migration

Apply it and generate the query types

Restart the development server

Query it from a loader or action

Commit and push

​What the push does

What the push does