Skip to main content
Geni provides full support for PostgreSQL databases with native schema dumping and migration management.

​
Connection URL Format

PostgreSQL connections use the following URL format: 
postgres://user:password@host:port/database
postgresql://user:password@host:port/database
psql://user:password@host:port/database
All three schemes (postgres://, postgresql://, psql://) are supported and will be normalized to postgresql:// internally.

​
URL Components

  • user - PostgreSQL username
  • password - User password (optional)
  • host - Database server hostname or IP address
  • port - Database port (default: 5432)
  • database - Database name
  • sslmode - SSL mode (e.g., disable, require, verify-ca, verify-full)

​
Setup

1

Set your database URL

export DATABASE_URL="postgres://postgres:password@localhost:5432/myapp?sslmode=disable"
2

Create the database

geni create
This executes: CREATE DATABASE myapp
3

Create your first migration

geni new create_users_table
4

Run migrations

geni up

​
Configuration Examples

DATABASE_URL="postgres://postgres@localhost:5432/development?sslmode=disable"
DATABASE_WAIT_TIMEOUT="30"
DATABASE_MIGRATIONS_FOLDER="./migrations"

​
Features

​
Transaction Support

PostgreSQL migrations run in transactions by default. To disable transactions for a specific migration, add a comment at the top of your migration file: 
-- transaction:no
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

​
Schema Dumping

Geni automatically dumps your PostgreSQL schema after each successful migration without requiring external binaries. The schema dump includes:
  • Extensions - PostgreSQL extensions installed in the public schema
  • Tables - Table definitions with columns and data types
  • Views - View definitions
  • Constraints - Primary keys, foreign keys, unique constraints, and check constraints
  • Indexes - Index definitions
  • Sequences - Sequence definitions
  • Comments - Table and column comments
The schema is generated using PostgreSQL’s information schema and system catalogs (source: postgres.rs:189-446).
PostgreSQL schema dumping works out of the box without requiring pg_dump or any external tools.

​
Wait Timeout

Geni can wait for PostgreSQL to be ready before running migrations. This is useful in containerized environments where the database might need time to start. 
DATABASE_WAIT_TIMEOUT="30" geni up
The driver will retry the connection every second until the timeout is reached (source: postgres.rs:34-54).

​
Parameterized Queries

PostgreSQL uses numbered parameters ($1, $2, etc.) for prepared statements. Geni handles this automatically when tracking migrations: 
INSERT INTO schema_migrations (id) VALUES ($1)
DELETE FROM schema_migrations WHERE id = $1

​
Database Operations

​
Create Database

geni create
Executes: CREATE DATABASE {database_name}

​
Drop Database

geni drop
Executes: DROP DATABASE {database_name}
Dropping a database is irreversible. All data will be permanently deleted.

​
Check Status

geni status
Shows pending migrations by querying the schema_migrations table.

​
Limitations

None. PostgreSQL is fully supported with all Geni features.

​
Examples

​
Basic Migration

Create a table in 20240115120000_create_users.up.sql: 
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
Rollback in 20240115120000_create_users.down.sql: 
DROP TABLE IF EXISTS users;

​
Advanced Features

Using PostgreSQL-specific features: 
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create table with advanced types
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  data JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create GIN index for JSONB
CREATE INDEX idx_events_data ON events USING GIN (data);

​
Troubleshooting

​
Connection Issues

If you see connection errors, verify:
  • PostgreSQL is running and accessible
  • Hostname and port are correct
  • User credentials are valid
  • Database exists (or use geni create)
  • SSL mode is appropriate for your setup

​
Migration Table

Geni creates a schema_migrations table to track applied migrations: 
CREATE TABLE IF NOT EXISTS schema_migrations (
  id VARCHAR(255) PRIMARY KEY
)
You can customize the table name: 
DATABASE_MIGRATIONS_TABLE="custom_migrations" geni up

Build docs developers (and LLMs) love

Get started for freeTalk to us