Xây dựng API to access D1 using proxy Worker Build an API to access D1 using a proxy Worker

In this tutorial, you will learn how to create an API that allows you to securely run queries against a D1 database.

This is useful if you want to access a D1 database outside of a Worker or Pages project, customize access controls and/or limit what tables can be queried.

D1's built-in REST API is best suited for administrative use as the global Cloudflare API rate limit applies.

To access a D1 database outside of a Worker project, you need to create an API using a Worker. Your application can then securely interact with this API to run D1 queries.

D1 uses parameterized queries. This prevents SQL injection. To make your API more secure, validate the input using a library like zod ↗.

1. Sign up for a Cloudflare account ↗.
2. Install Node.js ↗.
3. Have an existing D1 database. Refer to Get started tutorial for D1.

Node.js version manager

Use a Node version manager like Volta ↗ ornvm ↗ to avoid permission issues and change Node.js versions. Wrangler, discussed later in this guide, requires a Node version of 16.17.0 or later.

Create a new Worker to create and deploy your API.

1. Create a Worker named d1-http by running:

npm yarn pnpm

npm create cloudflare@latest -- d1-http

yarn create cloudflare d1-http

pnpm create cloudflare@latest d1-http

For setup, select the following options: For What would you like to start with?, choose Hello World example. For Which template would you like to use?, choose Worker only. For Which language do you want to use?, choose TypeScript. For Do you want to use git for version control?, choose Yes. * For Do you want to deploy your application?, choose No (we will be making some changes before deploying).

1. Change into your new project directory to start developing:

cd d1-http

In this tutorial, you will use Hono ↗, an Express.js-style framework, to build the API.

1. To use Hono in this project, install it using npm:

npm yarn pnpm bun

npm i hono

yarn add hono

pnpm add hono

bun add hono

You need an API key to make authenticated calls to the API. To ensure that the API key is secure, add it as a secret.

1. For local development, create a .dev.vars file in the root directory of d1-http.
2. Add your API key in the file as follows.

.dev.vars

API_KEY="YOUR_API_KEY"

Replace YOURAPIKEY with a valid string value. You can also generate this value using the following command. Terminal window

openssl rand -base64 32

In this step, we have defined the name of the API key to be API_KEY.

To initialize the application, you need to import the required packages, initialize a new Hono application, and configure the following middleware:

• Bearer Auth ↗: Adds authentication to the API.
• Logger ↗: Allows monitoring the flow of requests and responses.
• Pretty JSON ↗: Enables "JSON pretty print" for JSON response bodies.

1. Replace the contents of the src/index.ts file with the code below.

src/index.ts

import { Hono } from "hono";  
import { bearerAuth } from "hono/bearer-auth";  
import { logger } from "hono/logger";  
import { prettyJSON } from "hono/pretty-json";  
type Bindings = {  
  API_KEY: string;  
};  
const app = new Hono<{ Bindings: Bindings }>();  
app.use("*", prettyJSON(), logger(), async (c, next) => {  
  const auth = bearerAuth({ token: c.env.API_KEY });  
  return auth(c, next);  
});

1. Add the following snippet into your src/index.ts.

src/index.ts

// Paste this code at the end of the src/index.ts file  
app.post("/api/all", async (c) => {  
  return c.text("/api/all endpoint");  
});  
app.post("/api/exec", async (c) => {  
  return c.text("/api/exec endpoint");  
});  
app.post("/api/batch", async (c) => {  
  return c.text("/api/batch endpoint");  
});  
export default app;

This adds the following endpoints: POST /api/all POST /api/exec * POST /api/batch

1. Start the development server by running the following command:

npm yarn pnpm

npm run dev

yarn run dev

pnpm run dev

1. To test the API locally, open a second terminal.
2. In the second terminal, execute the below cURL command. Replace YOURAPIKEY with the value you set in the .dev.vars file.

curl -H "Authorization: Bearer YOUR_API_KEY" "http://localhost:8787/api/all" --data '{}'

You should get the following output:

/api/all endpoint

1. Stop the local server from running by pressing x in the first terminal.

The Hono application is now set up. You can test the other endpoints and add more endpoints if needed. The API does not yet return any information from your database. In the next steps, you will create a database, add its bindings, and update the endpoints to interact with the database.

If you do not have a D1 database already, you can create a new database with wrangler d1 create.

1. In your terminal, run:

