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 is compatible with most SQLite SQL conventions It leverages SQLite's query engine, enabling SQL commands for queryingD1 is compatible with most SQLite SQL conventionsSQLite SQL conventions

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:There are multiple ways to interact with a D1 database The following list enumerates the available interfacesThere are multiple ways to interact with a D1 databaseD1 database

1. Using D1 Workers Binding API in your code.
   Using D1 Workers Binding API in your code.
   Use D1 Workers Binding API in your code
   Use D1 Workers Binding API in your code
   D1 Workers Binding API
2. Using D1 REST API.
   Using D1 REST API.
   Use D1 REST API
   Use D1 REST API
   D1 REST API
3. Using D1 Wrangler commands.
   Using D1 Wrangler commands.
   Use D1 Wrangler commands
   Use D1 Wrangler commands
   D1 Wrangler commands

Use SQL to query D1

D1 uses SQLite semantics; query via Workers Binding API or 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 understands SQLite semantics for SQL queries via APIs Refer to D1 SQL API for supported SQL statementsD1 understands SQLite semantics for SQL queries via APIsSQLite semantics

Use foreign key relationships

Foreign key constraints enforce table relationships and prevent invalid deletions

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.Foreign key constraints enforce relationships across tables They prevent deleting rows that reference rows in other tablesForeign key constraints enforce relationships across tablesforeign key constraints

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.Refer to Define foreign keys for more informationRefer to Define foreign keys for more informationDefine foreign keys

Query JSON

D1 can query and parse JSON data stored in the database

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 queries and parses JSON data stored in database You can extract values inside a JSON objectD1 queries and parses JSON data stored in databaseJSON data

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.Extract values from JSON blob using json_extract Given a JSON object in a sensor_reading column, you can directly extract valuesExtract values from JSON blob using json_extractjson_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.Refer to Query JSON for more informationRefer to Query JSON for more informationQuery JSON

Query D1 with Workers Binding API

Workers Binding API queries D1 from a Worker via data plane

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 interacts with data plane to query D1 from WorkerWorkers Binding API interacts with data plane to query D1 from WorkerWorkers Binding API

This requires you to:This requires you to:This requires you to perform steps The following list details the required actionsThis requires you to perform stepssteps

1. Bind your D1 database to your Worker.
   Bind your D1 database to your Worker.
   Bind your D1 database to your Worker
   Bind your D1 database to your Worker
   Bind database
2. Prepare a statement.
   Prepare a statement.
   Prepare a statement
   Prepare a statement
   Prepare statement
3. Run the statement.
   Run the statement.
   Run the statement
   Run the statement
   Run 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.Refer to Workers Binding API for more informationRefer to Workers Binding API for more informationWorkers Binding API

Query D1 with REST API

REST API manages D1 databases via control plane

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 interacts with control plane to create and manage D1 databaseREST API interacts with control plane to create and manage D1 databaseREST API

Refer to D1 REST API for D1 REST API documentation.Refer to D1 REST API for D1 REST API documentation.Refer to D1 REST API for documentationRefer to D1 REST API for documentationD1 REST API

Query D1 with Wrangler commands

Wrangler commands query D1 databases using underlying REST APIs

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 commands query D1 database using REST APIs They use REST APIs to perform operationsWrangler commands query D1 database using REST APIsWrangler commands

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       │
└────────────┴─────────────────────┴───────────────────┘