PostgreSQL

The postgres driver connects to PostgreSQL. It is built into the tobira binary and uses github.com/jackc/pgx/v5. You can query, browse, edit rows, explain queries, run transactions, export with COPY, follow LISTEN channels and, with the pgvector extension, run similarity searches. The same driver also reaches Amazon Redshift, CockroachDB and Greenplum (see PostgreSQL-compatible databases).

This page covers connecting and the basics. The rest of the reference is split into:

• Queries, browsing and editing: running SQL, the schema browser, explain, inline edits and transactions.
• Read-only guard: what a read-only connection refuses, and what it cannot stop.
• LISTEN and COPY export: live tails of a channel and native csv export.
• pgvector: similarity search.

Quick start

tobira query "postgres://reader@db.internal/app?sslmode=verify-full" "select now()" --read-only
tobira connect "postgres://app@localhost:5432/app?sslmode=disable"

A connection in the config file, with the password kept out of the DSN:

connections:
  - name: app
    dsn: postgres://app@db.internal:5432/app?sslmode=verify-full
    password: env:APP_PG_PASSWORD
    read_only: true

Connecting

The driver accepts URL DSNs and libpq key=value strings:

postgres://user@localhost:5432/app?sslmode=require
postgresql://user@db.internal/app?sslmode=verify-full&sslrootcert=/etc/ssl/ca.pem
host=localhost port=5432 dbname=app user=reader sslmode=disable

On the command line, tobira query and tobira connect treat an argument as a DSN only when it contains ://. Put a key=value DSN in the config file.

tobira connects once when it opens the connection, so a wrong host, database or password fails at once (exit code 2 from tobira query).

Password

Keep the password out of the DSN. Use the connection’s password: setting with a secret reference, or the TOBIRA_CONN_<NAME>_PASSWORD environment variable. Either one overrides a password in the DSN. tobira never prints the password in an error.

TLS

TLS is set with the libpq parameters sslmode, sslrootcert, sslcert and sslkey.

Use verify-full for any server that is not on your machine. The allow_plaintext=1 rule of the HTTP drivers does not apply here: sslmode alone decides.

Options

The connection’s options: set session parameters. Only these three are accepted; any other key fails with unsupported option.

connections:
  - name: reports
    dsn: postgres://reader@db.internal/app?sslmode=verify-full
    options:
      application_name: tobira-reports
      search_path: reporting,public
      statement_timeout: 60s

Each query starts from a clean session, so a SET search_path in one query does not carry over to the next. Use the search_path option instead.

DSN parameters tobira checks

Capabilities

PostgreSQL-compatible databases

Three more drivers use this same code, each with only the capabilities its database has:

With a plain postgres:// DSN, tobira asks the server what it is when it connects and adapts. To skip the check, use the database’s own scheme or add flavor=. A scheme and a flavor= that disagree are refused.

Types

NULL is always NULL. When you edit a cell, decimals are sent as text so the server casts them, and a vector is sent as [1,2,3].

Limits

• One statement per query. Text with two statements is refused by the server.
• Intervals, enums, ranges and geometric types are shown as text.
• The table description has no CREATE statement.
• Browsing a table whose primary key is not totally ordered (a float key holding NaN) needs an explicit sort to page reliably.

Compatibility

Tested with PostgreSQL 17, with and without pgvector 0.8.7 installed.

Troubleshooting