D1 database

D1 is a serverless SQLite database built into functions: the familiar tables, columns, indexes and SQL, living next to the function and reachable by name, with no connection strings. One function can have several databases.

For “put — read” data without relations, KV is simpler; for an object with its own state, see Durable Objects.

Declaration

A database is declared in wrangler.jsonc under d1_databases:

{
  "d1_databases": [
    { "binding": "DB", "database_name": "app-db", "database_id": "00000000-0000-0000-0000-000000000001" }
  ]
}

• binding — the variable name under which the database is visible in code: env.DB.
• database_name — the name you pass to the CLI commands.
• database_id — a unique database identifier (UUID). Each database has its own; it is set once and never changes.

Migrations

The schema is kept with migrations — numbered SQL files in the migrations/ directory. Each migration runs exactly once, in numeric order.

migrations/
├── 0001_users.sql
└── 0002_posts.sql

0001_users.sql:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

0002_posts.sql:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL
);

Apply new migrations from the project directory:

webshield apps d1 migrate --app api app-db

Here app-db is the database_name from the config. The command packs the project directory and applies the migrations the database has not seen yet. To list the applied ones:

webshield apps d1 migrations --app api app-db

The migration name (0001_users.sql) does not go into the database — only the content and the order matter. Do not edit an already-applied file: the edit will not be reverted, and the history will drift. For a change, add a new migration with the next number.

Queries from code

In code the database is available as env.<binding>. Queries go through prepare with parameters — this is safe: values are bound as data, not as part of the SQL.

export default {
  async fetch(request, env) {
    const url = new URL(request.url);

    // Create a user
    if (url.pathname === "/users" && request.method === "POST") {
      const { email } = await request.json();
      const { meta } = await env.DB
        .prepare("INSERT INTO users (email) VALUES (?1)")
        .bind(email)
        .run();
      return Response.json({ id: meta.last_row_id }, { status: 201 });
    }

    // List users
    if (url.pathname === "/users") {
      const { results } = await env.DB
        .prepare("SELECT id, email FROM users ORDER BY id")
        .all();
      return Response.json(results);
    }

    // One user by id
    const id = url.pathname.split("/")[2];
    const user = await env.DB
      .prepare("SELECT id, email FROM users WHERE id = ?1")
      .bind(id)
      .first();
    return user
      ? Response.json(user)
      : Response.json({ error: "not found" }, { status: 404 });
  },
};

Methods:

• prepare(sql).bind(...).run() — a query that changes data (INSERT, UPDATE, DELETE, CREATE). Returns meta with details such as last_row_id.
• prepare(sql).bind(...).all() — a selection; returns { results } — an array of rows.
• prepare(sql).bind(...).first() — the first row of a selection, or null.
• .raw() — columns as they are, without key names.

Parameters — always via ?1, ?2, … and .bind(...). Never build SQL by concatenating strings from the request: "SELECT * FROM users WHERE id = " + id lets SQL injection through.

SQL outside the code

One-off queries and data loading use the execute command, which does not require the query to live in a migration:

# A single query
webshield apps d1 execute --app api app-db --command "SELECT count(*) FROM users"

# Or SQL from a file (e.g. seed.sql)
webshield apps d1 execute --app api app-db --file seed.sql

seed.sql:

INSERT INTO users (email) VALUES
  ('anna@example.com'),
  ('boris@example.com');

One request is up to 100 KB. Load large data sets with migrations, not a single execute.

Which one to use

• Migrations — the schema: tables, indexes. They change rarely, apply in order and are tracked.
• execute — one-off operations: load starter data, inspect state, fix a row.
• prepare in code — everything the function does on visitor requests.

Details

• The function must already be deployed — the commands run on its nodes, next to the database.
• Migrations repeat on their own if the connection to the function breaks: they track what is applied, so a retry is safe. execute does not repeat — the query may already have run, so check the result and run it again if needed.
• History and output of all operations is on the Data tab.
• Return to a point in time also restores the D1 database: Data tab → Return data to a moment. More in Functions.