Skip to main content

Connecting to PostgreSQL

PostgreSQL is a powerful open-source relational database. flAPI can query PostgreSQL databases directly or cache results for high-performance API serving.

Setup​

# flapi.yaml
connections:
  postgres-db:
    init: |
      INSTALL postgres;
      LOAD postgres;
    properties:
      host: localhost
      port: '5432'
      database: mydb
      user: '${POSTGRES_USER}'
      password: '${POSTGRES_PASSWORD}'

Connection properties are accessible inside SQL templates as {{ conn.host }}, {{ conn.database }}, etc.

Environment Variables​

Store sensitive credentials in environment variables:

export POSTGRES_USER=myuser
export POSTGRES_PASSWORD=mypassword
# flapi.yaml
template:
  environment-whitelist:
    - '^POSTGRES_.*'

Querying PostgreSQL​

The DuckDB Postgres extension typically reads its target via ATTACH (recommended) or postgres_scan once the connection is attached. Use init to attach the database, then reference it in templates.

connections:
  postgres-db:
    init: |
      INSTALL postgres;
      LOAD postgres;
      ATTACH IF NOT EXISTS 'host={{ conn.host }} port={{ conn.port }} dbname={{ conn.database }} user={{ conn.user }} password={{ conn.password }}' AS pg (TYPE postgres);
    properties:
      host: localhost
      port: '5432'
      database: mydb
      user: '${POSTGRES_USER}'
      password: '${POSTGRES_PASSWORD}'
-- Query an attached PostgreSQL table
SELECT *
FROM pg.public.users
WHERE active = true
{{#params.role}}
  AND role = '{{{ params.role }}}'
{{/params.role}}

Example: User API​

Endpoint (sqls/users.yaml):

url-path: /users/
method: GET
request:
  - field-name: role
    field-in: query
    required: false
    validators:
      - type: enum
        allowedValues: ['admin', 'user', 'guest']
  - field-name: active
    field-in: query
    required: false
    validators:
      - type: enum
        allowedValues: ['true', 'false']
template-source: users.sql
connection:
  - postgres-db

Template (sqls/users.sql):

SELECT
  id,
  username,
  email,
  role,
  created_at
FROM pg.public.users
WHERE 1=1
{{#params.active}}
  AND active = {{ params.active }}
{{/params.active}}
{{#params.role}}
  AND role = '{{{ params.role }}}'
{{/params.role}}
ORDER BY created_at DESC
LIMIT 100
note

Boolean and floating-point request parameters arrive as strings. Use an enum validator with the allowed string values (["true", "false"]) or a string validator with a regex: constraint. flAPI ships seven validator types: int, string, enum, email, uuid, date, time.

With Caching​

For high-traffic APIs, cache PostgreSQL data through DuckLake:

Cache Template (sqls/users_cache.sql):

INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT
  user_id,
  username,
  email,
  role,
  last_login
FROM pg.public.users
WHERE active = true

Endpoint configuration:

cache:
  enabled: true
  table: users_cache
  schema: analytics
  schedule: 15m  # Refresh every 15 minutes
  template-file: users_cache.sql

Connection Pooling​

The DuckDB Postgres extension manages its own pool when you ATTACH the database. Tune behavior with the upstream extension settings (pg_pages_per_task, pg_use_binary_copy, etc.) inside init: if needed — see the DuckDB Postgres extension docs.

Next Steps​

🍪 Cookie Settings