# D1 SQL Playground (/docs/experiments/d1-sql-playground)



Run safe, read-only `SELECT` queries against a seeded Cloudflare D1 database. Returns JSON rows, inferred column metadata, and query timing.

## Features [#features]

* **POST /query** - Execute a validated `SELECT` and receive rows plus column types
* **Seeded schema** - `products` catalog and `experiments` table from this repo
* **Read-only guardrails** - Rejects writes, DDL, semicolons, comments, `UNION`, and disallowed tables
* **GET /** - Lists queryable tables

## API Reference [#api-reference]

### POST /query [#post-query]

Execute a single read-only `SELECT` against the seeded database.

<TypeTable
  type="{
  sql: {
    description:
      &#x22;One `SELECT` statement. Must reference only `products` or `experiments`. No semicolons, SQL comments, or multiple statements.&#x22;,
    type: &#x22;string&#x22;,
    required: true,
  },
}"
/>

#### Example Request [#example-request]

```bash
curl -X POST "https://your-worker.workers.dev/query" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT name, price FROM products WHERE in_stock = 1 ORDER BY price DESC LIMIT 5"}'
```

#### Success Response [#success-response]

```json
{
  "columns": [
    { "name": "name", "type": "text" },
    { "name": "price", "type": "number" }
  ],
  "rows": [
    { "name": "Browser Rendering Frame", "price": 59.99 },
    { "name": "Workers AI Prompt Pack", "price": 49.99 }
  ],
  "rowCount": 2,
  "durationMs": 3
}
```

#### Error Codes [#error-codes]

* `400` - Invalid JSON (`INVALID_BODY`) or disallowed SQL (`INVALID_SQL`)
* `502` - D1 execution error (`QUERY_ERROR`)

### GET / [#get-]

Returns app metadata and the list of allowed tables (`products`, `experiments`).

## Seeded Tables [#seeded-tables]

| Table         | Columns (high level)                                        |
| ------------- | ----------------------------------------------------------- |
| `products`    | `id`, `name`, `category`, `price`, `in_stock`, `created_at` |
| `experiments` | `slug`, `name`, `category`, `description`                   |

Example queries:

```sql
SELECT category, COUNT(*) AS total FROM products GROUP BY category
SELECT slug, name FROM experiments WHERE category = 'Storage & Data'
SELECT p.name, e.name FROM products p JOIN experiments e ON p.category LIKE '%' || e.category || '%' LIMIT 5
```

## Use Cases [#use-cases]

* Learn D1 query patterns without provisioning your own schema
* Prototype read-only analytics APIs over SQLite at the edge
* Teach SQL safely with guardrails against writes and injection
* Explore JOINs and aggregations on realistic sample data

## Limitations [#limitations]

* Read-only: `INSERT`, `UPDATE`, `DELETE`, DDL, and `UNION` are rejected
* Only `products` and `experiments` tables are queryable
* Column types are inferred from the first result row (empty results return no column metadata)
* Requires D1 migrations before first use (local and remote)

## Deployment [#deployment]

<Steps>
  <Step>
    ### Click the deploy button [#click-the-deploy-button]

    [![Deploy to Cloudflare Workers](https://deploy.workers.cloudflare.com/button)](https://deploy.workers.cloudflare.com/?url=https://github.com/shrinathsnayak/cloudflare-experiments/tree/main/apps/experiments/d1-sql-playground)
  </Step>

  <Step>
    ### Configure D1 [#configure-d1]

    Create a D1 database, set `database_id` in `wrangler.json`, then apply migrations:

    ```bash
    npm run db:migrate
    ```
  </Step>

  <Step>
    ### Test your deployment [#test-your-deployment]

    ```bash
    curl -X POST "https://your-worker.workers.dev/query" \
      -H "Content-Type: application/json" \
      -d '{"sql":"SELECT * FROM products LIMIT 3"}'
    ```
  </Step>
</Steps>

## Local Development [#local-development]

```bash
cd apps/experiments/d1-sql-playground
npm install
npm run db:migrate:local
npm run dev
```

Test locally:

```bash
curl -X POST "http://localhost:8787/query" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT * FROM experiments LIMIT 5"}'
```

## Configuration [#configuration]

| Binding | Purpose                              |
| ------- | ------------------------------------ |
| `DB`    | D1 database (`d1-sql-playground-db`) |

Migrations live in `migrations/0000_init.sql`. Apply with `npm run db:migrate:local` (dev) or `npm run db:migrate` (remote).

## Cloudflare Features Used [#cloudflare-features-used]

* **[Workers](https://developers.cloudflare.com/workers/)** - Edge compute runtime
* **[D1](https://developers.cloudflare.com/d1/)** - SQLite database at the edge with seeded read-only playground data