General

D1 SQLite: Schema, migrations and seeds

Published Aug 30, 2024

Updated Aug 30, 2024

5 min read

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.

This Dot is a consultancy dedicated to guiding companies through their modernization and digital transformation journeys. Specializing in replatforming, modernizing, and launching new initiatives, we stand out by taking true ownership of your engineering projects.

We love helping teams with projects that have missed their deadlines or helping keep your strategic digital initiatives on course. Check out our case studies and our clients that trust us with their engineering.

About the author(s)

Dane Grant
JavaScript developer building things on web, native, and server environments. Free time filled with reading, cooking, dog, sports, comedy, and music.
@danecando @danecando

An example-based guide to CSS Cascade Layers

CSS is actually good now! If you’ve been a web developer for a while, you’ll know this hasn’t always been the case. Over the past few years, a lot of really amazing features have been added that now support all the major browsers. Cascading and selector specificity have always been a pain point when writing stylesheets. CSS cascade layers is a new feature that provides us with a lot more power and flexibility for tackling this problem. We no longer need to resort to tricky specificity hacks or order-of-appearance magic. Cascade layers are really easy to get started with. I think the best way to understand how and when they are useful is by walking through some practical examples. In this post, we’ll cover: * What CSS cascade layers are and how they work * Real-world examples of using layers to manage style priorities * How Tailwind CSS leverages cascade layers What are CSS Cascade Layers? Imagine CSS cascade layers as drawers in a filing cabinet, each holding a set of styles. The drawer at the top represents the highest priority, so when you open the cabinet, you first access the styles in that drawer. If a style isn't found there, you move down to the next drawer until you find what you need. Traditionally, CSS styles cascade by specificity (i.e., more specific selectors win) and source order (styles declared later in the file override earlier ones). Cascade layers add a new, structured way to manage styles within a single origin—giving you control over which layer takes precedence without worrying about specificity. This is useful when you need to control the order of styles from different sources, like: * Resets (e.g., Normalize) * Third-party libraries (e.g., Tailwind CSS) * Themes and overrides You define cascade layers using the @layer rule, assigning styles to a specific layer. The order in which layers are defined determines their priority in the cascade. Styles in later layers override those in earlier layers, regardless of specificity or order within the file. Here’s a quick example: ` In this example, since the theme layer comes after base, it overrides the paragraph text color to dark blue—even though both declarations have the same specificity. How Do CSS Layers Work? Cascade layers allow you to assign rules to specific named layers, and then control the order of those layers. This means that: * Layers declared later take priority over earlier ones. * You don’t need to increase selector specificity to override styles from another layer—just place it in a higher-priority layer. * Styles outside of any layer will always take precedence over layered styles unless explicitly ordered. Let’s break it down with a more detailed example. ` In this example: * The unlayered audio rule takes precedence because it’s not part of the reset layer, even though the audio[controls] rule has higher specificity. * Without the cascade layers feature, specificity and order-of-appearance would normally decide the winner, but now, we have clear control by defining styles in or outside of a layer. Use Case: Overriding Styles with Layers Cascade layers become especially useful when working with frameworks and third-party libraries. Say you’re using a CSS framework that defines a keyframe animation, but you want to override it in your custom styles. Normally, you might have to rely on specificity or carefully place your custom rules at the end. With layers, this is simplified: ` There’s some new syntax in this example. Multiple layers can be defined at once. This declares up front the order of the layers. With the first line defined, we could even switch the order of the framework and custom layers to achieve the same result. Here, the custom layer comes after framework, so the translate animation takes precedence, no matter where these rules appear in the file. Cascade Layers in Tailwind CSS Tailwind CSS, a utility-first CSS framework, uses cascade layers starting with version 3. Tailwind organizes its layers in a way that gives you flexibility and control over third-party utilities, customizations, and overrides. In Tailwind, the framework styles are divided into distinct layers like base, components, and utilities. These layers can be reordered or combined with your custom layers. Here's an example: ` Tailwind assigns these layers in a way that utilities take precedence over components, and components override base styles. You can use Tailwind’s @layer directive to extend or override any of these layers with your custom rules. For example, if you want to add a custom button style that overrides Tailwind’s built-in btn component, you can do it like this: ` Practical Example: Layering Resets and Overrides Let’s say you’re building a design system with both Tailwind and your own custom styles. You want a reset layer, some basic framework styles, and custom overrides. ` In this setup: * The reset layer applies basic resets (like box-sizing). * The framework layer provides default styles for elements like paragraphs. * Your custom layer overrides the paragraph color to black. By controlling the layer order, you ensure that your custom styles override both the framework and reset layers, without messing with specificity. Conclusion CSS cascade layers are a powerful tool that helps you organize your styles in a way that’s scalable, easy to manage, and doesn’t rely on specificity hacks or the appearance order of rules. When used with frameworks like Tailwind CSS, you can create clean, structured styles that are easy to override and customize, giving you full control of your project’s styling hierarchy. It really shines for managing complex projects and integrating with third-party CSS libraries....

Jan 22, 2025

4 mins

CSS

The Future of Dates in JavaScript: Introducing Temporal

The Future of Dates in JavaScript: Introducing Temporal What is Temporaal? Temporal is a proposal currently at stage 3 of the TC39 process. It's expected to revolutionize how we handle dates in JavaScript, which has always been a challenging aspect of the language. But what does it mean that it's at stage 3 of the process? * The specification is complete * It has been reviewed * It's unlikely to change significantly at this point Key Features of Temporal Temporal introduces a new global object with a fresh API. Here are some important things to know about Temporal: 1. All Temporal objects are immutable 2. They're represented in local calendar systems, but can be converted 3. Time values use 24-hour clocks 4. Leap seconds aren't represented Why Do We Need Temporal? The current Date object in JavaScript has several limitations: * No support for time zones other than the user's local time and UTC * Date objects can be mutated * Unpredictable behavior * No support for calendars other than Gregorian * Daylight savings time issues While some of these have workarounds, not all can be fixed with the current Date implementation. Let's see some useful examples where Temporal will improve our lives: Some Examples Creating a day without a time zone is impossible using Date, it also adds time beyond the date. Temporal introduces PlainDate to overcome this. ` But what if we want to add timezone information? Then we have ZonedDateTime for this purpose. The timezone must be added in this case, as it also allows a lot of flexibility when creating dates. ` Temporal is very useful when manipulating and displaying the dates in different time zones. ` Let's try some more things that are currently difficult or lead to unexpected behavior using the Date object. Operations like adding days or minutes can lead to inconsistent results. However, Temporal makes these operations easier and consistent. ` Another interesting feature of Temporal is the concept of Duration, which is the difference between two time points. We can use these durations, along with dates, for arithmetic operations involving dates and times. Note that Durations are serialized using the ISO 8601 duration format ` Temporal Objects We've already seen some of the objects that Temporal exposes. Here's a more comprehensive list. * Temporal * Temporal.Duration` * Temporal.Instant * Temporal.Now * Temporal.PlainDate * Temporal.PlainDateTime * Temporal.PlainMonthDay * Temporal.PlainTime * Temporal.PlainYearMonth * Temporal.ZonedDateTime Try Temporal Today If you want to test Temporal now, there's a polyfill available. You can install it using: ` Note that this doesn't install a global Temporal object as expected in the final release, but it provides most of the Temporal implementation for testing purposes. Conclusion Working with dates in JavaScript has always been a bit of a mess. Between weird quirks in the Date object, juggling time zones, and trying to do simple things like “add a day,” it’s way too easy to introduce bugs. Temporal is finally fixing that. It gives us a clear, consistent, and powerful way to work with dates and times. If you’ve ever struggled with JavaScript dates (and who hasn’t?), Temporal is definitely worth checking out....

Jul 25, 2025

2 mins

JavaScript

Let's innovate together!

We're ready to be your trusted technical partners in your digital innovation journey.

Whether it's modernization or custom software solutions, our team of experts can guide you through best practices and how to build scalable, performant software that lasts.

D1 SQLite: Schema, migrations and seeds

Introduction

Getting Started

Creating a schema

Typing our Schema

Seeding development data

Summary

Dane Grant

You might also like

An example-based guide to CSS Cascade Layers

The Future of Dates in JavaScript: Introducing Temporal

Let's innovate together!

You might also like

An example-based guide to CSS Cascade Layers

The Future of Dates in JavaScript: Introducing Temporal