In this post I’ll go over how to install Drizzle for sqlite on a SvelteKit project. This post is based on blog by sveltekit.io namely, Drizzle and SvelteKit Integration - The Ultimate Stack

I’ve read through it and found that there are some parts which are not applicable for 2024. Also, the database they use is Postgresql which I feel is overkill for the majority of usecases.

For this tutorial, I’ll be using Drizzle, with Turso integration. Turso is based on libSQL, which is a fork of SQLite. I.e. we’ll be using Drizzle with SQLite database.

1. Create a Sveltekit Project

We’ll start by creating a SvelteKit project

npm create svelte@latest
# Options:
#   Create a new directory for this project (my-project)
#   Use the SvelteKit demo app
#   Use the typescript syntax
#   No additional options is fine

cd my-project

# Install dependencies
npm install

2. Install Drizzle

Then we’ll install Drizzle itself and the libSQL client

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

3. Create a Schema

We then have to create a schema to let Drizzle know the shape of our data.

// src/lib/server/schema.ts
import { sqliteTable,integer, text } from 'drizzle-orm/sqlite-core';

export const usersTable = sqliteTable('users', {
	id: integer('id').primaryKey(),
	fullName: text('full_name').notNull(),
  phone: text('phone',{length: 256}).notNull()
});

This is a table of users with id as the primary key and some data fields.

4. Add an Environment File

We will then add a .env file to store the location of our database.

# .env
DATABASE_URL="file:./dev.db"

5. Add Drizzle Configuration File

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
	schema: './src/lib/server/schema.ts',
	out: './drizzle',
	driver: 'turso',
	dialect: "sqlite",
	dbCredentials: {
		url: process.env.DATABASE_URL ? process.env.DATABASE_URL : "file:./dev.db"
	}
} satisfies Config;

This configuration sets drizzle to use the turso integration and points to the database at our environment file, my-project/dev.db

6. Generate the Migration

npx drizzle-kit generate

This generates the migration SQL file, but does not change the database yet.

7. Push Changes to the Database

Finally, we push the migration to the database file.

npx drizzle-kit push

This completes the database generation.

8. Use the Database in Your Code

After configuring the database, we can now access it in our code.

// src/lib/server/db.ts
import { createClient } from "@libsql/client";
import { drizzle } from "drizzle-orm/libsql";
import { env } from "$env/dynamic/private"

const client = createClient({ url: env.DATABASE_URL })

export const db = drizzle(client)

9. Use the Database in your page

Finally, you can load the data into your page.

// src/routes/+page.server.ts
import { db } from "$lib/server/db";
import { usersTable } from "$lib/server/schema";
import type { PageServerLoad } from "./$types";

export const load: PageServerLoad = async () => {
  // INSERT
  await db.insert(usersTable).values({
      fullName: "a",
      phone: "b"
  })
  
  // SELECT
  const users = await db.select().from(usersTable).all()
  console.log(users)
};

10. Test the Page

Start the vite server, and see the results

npm run dev

Go to http://localhost:5173 and see the output in your console

[
  { id: 1, fullName: 'a', phone: 'b' }
]

Results

For your reference, the results are in my github:

https://github.com/toftpokk/drizzle-sveltekit

References