Postgres Drivers and Compatibility

XTDB supports the Postgres wire protocol and is therefore compatible with several Postgres tools. However, XTDB is not designed to support drop-in replacement with Postgres and full compatibility with Postgres is not a goal for XTDB.

More specifically, whilst XTDB offers some level of compatibility with Postgres’ actual SQL dialect, there are many syntactic and functional capabilities which Postgres offers that are not supported in XTDB, and users should generally always refer to XTDB’s SQL documentation.

The advantage of embracing wire protocol compatibility is that many PostgreSQL clients or drivers are able to connect to XTDB seamlessly and run many useful queries without issue. There will always be exceptions and this page will document them as comprehensively as possible.

Usage examples

This example uses the Psycopg 3 database adapter.

import asyncio
import psycopg as pg

DB_PARAMS = {
    "host": "localhost",
    "port": 5432
}

async def insert_trades(conn, trades):
    query = """
    INSERT INTO trades (_id, name, quantity) VALUES (%s, %s, %s)
    """

    async with conn.cursor() as cur:
        for trade in trades:
            trade_values = (trade["_id"], trade["name"], trade["quantity"])
            await cur.execute(query, trade_values)

async def get_trades_over(conn, quantity):
    query = """
    SELECT * FROM trades WHERE quantity > %s
    """
    async with conn.cursor() as cur:
        await cur.execute(query, (quantity,))
        return await cur.fetchall()

async def main():
    trades = [
        {"_id": 1, "name": "Trade1", "quantity": 1001},
        {"_id": 2, "name": "Trade2", "quantity": 15},
        {"_id": 3, "name": "Trade3", "quantity": 200},
    ]

    try:
        async with await pg.AsyncConnection.connect(**DB_PARAMS, autocommit=True) as conn:
            conn.adapters.register_dumper(str, pg.types.string.StrDumperVarchar) # required for now https://github.com/xtdb/xtdb/issues/3589
            await insert_trades(conn, trades)
            print("Trades inserted successfully")

            result = await get_trades_over(conn, 100)
            print(result)
    except Exception as error:
        print(f"Error occurred: {error}")

if __name__ == "__main__":
    asyncio.run(main())

This example uses the postgres package.

"use strict"

import postgres from 'postgres';

const sql = postgres({
  host: "localhost",
  port: 5432,
  fetch_types: false, // currently required https://github.com/xtdb/xtdb/issues/3607
  // debug: console.log
});

// Mapping for all needed JavaScript types to Postgres OIDs - a simpler strategy will be provided soon https://github.com/xtdb/xtdb/issues/3606
const typeToOid = {
  number: 23,  // Postgres OID for 'int4'
  string: 25   // Postgres OID for 'text
};
// All other types will be coerced to strings
// Note that this includes int8 (OID 20) due to JavaScript's 53-bit numeric precision
// For the full list of OIDs see "Supported types" for the Postgres Driver at https://docs.xtdb.com

function objsToTypedObjs(objs) {
  return objs.map(o => {
    const transformedEntry = {};
    for (const key in o) {
      if (o.hasOwnProperty(key)) {
        const jsType = typeof o[key];
        const oid = typeToOid[jsType];
        if (oid) {
          transformedEntry[key] = sql.typed(o[key], oid);
        } else {
          throw new Error(`Unsupported JS type: ${jsType} for key: ${key}`);
        }
      }
    }
    return transformedEntry;
  });
}

async function insertTrades(trades) {
  if (!Array.isArray(trades) || trades.length === 0) {
    throw new Error('Trades must be a non-empty array');
  }

  return await sql`
    INSERT INTO trades
    ${ sql(objsToTypedObjs(trades)) }
  `;}

async function getTradesOver(quantity) {
  return await sql`
    SELECT *
    FROM trades
    WHERE quantity > ${ sql.typed(quantity, 23) }
  `;}

async function main() {
  const trades = [
    { _id: 1, name: 'Trade1', quantity: 1001 },
    { _id: 2, name: 'Trade2', quantity: 15 },
    { _id: 3, name: 'Trade3', quantity: 200 },
  ];

  try {
    const tx = await insertTrades(trades);
    console.log('Trades inserted successfully');
  } catch (error) {
    console.error('Error inserting trades:', error);
  }

  try {
    const result = await getTradesOver(100);
    console.log(result);
  } catch (error) {
    console.error('Error fetching trades:', error);
  }
  process.exit()
}

main();

Recommended third party tools

The following list of third party tools includes drivers, clients or utility CLIs/GUIs that our team has tested. Picking an item from it will help us to ensure that your code will work reliably with XTDB.

We recognize that our community might value some features more than others and we encourage you to open an issue on GitHub if you think we are missing something important for your language or workflow.

CLIs

psql

GUIs

VSCode’s SQLTools extension

Libraries / Programmatic clients

Postgres.js - explicit parameter typing is required for now due to how the library is designed (by default it places the burden of calculating all type information onto the database)
Psycopg

Drivers

JDBC

Compatibility

Supported types

The definitive list of types and Postgres OIDs supported are available alongside other information in the pg_catalog metadata schema:

Run

Open in xt-play

Main caveats

You can use BEGIN and COMMIT/ROLLBACK to execute multiple statements in a single atomic transaction, however write-transactions are fundamentally non-interactive (see How XTDB works for details). Reads back to the client must take place in separate transactions.
JSON-ification of results - generic Postgres drivers can’t comprehensively handle the ‘polymorphic’ and nested types that XTDB supports internally, so many complex structures will be return using JSON columns. Given the lack of DDL functionality present in XTDB currently (we’re working on it), it is therefore important that you control the data you insert into a given column if you want to avoid a monomorphic (simple) type being unexpectedly later returned as a JSON string because the type definition was inadvertently widened by an errant INSERT for an unrelated row. For example, if you INSERT row X with a number for column foo and then query it back you will see that same number, however if you then INSERT row Y with a string for column foo now row X will now always return column foo as string (i.e. it’s now JSON). This widening behaviour cannot be undone using the supported XTDB APIs. Please get in touch if you run into difficulties: hello@xtdb.com
Lack of SHOW and SQL ‘window functions’ precludes various tools from working.
Certain classes of error messages may be opaque to the client - it may therefore be best to debug queries using the HTTP API during the development workflow for now.
pg_catalog is partially implemented but is only intended for interoperability with specific supported tools, please use the SQL standard information_schema instead
Drivers which do not supply proper parameter type information for prepared statements (e.g. node-postgres) may not be suitable for production usage.

List of supported connection properties

Name	Example	Description
user	ignored_user	XTDB does not support users currently, but many clients require specifying a user - provide a dummy string
password	ignored_password	XTDB does not support users currently (or their passwords), but many clients require specifying a password - provide a dummy string