# Database

Every Bastide project can carry a PostgreSQL 17 database, with its data API, authentication and storage. This page explains how your application connects to it, how to read and write it from the console, through an agent or through the API, which extensions are allowed, and what each tier changes to connections, timeouts and isolation.

## What you get

- **One database per project**, with a single set of roles. Every environment class of the project (production, staging, previews) talks to the **same** database: there is no per-branch database today (see [Branches and migrations](branches.md)).
- **A connection pooler** (Supavisor) in front of the database. Your application never reaches PostgreSQL directly.
- **A data API** in the Supabase format under the project address: `/rest/v1` (PostgREST), `/auth/v1`, `/storage/v1`, `/realtime/v1`.
- **pgvector**, built into the cluster image and allowed on every tier.

The database is created with the project when you create it with `project_create_with_backend`, which queues its provisioning. Its state is read with `backend_read`: `provisioning`, `ready`, `failed`, `deleting` or `retained`. A project without a database shows on the **Données** (Data) screen: « Ce projet n'a pas de base de données. » (this project has no database).

## Connecting from your application

On every deployment, Bastide sets two variables on the target environment, once the database is ready:

| Variable | Pooler mode | Use |
| --- | --- | --- |
| `DATABASE_URL` | transaction | The application's default address: Prisma, Drizzle, `pg`, NextAuth. |
| `DIRECT_URL` | session, with `connection_limit=1` | Migrations (`prisma migrate` holds a session advisory lock) and anything that needs session state. |

Both addresses carry the same role and password. The user name has the form `<role>.<database>`: that is how the pooler finds your database.

- **Your variable wins.** If you set `DATABASE_URL` or `DIRECT_URL` yourself (for instance to a database hosted elsewhere), Bastide leaves it alone.
- **Do not add `pgbouncer=true`.** With it, Prisma sends simple queries that transaction mode refuses (`EPSSIMPLEQUERY`). Without it, prepared statements work in both modes.
- **An isolated environment receives nothing.** A preview forked from a third-party repository carries no sensitive variable, and these two addresses are sensitive.

> **Attention.** The database is reachable only from your project's containers, on the platform's private network. No direct connection from your machine (`psql`, a desktop client) is open. To read or write by hand, use the console's SQL editor, an agent or the API, described below.

### What transaction mode does not keep

Transaction mode shares server connections between your clients. Three behaviours follow, each reproduced by a test:

| Ref | What happens | What to do instead |
| --- | --- | --- |
| LIM-03 | `NOTIFY` does not reach a client doing `LISTEN`. | Listen from a session-mode connection (`DIRECT_URL`). |
| LIM-04 | `pg_advisory_lock` does not exclude another connection of the same project. | `pg_advisory_xact_lock` inside a transaction, or session mode. |
| LIM-05 | A `SET`, a temporary table or a `WITH HOLD` cursor leaks into the next connection. | `SET LOCAL`, `ON COMMIT DROP`, a cursor without `WITH HOLD`, or session mode. |

## The data API

Your project's address is `https://<identifier>.donnees.bastide.app`, where the identifier is the backend's (`bak_…`, returned by `backend_read`) without the `bak_` prefix, in lower case. Supabase paths are served there without rewriting: the key in the `apikey` header, a session token in `Authorization`.

- Only the `public` schema is served by the API.
- A response carries at most 1000 rows, truncated without error (LIM-01): paginate with `Range`, or `limit` and `offset`.
- A resource is embedded only along a declared foreign key, otherwise 400 `PGRST200` (LIM-02).
- A table is granted to the API roles only if it has row-level security **and** at least one policy (LIM-11). See [Data security](data-security.md).

Publishable and secret keys are managed from the « Clés et points d'entrée » (keys and endpoints) section of **Données**: see [Authentication](auth.md).

## The console's SQL editor

Open **Données**, then the **Éditeur SQL** (SQL editor) section. The table list is on the left; a click shows rows or structure.

1. **Read-only by default.** The box is ticked: the database then refuses any write, whatever the statement looks like. One statement at a time, at most one hundred rows returned. Ctrl+Enter (Cmd+Enter on a Mac) runs it.
2. **Switching to write** requires the maintainer rank, which is only granted to an account holding a second factor (see [Account](account.md)). The button becomes « Mesurer l'effet » (measure the effect): the statement is executed then rolled back, and the dialog shows how many rows would be inserted, updated or deleted, and which tables would be dropped or truncated.
3. **Applying** replays the statement and commits only if the effect is the same. If the database changed in between, the count is redone and the dialog says so.
4. **A destructive effect** (table dropped or truncated, rows deleted) asks you to retype the number of affected rows.

Every applied write enters the audit log with its effect and your name. A result can be copied as CSV. Whatever the database returns (column names, cells, messages) is displayed as text, never interpreted.

## Tables

The « Tables » section of **Données** shows, in three columns, the table list, the first hundred rows of the one you pick, and on the right the essentials of the database: endpoints, state of the REST API, keys, realtime and security posture. The **Structure** button switches to the columns and their types. A table of the `public` schema without row-level security carries the label « Exposée sans politique » (exposed without a policy).

These reads go through the same operation as the editor, `database.sql.read`: read-only and audited.

## Through an agent, the command line or the API

| Action | MCP tool | Command line | API |
| --- | --- | --- | --- |
| Read | `database_sql_read` | `bastide database sql read` | `GET /v1/projects/{projectId}/database/sql` |
| Write | `database_sql_write` | `bastide database sql write` | `POST /v1/projects/{projectId}/database/sql` |
| Read the database and its keys | `backend_read` | `bastide backend read` | `GET /v1/projects/{projectId}/backend` |

