Skip to content
Cloudflare Docs

D1 Database

To interact with your D1 database from your Worker, you need to access it through the environment bindings provided to the Worker (env).

async fetch(request, env) {
  // D1 database is 'env.DB', where "DB" is the binding name from the Wrangler.toml file.
}

A D1 binding has the type D1Database, and supports a number of methods, as listed below.

Methods

prepare()

Prepares a query statement to be later executed.

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);

Parameters

  • query: String Required
    • The SQL query you wish to execute on the database.

Return values

Guidance

You can use the bind method to dynamically bind a value into the query statement, as shown below.

  • Example of a static statement without using bind:

    const stmt = db
      .prepare("SELECT * FROM Customers WHERE CompanyName = Alfreds Futterkiste AND CustomerId = 1")

  • Example of an ordered statement using bind:

    const stmt = db
      .prepare("SELECT * FROM Customers WHERE CompanyName = ? AND CustomerId = ?")
      .bind("Alfreds Futterkiste", 1);

Refer to the bind method documentation for more information.

batch()

Sends multiple SQL statements inside a single call to the database. This can have a huge performance impact as it reduces latency from network round trips to D1. D1 operates in auto-commit. Our implementation guarantees that each statement in the list will execute and commit, sequentially, non-concurrently.

Batched statements are SQL transactions ↗. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

To send batch statements, provide D1Database::batch a list of prepared statements and get the results in the same order.

const companyName1 = `Bs Beverages`;
const companyName2 = `Around the Horn`;
const stmt = env.DB.prepare(`SELECT * FROM Customers WHERE CompanyName = ?`);
const batchResult = await env.DB.batch([
  stmt.bind(companyName1),
  stmt.bind(companyName2)
]);

Parameters

Return values

  • results: Array
    • An array of D1Result objects containing the results of the D1Database::prepare statements. Each object is in the array position corresponding to the array position of the initial D1Database::prepare statement within the statements.
    • Refer to D1Result for more information about this object.

Example of return values

 
const companyName1 = `Bs Beverages`;
const companyName2 = `Around the Horn`;
const stmt = await env.DB.batch([
  env.DB.prepare(`SELECT * FROM Customers WHERE CompanyName = ?`).bind(companyName1),
  env.DB.prepare(`SELECT * FROM Customers WHERE CompanyName = ?`).bind(companyName2)
]);
return Response.json(stmt)
[
  {
    "success": true,
    "meta": {
      "served_by": "miniflare.db",
      "duration": 0,
      "changes": 0,
      "last_row_id": 0,
      "changed_db": false,
      "size_after": 8192,
      "rows_read": 4,
      "rows_written": 0
    },
    "results": [
      {
        "CustomerId": 11,
        "CompanyName": "Bs Beverages",
        "ContactName": "Victoria Ashworth"
      },
      {
        "CustomerId": 13,
        "CompanyName": "Bs Beverages",
        "ContactName": "Random Name"
      }
    ]
  },
  {
    "success": true,
    "meta": {
      "served_by": "miniflare.db",
      "duration": 0,
      "changes": 0,
      "last_row_id": 0,
      "changed_db": false,
      "size_after": 8192,
      "rows_read": 4,
      "rows_written": 0
    },
    "results": [
      {
        "CustomerId": 4,
        "CompanyName": "Around the Horn",
        "ContactName": "Thomas Hardy"
      }
    ]
  }
]
console.log(stmt[1].results);
[
  {
    "CustomerId": 4,
    "CompanyName": "Around the Horn",
    "ContactName": "Thomas Hardy"
  }
]

Guidance

  • You can construct batches reusing the same prepared statement:

      const companyName1 = `Bs Beverages`;
      const companyName2 = `Around the Horn`;
      const stmt = env.DB.prepare(`SELECT * FROM Customers WHERE CompanyName = ?`);
      const batchResult = await env.DB.batch([
        stmt.bind(companyName1),
        stmt.bind(companyName2)
      ]);
      return Response.json(batchResult);

exec()

Executes one or more queries directly without prepared statements or parameter bindings.

const returnValue = await env.DB.exec(`SELECT * FROM Customers WHERE CompanyName = "Bs Beverages"`);

Parameters

  • query: String Required
    • The SQL query statement without parameter binding.

Return values

  • D1ExecResult: Object
    • The count property contains the number of executed queries.
    • The duration property contains the duration of operation in milliseconds.

Example of return values

 
const returnValue = await env.DB.exec(`SELECT * FROM Customers WHERE CompanyName = "Bs Beverages"`);
return Response.json(returnValue);
{
  "count": 1,
  "duration": 1
}

Guidance

  • If an error occurs, an exception is thrown with the query and error messages, execution stops and further statements are not executed. Refer to Errors to learn more.
  • This method can have poorer performance (prepared statements can be reused in some cases) and, more importantly, is less safe.
  • Only use this method for maintenance and one-shot tasks (for example, migration jobs).
  • The input can be one or multiple queries separated by \n.

dump

Dumps the entire D1 database to an SQLite compatible file inside an ArrayBuffer.

const dump = await db.dump();
return new Response(dump, {
  status: 200,
  headers: {
    "Content-Type": "application/octet-stream",
  },
});

Parameters

  • None.

Return values

  • None.