Workers Binding API

You can execute SQL queries on your D1 database from a Worker using the Worker Binding API. To do this, you can perform the following steps:

Bind the D1 Database.
Prepare a statement.
Run the prepared statement.
Analyze the return object (if necessary).

Refer to the relevant sections for the API documentation.

TypeScript support

D1 Worker Bindings API is fully-typed via the runtime types generated by running wrangler types package, and also supports generic types as part of its TypeScript API. A generic type allows you to provide an optional type parameter so that a function understands the type of the data it is handling.

When using the query statement methods D1PreparedStatement::run, D1PreparedStatement::raw and D1PreparedStatement::first, you can provide a type representing each database row. D1's API will return the result object with the correct type.

For example, providing an OrderRow type as a type parameter to D1PreparedStatement::run will return a typed Array<OrderRow> object instead of the default Record<string, unknown> type:

// Row definition
type OrderRow = {
	Id: string;
	CustomerName: string;
	OrderDate: number;
};

// Elsewhere in your application
// env.MY_DB is the D1 database binding from your Wrangler configuration file
const result = await env.MY_DB.prepare(
	"SELECT Id, CustomerName, OrderDate FROM [Order] ORDER BY ShippedDate DESC LIMIT 100",
).run<OrderRow>();

Type conversion

D1 automatically converts supported JavaScript (including TypeScript) types passed as parameters via the Workers Binding API to their associated D1 types ¹. This conversion is permanent and one-way only. This means that when reading the written values back in your code, you will get the converted values rather than the originally inserted values.

Note

We recommend using STRICT tables in your SQL schema to avoid issues with mismatched types between values that are actually stored in your database compared to values defined by your schema.

The type conversion during writes is as follows:

JavaScript (write)	D1	JavaScript (read)
null	`NULL`	null
Number	`REAL`	Number
Number ²	`INTEGER`	Number
String	`TEXT`	String
Boolean ³	`INTEGER`	Number (`0`,`1`)
ArrayBuffer	`BLOB`	Array ⁴
ArrayBuffer View	`BLOB`	Array ⁴
undefined	Not supported. ⁵	-

¹ D1 types correspond to the underlying SQLite types.

² D1 supports 64-bit signed INTEGER values internally, however BigInts are not currently supported in the API yet. JavaScript integers are safe up to Number.MAX_SAFE_INTEGER.

³ Booleans will be cast to an INTEGER type where 1 is TRUE and 0 is FALSE.

⁴ ArrayBuffer and ArrayBuffer views are converted using Array.from.

⁵ Queries with undefined values will return a D1_TYPE_ERROR.

API playground

The D1 Worker Binding API playground is an index.js file where you can test each of the documented Worker Binding APIs for D1. The file builds from the end-state of the Get started code.

You can use this alongside the API documentation to better understand how each API works.

Follow the steps to setup your API playground.

1. Complete the Get started tutorial

Complete the Get started tutorial. Ensure you use JavaScript instead of TypeScript.

2. Modify the content of `index.js`

Replace the contents of your index.js file with the code below to view the effect of each API.

// D1 API Playground - Test each D1 Worker Binding API method
// Change the URL pathname to test different methods (e.g., /RUN, /RAW, /FIRST)
export default {
	async fetch(request, env) {
	  const { pathname } = new URL(request.url);

		// Sample data for testing
		const companyName1 = `Bs Beverages`;
		const companyName2 = `Around the Horn`;

		// Prepare reusable statements
		const stmt = env.DB.prepare(`SELECT * FROM Customers WHERE CompanyName = ?`);
		const stmtMulti = env.DB.prepare(`SELECT * FROM Customers; SELECT * FROM Customers WHERE CompanyName = ?`);
		const session = env.DB.withSession("first-primary")
		const sessionStmt = session.prepare(`SELECT * FROM Customers WHERE CompanyName = ?`);

      // Test D1PreparedStatement::run - returns full D1Result object
      if (pathname === `/RUN`){
    	const returnValue = await stmt.bind(companyName1).run();
    	return Response.json(returnValue);

      // Test D1PreparedStatement::raw - returns array of arrays
    } else if (pathname === `/RAW`){
    	const returnValue = await stmt.bind(companyName1).raw();
    	return Response.json(returnValue);

      // Test D1PreparedStatement::first - returns first row only
    } else if (pathname === `/FIRST`){
    	const returnValue = await stmt.bind(companyName1).first();
    	return Response.json(returnValue);

      // Test D1Database::batch - execute multiple statements
    } else if (pathname === `/BATCH`) {
    	const batchResult = await env.DB.batch([
    		stmt.bind(companyName1),
    		stmt.bind(companyName2)
    	]);
    	return Response.json(batchResult);

      // Test D1Database::exec - execute raw SQL without parameters
    } else if (pathname === `/EXEC`){
    	const returnValue = await env.DB.exec(`SELECT * FROM Customers WHERE CompanyName = "Bs Beverages"`);
    	return Response.json(returnValue);

      // Test D1 Sessions API with read replication
    } else if (pathname === `/WITHSESSION`){
    	const returnValue = await sessionStmt.bind(companyName1).run();
    	console.log("You're now using D1 Sessions!")
    	return Response.json(returnValue);
    }

      // Default response with instructions
      return new Response(
    	`Welcome to the D1 API Playground!
    	\nChange the URL to test the various methods inside your index.js file.`,
      );
    },

};

3. Deploy the Worker

Navigate to your tutorial directory you created by following step 1.

Run npx wrangler deploy.

npx wrangler deploy

⛅️ wrangler 3.112.0
--------------------

Total Upload: 1.90 KiB / gzip: 0.59 KiB
Your worker has access to the following bindings:
- D1 Databases:
	- DB: DATABASE_NAME (<DATABASE_ID>)
Uploaded WORKER_NAME (7.01 sec)
Deployed WORKER_NAME triggers (1.25 sec)
	https://jun-d1-rr.d1-sandbox.workers.dev
Current Version ID: VERSION_ID

Open a browser at the specified address.

4. Test the APIs

Change the URL to test the various D1 Worker Binding APIs.