# Database

Choose and configure a database for your Nextly application. PostgreSQL is the recommended production database; MySQL is supported; SQLite is for local demos only.

Nextly stores data through a small set of dialect-specific adapter packages built on a shared [Drizzle ORM](https://orm.drizzle.team/) core. Pick the adapter that matches your database, install it as a regular dependency, and Nextly handles dialect differences (placeholders, identifier escaping, RETURNING fallbacks, error classification) automatically.

## Supported databases

| Database                                                     | Package                      | Driver           | Use it for                                                                                                                                |
| ------------------------------------------------------------ | ---------------------------- | ---------------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| [PostgreSQL](https://nextlyhq.com/docs/database/postgres.md) | `@nextlyhq/adapter-postgres` | `pg`             | **Recommended for production.** Full feature set: JSONB, arrays, full-text search, ILIKE, savepoints, RETURNING.                          |
| [MySQL](https://nextlyhq.com/docs/database/mysql.md)         | `@nextlyhq/adapter-mysql`    | `mysql2`         | Supported. Use it when you already run MySQL or a MySQL-compatible cloud database. Some features are emulated -- see the page.            |
| [SQLite](https://nextlyhq.com/docs/database/sqlite.md)       | `@nextlyhq/adapter-sqlite`   | `better-sqlite3` | **Local demos only.** Single-writer, file-based, no SSL. Not recommended for serious local development -- run Postgres in Docker instead. |

For minimum versions, cloud-provider compatibility, and the policy on MySQL-compatible variants (MariaDB, TiDB, Aurora MySQL, PlanetScale, Vitess), see [database support](https://nextlyhq.com/docs/database/support.md).

## Quick recommendation

> **Use PostgreSQL.** It is the recommended production database, the only adapter with the full feature set, and the easiest to run locally with Docker (see [Local development with Docker](https://nextlyhq.com/docs/database/postgres.md#local-development-with-docker)). Pick MySQL only if you already run it. Pick SQLite only for one-off local demos.

## Installation

Each adapter is a regular npm package. Install the adapter and its peer driver in your project.

**pnpm**

```bash
# PostgreSQL (recommended)
pnpm add @nextlyhq/adapter-postgres pg

# MySQL
pnpm add @nextlyhq/adapter-mysql mysql2

# SQLite (local demos only)
pnpm add @nextlyhq/adapter-sqlite better-sqlite3
```

**npm**

```bash
# PostgreSQL (recommended)
npm install @nextlyhq/adapter-postgres pg

# MySQL
npm install @nextlyhq/adapter-mysql mysql2

# SQLite (local demos only)
npm install @nextlyhq/adapter-sqlite better-sqlite3
```

**yarn**

```bash
# PostgreSQL (recommended)
yarn add @nextlyhq/adapter-postgres pg

# MySQL
yarn add @nextlyhq/adapter-mysql mysql2

# SQLite (local demos only)
yarn add @nextlyhq/adapter-sqlite better-sqlite3
```

**bun**

```bash
# PostgreSQL (recommended)
bun add @nextlyhq/adapter-postgres pg

# MySQL
bun add @nextlyhq/adapter-mysql mysql2

# SQLite (local demos only)
bun add @nextlyhq/adapter-sqlite better-sqlite3
```

## Selecting a dialect

Nextly's runtime picks the dialect from environment variables, in this order:

1. **`DB_DIALECT`** -- explicit override. One of `postgresql`, `mysql`, `sqlite`.
2. **`DATABASE_URL`** protocol -- `postgres://` / `postgresql://` -> PostgreSQL, `mysql://` -> MySQL, `file:` (or `*.db` / `*.sqlite`) -> SQLite.
3. **Fallback** -- PostgreSQL with a warning. Always set `DB_DIALECT` explicitly to avoid the warning.

Minimal `.env` files for each dialect:

```bash title=".env (PostgreSQL)"
DB_DIALECT=postgresql
DATABASE_URL=postgres://nextly:nextly@localhost:5432/nextly_dev
```

```bash title=".env (MySQL)"
DB_DIALECT=mysql
DATABASE_URL=mysql://nextly:nextly@localhost:3306/nextly_dev
```

```bash title=".env (SQLite, local demo only)"
DB_DIALECT=sqlite
DATABASE_URL=file:./data/nextly.db
```

## Feature comparison

The adapters share a single CRUD API, but underlying dialect capabilities differ. The shared `DrizzleAdapter` base reports per-dialect capabilities at runtime; this table is a quick reference.

| Feature                                               | PostgreSQL                                                                          | MySQL                                                               | SQLite                                           |
| ----------------------------------------------------- | ----------------------------------------------------------------------------------- | ------------------------------------------------------------------- | ------------------------------------------------ |
| JSONB columns                                         | Yes                                                                                 | No (JSON only)                                                      | No (JSON as TEXT)                                |
| Array columns                                         | Yes                                                                                 | No                                                                  | No                                               |
| `RETURNING` clause                                    | Yes                                                                                 | No (extra `SELECT` after writes)                                    | Yes (3.35+)                                      |
| Case-insensitive text matching (`ILIKE`, `ICONTAINS`) | `LOWER(...) LIKE LOWER(...)`; ASCII letters only under the `C` or `POSIX` collation | `LOWER(...) LIKE LOWER(...)`, even under a case-sensitive collation | `LOWER(...) LIKE LOWER(...)`, ASCII letters only |
| Savepoints (nested transactions)                      | Yes                                                                                 | Disabled by adapter                                                 | Yes                                              |
| Full-text search                                      | `tsvector`                                                                          | `FULLTEXT` indexes                                                  | FTS5 (extension)                                 |
| Upsert syntax                                         | `ON CONFLICT DO UPDATE`                                                             | `ON DUPLICATE KEY UPDATE`                                           | `ON CONFLICT DO UPDATE`                          |
| Connection pooling                                    | `pg.Pool`                                                                           | `mysql2` pool                                                       | None (single file handle)                        |
| Generated columns                                     | Yes                                                                                 | Yes                                                                 | Yes                                              |
| Max query parameters                                  | 65,535                                                                              | 65,535                                                              | 999                                              |
| SSL / TLS                                             | Yes                                                                                 | Yes                                                                 | N/A (local file)                                 |
| Deadlock retry                                        | On `40001` / `40P01` (configurable)                                                 | On error 1213 (configurable)                                        | N/A                                              |
| Auto-detected providers                               | Neon, Supabase, standard                                                            | None                                                                | None                                             |

## Text matching

The adapter's where clauses have four text operators, and they compile the same way on every dialect:

| Operator    | Matches     | Case                               | `%` and `_` in the value |
| ----------- | ----------- | ---------------------------------- | ------------------------ |
| `LIKE`      | a pattern   | as the column's collation compares | wildcards                |
| `ILIKE`     | a pattern   | ignored                            | wildcards                |
| `CONTAINS`  | a substring | as the column's collation compares | literal characters       |
| `ICONTAINS` | a substring | ignored                            | literal characters       |

A collection's `contains` and `search` filters, its search box in the admin and the API, and the admin's search of collections, Singles and field groups all match this way.

Use `ICONTAINS` for a search box: it finds the text the person typed, whatever its case, and a `%` or `_` they type is searched for rather than read as a wildcard. `CONTAINS` and `ICONTAINS` escape `%`, `_` and the escape character `!` themselves; with `LIKE` and `ILIKE` the value is a pattern you build, so escape anything that should match literally.

```ts
import type { WhereClause } from "@nextlyhq/adapter-drizzle/types";

// Finds "Spring launch", "SPRING SALE" and "Last spring".
const where: WhereClause = {
  and: [{ column: "title", op: "ICONTAINS", value: "spring" }],
};
```

This matters on MySQL: a column under a case-sensitive collation such as `utf8mb4_bin` makes a plain `LIKE` (and `CONTAINS`) case-sensitive, while `ILIKE` and `ICONTAINS` lower-case both sides and ignore case there too. Which letters count as the same in another case is the database's own `lower()`, following the column's collation. On MySQL that is the Unicode case mapping of the collation's version, so a `0900` collation knows more letters than a legacy one such as `utf8mb4_unicode_ci`. On PostgreSQL a linguistic collation such as `en_US.UTF-8` folds non-ASCII letters too. SQLite, and a PostgreSQL column under the `C` or `POSIX` collation, fold ASCII letters only, so there "É" and "é" still differ.

All four are for text columns. Matching through `LOWER()` cannot use an ordinary index on the column, so on a large table a substring search reads every row.

## Next steps

* [PostgreSQL setup](https://nextlyhq.com/docs/database/postgres.md) -- recommended for production
* [MySQL setup](https://nextlyhq.com/docs/database/mysql.md) -- supported with caveats
* [SQLite setup](https://nextlyhq.com/docs/database/sqlite.md) -- local demos only
* [Database support](https://nextlyhq.com/docs/database/support.md) -- minimum versions, cloud providers, MySQL variant policy
* [Environment variables](https://nextlyhq.com/docs/configuration/environment.md) -- canonical env var reference
* [Production migrations](https://nextlyhq.com/docs/guides/production-migrations.md) -- how schema changes ship to production