SvelteKit with SQLite and Drizzle
By Justin Ahinon.
Table of Contents
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.
Loading code block
We also want to use the Node adapter for SvelteKit, so we'll install that as well.
Loading code block
Let's now replace the default set auto adapter with the Node adapter in svelte.config.js
.
Loading code block
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.
Loading code block
We now need to create a database client, and connect to the database.
Loading code block
Let's make sure the database URL is set in the .env
file.
Loading code block
We also want to git ignore this file, so we'll add it to the .gitignore
file.
Loading code block
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.
Loading code block
Database schema
Now we are good to go. Let's create a simple database schema for a blog.
Loading code block
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.
Loading code block
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.
Loading code block
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.
Loading code block
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:
Loading code block
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.
Loading code block
Loading code block
Basic CRUD operations
Everything is now set up on our database side. Let's create a few CRUD functions to see how Drizzle works.
Loading code block
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. Theget()
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!
Stay Updated with the Full Stack SvelteKit course
Want to be the first to know when the Full Stack SvelteKit course launches? Sign up now to receive updates, exclusive content, and early access!