Skip to content
BataDB

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:

Terminal
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:

Terminal
curl https://api.batadata.com/v1/connection-info/<project-id> \
  -H "Authorization: Bearer <your-api-key>"

The response lists every branch:

JSON
{
  "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

Output
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_dump and the drivers that wrap libpq) do not check the certificate under require out of the box. Use sslmode=verify-full with sslrootcert=system to check the certificate and the host name against your system's trusted certificate authorities. sslrootcert=system needs libpq 16 or newer. With an older libpq, set sslrootcert to your system's CA bundle file instead, for example /etc/ssl/certs/ca-certificates.crt on Debian and Ubuntu.
  • node-postgres (pg 8) already checks the certificate and the host name under sslmode=require. Its next major version gives require the weaker libpq meaning, so write sslmode=verify-full to keep the check. Do not add sslrootcert=system for node-postgres: it reads sslrootcert as a file path.

With psql, a verified direct connection looks like this:

Terminal
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

PooledDirect
Host<endpoint-id>-pooler.<region>.db.batadata.com<endpoint-id>.<region>.db.batadata.com
Use it forApp queries, serverless functions, anything that opens many short connectionsMigrations, pg_dump, psql, LISTEN/NOTIFY, advisory locks, anything that needs session state
How it worksTransaction pooling: each transaction may run on a different server connectionOne 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:

SQL
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.

Prisma schema
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 usage a day after launch.
  • A suspend you ask for (bata compute suspend, or POST /v1/computes/<compute-id>/suspend) can return status suspending until the compute is parked, usually within a few seconds, and then reads suspended. 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:

Terminal
npm install @batadata/serverless
TypeScript
import { 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:

TypeScript
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 --json or the connection-info API into logs, issues or chat.
  • Give previews and local development a branch of their own instead of the production strings.