netlify-database

Files (7)

• SKILL.md
• cli-commands.md · references/
• legacy-extension.md · references/
• local-dev.md · references/
• migration-from-extension.md · references/
• migrations.md · references/
• operational-footguns.md · references/

SKILL.md (first lines)

# Netlify Database

Zero-config managed Postgres. Install `@netlify/database`, write migrations under `netlify/database/migrations/`, deploy — Netlify provisions the DB and applies migrations automatically. Queryable from Functions, Edge Functions, Builds, and Agent Runners.

## Modern client (reach for this)

```ts
import { getDatabase } from "@netlify/database";

const db = getDatabase();               // auto-selects connection for the runtime
const userId = 42;
const users = await db.sql`SELECT * FROM users WHERE id = ${userId}`;  // auto-parameterized
```

Own driver / ORM instead:
```ts
import { getConnectionString } from "@netlify/database";
const connectionString = getConnectionString();  // correct branch for this env
```

**Legacy — do NOT use for new code:** `import { neon } from "@netlify/neon"`. Superseded by `@netlify/database`. Replace `neon()` calls with the Drizzle `netlify-db` adapter or a Postgres driver via `getConnectionString()`. The legacy env var `NETLIFY_DATABASE_URL` is replaced by `NETLIFY_DB_URL`.

## Where things go

| What | Location |
|------|----------|
| Migrations | `netlify/database/migrations/` (SQL files or subdirs with `migration.sql`) |
| Query code | Functions (`netlify/functions/`), Edge Functions |
| Drizzle schema | `db/schema.ts` (convention) |
| Drizzle client | `db/index.ts` (convention) |
| Connection string | `NETLIFY_DB_URL` env var, or `getConnectionString()` |

## Querying

`getDatabase(options?)` returns a client with `sql` and `pool`. `options.connectionString` overrides the auto-provisioned one; `options.debug` enables logging.

```ts
const db = getDatabase();
const active = await db.sql`SELECT * FROM users WHERE active = ${true}`;
await db.sql`INSERT INTO users (name, email) VALUES (${"Ada"}, ${"ada@example.com"})`;
await db.sql`UPDATE users SET name = ${"Ada Lovelace"} WHERE id = ${1}`;
await db.sql`DELETE FROM users WHERE id = ${1}`;

// Type the rows
interface User { id: number; name: string; email: string; }
const typed = await db.sql<User>`SELECT * FROM users`;

// Stream
for await (const row of db.sql`SELECT * FROM users`.stream()) { /* ... */ }
for await (const chunk of db.sql`SELECT * FROM users`.chunked(100)) { /* ... */ }
```

`SQLTemplate` methods: `execute()` → `Promise<T[]>`, `stream()` → `AsyncGenerator<T>`, `chunked(n)` → `AsyncGenerator<T[]>`, `toSQL()` → raw SQL + params without executing.

`sql` helpers:
- `sql.identifier(value)` — safe table/column name. String, string[], or `{ schema, table, column, as }`.
- `sql.values(rows)` — bulk-insert values list from a 2D array.
- `sql.default` — the SQL `DEFAULT` keyword.
- `sql.raw(value)` — **injects unparameterized SQL; bypasses injection protection. Only for trusted constants (e.g. `"DESC"`), never user input.**
- `sql.unsafe(query, params?, { rowMode })` — raw query string with `$1` params; `rowMode` is `"array"` or `"object"`.

### Transactions — use `pool`

`db.pool` is a [`pg.Pool`](https://node-postgres.com/apis/pool). `BEGIN`/queries/`COMMIT` must run on the same connection:
```ts
const client = await db.pool.connect();
try {
  await client.query("BEGIN");
  await client.query("INSERT INTO users (name, email) VALUES ($1, $2)", ["Ada", "ada@example.com"]);
  await client.query("INSERT INTO posts (author_id, title) VALUES ($1, $2)", [1, "First post"]);
  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();
}
```

Own drivers:
```ts
import { getConnectionString } from "@netlify/database";
import pg from "pg";
const pool = new pg.Pool({ connectionString: getConnectionString() });

// or the `postgres` driver via env var
import postgres from "postgres";
const sql = postgres(process.env.NETLIFY_DB_URL);
```

## Drizzle ORM

**Install both packages from `@beta` — required.** `latest` lacks the `drizzle-orm/netlify-db` adapter and will fail.
```bash
npm install @netlify/database drizzle-orm@beta
npm install -D drizzle-kit@beta
```

`drizzle.config.ts` — you **MUST** set `out` to the Netlify migrations directory or Netlify won't apply generated migrations:
```ts title="drizzle.config.ts"
import { defineConfig } from "drizzle-kit";
export default defineConfig({
  dialect: "postgresql",
  schema: "./db/schema.ts",
  out: "netlify/database/migrations",   // NOT the default "drizzle"
});
```

```ts title="db/schema.ts"
import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
  id: serial().primaryKey(),
  name: text().notNull(),
  email: text().notNull().unique(),
  createdAt: timestamp().defaultNow(),
});
```

```ts title="db/index.ts"

From netlify/context-and-tools, MIT.

More from netlify/context-and-tools