PostgreSQL

Apps start WITHOUT a database to save resources. Provision one ON DEMAND the first time the app needs to persist data:

create_database({ project_id: "my-app" })
// optionally seed the schema in the same call:
create_database({ project_id: "my-app", init_sql: "CREATE TABLE IF NOT EXISTS notes (id SERIAL PRIMARY KEY, body TEXT NOT NULL)" })

create_database provisions PostgreSQL, runs your optional init_sql, and redeploys the app so DATABASE_URL is injected. It is idempotent (keeps existing data). Until you call it, tools that need a database (execute_sql, query_sql, introspect_schema, apply_migrations, reset_database) return NO_DATABASE. Once the database exists, the DATABASE_URL is injected automatically on every deploy — do not hardcode it.

Mandatory connection pattern

Use npm:pg with ssl: false. The database is on an IPv6 host and does not support SSL from app code.

import { Pool } from "npm:pg";

function pgConfig(s: string) {
  const u = new URL(s);
  return {
    host: u.hostname.replace(/^\[|\]$/g, ""),
    port: parseInt(u.port) || 5432,
    user: u.username,
    password: decodeURIComponent(u.password),
    database: u.pathname.slice(1).split("?")[0],
    ssl: false,
    connectionTimeoutMillis: 8000,
  };
}

const pool = new Pool(pgConfig(Deno.env.get("DATABASE_URL")!));

export default async function handler(req: Request): Promise<Response> {
  const { rows } = await pool.query("SELECT NOW() as time");
  return Response.json({ time: rows[0].time });
}

Schema setup

Create schema BEFORE the first deploy using the execute_sql tool:

CREATE TABLE IF NOT EXISTS items (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Common errors