PostgreSQL

PostgreSQL is a powerful, open-source relational database system known for its reliability, feature richness, and standards compliance. Hyperterse provides full support for PostgreSQL with connection pooling, SSL/TLS encryption, transaction support, and all standard SQL features including JSON operations, full-text search, and advanced data types.

Connecting to your server

PostgreSQL adapters use the standard PostgreSQL connection URI format:

adapters:
  my_postgres:
    connector: postgres
    connection_string: 'postgresql://user:password@host:5432/database'

Adapter options

Configure connection behavior and performance settings through adapter options:

adapters:
  production_db:
    connector: postgres
    connection_string: 'postgresql://user:pass@host:5432/db'
    options:
      max_connections: 10
      ssl_mode: 'require'
      connect_timeout: 10

For a complete list of connector options and SSL modes, see the PostgreSQL connection documentation.

Use require or stricter SSL modes in production. Never use disable with cloud databases or production environments.

Setting up a database

It is very easy to set up a database for Hyperterse. You can use your existing database or create a new one and wire it up to Hyperterse.

1. Create database and user

   Create a dedicated database and user for Hyperterse:

   CREATE DATABASE myapp;
   CREATE USER hyperterse WITH PASSWORD 'secure_password';
   GRANT CONNECT ON DATABASE myapp TO hyperterse;

2. Grant permissions

   Grant only the permissions Hyperterse needs based on your use case:

   -- Read-only access for all tables
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO hyperterse;
   GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO hyperterse;
   
   -- For future tables
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO hyperterse;

   For write operations, grant additional permissions as needed:

   GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO hyperterse;

3. Configure Hyperterse

   Add the PostgreSQL adapter to your configuration:

   adapters:
     main_db:
       connector: postgres
       connection_string: 'postgresql://hyperterse:secure_password@localhost:5432/myapp'
       options:
         ssl_mode: 'prefer'
         max_connections: 10

4. Verify connection

   Test the connection using the development server:

   hyperterse dev -f config.terse

   Look for a successful connection message:

   INFO  Connected to adapter: main_db

Usage

PostgreSQL queries in Hyperterse support all standard SQL features. Use parameterized queries with input variables for safe execution:

queries:
  get-user:
    use: main_db
    description: 'Retrieve user by ID'
    statement: |
      SELECT id, name, email, created_at
      FROM users
      WHERE id = {{ inputs.userId }}
    inputs:
      userId:
        type: int
        description: 'User ID'

PostgreSQL-specific features like JSON operations, full-text search, array operations, and window functions are fully supported. Use standard PostgreSQL syntax in your query statements.

Performance

Hyperterse inherently does not limit any performance optimizations. You can optimize your queries and database to whatever degree Postgres allows.

Read replicas

For read-heavy workloads, you can configure separate adapters for primary and replica databases:

adapters:
  primary:
    connector: postgres
    connection_string: '{{ env.PRIMARY_DB_URL }}'
  replica:
    connector: postgres
    connection_string: '{{ env.REPLICA_DB_URL }}'

Route read queries to the replica and write queries to the primary.

Troubleshooting

Connection refused

Verify PostgreSQL is running and accessible:

psql -h localhost -U user -d database

Check firewall rules and network connectivity if connecting to a remote server.

SSL certificate errors

For self-signed certificates or custom CA certificates, adjust the SSL mode:

connection_string: 'postgresql://user:pass@host:5432/db?sslmode=require'

For full certificate verification, ensure the CA certificate is properly installed and use verify-ca or verify-full SSL modes.

Permission denied

Verify user permissions:

-- Check current grants
\dp table_name

-- Grant if needed
GRANT SELECT ON table_name TO hyperterse;

Ensure the user has CONNECT privilege on the database and appropriate table-level permissions.