Postgres

Managed PostgreSQL databases, locally and in production.

Managed PostgreSQL database instances, powered by Neon in production. One line in specific.hcl gives you a local database during specific dev and a managed production database on deploy.

service "api" {
  build = build.api
  command = "./api"

  endpoint {
    public = true
  }

  env = {
    DATABASE_URL = postgres.main.url
  }
}

postgres "main" {}

Connection attributes

Reference these in env blocks:

Attribute	Description
`url`	Full connection string (for example, `postgres://user:pass@host:5432/dbname`).
`host`	Database host.
`port`	Database port.
`user`	Database user.
`password`	Database password.
`name`	Database name.
`direct_url`	Non-pooled connection string, bypassing the connection pooler. Use when you need a direct connection (for example, Prisma’s `directUrl`).
`direct_host`	Non-pooled database host.

Extensions

The following PostgreSQL extensions are available in both development and production. Enable each with CREATE EXTENSION in a migration:

Extension	Enable with	Use for
pgvector	`CREATE EXTENSION vector;`	Vector similarity search.
pg_search	`CREATE EXTENSION pg_search;`	Full-text search with BM25 ranking, powered by ParadeDB.
PostGIS	`CREATE EXTENSION postgis;`	Geospatial queries and indexing.
pg_trgm	`CREATE EXTENSION pg_trgm;`	Trigram-based fuzzy text matching and `LIKE`/`ILIKE` indexes.
ltree	`CREATE EXTENSION ltree;`	Hierarchical structures: categories, org charts, taxonomies.
btree_gist	`CREATE EXTENSION btree_gist;`	B-tree types in GiST indexes: exclusion constraints (for example, no overlapping bookings) and mixed-type multicolumn indexes.
unaccent	`CREATE EXTENSION unaccent;`	Accent-insensitive text search (strips diacritics).
pgcrypto	`CREATE EXTENSION pgcrypto;`	Cryptographic functions: digests, HMAC, bcrypt password hashing, PGP encryption, random UUIDs and bytes.
pgRouting	`CREATE EXTENSION pgrouting CASCADE;`	Routing and network analysis (requires PostGIS, installed by `CASCADE`).

For graph-shaped workloads, use ltree for hierarchies and pgrouting for weighted networks and shortest-path queries. Apache AGE and Cypher queries are not supported.

Querying the database

Local development

Use specific psql to connect to a development database. The database is started automatically if it isn’t running.

# Interactive session
specific psql main

# Run a single query
specific psql main -- -c "SELECT * FROM users LIMIT 5"

# List tables
specific psql main -- -c "\dt"

Production

Use specific query --db <name> to run read-only Postgres SQL against a deployed environment’s database:

# Query the production "main" database
specific query --db main "SELECT * FROM users LIMIT 5"

# Target a specific environment with machine-readable output
specific query --db main --environment staging --format jsonl \
  "SELECT id, email FROM users ORDER BY created_at DESC LIMIT 20"

Production queries are read-only: INSERT, UPDATE, DELETE, and DDL are rejected (the query runs inside a read-only transaction). Without --db, specific query targets observability data instead. You can also browse data and manage the database from the dashboard.

Schema migrations

For zero-downtime schema migrations, enable Reshape:

postgres "main" {
  reshape {
    enabled = true
  }
}

See Reshape migrations for how migrations are written and managed. If you prefer your ORM’s migration tooling instead, run it via a pre_deploy hook; run specific docs integrations/prisma or specific docs integrations/drizzle for setup guidance.

Real-time sync

Postgres changes can be streamed to clients in real time. See Real-time sync.

Preview environments

In preview environments, databases are branched copy-on-write from the parent environment, so previews start with real data instantly.