SvelteKit with SQLite and Drizzle

Connecting to and interacting with a database are very common tasks when building web applications. This tutorial is a simple guide to connecting SvelteKit to a SQLite database using Drizzle ORM.

This will also serve as a reference for future content both on this blog, and for the  Full Stack SvelteKit course  when it comes to interacting with a database.

SQLite?

For quite some time, SQLite has been considered a “hobby” database. Part of this could be because of the “lite” in the name, or because it's just a file. However, SQLite remains one of the most popular databases, and is being used for a wide range of applications and use cases.

And today, with projects like  Litestream  or  Turso, it's possible to reliably use SQLite as a production database, with features like automated backups, replication, and high availability.

Drizzle

Drizzle is a framework and database agnostic ORM for TypeScript applications. It provides a simple and intuitive  SQL-like  API for interacting with databases, and has support for advanced concepts like transactions, batch operations and relations.

Its companion CLI project,  Drizzle Kit,  allows running SQL migrations and rapid prototyping.

With Drizzle, you write your database schema in TypeScript, and, with that source of truth, you get type safety and autocompletion for your queries.

Setting up a SvelteKit project

Let's get started by creating a new SvelteKit project with TypeScript.

pnpx create-svelte@latest sqlite-drizzle

We also want to use the Node adapter for SvelteKit, so we'll install that as well.

pnpm uninstall @sveltejs/adapter-auto
pnpm i @sveltejs/adapter-node -D

Let's now replace the default set auto adapter with the Node adapter in  svelte.config.js .

import adapter from "@sveltejs/adapter-auto";
import adapter from "@sveltejs/adapter-node";

// ...

Setting up the database

Drizzle abstracts the concept of interacting with databases through “adapters”, that allow to connect to different types of databases; from raw MySQL or PostgreSQL to managed database providers like Supabase or Turso.

In this tutorial, we will use the  BetterSqlite3  to connect to a SQLite database.

Let's start by installing the required dependencies.

pnpm add drizzle-orm better-sqlite3 dotenv
pnpm add -D drizzle-kit @types/better-sqlite3

We now need to create a database client, and connect to the database.

// src/lib/server/db/client.ts

import Database from "better-sqlite3";
import * as dotenv from "dotenv";
import { drizzle } from "drizzle-orm/better-sqlite3";

dotenv.config();

const sqlite = new Database(process.env.DB_URL);
const db = drizzle(sqlite);

export { db, sqlite };

Let's make sure the database URL is set in the  .env  file.

echo "DB_URL=src/lib/server/db/sqlite.db" >> .env

We also want to git ignore this file, so we'll add it to the  .gitignore  file.

echo "src/lib/server/db/sqlite.db" >> .gitignore

Now, we need to create a configuration file for Drizzle. This tells Drizzle where to find the database schema, and also where to store the migrations.

// src/lib/server/db/drizzle.config.ts

import * as dotenv from "dotenv";
import type { Config } from "drizzle-kit";

dotenv.config();

export default {
  schema: "./src/lib/server/db/schema.ts",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.DB_URL as string,
  },
  out: "./src/lib/server/db/migrations",
} satisfies Config;

Database schema

Now we are good to go. Let's create a simple database schema for a blog.

// src/lib/server/db/schema.ts

import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
import { generateId } from "./utils";

const timestamp = {
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
};

const posts = sqliteTable("posts", {
  id: text("id")
    .primaryKey()
    .notNull()
    .$defaultFn(() => generateId(15)),
  title: text("title", { length: 255 }).notNull(),
  slug: text("slug", { length: 255 }).notNull(),
  body: text("body"),
  tags: text("tags").$type<string[]>(),

  ...timestamp,
});

export { posts };

As you can see, Drizzle let you do the following things:

• Set default values for columns, using either  $defaultFn  (for functions) or  $default  (for values).

• Set the type of column, using either  $type  (for a specific type) or  $type()  (for a generic type).

The  generateId()  is a helper function I usually use to generate unique IDs for my tables. It's based on  this function  in the  oslo/crypto  package.

Add push and migration commands to package.json

Let's add a few commands to our  package.json  file to make it easier to run the migrations and push them to the database.

// package.json
// ...

