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:

npm create cloudflare@latest d1-tutorial

using create-cloudflare version 2.22.0

╭ Create an application with Cloudflare Step 1 of 3
│
├ In which directory do you want to create your application?
│ dir ./d1-tutorial
│
├ What type of application do you want to create?
│ type "Hello World" Worker
│
├ Do you want to use TypeScript?
│ yes typescript
│
├ Copying template files
│ files copied to project directory
│
├ Updating name in `package.json`
│ updated `package.json`
│
├ Installing dependencies
│ installed via `npm install`
│
╰ Application created

cd ./d1-tutorial
npx wrangler d1 create test-db

✅ Successfully created DB 'test-db' in region ENAM
Created your new D1 database.

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "test-db"
database_id = "your-database-id"

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.

npm run cf-typegen

> d1-tutorial@0.0.0 cf-typegen
> wrangler types

 ⛅️ wrangler 3.65.0
-------------------

interface Env {
	DB: D1Database;
}

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:

return new Response(
  await env.DB.prepare("SELECT 1 + 1;")
    .first()
    .then((row) => JSON.stringify(row))
);

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:

npx wrangler d1 migrations create DB initial_schema

 ⛅️ wrangler 3.65.0
-------------------

✔ No migrations folder found. Set `migrations_dir` in wrangler.toml to choose a different path.
Ok to create /Users/dane/Projects/d1-tutorial/migrations? … yes
✅ Successfully created Migration '0001_initial_schema.sql'!

The migration is available for editing here
/Users/dane/Projects/d1-tutorial/migrations/0001_initial_schema.sql

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:

-- Migration number: 0001 	 2024-07-19T15:17:15.625Z

-- Create Authors table
CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR(100) NOT NULL,
    bio TEXT
);

-- Create Posts table
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    author_id INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);

-- Create Tags table
CREATE TABLE tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Create Posts_Tags junction table to implement many-to-many relationship between posts and tags
CREATE TABLE posts_tags (
    post_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

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:

npx wrangler d1 migrations apply DB --local

 ⛅️ wrangler 3.65.0 (update available 3.65.1)
-------------------------------------------------------

Migrations to be applied:
┌─────────────────────────┐
│ name                    │
├─────────────────────────┤
│ 0001_initial_schema.sql │
└─────────────────────────┘
✔ About to apply 1 migration(s)
Your database may not be available to serve requests during the migration, continue? … yes
🌀 Executing on local database DB (5440f1ab-dd2e-42d7-a7d4-16d855948517) from .wrangler/state/v3/d1:
🌀 To execute on your remote database, add a --remote flag to your wrangler command.
┌─────────────────────────┬────────┐
│ name                    │ status │
├─────────────────────────┼────────┤
│ 0001_initial_schema.sql │ ✅       │
└─────────────────────────┴────────┘

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.

export type Author = {
	id: number;
	name: string;
	bio: string | null;
};

export type Post = {
	id: number;
	author_id: number;
	title: string;
	content: string;
	published_at: string;
};

export type Tag = {
	id: number;
	name: string;
};

export type PostTag = {
	post_id: number;
	tag_id: number;
};

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:

-- Seed script for demo blog database

-- Insert authors
INSERT INTO authors (name, bio) VALUES
('Alice Smith', 'Alice is a freelance writer and photographer.'),
('Bob Johnson', 'Bob is a passionate travel blogger.'),
('Charlie Davis', 'Charlie is a tech enthusiast and gadget reviewer.');

-- Insert tags
INSERT INTO tags (name) VALUES
('Travel'),
('Technology'),
('Photography'),
('Lifestyle'),
('Food');

INSERT INTO posts (author_id, title, content) VALUES
(1, 'Exploring the Alps', 'Content about exploring the Alps...'),
(1, 'A Guide to Underwater Photography', 'Content about underwater photography...'),
(1, 'The Best Street Food Around the World', 'Content about street food...');

-- Insert posts by Bob Johnson
INSERT INTO posts (author_id, title, content) VALUES
(2, 'The Ultimate Guide to Backpacking in South America', 'Content about backpacking in South America...'),
(2, 'Discovering Hidden Gems in Europe', 'Content about hidden gems in Europe...'),
(2, 'How to Travel on a Budget', 'Content about budget travel...');

-- Insert posts by Charlie Davis
INSERT INTO posts (author_id, title, content) VALUES
(3, 'The Future of Smart Homes', 'Content about smart homes...'),
(3, 'Top 10 Gadgets for Tech Enthusiasts', 'Content about gadgets for tech enthusiasts...'),
(3, 'How to Build Your Own PC', 'Content about building your own PC...');

-- Associate posts with tags randomly
INSERT INTO posts_tags (post_id, tag_id) VALUES
(1, 1), (1, 3),
(2, 3), (2, 4),
(3, 4), (3, 5),
(4, 1), (4, 2),
(5, 1), (5, 4),
(6, 1), (6, 5),
(7, 2), (7, 3),
(8, 2), (8, 4),
(9, 2), (9, 5);

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.

DROP TABLE IF EXISTS posts_tags;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS d1_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.

npx wrangler d1 execute DB --local --file="./seeds/dev.sql"

 ⛅️ wrangler 3.65.0 (update available 3.67.1)
-------------------------------------------------------

🌀 Executing on local database DB (530df8ea-f4e0-4548-856d-be76f46e421c) from .wrangler/state/v3/d1:
🌀 To execute on your remote database, add a --remote flag to your wrangler command.

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.

npx wrangler d1 execute DB --local --command="select * from posts;"

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.