Use Drizzle with SQLite in SvelteKit

Drizzle is a TypeScript ORM. You describe tables in TypeScript. Queries against those tables get their types from the schema. Below we connect it to SQLite in a SvelteKit app and build a notes page.

SQLite is a file on disk. You do not run a database server for local work. The query shapes match Drizzle with Postgres. The driver and the schema helpers are different.

The query has to run on the server. You want adapter-node, adapter-vercel, or anything else that runs server code. adapter-static has no server, so nothing can open the database file. load and form actions already run on the server. Put the query there. That is the full-stack part.

The Svelte CLI can scaffold the connection files:

npx sv add drizzle="database:sqlite+client:libsql"

libsql talks SQLite. A local file: URL works on your laptop. Later you can point the same client at Turso with a libsql:// URL. The CLI also offers better-sqlite3. That one is a native Node module with a sync API. Use it if you self-host on Node and the file stays on that machine. Use libsql if you might move to Turso, or if you are tired of rebuilding native bindings when the deploy OS differs from your laptop.

If you already ran the CLI, skip to the schema and the page. Otherwise create the files below by hand. You need a SvelteKit app first. npx sv create will give you one.

We will end up with these files:

drizzle.config.ts
local.db
src/lib/server/db/
├── index.ts
└── schema.ts
src/routes/notes/
├── +page.svelte
└── +page.server.ts

local.db appears after the first push. You also need a DATABASE_URL in .env.

Step 1: packages and env

Install Drizzle ORM, the @libsql/client driver, and Drizzle Kit. Add dotenv so Drizzle Kit can read .env:

npm install drizzle-orm @libsql/client
npm install -D drizzle-kit dotenv

Put the file URL in .env at the project root. Vite already loads this file for SvelteKit. Drizzle Kit does not use Vite, so it needs dotenv.

DATABASE_URL=file:local.db

The file: prefix is required by libsql. The path is relative to the process working directory, which is the project root when you run vite or drizzle-kit.

Keep .env out of git. Copy the same key into .env.example with an empty value so the next person knows it exists. Add local.db and local.db-* to .gitignore. Committing a laptop database is a mess you do not need.

Add these next to your existing package.json scripts:

"db:push": "drizzle-kit push",
"db:studio": "drizzle-kit studio"

Step 2: the schema

Create src/lib/server/db/schema.ts. Drizzle Kit turns this file into SQL. Your queries import the same objects. Rename a column and the next db.select() does not compile. You find out before a request hits the file.

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

export const notes = sqliteTable('notes', {
  id: integer().primaryKey({ autoIncrement: true }),
  title: text().notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date())
});

sqliteTable is the SQLite helper. Postgres uses pgTable. autoIncrement: true makes SQLite fill in id on insert. mode: 'timestamp' stores a Unix time as an integer and gives you a Date in TypeScript. The TypeScript key is createdAt. The database column is created_at because of the string argument.

Anything under $lib/server is server only. If a .svelte file imports this module, the build fails. The schema and the connection string next to it stay out of the client bundle.

Step 3: drizzle-kit config

Create drizzle.config.ts at the project root. This file is for Drizzle Kit. It is not part of the SvelteKit app, so it cannot use $env/static/private. That alias only exists inside Vite.

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL is not set');
}

export default defineConfig({
  schema: './src/lib/server/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  dbCredentials: {
    url: process.env.DATABASE_URL
  }
});

out is where SQL migration files go if you later run drizzle-kit generate. For this tutorial we will push the schema straight to the database.

Step 4: the database client

Create src/lib/server/db/index.ts. Read the URL through $env/static/private so client code cannot import it.

import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';
import { DATABASE_URL } from '$env/static/private';
import * as schema from './schema';

if (!DATABASE_URL) {
  throw new Error('DATABASE_URL is not set');
}

const client = createClient({ url: DATABASE_URL });
export const db = drizzle({ client, schema });

Create the client once at module load. Do not create it inside load or an action. A new client on every request opens another handle on the same file for no gain.

Passing schema into drizzle() is optional for db.select() and db.insert(). You need it later if you use db.query.

Step 5: create the table

With DATABASE_URL set:

npm run db:push

