PostgreSQL

Connect Brokoli to PostgreSQL for reading and writing data.

Connection config

Field	Value	Example
`type`	`postgres`
`host`	Hostname or IP	`db.example.com`
`port`	Port (default: 5432)	`5432`
`schema`	Database name	`analytics`
`login`	Username	`etl_user`
`password`	Password	`secret123`

Create via API

curl -X POST http://localhost:8080/api/connections \
  -H "Content-Type: application/json" \
  -d '{
    "conn_id": "prod-postgres",
    "type": "postgres",
    "host": "db.example.com",
    "port": 5432,
    "schema": "analytics",
    "login": "etl_user",
    "password": "secret123"
  }'

URI format

Brokoli constructs the connection URI internally:

postgres://etl_user:secret123@db.example.com:5432/analytics?sslmode=disable

To customize SSL mode or other options, use the extra field:

{
  "extra": "{\"sslmode\": \"require\"}"
}

Reading data (source_db)

{
  "type": "source_db",
  "config": {
    "conn_id": "prod-postgres",
    "query": "SELECT id, name, email FROM users WHERE active = true"
  }
}

Use parameterized queries with pipeline variables:

{
  "conn_id": "prod-postgres",
  "query": "SELECT * FROM events WHERE date = '${param.date}'"
}

Writing data (sink_db)

Insert

{
  "type": "sink_db",
  "config": {
    "conn_id": "prod-postgres",
    "table": "dim_users",
    "mode": "insert"
  }
}

Upsert

{
  "type": "sink_db",
  "config": {
    "conn_id": "prod-postgres",
    "table": "dim_users",
    "mode": "upsert",
    "conflict_key": "user_id"
  }
}

Migrations

Run DDL statements with a migrate node:

{
  "type": "migrate",
  "config": {
    "conn_id": "prod-postgres",
    "sql": "CREATE TABLE IF NOT EXISTS events (id SERIAL PRIMARY KEY, name TEXT, created_at TIMESTAMP DEFAULT NOW())"
  }
}

Testing the connection

curl -X POST http://localhost:8080/api/connections/prod-postgres/test

{"success": true, "message": "Connected successfully (pgx)", "driver": "pgx"}

Using Brokoli's own database as PostgreSQL

Brokoli can use PostgreSQL as its internal database instead of SQLite:

broked serve --db "postgres://brokoli:pass@localhost:5432/brokoli?sslmode=disable"

This is recommended for production deployments with multiple concurrent users.

On this page