DocsGet started
Connecting
Pooled and direct connection strings, Prisma, scale to zero, and SQL over HTTP.
Every branch has two Postgres connection strings, pooled and direct. This page covers which one to use where, how to get them, and how to query over HTTP when a runtime cannot open a TCP connection.
Get your connection strings
From the CLI:
bata db url --json # both strings for the default project's primary branch
bata db url --branch preview --json # another branch
bata db url # the direct string alone, handy in scripts--json prints direct, pooled, and the project and branch they belong to.
Pass --project <project-id> to pick a project other than the linked or
default one.
From the console: open the project. Its connection details show both strings.
From the API, with a key that has the sql scope:
curl https://api.batadata.com/v1/connection-info/<project-id> \
-H "Authorization: Bearer <your-api-key>"The response lists every branch:
{
"project_id": "<project-id>",
"project_name": "my-app",
"region": "<region>",
"connections": [
{
"branch_id": "<branch-id>",
"branch_name": "main",
"is_primary": true,
"compute_status": "active",
"direct": "postgresql://...",
"pooled": "postgresql://...",
"params": { "host": "...", "pooled_host": "...", "port": 5432, "database": "...", "user": "...", "password": "...", "ssl": true, "sslmode": "require" }
}
]
}Add ?branch_id=<branch-id> to get one branch only.
What the strings look like
Pooled: postgresql://<user>:<password>@<endpoint-id>-pooler.<region>.db.batadata.com:5432/<database>?sslmode=require&options=endpoint%3D<endpoint-id>-pooler
Direct: postgresql://<user>:<password>@<endpoint-id>.<region>.db.batadata.com:5432/<database>?sslmode=require&options=endpoint%3D<endpoint-id>- TLS is required (
sslmode=require). To also check the server's certificate, see Verify the server certificate. - Keep the
options=endpoint%3D...parameter. It routes clients whose TLS library does not send the host name. - Both strings log in as your project's owner role, which can run migrations and any DDL.
- Each branch has its own endpoint, so a branch's strings never reach another branch's data.
Verify the server certificate
Every endpoint serves a certificate from a public certificate authority that covers the host names in your connection strings. Your client can use it to check that it is talking to BataDB and not to something in between.
sslmode=require always encrypts the connection. Whether it also checks the
certificate depends on the client.
- Clients built on libpq (
psql,pg_dumpand the drivers that wrap libpq) do not check the certificate underrequireout of the box. Usesslmode=verify-fullwithsslrootcert=systemto check the certificate and the host name against your system's trusted certificate authorities.sslrootcert=systemneeds libpq 16 or newer. With an older libpq, setsslrootcertto your system's CA bundle file instead, for example/etc/ssl/certs/ca-certificates.crton Debian and Ubuntu. - node-postgres (
pg8) already checks the certificate and the host name undersslmode=require. Its next major version givesrequirethe weaker libpq meaning, so writesslmode=verify-fullto keep the check. Do not addsslrootcert=systemfor node-postgres: it readssslrootcertas a file path.
With psql, a verified direct connection looks like this:
psql "postgresql://<user>:<password>@<endpoint-id>.<region>.db.batadata.com:5432/<database>?sslmode=verify-full&sslrootcert=system&options=endpoint%3D<endpoint-id>"Pooled or direct
| Pooled | Direct | |
|---|---|---|
| Host | <endpoint-id>-pooler.<region>.db.batadata.com | <endpoint-id>.<region>.db.batadata.com |
| Use it for | App queries, serverless functions, anything that opens many short connections | Migrations, pg_dump, psql, LISTEN/NOTIFY, advisory locks, anything that needs session state |
| How it works | Transaction pooling: each transaction may run on a different server connection | One server connection for the life of your connection |
The pooled endpoint resets session state after every transaction. A plain
SET, a temporary table, an advisory lock or a LISTEN lasts only until the
end of the transaction that made it. Avoid SQL-level PREPARE there too: the
next transaction may run on a different server connection. When you need
session state, set it inside the transaction with SET LOCAL, or use the
direct endpoint.
Never rely on a session-level SET on the pooled endpoint, for example as a
read-only guard. Start a read-only transaction instead:
BEGIN TRANSACTION READ ONLY;
SELECT count(*) FROM orders;
COMMIT;Prisma
Give Prisma both strings. Queries go through the pool. prisma migrate takes
a session-level advisory lock, so it needs the direct endpoint.
datasource db {
provider = "postgresql"
url = env("DATABASE_URL") // pooled
directUrl = env("DIRECT_URL") // direct
}New projects run Postgres 17, and Prisma needs nothing extra on the pooled
string. On a Postgres 16 project the pooled endpoint does not support
prepared statements: add &pgbouncer=true to the pooled string, or use the
direct one.
The same rule holds for other migration tools, Drizzle and Turbine included: point the migration runner at the direct string.
Scale to zero and wake
A serverless compute suspends after 5 minutes without a query. While it is suspended it costs nothing for compute. The next connection or query wakes it.
- The first query after a wake waits for the compute to start. Set your driver's connect timeout to allow for that.
- Connections still open when a compute suspends are closed. Use a pool that replaces closed connections; most do.
- Something that polls the database (a health check, a short cron) keeps the compute awake and billing. Check
bata usagea day after launch. - A suspend you ask for (
bata compute suspend, orPOST /v1/computes/<compute-id>/suspend) can return statussuspendinguntil the compute is parked, usually within a few seconds, and then readssuspended. Until then it still serves open connections and is still billed. A new connection in that window keeps it running, or waits for the park and wakes it.
To change the timeout, send suspend_timeout_seconds (0 to 86400) in
PATCH https://api.batadata.com/v1/computes/<compute-id> with an admin key.
0 turns idle suspend off. bata db branch info main --json shows the compute
id. On the Team plan you can also make a compute always-on, so it never
suspends: bata compute set --branch main --always-on.
Postgres ends a session that sits idle inside an open transaction for 15
minutes (idle_in_transaction_session_timeout), so a forgotten transaction
cannot hold locks or keep the compute awake. Change it per role with
ALTER ROLE app SET idle_in_transaction_session_timeout = '1h';. To turn it
off for one session, run SET idle_in_transaction_session_timeout = 0; on the
direct endpoint, or use SET LOCAL inside the transaction on the pooled one.
SQL over HTTP
Use HTTP only where TCP is not available, such as an edge runtime, or for something as small as a page-view counter. On a Node server, use the pooled string with a normal driver or ORM.
The key in a deployment should be an app key. It never expires, rotates without downtime, and runs its SQL as a Postgres role that holds only the table grants you gave it.
With @batadata/serverless:
npm install @batadata/serverlessimport { Pool } from '@batadata/serverless';
const pool = new Pool({ apiKey: process.env.BATA_APP_KEY });
const { rows } = await pool.query('select id, email from users where id = $1', [userId]);An app key is bound to one project and branch, so no project or branch option
is needed. The driver retries while a suspended compute wakes, up to 30 seconds
by default (wakeRetry).
Or with plain fetch:
const res = await fetch('https://api.batadata.com/v1/sql', {
method: 'POST',
headers: {
authorization: `Bearer ${process.env.BATA_APP_KEY}`,
'content-type': 'application/json',
},
body: JSON.stringify({ query: 'select now()', params: [] }),
});A 503 with code: "COMPUTE_STARTING" means the compute is waking. Wait for
the Retry-After header and send the request again.
Keep the strings secret
Both strings carry the database password.
- Keep them in environment variables or your platform's secret store, never in code or in git.
- Do not paste the JSON from
bata db url --jsonor the connection-info API into logs, issues or chat. - Give previews and local development a branch of their own instead of the production strings.