Connecting

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).
• 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.

Pooled or direct#

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 usage a day after launch.

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.

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/serverless

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:

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.