Query a database

D1 is compatible with most SQLite's SQL convention since it leverages SQLite's query engine. You can use SQL commands to query D1.D1 is compatible with most SQLite's SQL convention since it leverages SQLite's query engine. You can use SQL commands to query D1.D1 兼容大多数 SQLite SQL 约定 它利用 SQLite 的查询引擎，从而可以使用 SQL 命令进行查询D1 兼容大多数 SQLite SQL 约定SQLite SQL 约定

There are a number of ways you can interact with a D1 database:There are a number of ways you can interact with a D1 database:有多种方式可以与 D1 数据库交互 下面的列表列举了可用的接口有多种方式可以与 D1 数据库交互D1 数据库

1. Using D1 Workers Binding API in your code.
   Using D1 Workers Binding API in your code.
   在代码中使用 D1 Workers Binding API
   在代码中使用 D1 Workers Binding API
   D1 Workers Binding API
2. Using D1 REST API.
   Using D1 REST API.
   使用 D1 REST API
   使用 D1 REST API
   D1 REST API
3. Using D1 Wrangler commands.
   Using D1 Wrangler commands.
   使用 D1 Wrangler 命令
   使用 D1 Wrangler 命令
   D1 Wrangler 命令

Use SQL to query D1

D1 使用 SQLite 语义；通过 Workers Binding API 或 REST API 查询

D1 understands SQLite semantics, which allows you to query a database using SQL statements via Workers BindingAPI or REST API (including Wrangler commands). Refer to D1 SQL API to learn more about supported SQL statements.D1 understands SQLite semantics, which allows you to query a database using SQL statements via Workers BindingAPI or REST API (including Wrangler commands).D1 理解 SQLite 语义，可通过 API 进行 SQL 查询 参考 D1 SQL API 了解支持的 SQL 语句D1 理解 SQLite 语义，可通过 API 进行 SQL 查询SQLite 语义

Use foreign key relationships

外键约束强制表关系并防止无效删除

When using SQL with D1, you may wish to define and enforce foreign key constraints across tables in a database. Foreign key constraints allow you to enforce relationships across tables, or prevent you from deleting rows that reference rows in other tables. An example of a foreign key relationship is shown below.Foreign key constraints allow you to enforce relationships across tables, or prevent you from deleting rows that reference rows in other tables.外键约束强制跨表关系 它们防止删除引用其他表中行的行外键约束强制跨表关系外键约束

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email_address TEXT,
    name TEXT,
    metadata TEXT
)

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    status INTEGER,
    item_desc TEXT,
    shipped_date INTEGER,
    user_who_ordered INTEGER,
    FOREIGN KEY(user_who_ordered) REFERENCES users(user_id)
)

Refer to Define foreign keys for more information.Refer to Define foreign keys for more information.参考定义外键获取更多信息参考定义外键获取更多信息定义外键

Query JSON

D1 可以查询和解析存储在数据库中的 JSON 数据

D1 allows you to query and parse JSON data stored within a database. For example, you can extract a value inside a JSON object.D1 allows you to query and parse JSON data stored within a database.D1 查询和解析数据库中存储的 JSON 数据 您可以提取 JSON 对象内的值D1 查询和解析数据库中存储的 JSON 数据JSON 数据

Given the following JSON object (type:blob) in a column named sensor_reading, you can extract values from it directly.Given the following JSON object (type:blob) in a column named sensor_reading, you can extract values from it directly.使用 json_extract 从 JSON blob 中提取值 给定 sensor_reading 列中的 JSON 对象，您可以直接提取值使用 json_extract 从 JSON blob 中提取值json_extract

{
    "measurement": {
        "temp_f": "77.4",
        "aqi": [21, 42, 58],
        "o3": [18, 500],
        "wind_mph": "13",
        "location": "US-NY"
    }
}

-- Extract the temperature value
SELECT json_extract(sensor_reading, '$.measurement.temp_f')-- returns "77.4" as TEXT

Refer to Query JSON to learn more about querying JSON objects.Refer to Query JSON to learn more about querying JSON objects.参考查询 JSON 获取更多信息参考查询 JSON 获取更多信息查询 JSON

Query D1 with Workers Binding API

Workers Binding API 通过数据平面从 Worker 查询 D1

Workers Binding API primarily interacts with the data plane, and allows you to query your D1 database from your Worker.Workers Binding API primarily interacts with the data plane, and allows you to query your D1 database from your Worker.Workers Binding API 与数据平面交互，从 Worker 查询 D1Workers Binding API 与数据平面交互，从 Worker 查询 D1Workers Binding API

This requires you to:This requires you to:这要求您执行步骤 以下列表详细说明了所需的操作这要求您执行步骤步骤

1. Bind your D1 database to your Worker.
   Bind your D1 database to your Worker.
   将 D1 数据库绑定到您的 Worker
   将 D1 数据库绑定到您的 Worker
   绑定数据库
2. Prepare a statement.
   Prepare a statement.
   准备一个语句
   准备一个语句
   准备语句
3. Run the statement.
   Run the statement.
   运行语句
   运行语句
   运行语句

export default {
    async fetch(request, env) {
        const {pathname} = new URL(request.url);
        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);
        }

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

Refer to Workers Binding API for more information.Refer to Workers Binding API for more information.参考 Workers Binding API 获取更多信息参考 Workers Binding API 获取更多信息Workers Binding API

Query D1 with REST API

REST API 通过控制平面管理 D1 数据库

REST API primarily interacts with the control plane, and allows you to create/manage your D1 database.REST API primarily interacts with the control plane, and allows you to create/manage your D1 database.REST API 与控制平面交互以创建和管理 D1 数据库REST API 与控制平面交互以创建和管理 D1 数据库REST API

Refer to D1 REST API for D1 REST API documentation.Refer to D1 REST API for D1 REST API documentation.参考 D1 REST API 获取文档参考 D1 REST API 获取文档D1 REST API

Query D1 with Wrangler commands

Wrangler 命令使用底层 REST API 查询 D1 数据库

You can use Wrangler commands to query a D1 database. Note that Wrangler commands use REST APIs to perform its operations.You can use Wrangler commands to query a D1 database. Note that Wrangler commands use REST APIs to perform its operations.Wrangler 命令使用 REST API 查询 D1 数据库 它们使用 REST API 执行操作Wrangler 命令使用 REST API 查询 D1 数据库Wrangler 命令

npx wrangler d1 execute prod-d1-tutorial --command="SELECT * FROM Customers"

🌀 Mapping SQL input into an array of statements
🌀 Executing on local database production-db-backend (<DATABASE_ID>) from .wrangler/state/v3/d1:
┌────────────┬─────────────────────┬───────────────────┐
│ CustomerId │ CompanyName         │ ContactName       │
├────────────┼─────────────────────┼───────────────────┤
│ 1          │ Alfreds Futterkiste │ Maria Anders      │
├────────────┼─────────────────────┼───────────────────┤
│ 4          │ Around the Horn     │ Thomas Hardy      │
├────────────┼─────────────────────┼───────────────────┤
│ 11         │ Bs Beverages        │ Victoria Ashworth │
├────────────┼─────────────────────┼───────────────────┤
│ 13         │ Bs Beverages        │ Random Name       │
└────────────┴─────────────────────┴───────────────────┘