Skip to content

Postgres

Juptune provides a native Postgres driver, in both high-level and low-level forms.

Any authentication method that isn’t SCRAM-SHA256 is unsupported.

  • Replication streaming
  • COPY support
  • Query cancellation

This is the high-level client that most code should be using.

The connection will automatically be upgraded to TLS if possible/required.

PostgresConnectInfo has settings for controlling which certificate store gets used, and whether to trust server certificates that are self-signed.

import juptune.core.util : resultAssert;
import juptune.event : IpAddress;
import juptune.postgres : PostgresClient, PostgresProtocolConfig, PostgresConnectInfo, PostgresTlsMode;
// TODO: Need to add native parsing into the connect function.
IpAddress ip;
IpAddress.parse(ip, "127.0.0.1:5432").resultAssert;
auto psql = PostgresClient(PostgresProtocolConfig());
psql.connect(ip, PostgresConnectInfo(
user: "dummy",
databaseOrNull: "transactions",
plaintextPassword: "dummy",
tlsMode: PostgresTlsMode.always,
)).resultAssert;

It’s recommended to go read through the documentation for PostgresClient in general for specifics, but here’s a quick overview.

When using the higher-level client, all variable length data can only be read into types that allocate memory.

e.g. textual types can only ever be read into string and String, so there’s no worry needed around the lifecycle of scanned results.

  1. Let’s call .exec to put some data into a table - here’s a very verbose example showing all of the supported types:

    psql.exec(
    `
    INSERT INTO test_scratchpad (
    cn, // CHARACTER (10)
    cnv, // CHARACTER VARYING (10)
    t, // TEXT
    i, // INT
    si, // SMALLINT
    b, // BOOLEAN
    ti, // TIME
    tiz, // TIME WITH TIME ZONE
    d, // DATE
    ts, // TIMESTAMP
    tstz // TIMESTAMP WITH TIME ZONE
    ) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6,
    $7,
    $8,
    $9,
    $10,
    $11
    )
    `,
    "cn val", // $1
    "cnv val", // $2
    "text val", // $3
    int.max, // $4
    short.max, // $5
    true, // $6
    2.hours, // $7
    PostgresTimetz( // $8
    4.hours,
    0.hours
    ),
    PostgresDate( // $9
    1234,
    6,
    7
    ),
    PostgresTimestamp( // $10
    PostgresDate(1234, 5, 6),
    3.hours
    ),
    PostgresTimestamptz(// $11
    PostgresDate(1234, 5, 6),
    PostgresTimetz(6.hours, 0.hours)
    ),
    ).resultAssert;
  2. Then we can query a single row using .rowScanner().queryRow():

    int id;
    String cn, cnv; // (When working with @nogc code)
    string t; // (When working with @gc code)
    int i;
    short si;
    bool b;
    Duration ti;
    PostgresTimetz tiz;
    PostgresDate d;
    PostgresTimestamp ts;
    PostgresTimestamptz tstz;
    psql.rowScanner(id, cn, cnv, t, i, si, b, ti, tiz, d, ts, tstz)
    .queryRow(`SELECT * FROM test_scratchpad;`)
    .resultAssert;
    assert(t == "text val");
    // etc.
  3. Or even multiple rows from a single query:

    int id;
    String cn, cnv; // (When working with @nogc code)
    string t; // (When working with @gc code)
    int i;
    short si;
    bool b;
    Duration ti;
    PostgresTimetz tiz;
    PostgresDate d;
    PostgresTimestamp ts;
    PostgresTimestamptz tstz;
    psql.rowScanner(id, cn, cnv, t, i, si, b, ti, tiz, d, ts, tstz)
    // (Alternatively: .queryGc)
    .query(
    `SELECT * FROM test_scratchpad;`,
    onRow: () @nogc nothrow {
    assert(t == "text val");
    // etc. - please remember that each call to `onRow` means the stack variables have been overwritten with new values.
    return Result.noError;
    }
    )
    .resultAssert;

(Currently no high-level support, though the low-level PostgresProtocol can generate the protocol messages needed for it).

I’m mainly trying to figure lifecycle logistics more than anything. It’d be awesome if prepared queries could be given a typed interface for all of the columns it returns, or something fancy like that.

If the client returns an error, you MUST check psqlClient.protocol.isReadyForQuery before using it again. If it’s false then you must discard/recreate the client.

This is because certain errors cannot be recovered from without initialising a new connection.

This is the low-level driver, it doesn’t give completely raw access to the underlying protocol, but it gets very close to it.

Maybe in the future I may add a public interface for sending individual protocol messages, rather than the higher-level ones that bundle a bunch together.

(It’s almost literally the same as PostgresClient right now, just change PostgresClient to PostgresProtocol).

This is a relatively high-level helper function for more easily querying with PostgresProtocol - it’s not as simple as PostgresClient, however it does allow you to process queries without allocating memory.

This is a very verbose example from the driver’s integration test suite, showcasing the full extent of its usage:

TODO:

As with PostgresClient, if PostgresProtocol ever produces an error then you MUST check protocol.isReadyForQuery.

  • SCRAM-SHA256
    • (SASL is peak 90’s protocol design, in all the weird ways).

Juptune supports the text formats of most Postgres types, and a smaller amount of binary formats.

Currently all data is always requested and sent via their text format when using PostgresClient. This is mainly due to a lack of effort to switch formats for certain data types rather than a technical limitation.

Due to how the Postgres protocol works, attemping to parse the data in chunks (i.e. using a fixed-size buffer) isn’t very easy to deal with.

So instead PostgresProtocol (and PostgresClient by proxy) will dynamically allocate memory to store the entire response from the server before processing it, hence why there’s never a need to pass in a buffer like with most other protocol implementations in Juptune.

You can limit the min and max size of this buffer inside of PostgresProtocolConfig.