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
      username: ${POSTGRES_USER}
      password: ${POSTGRES_PASSWORD}

Environment Variables​

Store sensitive credentials in environment variables:

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

Querying PostgreSQL​

Using postgres_scan​

-- Query a PostgreSQL table
SELECT *
FROM postgres_scan('mydb', 'public', 'users')
WHERE active = true
{{#params.role}}
  AND role = '{{{params.role}}}'
{{/params.role}}

Connection String​

You can also use a connection string:

connections:
  postgres-db:
    init: |
      INSTALL postgres;
      LOAD postgres;
    properties:
      connection_string: 'postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@localhost:5432/mydb'

Example: User API​

Endpoint (sqls/users.yaml):

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

Template (sqls/users.sql):

SELECT
  id,
  username,
  email,
  role,
  created_at
FROM postgres_scan('mydb', '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

With Caching​

For high-traffic APIs, cache PostgreSQL data:

Cache Template:

CREATE OR REPLACE TABLE {{cache.table}} AS
SELECT
  user_id,
  username,
  email,
  role,
  last_login
FROM postgres_scan('mydb', 'public', 'users')
WHERE active = true

Configuration:

cache:
  enabled: true
  table: users_cache
  schedule: 15m  # Refresh every 15 minutes
  template_file: users_cache.sql

Connection Pooling​

PostgreSQL connections are pooled automatically for performance:

connections:
  postgres-db:
    init: |
      INSTALL postgres;
      LOAD postgres;
    properties:
      host: localhost
      database: mydb
      username: ${POSTGRES_USER}
      password: ${POSTGRES_PASSWORD}
    pool:
      min_size: 2
      max_size: 10
      idle_timeout: 300

Next Steps​

🍪 Cookie Settings