# Choose a database

> Choose and configure SQLite, D1, Hyperdrive, PostgreSQL, or libSQL for EmDash.

Source: https://emdashcmseverything.com/official-docs/deployment/database/

Choose one database adapter for each deployment. The database holds the content model, entries, users, settings, and plugin data. Media binaries belong in a separate [storage backend](https://emdashcmseverything.com/official-docs/deployment/storage/).

## Overview

| Database       | Use it when                                                        | Runtime              |
| -------------- | ------------------------------------------------------------------ | -------------------- |
| **SQLite**     | One Node.js process has a persistent disk                           | Node.js or local dev |
| **D1**         | The site runs on Cloudflare Workers and should use Cloudflare SQL   | Cloudflare Workers   |
| **Hyperdrive** | The site runs on Workers and must use an existing PostgreSQL origin | Cloudflare Workers   |
| **PostgreSQL** | Several Node.js processes need one shared database                  | Node.js              |
| **libSQL**     | A Node.js deployment needs a remote SQLite-compatible database      | Node.js              |

D1 is the default for the Cloudflare templates. SQLite is the simplest Node.js option, but it requires one writable persistent volume and operational database backups.

## SQLite

SQLite uses Node.js's built-in database driver and is the simplest option for Node.js deployments.

```js title="astro.config.mjs"
import { sqlite } from "emdash/db";

export default defineConfig({
	integrations: [
		emdash({
			database: sqlite({ url: "file:./data.db" }),
		}),
	],
});
```

### Configuration

| Option | Type     | Description                   |
| ------ | -------- | ----------------------------- |
| `url`  | `string` | File path with `file:` prefix |

### File Path

The `url` must start with `file:`:

```js
// Relative path
database: sqlite({ url: "file:./data/emdash.db" });

// Absolute path
database: sqlite({ url: "file:/var/data/emdash.db" });

// From environment variable
database: sqlite({ url: `file:${process.env.DATABASE_PATH}` });
```

> **Note**
>
> SQLite requires a persistent filesystem. It does not work on platforms with ephemeral storage
> without additional configuration.

### Write-ahead logging

EmDash opens SQLite databases in [write-ahead logging (WAL) mode](https://sqlite.org/wal.html). While the site runs, SQLite keeps two extra files next to the database, such as `emdash.db-wal` and `emdash.db-shm`. The process needs write access to the database directory to create them.

The `-wal` file can hold committed changes that are not yet in the main database file. Back up with SQLite's backup command instead of copying only the `.db` file. See [SQLite backup and recovery](https://emdashcmseverything.com/official-docs/guides/backups/#sqlite-backup-and-recovery).

WAL requires shared memory, so keep the database on a local disk or block volume rather than a network filesystem such as NFS or SMB.

## Cloudflare D1

D1 is Cloudflare's serverless SQLite database. Use it when deploying to Cloudflare Workers.

```js title="astro.config.mjs"
import { d1 } from "@emdash-cms/cloudflare";

export default defineConfig({
	integrations: [
		emdash({
			database: d1({ binding: "DB" }),
		}),
	],
});
```

### Configuration

| Option           | Type     | Default              | Description                           |
| ---------------- | -------- | -------------------- | ------------------------------------- |
| `binding`        | `string` | —                    | D1 binding name from `wrangler.jsonc` |
| `session`        | `string` | `"disabled"`         | D1 Sessions API mode (see below)      |
| `bookmarkCookie` | `string` | `"__em_d1_bookmark"` | Cookie name for session bookmarks     |
| `coalesce`       | `boolean` | `false`             | Experimental: batch reads issued together into one round trip (see below) |

### Wrangler binding

#### wrangler.jsonc

```jsonc
{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "emdash-db"
    }
  ]
}
```

#### wrangler.toml

```toml
[[d1_databases]]
binding = "DB"
database_name = "emdash-db"
```

Wrangler can provision a missing D1 database from this binding during deployment. EmDash migrations are a separate step. Follow [Deploy to Cloudflare](https://emdashcmseverything.com/official-docs/deployment/cloudflare/) for the complete binding set and [Manage core database migrations](https://emdashcmseverything.com/official-docs/deployment/core-migrations/) for the migration runbook.

### Read replicas

D1 supports [read replication](https://developers.cloudflare.com/d1/configuration/read-replication/) to lower read latency for globally distributed sites. When enabled, read queries are routed to nearby replicas instead of always hitting the primary database.

EmDash uses the D1 Sessions API to manage this transparently. Enable it with the `session` option:

```js title="astro.config.mjs"
import { d1 } from "@emdash-cms/cloudflare";

export default defineConfig({
	integrations: [
		emdash({
			database: d1({
				binding: "DB",
				session: "auto",
				coalesce: true,
			}),
		}),
	],
});
```

A session runs the queries of a request one at a time, to keep its bookmark in order. Without `coalesce`, every query of a page therefore waits for the previous one. The experimental `coalesce` option sends reads that a request issues together as one D1 batch, so they share a round trip. This pays off even without read replication, so the Cloudflare templates enable both options. It also lets EmDash batch the site settings, menus, widget areas, and taxonomy terms of an HTML page into one round trip at the start of the request; without `coalesce`, EmDash skips that preload. With `coalesce`, a read and a write started together may run write-first, so await a read before a write that must not affect it.

#### Session modes

| Mode             | Behavior                                                                                           |
| ---------------- | -------------------------------------------------------------------------------------------------- |
| `"disabled"`     | No sessions. All queries go to primary. Default.                                                   |
| `"auto"`         | Anonymous requests read from the nearest replica. Authenticated users get read-your-writes consistency via bookmark cookies. |
| `"primary-first"`| Like `"auto"`, but the first query always goes to the primary. Use for sites with very frequent writes. |

#### How it works

- **Anonymous visitors** get `first-unconstrained` — reads go to the nearest replica for the lowest latency. Since anonymous users never write, they don't need consistency guarantees.
- **Authenticated users** (editors, authors) get bookmark-based sessions. After a write, a bookmark cookie ensures the next request sees at least that state.
- **Write requests** (`POST`, `PUT`, `DELETE`) always start at the primary database.
- **Build-time queries** (Astro content collections) bypass sessions entirely and use the primary directly.

> **Tip**
>
> Read replication must also be enabled on the D1 database itself via the Cloudflare dashboard or
> REST API. The `session` option only controls EmDash's client-side session management.

> **Incompatible with global_fetch_strictly_public**
>
> Do not combine `session: "auto"` or `"primary-first"` with the
> [`global_fetch_strictly_public`](https://developers.cloudflare.com/workers/configuration/compatibility-flags/#global-fetch-strictly-public)
> compatibility flag. The flag blocks the internal Cloudflare request that the D1 Sessions API uses
> to route queries, so session queries never complete.
>
> Since `@emdash-cms/cloudflare` 0.34.0, EmDash waits five seconds on the first session query in each
> Worker isolate, then logs `[emdash] A D1 session query hung for 5000ms`, re-runs `SELECT` queries on
> the primary database, and turns sessions off for the rest of that isolate. A write caught in that
> window fails and must be retried. Every new isolate repeats the delay, and no request reads from a
> replica. Earlier versions have no timeout: every SSR request hangs with nothing in the logs. The
> problem may only appear once replicas finish provisioning, so it can pass an initial post-deploy
> check.
>
> If your Worker needs `global_fetch_strictly_public`, keep `session` at its default, `"disabled"`.
> All queries then go to the primary database. See
> [#1273](https://github.com/emdash-cms/emdash/issues/1273).

> **Note**
>
> Anonymous visitors may see data that is a few seconds stale. For a CMS this is expected — content
> changes are not real-time, and if you have route caching enabled the response is already stale by
> definition.

## libSQL

libSQL is a fork of SQLite that supports remote connections. Use it when you need a remote database without Cloudflare D1.

```js title="astro.config.mjs"
import { libsql } from "emdash/db";

export default defineConfig({
	integrations: [
		emdash({
			database: libsql({
				url: process.env.LIBSQL_DATABASE_URL,
				authToken: process.env.LIBSQL_AUTH_TOKEN,
			}),
		}),
	],
});
```

### Configuration

| Option                  | Type     | Description                                                        |
| ----------------------- | -------- | ------------------------------------------------------------------ |
| `url`                   | `string` | Database URL (`libsql://...` or `file:...`)                        |
| `authToken`             | `string` | Runtime auth token for remote databases (optional for local)       |
| `migrationAuthTokenEnv` | `string` | Migration token variable name (default `TURSO_AUTH_TOKEN`)          |

### Local development

Use a local libSQL file during development:

```js
database: libsql({ url: "file:./data.db" });
```

## PostgreSQL

PostgreSQL is supported for Node.js deployments that need a full relational database.

```js title="astro.config.mjs"
import { postgres } from "emdash/db";

export default defineConfig({
	integrations: [
		emdash({
			database: postgres({
				connectionString: process.env.DATABASE_URL,
			}),
		}),
	],
});
```

### Configuration

You can connect with a connection string or individual parameters:

```js
// Connection string
database: postgres({
	connectionString: "postgres://user:password@localhost:5432/emdash",
});

// Individual parameters
database: postgres({
	host: "localhost",
	port: 5432,
	database: "emdash",
	user: "emdash",
	password: process.env.DB_PASSWORD,
	ssl: true,
});
```

| Option                         | Type      | Description                                          |
| ------------------------------ | --------- | ---------------------------------------------------- |
| `connectionString`             | `string`  | PostgreSQL connection URL                            |
| `host`                         | `string`  | Database host                                        |
| `port`                         | `number`  | Database port                                        |
| `database`                     | `string`  | Database name                                        |
| `user`                         | `string`  | Database user                                        |
| `password`                     | `string`  | Database password                                    |
| `ssl`                          | `boolean` | Enable SSL                                           |
| `pool.min`                     | `number`  | Minimum pool connections (default 0)                 |
| `pool.max`                     | `number`  | Maximum pool connections (default 10)                |
| `pool.connectionTimeoutMillis` | `number`  | Maximum connection wait (pg default: 0, no timeout)  |
| `pool.idleTimeoutMillis`       | `number`  | Idle-client lifetime (pg default: 10,000 ms)         |
| `migrationConnectionStringEnv` | `string` | Migration connection-string variable name (default `DATABASE_URL`) |

Set `pool.connectionTimeoutMillis` to a nonzero value to bound how long a request waits when PostgreSQL is unreachable or no pooled connection becomes available. Set `pool.idleTimeoutMillis` to `0` to keep idle clients open until the pool closes. Omitting either option preserves the pg default.

### Database role requirements

EmDash creates and updates its own PostgreSQL tables. Core migrations create and alter system and collection tables, content types create `ec_*` tables, and adding or removing a field alters its collection table. The configured PostgreSQL role therefore needs schema authority for the lifetime of the site, not only during initial setup.

Use one canonical role for EmDash. It needs:

- `CONNECT` on the database;
- `USAGE` and `CREATE` on the active schema;
- ownership of every EmDash table and function, either directly or through membership with `INHERIT` in the owning role; and
- `SELECT`, `INSERT`, `UPDATE`, and `DELETE` on those tables.

It does not need to be a superuser, have `CREATEDB` or `CREATEROLE`, or create extensions. PostgreSQL does not provide an `ALTER` or `DROP` table grant: those operations belong to the object owner and roles that inherit its privileges. Granting `ALL` on a table to a different role does not make that role an owner. EmDash does not run `SET ROLE`, so membership configured without inheritance is not sufficient.

> **Keep the role stable**
>
> PostgreSQL assigns each object to the role that creates it. Use a non-expiring role for the primary EmDash connection: remove any TTL or automatic expiration that would delete the role and replace it with another. Rotating the password for the same role is safe after updating the connection credentials, but changing the user in `DATABASE_URL` or a Hyperdrive configuration does not transfer existing objects. A replacement role may be able to read and write rows but still fail on the next migration or content-type change with `must be owner of table`.

Most installations can use the database's existing schema, commonly `public`. This is the simplest option when the database is dedicated to EmDash. In the examples below, `emdash_app` is the login role in EmDash's connection string; use an existing provider role or create a dedicated login. Grant it access with an administrative connection, substituting your database, schema, and role names:

```sql
GRANT CONNECT ON DATABASE app TO emdash_app;
GRANT USAGE, CREATE ON SCHEMA public TO emdash_app;
```

These grants let the role create new objects. They do not change the owner of existing tables; use the [PostgreSQL ownership repair runbook](https://emdashcmseverything.com/official-docs/deployment/core-migrations/#repair-mixed-postgresql-ownership) when an existing site has mixed owners.

EmDash uses PostgreSQL's active `current_schema()`. It does not create a schema or set `search_path`, so verify the connection before deployment:

```sql
SELECT
  current_database(),
  session_user,
  current_user,
  current_schema(),
  current_setting('search_path');
```

#### Optional: use a dedicated schema

Use a dedicated schema when EmDash shares a database with another application or when you want its objects isolated from `public`. This is optional and is easiest to configure before the first EmDash setup. A database dedicated to EmDash does not need a separate schema.

Assuming the canonical `emdash_app` role already exists, create and select its schema with an administrative connection:

```sql
GRANT CONNECT ON DATABASE app TO emdash_app;
CREATE SCHEMA emdash AUTHORIZATION emdash_app;
ALTER ROLE emdash_app IN DATABASE app SET search_path = emdash;
```

This does not move an existing installation from `public` or repair mixed ownership. Existing sites should keep their current schema and use the [PostgreSQL ownership repair runbook](https://emdashcmseverything.com/official-docs/deployment/core-migrations/#repair-mixed-postgresql-ownership) instead.

### Connection pooling

The adapter uses `pg.Pool`. Tune pool size based on your deployment:

```js
database: postgres({
	connectionString: process.env.DATABASE_URL,
	pool: { min: 2, max: 20 },
});
```

> **Note**
>
> PostgreSQL requires the `pg` package. Install it as a dependency:
> ```bash
> pnpm add pg
> ```

## Hyperdrive

Use the `hyperdrive()` adapter to run EmDash on **Cloudflare Workers** backed by an existing PostgreSQL — or Postgres-compatible (e.g. PlanetScale Postgres) — database. [Hyperdrive](https://developers.cloudflare.com/hyperdrive/) pools and accelerates the connection over Cloudflare's network; EmDash's PostgreSQL dialect runs the queries.

```js title="astro.config.mjs"
import { hyperdrive, r2 } from "@emdash-cms/cloudflare";

export default defineConfig({
	integrations: [
		emdash({
			database: hyperdrive({ binding: "HYPERDRIVE" }),
			storage: r2({ binding: "MEDIA" }),
		}),
	],
});
```

### Requirements

- `pg >= 8.16.3` installed in your site (`pnpm add pg`)
- `compatibility_flags: ["nodejs_compat"]`
- `compatibility_date >= "2024-09-23"`

### Setup

First [prepare the PostgreSQL role](#database-role-requirements). Then create the Hyperdrive configuration with that role's connection string and add the binding to your Wrangler config:

```sh
wrangler hyperdrive create emdash-db \
  --connection-string "postgres://user:password@host/db?sslmode=verify-full" \
  --caching-disabled
```

#### wrangler.jsonc

```jsonc
{
  "hyperdrive": [
    {
      "binding": "HYPERDRIVE",
      "id": "<your-hyperdrive-id>"
    }
  ]
}
```

#### wrangler.toml

```toml
[[hyperdrive]]
binding = "HYPERDRIVE"
id = "<your-hyperdrive-id>"
```

> **Disable query caching on the primary binding**
>
> Hyperdrive's query cache is **on by default and must be turned off** for the primary EmDash binding. EmDash runs its own caching layer and relies on read-after-write consistency — the admin and setup wizard write a row and immediately read it back. With caching on, Hyperdrive can serve the pre-write result within its TTL, which corrupts setup (e.g. "collection already exists", then half-created tables) and shows editors stale content. Disable it:
>
> ```sh
> wrangler hyperdrive update <id> --caching-disabled
> # or at create time: wrangler hyperdrive create ... --caching-disabled
> ```
>

> **Co-locate the Worker with your database**
>
> The request path makes multiple round trips, so pair the deploy with a [Smart Placement](https://developers.cloudflare.com/workers/configuration/smart-placement/) hint to run the Worker in the Cloudflare data center closest to your database's region:
>
> ```jsonc
> // wrangler.jsonc
> { "placement": { "region": "aws:us-east-1" } }
> ```
>

### Configuration

| Option                         | Type     | Default                                                        | Description                                                                                                      |
| ------------------------------ | -------- | -------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- |
| `binding`                      | `string` | `"HYPERDRIVE"`                                                 | Primary (caching-disabled) Hyperdrive binding name                                                               |
| `cachedBinding`                | `string` | —                                                              | Optional caching-enabled binding for anonymous reads (see below)                                                 |
| `preferUncachedAfterWriteMs`   | `number` | `60000`\*                                                      | After a content publish, prefer `binding` for this many ms on anonymous public reads (match Hyperdrive `max_age`) |
| `migrationConnectionStringEnv` | `string` | `CLOUDFLARE_HYPERDRIVE_LOCAL_CONNECTION_STRING_<BINDING>`      | Environment variable containing the direct PostgreSQL origin URL for `emdash migrate`                            |
| `max`                          | `number` | `5`                                                            | Max size of the in-Worker connection pool to Hyperdrive                                                          |

\*Default `60000` applies only when `cachedBinding` is set; ignored otherwise.

### Serving anonymous reads from cache

By default you disable Hyperdrive caching entirely, because the admin and writes need read-after-write consistency. But **anonymous public requests using `GET` or `HEAD`** can tolerate a short staleness window. If that trade-off is acceptable, run **two Hyperdrive configurations over the same database**: one with caching off (the primary `binding`) and one with caching on (`cachedBinding`). EmDash routes those anonymous public requests through the cache-enabled binding and every other request through the uncached primary.

```sh
# Primary — caching OFF (used by admin, auth'd requests, writes, migrations)
wrangler hyperdrive create emdash-db \
  --connection-string "postgres://user:password@host/db?sslmode=verify-full" \
  --caching-disabled

# Cached — SAME database role and connection string, caching ON
wrangler hyperdrive create emdash-db-cached \
  --connection-string "postgres://user:password@host/db?sslmode=verify-full"
```

```jsonc title="wrangler.jsonc"
{
	"hyperdrive": [
		{ "binding": "HYPERDRIVE", "id": "<caching-disabled-id>" },
		{ "binding": "HYPERDRIVE_CACHED", "id": "<caching-enabled-id>" }
	]
}
```

```js title="astro.config.mjs"
database: hyperdrive({ binding: "HYPERDRIVE", cachedBinding: "HYPERDRIVE_CACHED" });
```

This is the [two-configuration pattern](https://developers.cloudflare.com/hyperdrive/configuration/query-caching/#disable-caching) Cloudflare documents for caching. EmDash decides which binding to use per request:

- **Anonymous reads of public-site paths** (`GET`/`HEAD`, no session, not under `/_emdash`) → cache-enabled `cachedBinding`, **except** for a short window after a content publish (default 60s; set `preferUncachedAfterWriteMs` to your Hyperdrive `max_age`) when EmDash prefers the uncached `binding` so a rebuild cannot reseed edge/object caches from still-stale Hyperdrive results.
- **Authenticated requests** (editors, authors) → uncached `binding`.
- **Mutation requests** (`POST`, `PUT`, `PATCH`, `DELETE`, including anonymous ones) → uncached `binding`.
- **Any request under `/_emdash`** (admin, setup, auth, internal APIs), even an anonymous `GET` → uncached `binding`.
- **Runtime migrations and cold-start** → always the primary `binding`.
- **Deployment-managed migrations** → connect directly to the PostgreSQL origin using `migrationConnectionStringEnv`; they never use either Hyperdrive binding.

> **Note**
>
> The `/_emdash` carve-out matters: the setup wizard and login-state checks are
> anonymous `GET`s that read a row written moments earlier. Caching them would
> loop the setup wizard or show stale auth state, so they always use the
> uncached primary.

> **Caution**
>
> Both configurations should use the **same PostgreSQL role**, database, and schema. If you later replace the primary configuration with a different role, PostgreSQL does not transfer the existing objects with it. Set `preferUncachedAfterWriteMs` to your cached config's `max_age` (default 60s) so post-publish public rebuilds read fresh SQL. Outside that window, anonymous public reads can still be up to `max_age` stale relative to non-content writes. Only opt in if that trade-off is acceptable; otherwise omit `cachedBinding` and keep caching disabled.

> **Note**
>
> Cross-isolate post-write routing uses the configured distributed Object Cache backend to share the last-content-write timestamp. Without a distributed Object Cache, the prefer-uncached window is known only to the Worker isolate that handled the write.

> **Note**
>
> **Sandboxed plugins are D1-only.** The sandbox plugin bridge talks to a D1 binding directly, independent of the configured adapter, so sandboxed plugins aren't available on a Hyperdrive deployment.

#### Optional: use a separate cached role

Migrations, setup, authenticated requests, and explicit write requests always use the primary `binding`. A separate role for `cachedBinding` does not need schema ownership or `CREATE`, but it needs `CONNECT`, schema `USAGE`, and `SELECT` on every table used by the public site.

Anonymous public `GET` and `HEAD` requests can also record redirect hits and 404s. To preserve those features, the cached role additionally needs `UPDATE` on `_emdash_redirects` and `SELECT`, `INSERT`, `UPDATE`, and `DELETE` on `_emdash_404_log`. Plugins or application code that writes during a public `GET` or `HEAD` may require more. Use the same role for both bindings unless you have tested the site with a restricted cached role.

Add the cached role after EmDash has completed its initial migrations. The examples below use the optional `emdash` schema; substitute your active schema, such as `public`. Create the login and database settings with your provider's administrative role:

```sql
CREATE ROLE emdash_cached LOGIN PASSWORD 'replace-with-a-secret';
GRANT CONNECT ON DATABASE app TO emdash_cached;
ALTER ROLE emdash_cached IN DATABASE app SET search_path = emdash;
```

Then connect as `emdash_app`, the schema and table owner, to grant access to existing and future tables:

```sql
GRANT USAGE ON SCHEMA emdash TO emdash_cached;
GRANT SELECT ON ALL TABLES IN SCHEMA emdash TO emdash_cached;
GRANT UPDATE ON emdash._emdash_redirects TO emdash_cached;
GRANT SELECT, INSERT, UPDATE, DELETE ON emdash._emdash_404_log TO emdash_cached;

ALTER DEFAULT PRIVILEGES IN SCHEMA emdash
  GRANT SELECT ON TABLES TO emdash_cached;
```

Connect with both roles and verify they report the same `current_database()` and `current_schema()` before enabling `cachedBinding`. On a shared schema, `GRANT SELECT ON ALL TABLES` also exposes unrelated tables. Grant access to individual EmDash tables instead, and update those grants when collections or other schema objects are added.

## Core migrations

EmDash runs core migrations automatically by default for every supported dialect. Astro build and sync also emit a validated, secret-free `.emdash/migrations.json`, which `emdash migrate` can apply before deployment. SQLite, libSQL, PostgreSQL, D1, and the direct PostgreSQL origin behind Hyperdrive have deployment executors.

See [Manage Core Database Migrations](https://emdashcmseverything.com/official-docs/deployment/core-migrations/) for target credentials, CI serialization, `auto`/`check`/`manual` runtime policy, and recovery from unknown records or ambiguous D1 writes.

For PostgreSQL, runtime migrations run through the configured connection; Hyperdrive runtime migrations always use its primary binding. Deployment-managed Hyperdrive migrations connect directly to the PostgreSQL origin. Core migrations may create tables, indexes, and functions, alter or drop columns and constraints, and update existing rows. A role that can connect and modify rows but does not own the existing EmDash objects is not sufficient. The setup wizard cannot repair missing database privileges because runtime migrations run before setup.

Until the setup wizard has been completed, EmDash also applies the schema and structure of a seed file once, on the first request. Sample content from the seed is applied only when an administrator chooses it in the setup wizard. The seed is read from `.emdash/seed.json`, the path in `package.json#emdash.seed`, or `seed/seed.json` — whichever is found first — and inlined into the build at compile time. If none is present, a built-in default seed is used. Subsequent boots against an existing database leave its content alone.

## Use separate databases for separate environments

Give development, preview, staging, and production their own database. A preview deployment pointed at production can run core migrations or destructive content-model commands against live data.

For Cloudflare, define each D1 or Hyperdrive binding under the matching Wrangler environment and pass `--env` to Wrangler commands. For Node.js, inject a different database URL into each runtime environment. Keep credentials in runtime secrets, not in `astro.config.mjs`.