"scripts": {
    // ...
    "generate-migrations:db": "drizzle-kit generate --config=src/lib/server/db/drizzle.config.ts",
    "push:db": "drizzle-kit push --config=src/lib/server/db/drizzle.config.ts",
    "migrate:db": "tsx src/lib/server/db/migrate.ts"
},

// ...

Migrations

Now that we have a schema up, we can take care of the migrations. Notice that in the Drizzle configuration file, we have set the  out  property to  ./src/lib/server/db/migrations . This is where Drizzle will look for migration files when running migrations, and where it will write the new migration files.

Drizzle Kit is very unopinionated about how you run the migrations files . It takes care of generating the SQL files for the migrations, and it's up to you to run them how you want. You could either apply those SQL files manually to your database, or create a script that runs them automatically.

Let's create a script that runs the migrations.

// src/lib/server/db/migrate.ts

import * as dotenv from "dotenv";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import { db } from "./client";
import config from "./drizzle.config";

dotenv.config();

migrate(db, {
  migrationsFolder: config.out,
});

Although Drizzle Kit is not opinionated about how you run migrations, it does provide a migrator that you can use to run them.

Now, we can generate the migrations file, and run them.

pnpm generate-migrations:db
pnpm migrate:db

The first command will generate the migrations files in the  ./src/lib/server/db/migrations  folder, and the second will apply those files to the database.

Here's what the generated migration file looks like:

CREATE TABLE `posts` (
	`id` text PRIMARY KEY NOT NULL,
	`title` text(255) NOT NULL,
	`slug` text(255) NOT NULL,
	`body` text,
	`tags` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);

As you can see, these are just plain SQL files that you can apply to your database if you like.

If we update the schema to make the  slug  column unique, and run generate the migrations again, a new migration file will be created.

// src/lib/server/db/schema.ts

// ...
slug: text("slug", { length: 255 }).notNull(),
slug: text("slug", { length: 255 }).notNull().unique()

// ...

CREATE UNIQUE INDEX `posts_slug_unique` ON `posts` (`slug`);

Basic CRUD operations

Everything is now set up on our database side. Let's create a few CRUD functions to see how Drizzle works.

// src/lib/server/db/posts.ts

import { eq } from "drizzle-orm";
import { db } from "./client";
import { posts } from "./schema";

const getPostById = (id: string) => {
  return db.select().from(posts).where(eq(posts.id, id)).get();
};

const getPostBySlug = (slug: string) => {
  return db.select().from(posts).where(eq(posts.slug, slug)).get();
};

const getPostTitleById = (id: string) => {
  return db
    .select({
      title: posts.title,
    })
    .from(posts)
    .where(eq(posts.id, id))
    .get();
};

const createNewPost = (data: typeof posts.$inferInsert) => {
  return db.insert(posts).values(data).returning().get();
};

const updatePost = (id: string, data: Partial<typeof posts.$inferInsert>) => {
  db.update(posts).set(data).where(eq(posts.id, id)).execute();
};

export {
  createNewPost,
  getPostById,
  getPostBySlug,
  getPostTitleById,
  updatePost,
};

A few interesting things to note here:

• The select and insertion functions use the table schema to determine where the operations should be performed.

• When you just run  db.select().from(posts).where(eq(posts.id, id))  what's returned is an array of posts objects. The  get()  method will actually return the first object in that array or undefined if the array is empty. (This method is not available on all database adapters)

• You can select which fields you want to return by passing them as an object to the  select()  method.

• With the  returning()  method, you can return the newly created object from the database.

Wrapping up

Connecting SvelteKit to a SQLite database using Drizzle ORM offers a powerful yet simple approach to managing your data layer. SQLite, often underrated as a “hobby” database, proves its versatility and reliability, especially with modern advancements like Litestream and Turso enhancing its production capabilities.

Drizzle ORM brings a TypeScript-first approach, ensuring type safety and autocompletion, which streamlines the development process and reduces potential errors.

This tutorial covered the essentials—from setting up your SvelteKit project and configuring the Node adapter, to integrating Drizzle ORM with SQLite, and finally, performing basic CRUD operations.

Stay tuned for more advanced topics and deeper dives into optimizing your SvelteKit applications with various database solutions.

Additionally, don't miss the guide on email and password authentication with SvelteKit to secure your applications.

Happy coding!