> ## Documentation Index
> Fetch the complete documentation index at: https://docs.usecontextlayer.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Add a database

> Declare a SQLite database, author its migrations with the Kysely CLI, generate its query types, and query it from a loader or action.

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](https://docs.usecontextlayer.com/core-concepts/resources/databases.md) 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.

## 1. 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:

```json public/slate.json theme={null}
{
  "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.

## 2. Add its Kysely configuration

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

```ts databases/tasks/kysely.config.ts theme={null}
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.

## 3. Create a migration

```sh theme={null}
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.

```ts theme={null}
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.

## 4. Apply it and generate the query types

```sh theme={null}
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:

```ts theme={null}
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.

## 5. 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.

## 6. 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:

```tsx app/routes/home.tsx theme={null}
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.

## 7. Commit and push

Commit the declaration, Kysely configuration, migration, generated query types, and any app changes together, then push `main`. See [Deploy](https://docs.usecontextlayer.com/how-to/deploy.md).

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.

> [!WARNING]
>
> 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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.