SQL statements
- Supported SQLite extensions
- Compatible PRAGMA statements

SQL statements

Last updated Apr 21, 2026 D1 is compatible with most SQLite's SQL convention since it leverages SQLite's query engine. D1 supports a number of database-level statements that allow you to list tables, indexes, and inspect the schema for a given table or index.

You can execute any of these statements via the D1 console in the Cloudflare dashboard, wrangler d1 execute, or with the D1 Worker Bindings API.

Supported SQLite extensions

D1 supports a subset of SQLite extensions for added functionality, including:

FTS5 module ↗︎ for full-text search (including fts5vocab).
JSON extension ↗︎ for JSON functions and operators.
Math functions ↗︎.

Refer to the source code ↗︎ for the full list of supported functions.

Compatible PRAGMA statements

D1 supports some SQLite PRAGMA ↗︎ statements. The PRAGMA statement is an SQL extension for SQLite. PRAGMA commands can be used to:

Modify the behavior of certain SQLite operations.
Query the SQLite library for internal data about schemas or tables (but note that PRAGMA statements cannot query the contents of a table).
Control environmental variables.

The PRAGMA statement examples on this page use the following SQL.

PRAGMA foreign_keys=off;
DROP TABLE IF EXISTS "Employee";
DROP TABLE IF EXISTS "Category";
DROP TABLE IF EXISTS "Customer";
DROP TABLE IF EXISTS "Shipper";
DROP TABLE IF EXISTS "Supplier";
DROP TABLE IF EXISTS "Order";
DROP TABLE IF EXISTS "Product";
DROP TABLE IF EXISTS "OrderDetail";
DROP TABLE IF EXISTS "CustomerCustomerDemo";
DROP TABLE IF EXISTS "CustomerDemographic";
DROP TABLE IF EXISTS "Region";
DROP TABLE IF EXISTS "Territory";
DROP TABLE IF EXISTS "EmployeeTerritory";
DROP VIEW IF EXISTS [ProductDetails_V];
CREATE TABLE IF NOT EXISTS "Employee" ( "Id" INTEGER PRIMARY KEY, "LastName" VARCHAR(8000) NULL, "FirstName" VARCHAR(8000) NULL, "Title" VARCHAR(8000) NULL, "TitleOfCourtesy" VARCHAR(8000) NULL, "BirthDate" VARCHAR(8000) NULL, "HireDate" VARCHAR(8000) NULL, "Address" VARCHAR(8000) NULL, "City" VARCHAR(8000) NULL, "Region" VARCHAR(8000) NULL, "PostalCode" VARCHAR(8000) NULL, "Country" VARCHAR(8000) NULL, "HomePhone" VARCHAR(8000) NULL, "Extension" VARCHAR(8000) NULL, "Photo" BLOB NULL, "Notes" VARCHAR(8000) NULL, "ReportsTo" INTEGER NULL, "PhotoPath" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Category" ( "Id" INTEGER PRIMARY KEY, "CategoryName" VARCHAR(8000) NULL, "Description" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Customer" ( "Id" VARCHAR(8000) PRIMARY KEY, "CompanyName" VARCHAR(8000) NULL, "ContactName" VARCHAR(8000) NULL, "ContactTitle" VARCHAR(8000) NULL, "Address" VARCHAR(8000) NULL, "City" VARCHAR(8000) NULL, "Region" VARCHAR(8000) NULL, "PostalCode" VARCHAR(8000) NULL, "Country" VARCHAR(8000) NULL, "Phone" VARCHAR(8000) NULL, "Fax" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Shipper" ( "Id" INTEGER PRIMARY KEY, "CompanyName" VARCHAR(8000) NULL, "Phone" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Supplier" ( "Id" INTEGER PRIMARY KEY, "CompanyName" VARCHAR(8000) NULL, "ContactName" VARCHAR(8000) NULL, "ContactTitle" VARCHAR(8000) NULL, "Address" VARCHAR(8000) NULL, "City" VARCHAR(8000) NULL, "Region" VARCHAR(8000) NULL, "PostalCode" VARCHAR(8000) NULL, "Country" VARCHAR(8000) NULL, "Phone" VARCHAR(8000) NULL, "Fax" VARCHAR(8000) NULL, "HomePage" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Order" ( "Id" INTEGER PRIMARY KEY, "CustomerId" VARCHAR(8000) NULL, "EmployeeId" INTEGER NOT NULL, "OrderDate" VARCHAR(8000) NULL, "RequiredDate" VARCHAR(8000) NULL, "ShippedDate" VARCHAR(8000) NULL, "ShipVia" INTEGER NULL, "Freight" DECIMAL NOT NULL, "ShipName" VARCHAR(8000) NULL, "ShipAddress" VARCHAR(8000) NULL, "ShipCity" VARCHAR(8000) NULL, "ShipRegion" VARCHAR(8000) NULL, "ShipPostalCode" VARCHAR(8000) NULL, "ShipCountry" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Product" ( "Id" INTEGER PRIMARY KEY, "ProductName" VARCHAR(8000) NULL, "SupplierId" INTEGER NOT NULL, "CategoryId" INTEGER NOT NULL, "QuantityPerUnit" VARCHAR(8000) NULL, "UnitPrice" DECIMAL NOT NULL, "UnitsInStock" INTEGER NOT NULL, "UnitsOnOrder" INTEGER NOT NULL, "ReorderLevel" INTEGER NOT NULL, "Discontinued" INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS "OrderDetail" ( "Id" VARCHAR(8000) PRIMARY KEY, "OrderId" INTEGER NOT NULL, "ProductId" INTEGER NOT NULL, "UnitPrice" DECIMAL NOT NULL, "Quantity" INTEGER NOT NULL, "Discount" DOUBLE NOT NULL);
CREATE TABLE IF NOT EXISTS "CustomerCustomerDemo" ( "Id" VARCHAR(8000) PRIMARY KEY, "CustomerTypeId" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "CustomerDemographic" ( "Id" VARCHAR(8000) PRIMARY KEY, "CustomerDesc" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Region" ( "Id" INTEGER PRIMARY KEY, "RegionDescription" VARCHAR(8000) NULL);
CREATE TABLE IF NOT EXISTS "Territory" ( "Id" VARCHAR(8000) PRIMARY KEY, "TerritoryDescription" VARCHAR(8000) NULL, "RegionId" INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS "EmployeeTerritory" ( "Id" VARCHAR(8000) PRIMARY KEY, "EmployeeId" INTEGER NOT NULL, "TerritoryId" VARCHAR(8000) NULL);
CREATE VIEW [ProductDetails_V] as select p.*, c.CategoryName, c.Description as [CategoryDescription], s.CompanyName as [SupplierName], s.Region as [SupplierRegion] from [Product] p join [Category] c on p.CategoryId = c.id join [Supplier] s on s.id = p.SupplierId;

Caution

D1 PRAGMA statements only apply to the current transaction.

`PRAGMA table_list`

Lists the tables and views in the database. This includes the system tables maintained by D1.

Return values

One row per each table. Each row contains:

Schema: the schema in which the table appears (for example, main or temp)
name: the name of the table
type: the type of the object (one of table, view, shadow, virtual)
ncol: the number of columns in the table, including generated or hidden columns
wr: 1 if the table is a WITHOUT ROWID table, 0 otherwise
strict: 1 if the table is a STRICT table, 0 otherwise

Example of <code>PRAGMA table_list</code>

</summary>

npx wrangler d1 execute [DATABASE_NAME] --command='PRAGMA table_list'

🌀 Executing on remote database [DATABASE_NAME] (DATABASE_ID):
🌀 To execute on your local development database, remove the --remote flag from your wrangler command.
🚣 Executed 1 commands in 0.5874ms
┌────────┬──────────────────────┬───────┬──────┬────┬────────┐
│ schema │ name                 │ type  │ ncol │ wr │ strict │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Territory            │ table │ 3    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ CustomerDemographic  │ table │ 2    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ OrderDetail          │ table │ 6    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ sqlite_schema        │ table │ 5    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Region               │ table │ 2    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ _cf_KV               │ table │ 2    │ 1  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ ProductDetails_V     │ view  │ 14   │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ EmployeeTerritory    │ table │ 3    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Employee             │ table │ 18   │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Category             │ table │ 3    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Customer             │ table │ 11   │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Shipper              │ table │ 3    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Supplier             │ table │ 12   │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Order                │ table │ 14   │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ CustomerCustomerDemo │ table │ 2    │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ main   │ Product              │ table │ 10   │ 0  │ 0      │
├────────┼──────────────────────┼───────┼──────┼────┼────────┤
│ temp   │ sqlite_temp_schema   │ table │ 5    │ 0  │ 0      │
└────────┴──────────────────────┴───────┴──────┴────┴────────┘

</details>

`PRAGMA table_info("TABLE_NAME")`

Shows the schema (columns, types, null, default values) for the given TABLE_NAME.

Return values

One row for each column in the specified table. Each row contains:

cid: a row identifier
name: the name of the column
type: the data type (if provided), '' otherwise
notnull: 1 if the column can be NULL, 0 otherwise
dflt_value: the default value of the column
pk: 1 if the column is a primary key, 0 otherwise

Example of <code>PRAGMA table_info</code>

</summary>

npx wrangler d1 execute [DATABASE_NAME] --command='PRAGMA table_info("Order")'

🌀 Executing on remote database [DATABASE_NAME] (DATABASE_ID):
🌀 To execute on your local development database, remove the --remote flag from your wrangler command.
🚣 Executed 1 commands in 0.8502ms
┌─────┬────────────────┬───────────────┬─────────┬────────────┬────┐
│ cid │ name           │ type          │ notnull │ dflt_value │ pk │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 0   │ Id             │ INTEGER       │ 0       │            │ 1  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 1   │ CustomerId     │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 2   │ EmployeeId     │ INTEGER       │ 1       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 3   │ OrderDate      │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 4   │ RequiredDate   │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 5   │ ShippedDate    │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 6   │ ShipVia        │ INTEGER       │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 7   │ Freight        │ DECIMAL       │ 1       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 8   │ ShipName       │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 9   │ ShipAddress    │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 10  │ ShipCity       │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 11  │ ShipRegion     │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 12  │ ShipPostalCode │ VARCHAR(8000) │ 0       │            │ 0  │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┤
│ 13  │ ShipCountry    │ VARCHAR(8000) │ 0       │            │ 0  │
└─────┴────────────────┴───────────────┴─────────┴────────────┴────┘

</details>

`PRAGMA table_xinfo("TABLE_NAME")`

Similar to PRAGMA table_info(TABLE_NAME) but also includes generated columns.

Example of <code>PRAGMA table_xinfo</code>

</summary>

npx wrangler d1 execute [DATABASE_NAME] --command='PRAGMA table_xinfo("Order")'

🌀 Executing on remote database [DATABASE_NAME] (DATABASE_ID):
🌀 To execute on your local development database, remove the --remote flag from your wrangler command.
🚣 Executed 1 commands in 0.3854ms
┌─────┬────────────────┬───────────────┬─────────┬────────────┬────┬────────┐
│ cid │ name           │ type          │ notnull │ dflt_value │ pk │ hidden │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 0   │ Id             │ INTEGER       │ 0       │            │ 1  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 1   │ CustomerId     │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 2   │ EmployeeId     │ INTEGER       │ 1       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 3   │ OrderDate      │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 4   │ RequiredDate   │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 5   │ ShippedDate    │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 6   │ ShipVia        │ INTEGER       │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 7   │ Freight        │ DECIMAL       │ 1       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 8   │ ShipName       │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 9   │ ShipAddress    │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 10  │ ShipCity       │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 11  │ ShipRegion     │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 12  │ ShipPostalCode │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
├─────┼────────────────┼───────────────┼─────────┼────────────┼────┼────────┤
│ 13  │ ShipCountry    │ VARCHAR(8000) │ 0       │            │ 0  │ 0      │
└─────┴────────────────┴───────────────┴─────────┴────────────┴────┴────────┘