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.
D1 Worker Bindings API is fully-typed via the @cloudflare/workers-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 definitiontype OrderRow = {Id: string;CustomerName: string;OrderDate: number;};
// Elsewhere in your applicationconst result = await env.MY_DB.prepare("SELECT Id, CustomerName, OrderDate FROM [Order] ORDER BY ShippedDate DESC LIMIT 100",).run<OrderRow>();
D1 automatically converts supported JavaScript (including TypeScript) types passed as parameters via the Workers Binding API to their associated D1 types. The type conversion is as follows:
JavaScript | D1 |
---|---|
null | NULL |
Number | REAL |
Number 1 | INTEGER |
String | TEXT |
Boolean 2 | INTEGER |
ArrayBuffer | BLOB |
undefined | Not supported. Queries with undefined values will return a D1_TYPE_ERROR |
1 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
↗.
2 Booleans will be cast to an INTEGER
type where 1
is TRUE
and
0
is FALSE
.
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.
Complete the Get started tutorial. Ensure you use JavaScript instead of TypeScript.
Replace the contents of your index.js
file with the code below to view the effect of each API.
index.js
export default { async fetch(request, env) { const { pathname } = new URL(request.url);
// if (pathname === "/api/beverages") { // // If you did not use `DB` as your binding name, change it here // const { results } = await env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?",).bind("Bs Beverages").all(); // return Response.json(results); // }
const companyName1 = `Bs Beverages`; const companyName2 = `Around the Horn`; const stmt = env.DB.prepare(`SELECT * FROM Customers WHERE CompanyName = ?`);
if (pathname === `/RUN`){ const returnValue = await stmt.bind(companyName1).run(); return Response.json(returnValue);
} else if (pathname === `/RAW`){ const returnValue = await stmt.bind(companyName1).raw(); return Response.json(returnValue);
} else if (pathname === `/FIRST`){ const returnValue = await stmt.bind(companyName1).first(); return Response.json(returnValue);
} else if (pathname === `/BATCH`) { const batchResult = await env.DB.batch([ stmt.bind(companyName1), stmt.bind(companyName2) ]); return Response.json(batchResult);
} else if (pathname === `/EXEC`){ const returnValue = await env.DB.exec(`SELECT * FROM Customers WHERE CompanyName = "Bs Beverages"`); return Response.json(returnValue); }
return new Response( `Welcome to the D1 API Playground! \nChange the URL to test the various methods inside your index.js file.`, ); },};
- Navigate to your tutorial directory you created by following step 1.
- Run
npx wrangler dev
.Terminal window npx wrangler dev⛅️ wrangler 3.85.0 (update available 3.86.1)-------------------------------------------------------Your worker has access to the following bindings:- D1 Databases:- DB: <DATABASE_NAME> (DATABASE_ID) (local)⎔ Starting local server...[wrangler:inf] Ready on http://localhost:8787╭───────────────────────────╮│ [b] open a browser ││ [d] open devtools ││ [l] turn off local mode ││ [c] clear console ││ [x] to exit │╰───────────────────────────╯ - Open a browser at the specified address.
Change the URL to test the various D1 Worker Binding APIs.