npx wrangler d1 create d1-http-example

You may be asked to login to your Cloudflare account. Once logged in, the command will create a new D1 database. You should see a similar output in your terminal.

✅ Successfully created DB 'd1-http-example' in region EEUR  
Created your new D1 database.  
[[d1_databases]]  
binding = "DB" # i.e. available in your Worker on env.DB  
database_name = "d1-http-example"  
database_id = "1234567890"

Make a note of the displayed databasename and databaseid. You will use this to reference the database by creating a binding.

1. From your d1-http folder, open the Wrangler file, Wrangler's configuration file.
2. Add the following binding in the file. Make sure that the databasename and the databaseid are correct.

wrangler.jsonc wrangler.toml

{  
  "d1_databases": [  
    {  
      "binding": "DB", // i.e. available in your Worker on env.DB  
      "database_name": "d1-http-example",  
      "database_id": "1234567890"  
    }  
  ]  
}

[[d1_databases]]  
binding = "DB"  
database_name = "d1-http-example"  
database_id = "1234567890"

1. In your src/index.ts file, update the Bindings type by adding DB: D1Database.

type Bindings = {  
  DB: D1Database;  
  API_KEY: string;  
};

You can now access the database in the Hono application.

To create a table in your newly created database:

1. Create a new folder called schemas inside your d1-http folder.
2. Create a new file called schema.sql, and paste the following SQL statement into the file.

schema.sql

DROP TABLE IF EXISTS posts;  
CREATE TABLE IF NOT EXISTS posts (  
  id integer PRIMARY KEY AUTOINCREMENT,  
  author text NOT NULL,  
  title text NOT NULL,  
  body text NOT NULL,  
  post_slug text NOT NULL  
);  
INSERT INTO posts (author, title, body, post_slug) VALUES ('Harshil', 'D1 HTTP API', 'Learn to create an API to query your D1 database.','d1-http-api');

The code drops any table named posts if it exists, then creates a new table posts with the field id, author, title, body, and post_slug. It then uses an INSERT statement to populate the table.

1. In your terminal, execute the following command to create this table:

npx wrangler d1 execute d1-http-example --file=./schemas/schema.sql

Upon successful execution, a new table will be added to your database.

The table will be created in the local instance of the database. If you want to add this table to your production database set "remote" : true in the D1 binding configuration. Refer to the remote bindings documentation for more information.

Your application can now access the D1 database. In this step, you will update the API endpoints to query the database and return the result.

1. In your src/index.ts file, update the code as follow.

src/index.ts

// Update the API routes  
/**  
* Executes the `stmt.run()` method.  
* https://developers.cloudflare.com/d1/worker-api/prepared-statements/#run  
*/  
app.post('/api/all', async (c) => {  
    return c.text("/api/all endpoint");  
  try {  
    let { query, params } = await c.req.json();  
    let stmt = c.env.DB.prepare(query);  
    if (params) {  
      stmt = stmt.bind(params);  
    }  
    const result = await stmt.run();  
    return c.json(result);  
  } catch (err) {  
    return c.json({ error: `Failed to run query: ${err}` }, 500);  
  }  
});  
/**  
* Executes the `db.exec()` method.  
* https://developers.cloudflare.com/d1/worker-api/d1-database/#exec  
*/  
app.post('/api/exec', async (c) => {  
    return c.text("/api/exec endpoint");  
  try {  
    let { query } = await c.req.json();  
    let result = await c.env.DB.exec(query);  
    return c.json(result);  
  } catch (err) {  
    return c.json({ error: `Failed to run query: ${err}` }, 500);  
  }  
});  
/**  
* Executes the `db.batch()` method.  
* https://developers.cloudflare.com/d1/worker-api/d1-database/#batch  
*/  
app.post('/api/batch', async (c) => {  
    return c.text("/api/batch endpoint");  
  try {  
    let { batch } = await c.req.json();  
    let stmts = [];  
    for (let query of batch) {  
      let stmt = c.env.DB.prepare(query.query);  
      if (query.params) {  
        stmts.push(stmt.bind(query.params));  
      } else {  
        stmts.push(stmt);  
      }  
    }  
    const results = await c.env.DB.batch(stmts);  
    return c.json(results);  
  } catch (err) {  
    return c.json({ error: `Failed to run query: ${err}` }, 500);  
  }  
});  
...

