By Flavio Copes

How to create a D1 database, manage its schema with migrations, and query it from a Worker. SQLite, served from the edge.

~~~

Most apps need a real database. On Cloudflare, that’s D1.

D1 is a SQL database built on SQLite. You get tables, indexes, joins, transactions, the whole thing. It lives next to your Workers, so queries are fast, and there’s no connection string to manage or server to keep running.

If you know SQLite, you already know D1. Let’s build with it.

If you’re wondering what D1 costs at your scale, I built a free Workers cost estimator that covers Workers, KV, D1, R2 and Durable Objects.

Create a database

Create one with Wrangler:

npx wrangler d1 create my-app-db

This prints a block of config. Copy it into your wrangler.jsonc so your Worker can find the database:

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "my-app-db",
      "database_id": "the-id-it-printed"
    }
  ]
}

The binding is the important part. It’s the name you’ll use in code, here env.DB.

Set up your schema with migrations

You don’t create tables by hand in production. You write migrations: ordered SQL files that describe how your schema changes over time.

Create one:

npx wrangler d1 migrations create my-app-db create_posts

This makes a numbered .sql file in a migrations folder. Open it and write the SQL:

create table posts (
  id integer primary key autoincrement,
  title text not null,
  body text,
  created_at integer not null
);

Apply it to your local database while developing:

npx wrangler d1 migrations apply my-app-db --local

And to the real one when you deploy:

npx wrangler d1 migrations apply my-app-db --remote

Migrations run in order, and each runs once. So your local and production databases stay in sync.

Query from a Worker

Now the fun part. You query D1 through the env.DB binding.

To read rows, use prepare, then all():

export default {
  async fetch(request, env) {
    const { results } = await env.DB.prepare(
      'select * from posts order by created_at desc'
    ).all()

    return Response.json(results)
  },
}

results is a plain array of objects, one per row.

Use parameters, always

When a value comes from the user, never glue it into the SQL string. Use ? placeholders and bind:

const { results } = await env.DB.prepare(
  'select * from posts where id = ?'
).bind(postId).all()

This is how you avoid SQL injection. D1 handles the escaping for you.

Insert and update

For writes, use run():

await env.DB.prepare(
  'insert into posts (title, body, created_at) values (?, ?, ?)'
).bind('Hello', 'My first post', Date.now()).run()

When you only expect one row back, first() is handy:

const post = await env.DB.prepare(
  'select * from posts where id = ?'
).bind(postId).first()

first() returns the single object, or null if nothing matched.

Many statements at once

If you need to run several writes together, batch sends them in one round trip:

await env.DB.batch([
  env.DB.prepare('update accounts set balance = balance - ? where id = ?').bind(10, 'alice'),
  env.DB.prepare('update accounts set balance = balance + ? where id = ?').bind(10, 'bob'),
])

When D1 is the right call

D1 is great for the structured data at the heart of an app: users, posts, orders, settings. Anything you’d reach for SQL to query.

It’s not meant for huge blobs of files (that’s R2) or simple key lookups where you don’t need queries (that’s KV). I’ll cover both of those next.

For everything relational, D1 is my default. The full reference is in the D1 docs.

~~~

Related posts about cloudflare: