Postgres.jl

Postgres.jl is a PostgreSQL client written in Julia that implements the v3 wire protocol with DBInterface and Tables integration.

Installation

import Pkg
Pkg.add("Postgres")

Quick start

using Postgres
DBInterface.connect(Postgres.Connection, "host=127.0.0.1;port=5432;user=postgres;password=postgres;dbname=postgres") do conn
    row = only(DBInterface.execute(conn, "SELECT 1 AS a"))
    @show row.a
end

Connections

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "postgresql://postgres:postgres@127.0.0.1:5432/postgres?sslmode=disable")
DBInterface.close!(conn)

Connection options support:

• libpq-style keyword strings such as host=127.0.0.1 port=5432 user=postgres dbname=postgres.
• PostgreSQL URIs such as postgresql://postgres:postgres@127.0.0.1:5432/postgres.
• Environment defaults: PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, PGAPPNAME, PGCONNECT_TIMEOUT, and TLS-related PGSSL* variables.
• sslmode values: disable, prefer (the default), require, verify-full. Only verify-full verifies the server's certificate; require encrypts without authenticating the server, and the default prefer falls back to an unencrypted connection if the server declines TLS. Use verify-full with sslrootcert when the connection needs to be authenticated.
• TLS files: sslrootcert, sslcert, sslkey, and sslcapath (sslcapath is a fallback CA bundle or directory, used only when sslrootcert is unset and ignored otherwise). sslservername overrides the TLS server name when connecting to a pre-resolved address; under verify-full it is also the name the certificate is verified against, so it must name the server you intend to authenticate.
• connect_timeout (seconds) and statement_timeout (milliseconds).
• application_name and statement_cache_maxsize.

See the 1.0 support policy for tested Julia and PostgreSQL versions, TLS limits, and transaction-pooler requirements.

You can also use ConnectionParams:

using Postgres, DBInterface
params = Postgres.ConnectionParams(host="127.0.0.1", user="postgres", password="postgres", dbname="postgres", sslmode="disable")
conn = DBInterface.connect(Postgres.Connection, params)
DBInterface.close!(conn)

Queries and prepared statements

using Postgres, DBInterface, Tables
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
rows = Tables.rowtable(DBInterface.execute(conn, raw"SELECT $1::int AS val", (42,)))
@show rows[1].val
stmt = DBInterface.prepare(conn, raw"SELECT $1::int AS val")
rows = Tables.rowtable(DBInterface.execute(stmt, (7,)))
DBInterface.close!(stmt)
DBInterface.close!(conn)

Postgres.jl can also deserialize result rows directly into structs through StructUtils.jl. If column names match field names, pass the target type as the fourth DBInterface.execute argument.

using Postgres, DBInterface, StructUtils

struct CountRow
    count::Int
end

row = DBInterface.execute(conn, "SELECT count(*)::int AS count FROM users", (), CountRow)
@show row.count

Use StructUtils.@tags with the postgres namespace when table columns use a different naming convention than Julia fields.

using Dates, Postgres, DBInterface, StructUtils

StructUtils.@tags struct ProfileSummary
    profileId::Int &(postgres=(name=:profile_id,),)
    firstName::Union{Missing, String} &(postgres=(name=:first_name,),)
    lastName::Union{Missing, String} &(postgres=(name=:last_name,),)
    createdAt::DateTime &(postgres=(name=:created_at,),)
end

profile = DBInterface.execute(conn, raw"""
    SELECT profile_id, first_name, last_name, created_at
    FROM profiles
    WHERE profile_id = $1
    """, (profile_id,), ProfileSummary)

profiles = DBInterface.execute(conn, """
    SELECT profile_id, first_name, last_name, created_at
    FROM profiles
    ORDER BY created_at DESC
    LIMIT 10
    """, (), Vector{ProfileSummary})

Postgres.command_tag(result) and Postgres.rows_affected(result) expose PostgreSQL command completion metadata.

Explicit named prepared statements use an LRU backend cache. Caller handles are independent. Set statement_cache_maxsize=0 to disable this cache.

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres"; statement_cache_maxsize=10)
Postgres.set_statement_cache_maxsize!(conn, 5)
cached = Postgres.get_cached_statements(conn)
Postgres.clear_statement_cache!(conn)
DBInterface.close!(conn)

Transactions

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
Postgres.transaction(conn) do tx
    DBInterface.execute(tx, "CREATE TEMP TABLE tx_demo (id int)")
    DBInterface.execute(tx, "INSERT INTO tx_demo VALUES (1)")
end
Postgres.@transaction conn begin
    DBInterface.execute(conn, "INSERT INTO tx_demo VALUES (2)")
end
DBInterface.close!(conn)

Nested transactions are implemented with savepoints.

COPY protocol

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
DBInterface.execute(conn, "CREATE TEMP TABLE copy_demo (id int, name text)")
Postgres.copy_from(conn, "COPY copy_demo (id, name) FROM STDIN", "1\talpha\n2\tbeta\n")
bytes = Postgres.copy_to(conn, "COPY copy_demo TO STDOUT (FORMAT BINARY)")
Postgres.copy_from(conn, "COPY copy_demo FROM STDIN (FORMAT BINARY)", bytes)
DBInterface.close!(conn)

LISTEN/NOTIFY

using Postgres, DBInterface
listener = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
notifier = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
Postgres.listen!(listener, "events")
Postgres.notify!(notifier, "events", "hello")
notice = Postgres.wait_for_notification(listener; timeout=5.0)
@show notice.channel notice.payload
DBInterface.close!(notifier)
DBInterface.close!(listener)

Cursor streaming

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
cur = Postgres.cursor(conn, "SELECT generate_series(1, 5) AS n"; fetchsize=2)
for row in cur
    @show row.n
end
DBInterface.close!(cur)
DBInterface.close!(conn)

Type registry

using Postgres, DBInterface, Tables
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
DBInterface.execute(conn, "CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')")
Postgres.register_enum!(conn, "mood")
row = only(Tables.rowtable(DBInterface.execute(conn, "SELECT 'happy'::mood AS mood")))
@show row.mood
DBInterface.close!(conn)

Numeric values are returned as Postgres.Numeric, interval values as Dates.Period or Dates.CompoundPeriod, and range types as Postgres.PostgresRange{T}. Custom enum, composite, and range registration controls result decoding. Those custom Julia values are not accepted as direct query parameters in 1.0; bind a PostgreSQL text representation with an explicit SQL cast instead.

Query logging and driver styles

Driver behavior — query logging, server notices, asynchronous notifications — is customized by defining a driver "style": subtype Postgres.AbstractPostgresStyle, overload the behavior hooks for it, and pass an instance via the style connection keyword.

using Postgres, DBInterface

struct LoggingStyle <: Postgres.AbstractPostgresStyle end
Postgres.query_logging_enabled(::LoggingStyle) = true
Postgres.query_logger(::LoggingStyle, event::Symbol, info::NamedTuple) = @info "query" event info.success info.duration_ns

conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres"; style=LoggingStyle())
DBInterface.execute(conn, "SELECT 1")
DBInterface.close!(conn)

Connection pooling

using Postgres, DBInterface
pool = Postgres.ConnectionPool(Postgres.Connection, "127.0.0.1", "postgres", "postgres"; dbname="postgres", limit=5)
Postgres.with_connection(pool) do conn
    DBInterface.execute(conn, "SELECT 1")
end
DBInterface.close!(pool)

Errors and cancellation

Postgres.Error represents server errors and includes SQLSTATE codes; Postgres.PostgresInterfaceError covers client-side failures. Use Postgres.cancel_query!(conn) to send a CancelRequest to the server.

Development disclosure

The 1.0 release preparation used Claude Code and OpenAI Codex for implementation assistance and adversarial review. Maintainer decisions, source history, review discussion, and validation results are recorded in pull request #5.