Skip to content

Latest commit

 

History

History
166 lines (128 loc) · 7.54 KB

connection-uri.md

File metadata and controls

166 lines (128 loc) · 7.54 KB

URI Connection String

Is it possible to specify most of connection parameters using a URI string. It reminds Connection URI in JDBC but with minor differences. A URI is useful when you cannot pass a structured data like JSON or EDN.

A URI might carry a username, a password, a host, a port, and a database name. A query string also passes additional parameters. Here is an example of a URI string:

jdbc:postgresql://fred:secret@localhost:5432/test?ssl=true

Above, the jdbc: prefix is ignored; it is for compatibility with JDBC only. When parsed, the URI becomes a map like this:

{:user "fred"
 :password "secret"
 :host "localhost"
 :port 5432
 :database "test"
 :ssl? true}

Most of the fields can be skipped or overridden with a query string. For example, both user and password fields can be specified like this:

jdbc:postgresql://localhost:5432/test?ssl=true?user=ivan&password=secret123

To connect using a URI, pass it under the :connection-uri parameter as follows:

(def URI "postgresql://test:[email protected]:5432/test?ssl=false")

(pg/with-conn [conn {:connection-uri URI}]
  (let [res (pg/query conn "select 1 as num")]
    ...))

The top-level options override values from a URI. For example, it's unsafe to pass the password in URI. It's better to submit it through a dedicated :password top-level field:

(def URI "postgresql://[email protected]:5432/test?ssl=false")

(pg/with-conn [conn {:connection-uri URI
                     :password (System/getenv "DB_PASSWORD")}]
  (let [res (pg/query conn "select 1 as num")]
    ...))

Query Parameters

This table lists query parameters supported by URI. They act like the standard configuration options described in the Connecting to the Server section with some minor differences in names (e.g. "boolean?" names don't have a question mark at the end).

Before you read, here is a brief description of types and parsing agreements.

  • boolean values are passed as true, on, 1, yes for true, and false, off, 0, no for false, for example read-only=true or read-only=off;

  • long values are passed as a group of numbers: so-timeout=5000;

  • reference values must be fully qualified strings pointing to a certain Clojure definition, for example:

    fn-notification=com.acme.server/my-handler

    The com.acme.server/my-handler string gets transformed into a symbol first, and then passed to the requiring-resolve function that returns a Clojure variable. It also loads a namepsace if it was not loaded before. Passing a string that points to non-existing object will cause an exception.

  • nested means nested maps, for example: foo.bar=1&foo.kek=2. This expression becomes {:foo {:bar 1 :kek 2}} when parsed. Each parameter is split with a dot on a vector of lexems, and each vector is passed into the assoc-in function in a loop. So far, only one group of parameters rely on nesting (see below).

Parameter Parsed as Comment
read-only bool True if make connection always read only
so-keep-alive bool Enable the standard "keep alive" socket option
so-tcp-no-delay bool Enable the standard "no delay" socket option
so-timeout long Long value for the standard "timeout" socket option
so-recv-buf-size long Socket receive buffer size
so-send-buf-size long Socket send buffer size
binary-encode bool Whether to use binary encoding
binary-decode bool Whether to use binary decoding
in-stream-buf-size long BufferedInputStream default size
out-stream-buf-size long BufferedOutputStream default size
ssl bool Whether to use SSL connection
ssl-context ref A reference to a custom SSLContext object
fn-notification ref A reference to a function handling notifications
fn-protocol-version ref A reference to a function handling protocol mismatch event
fn-notice ref A reference to a function handling notices
cancel-timeout-ms long A custom timeout duration when cancelling queries
protocol-version long A custom protocol version
object-mapper ref A reference to custom JSON ObjectMapper instance
pool-min-size long Minimum pool connection size
pool-max-size long Maximum pool connection size
pool-expire-threshold-ms long Pool connection expire lifetime
pool-borrow-conn-timeout-ms long How long to wait when borrowing a connection from a pool
with-pgvector bool Parse vector types provided by the pgvector extension
pg-params nested A nested map of Postgres runtime parameters (see below)

JDBC Compatible Parameters

The following parameters are borrowed from the official JDBC URI specification and act like aliases:

JDBC Parameter Alias for
readOnly read-only
connectTimeout so-timeout
ApplicationName pg-params.application_name
cancelSignalTimeout cancel-timeout-ms
binaryTransfer Enables both binary-encode and binary-decode
tcpKeepAlive so-keep-alive
tcpNoDelay so-tcp-no-delay
protocolVersion protocol-version

Postgres Runtime Parameters (pg-params)

The pg-params option represents a map of Postgres runtime settings. These settings apply to the current connection only and do not affect global server configuration.

Each option should be passes as follows:

pg-param.<setting1>=<value1>&pg-param.<setting2>=<value2>...

where

  • settingN is a name of a setting, e.g. application_name, or default_transaction_read_only, or something else. It should exactly match the name of a Postgres runtime parameter, e.g. keep underscores;

  • valueN is a value of a runtime parameter, e.g. a string or a number, or a comma-separated list.

Example:

pg-param.application_name=my-super-app&pg-param.default_transaction_read_only=off

For more details, take a look at the list of Postgres runtime parameters supported.