D1 SQLite: Schema, migrations and seeds
I’ve written posts about some of the popular ORM’s in TypeScript and covered their pros and cons. Prisma is probably the most well known and Drizzle is a really popular up and comer. I like and use ORM’s in most of my projects but there’s also a camp of folks who believe they shouldn’t be used.
I started a small Cloudflare Workers project recently and decided to try using their D1 SQLite database without adding any ORM. This is the first post in a 2 part series where we’ll explore what this experience is like using only the driver and utilities made available in the Wrangler CLI.
Introduction
If you’re unfamiliar with Cloudflare D1 - it’s a distributed SQLite database for the Cloudflare Workers platform. Workers are lightweight serverless functions/compute distributed across a global network. The platform includes services and API’s like D1 that provide extended capabilities to your Workers.
At the time of this writing, there are only two ways to interact with a D1 database that I’m aware of.
* From a Worker using the D1 Client API
* D1 HTTP API
In this 2 part series, we will create a simple Workers project and use the D1 Client API to build out our queries.
Getting Started
For this tutorial, we’ll create a simple Cloudflare Worker project and treat it like a simple node script/http server to do our experiments.
The first step is initializing a new Cloudflare Worker and D1 database:
`
`
We need to take our binding and add it to our project wrangler.toml configuration. Once our binding is added we can re-generate the types for our Worker project.
`
We now have our DB binding added to our project Env types.
Let’s add a simple query to our worker script to make sure our database is setup and working:
`
Start the development server with npm run dev and access the server at http://localhost:8787 . When the page loads we should see a successful result {"1 + 1":2} .
We now have a working SQLite database available.
Creating a schema
Since we’re not using an ORM with some kind of DSL to define a schema for our database, we’re going to do things the old fashioned way. “Schema” will just refer to the data model that we create for our database. We’ll define it using SQL and the D1 migrations utility.
Let’s create a migration to define our initial schema:
`
For our demo purposes we will build out a simple blog application database. It includes posts, authors, and tags to include some relational data.
We need to write the SQL in our migration to create all the tables and columns that we need:
`
The SQL above defines our tables, columns, and their relations with foreign keys. It also includes a join table for posts and tags to represent a many-to-many relationship.
If you don’t have a lot of experience writing SQL queries it might look a little bit intimidating at first. Once you take some time to learn it it’s actually pretty nice. DataLemur is a pretty great resource for learning SQL.
If you need help with a specific query, Copilot and GPT are quite good at generating SQL queries. Just make sure you take some time to try to understand them and check for any potential issues.
After completing a migration script it needs to be applied:
`
I added the --local flag so that we’re working against a local database for now.
Typing our Schema
One of the downsides of our ORMless approach is we don’t get TypeScript types out of the box. In a smaller project, I think the easiest approach is just to manage your own types. It’s not hard, you can even have GPT help if you want.
If managing type definitions for your schema is not acceptable for your project or use case you can look for a code generation tool or switch to an ORM / toolset that provides types.
For this example I created some basic types to map to our schema so that we can get help from the lsp when working with our queries.
`
Seeding development data
Outside of our migrations, we can write SQL scripts and execute them against our D1 SQLite database using wrangler. To start we can create a simple seeds/dev.sql script to load some initial development seed data into our local database. Another example might be a reset.sql that drops all of our tables so we can easily reset our database during development as we rework the schema or run other experiments.
Since our database is using auto incrementing integer ID’s, we can know up front what the ID’s for the rows we are creating are since our database is initially empty. This can be a bit tricky if you’re using randomly generated ID’s. In that case you would probably want to write a script that can collect ID’s of newly created records and use them for creating related records. Here we are just passing the integer ID directly in our SQL script. As an example, we know up front that the author Alice Smith will have the id 1, Bob Johnson 2, and so on.
Post_tags looks a little bit crazy since it’s just a join table. Each row is just a post_id and a tag_id. (1, 1), (1 2), etc.
Here’s the code for a dev seed script:
`
Here’s the code for a reset script - it’s important to remember to drop the migrations table in your reset so you can apply your migrations.
`
Using the wrangler CLI we can execute our script files against our local development and remote d1 database instances.
Since we have already applied our migrations to our local database, we can use our dev.sql seed script to load some data into our db.
`
The Wrangler output is pretty helpful - it lets us know to add the --remote flag to run against our remote instance.
We can also use execute to run commands against our database. Lets run a select to look at the data added to our posts table.
`
This command should output a table showing the columns of our db and the 7 rows we added from the dev seed script.
Summary
Using wrangler and the Cloudflare D1 platform we’ve already gotten pretty far without an ORM or any other additional tooling. We have a simple but effective migrations system in place and some initial scripts for easily seeding and resetting our databases.
There are also some other really great things built-in to the D1 platform like time travel and backups. I definitely recommend at least skimming through the documentation at some point.
In the next post we will start interacting with our database and sample data using the D1 Client API....