In the above code, the endpoints are updated to receive query and params. These queries and parameters are passed to the respective functions to interact with the database.

• If the query is successful, you receive the result from the database.
• If there is an error, the error message is returned.

Now that the API can query the database, you can test it locally.

1. Start the development server by executing the following command:

npm yarn pnpm

npm run dev

yarn run dev

pnpm run dev

1. In a new terminal window, execute the following cURL commands. Make sure to replace YOURAPIKEY with the correct value.

/api/all

curl -H "Authorization: Bearer YOUR_API_KEY" "http://localhost:8787/api/all" --data '{"query": "SELECT title FROM posts WHERE id=?", "params":1}'

/api/batch

curl -H "Authorization: Bearer YOUR_API_KEY" "http://localhost:8787/api/batch" --data '{"batch": [ {"query": "SELECT title FROM posts WHERE id=?", "params":1},{"query": "SELECT id FROM posts"}]}'

/api/exec

curl -H "Authorization: Bearer YOUR_API_KEY" "localhost:8787/api/exec" --data '{"query": "INSERT INTO posts (author, title, body, post_slug) VALUES ('\''Harshil'\'', '\''D1 HTTP API'\'', '\''Learn to create an API to query your D1 database.'\'','\''d1-http-api'\'')" }'

If everything is implemented correctly, the above commands should result successful outputs.

Now that everything is working as expected, the last step is to deploy it to the Cloudflare network. You will use Wrangler to deploy the API.

1. To use the API in production instead of using it locally, you need to add the table to your remote (production) database. To add the table to your production database, run the following command:

npx wrangler d1 execute d1-http-example --file=./schemas/schema.sql --remote

You should now be able to view the table on the Cloudflare dashboard > Storage & Databases \> D1. ↗

1. To deploy the application to the Cloudflare network, run the following command:

npx wrangler deploy

⛅️ wrangler 3.78.4 (update available 3.78.5)  
-------------------------------------------------------  
Total Upload: 53.00 KiB / gzip: 13.16 KiB  
Your worker has access to the following bindings:  
- D1 Databases:  
  - DB: d1-http-example (DATABASE_ID)  
Uploaded d1-http (4.29 sec)  
Deployed d1-http triggers (5.57 sec)  
  [DEPLOYED_APP_LINK]  
Current Version ID: [BINDING_ID]

Upon successful deployment, you will get the link of the deployed app in the terminal (DEPLOYEDAPPLINK). Make a note of it.

1. Generate a new API key to use in production.

Terminal window

openssl rand -base64 32

[YOUR_API_KEY]

1. Execute the wrangler secret put command to add an API to the deployed project.

npx wrangler secret put API_KEY

✔ Enter a secret value:

The terminal will prompt you to enter a secret value.

1. Enter the value of your API key (YOURAPIKEY). Your API key will now be added to your project. Using this value you can make secure API calls to your deployed API.

Terminal window

✔ Enter a secret value: [YOUR_API_KEY]

🌀 Creating the secret for the Worker "d1-http"  
✨ Success! Uploaded secret API_KEY

1. To test it, run the following cURL command with the correct YOURAPIKEY and DEPLOYEDAPPLINK.

Use the YOURAPIKEY you have generated as the secret API key. You can also find your DEPLOYEDAPPLINK from the Cloudflare dashboard > Workers & Pages \> d1-http \> Settings \> Domains & Routes.

curl -H "Authorization: Bearer YOUR_API_KEY" "https://DEPLOYED_APP_LINK/api/exec" --data '{"query": "SELECT 1"}'

In this tutorial, you have:

1. Created an API that interacts with your D1 database.
2. Deployed this API to the Workers. You can use this API in your external application to execute queries against your D1 database. The full code for this tutorial can be found on GitHub ↗.

You can check out a similar implementation that uses Zod for validation in this GitHub repository ↗. If you want to build an OpenAPI compliant API for your D1 database, you should use the Cloudflare Workers OpenAPI 3.1 template ↗.

{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"item":{"@id":"/directory/","name":"Directory"}},{"@type":"ListItem","position":2,"item":{"@id":"/d1/","name":"D1"}},{"@type":"ListItem","position":3,"item":{"@id":"/d1/tutorials/","name":"Tutorials"}},{"@type":"ListItem","position":4,"item":{"@id":"/d1/tutorials/build-an-api-to-access-d1/","name":"Build an API to access D1 using a proxy Worker"}}]}