A read (rank `developer`, scope `db:read`):

```json
{
  "projectId": "prj_01JQ8ZK4M7NP2RSTVWXYZ34567",
  "sql": "select id, total from commandes order by id desc limit 10"
}
```

A write (rank `maintainer`, scope `db:write`) takes two steps. The first call executes, measures, rolls back and returns a nonce; the second, with `confirmationNonce`, commits only if the effect is identical. A destructive effect also requires `typedConfirmation` (the number of rows, in digits) and the `db:destructive` scope. A person approves the call before it goes out; a service account cannot write.

```json
{
  "projectId": "prj_01JQ8ZK4M7NP2RSTVWXYZ34567",
  "sql": "delete from commandes where etat = 'annulee'",
  "idempotencyKey": "commandes-annulees-2026-10-05"
}
```

An agent reads no column classified as personal data: the statement is refused with `colonne_personnelle_refusee` (see [Data security](data-security.md)).

## Allowed extensions

An extension is installed with `create extension`, in a migration or in the SQL editor in write mode. The database accepts only those on the allow-list of the project's tier.

| Tier | Allowed extensions |
| --- | --- |
| Discovery | `btree_gin`, `btree_gist`, `citext`, `cube`, `earthdistance`, `fuzzystrmatch`, `hstore`, `intarray`, `isn`, `lo`, `ltree`, `moddatetime`, `pg_trgm`, `pgcrypto`, `seg`, `tablefunc`, `tsm_system_rows`, `unaccent`, `uuid-ossp`, `vector` |
| Standard, Pro, Sovereign | Discovery's, plus `autoinc`, `bloom`, `dict_int`, `dict_xsyn`, `insert_username`, `intagg`, `refint`, `sslinfo`, `tcn`, `tsm_system_time` |

The price grid states ceilings (20 on Discovery, 50 on Standard): the Standard list deliberately names fewer. Arbitrary extensions on Pro and Sovereign require a dedicated instance, which is not served yet: a Pro project follows the Standard list today.

Never allowed on the shared cluster: `adminpack`, `amcheck`, `dblink`, `file_fdw`, `pageinspect`, `pg_buffercache`, `pg_freespacemap`, `pg_prewarm`, `pg_stat_statements`, `pg_surgery`, `pg_visibility`, `pg_walinspect`, `pgrowlocks`, `pgstattuple`, `plperl`, `plpython3u`, `pltcl`, `postgres_fdw`, `xml2`. They open the network from the data node, the file system, the shared cache, or arbitrary code.

Changing tier replaces the list but removes no extension already installed: its next installation is what would be refused.

## Connections and timeouts by tier

| Tier | Client / server connections | Statement timeout | Idle-in-transaction timeout |
| --- | --- | --- | --- |
| Discovery | 30 / 3 | 30 s | 60 s |
| Standard | 60 / 5 | 30 s | 60 s |
| Pro | 200 / 15 | 300 s | 300 s |
| Sovereign | negotiated | 300 s | 300 s |

- **Beyond the client connections**, the pooler refuses the extra client with a message naming the ceiling (« max client connections reached, limit: 60 »).
- **Beyond the timeout**, PostgreSQL cancels the statement (code `57014`) or closes a connection left idle in a transaction (code `25P03`). The timeout in force is read with `SHOW statement_timeout`.
- **A tier change** takes effect without a restart: the connections of that project alone are closed once, and reopen under the new limits.
- **Realtime** holds one session-mode connection per instance while a client is connected: it counts against your server ceiling (three on Discovery).

## Isolation by tier

| Tier | Database isolation, per the price grid | What is served today |
| --- | --- | --- |
| Discovery | Shared cluster | Shared cluster, at most 800 projects per cluster |
| Standard | Shared cluster | Shared cluster, at most 400 projects per cluster once it holds a Standard project |
| Pro | Dedicated PostgreSQL instance | Not yet: the project stays on the shared cluster, with the Pro limits above |
| Sovereign | Dedicated instance and node | Not yet: on quotation |

On the shared cluster, each project has its own database and roles; the project's roles connect only through the pooler. The number of projects per cluster is a published risk choice: a cluster incident hits everything it carries at once.

## Common errors

- **`variables_requises_manquantes` on `DATABASE_URL`**: the database was not ready at deployment time. Wait for the `ready` state (`backend_read`), then redeploy.
- **`EPSSIMPLEQUERY`**: remove `pgbouncer=true` from the address.
- **`backend_indisponible`**: the platform cannot reach the database; the message names the cause.
- **`colonne_personnelle_refusee`**: an agent's statement touches a column classified as personal. A person reads it from the console.
- **`42704` on a `grant … to authenticated`**: roles carry the database name as a prefix. Enable row-level security and write a policy: the grants follow (LIM-11).
- **`57014`**: the statement exceeded the tier's timeout. Add an index, split the work, or change tier.

## See also

- [Branches and migrations](branches.md), [Backups](backups.md), [Data security](data-security.md), [Authentication](auth.md)
- [Limits by tier](limits.md), [Migrate from Supabase](from-supabase.md)
- Reference: [database.sql.read](../reference/operations/database.sql.read.md), [database.sql.write](../reference/operations/database.sql.write.md), [backend.read](../reference/operations/backend.read.md)