Drizzle Kit creates local.db if it is missing, diffs schema.ts against that file, and applies the difference. Fine on a laptop. Do not do this to a shared production database. Generate SQL with drizzle-kit generate and apply it with drizzle-kit migrate so every environment runs the same files.

npm run db:studio opens Drizzle Studio in the browser if you want to inspect rows without writing SQL.

Step 6: a notes page

Create src/routes/notes/+page.server.ts:

import { fail } from '@sveltejs/kit';
import { desc, eq } from 'drizzle-orm';
import { db } from '$lib/server/db';
import { notes } from '$lib/server/db/schema';
import type { Actions, PageServerLoad } from './$types';

export const load: PageServerLoad = async () => {
  const rows = await db.select().from(notes).orderBy(desc(notes.createdAt));
  return { notes: rows };
};

export const actions: Actions = {
  default: async ({ request }) => {
    const data = await request.formData();
    const title = String(data.get('title') ?? '').trim();
    if (!title) {
      return fail(400, { message: 'Title is required' });
    }
    await db.insert(notes).values({ title });
    return { message: 'Saved.' };
  },
  delete: async ({ request }) => {
    const data = await request.formData();
    const id = Number(data.get('id'));
    if (!Number.isInteger(id) || id < 1) {
      return fail(400, { message: 'Invalid note' });
    }
    await db.delete(notes).where(eq(notes.id, id));
  }
};

load selects every note, newest first. The default action inserts. delete removes one row. eq and desc come from drizzle-orm, not from the driver. After a successful action, SvelteKit reruns load. The list updates. You do not query again in the action.

Create src/routes/notes/+page.svelte:

<script lang="ts">
  import { enhance } from '$app/forms';
  import type { ActionData, PageData } from './$types';

  let { data, form }: { data: PageData; form: ActionData } = $props();
</script>

<h1>Notes</h1>

<form method="POST" use:enhance>
  <label>
    Title
    <input name="title" required />
  </label>
  <button type="submit">Add</button>
</form>

{#if form?.message}
  <p>{form.message}</p>
{/if}

<ul>
  {#each data.notes as note (note.id)}
    <li>
      {note.title}
      <form method="POST" action="?/delete" use:enhance>
        <input type="hidden" name="id" value={note.id} />
        <button type="submit">Delete</button>
      </form>
    </li>
  {/each}
</ul>

action="?/delete" hits the named action. The add form has no action, so it hits default. use:enhance turns the submit into a fetch. The forms still work with JavaScript off.

Open /notes, type a title, and submit. You should see the row under the form. Delete should remove it.

Things that trip people up

Do not import $lib/server/db from a .svelte file or from +page.ts. +page.ts runs in the browser on navigation. Put database code in +page.server.ts, +server.ts, hooks.server.ts, or other $lib/server modules.

If you put DATABASE_URL in $env/static/public or any PUBLIC_ variable, Vite compiles it into the client. Private env vars belong in $env/static/private.

A file:local.db path only works where the process can write and keep that file. On Vercel and most serverless hosts the filesystem is ephemeral or read-only. The write fails, or the file vanishes on the next deploy. Point libsql at Turso with a libsql:// URL and an auth token. Or use Postgres with a pooler.

SQLite allows one writer at a time. A small app on adapter-node is fine. Heavy concurrent writes belong on Postgres.

Pushing schema from a laptop onto a shared database leaves you with no migration history. Use generate and migrate once more than one environment needs the same change.

If you prerender /notes, load runs at build time against whatever local.db the build machine has. The HTML matches that snapshot. A live list needs a server at request time.

Anyone who can load /notes can POST to it. If notes belong to a user, check locals.user in both load and the actions the same way HttpOnly cookie auth does.

What to add next

The cookie auth example stores sessions in a Map. That empties on restart. Replace it with a sessions table. Columns are id, user_id, and expires_at. Use the same db client.

If you upload files or send them to S3, store the object key and owner in a table instead of a JSON file. Serving those files behind auth is then a select plus the same session check.

For a shared or serverless deploy, keep libsql and point DATABASE_URL at Turso. If you need many concurrent writers, switch the dialect to Postgres. The select / insert / delete calls stay almost the same.