Pgsql protocol compatibility

immudb can talk the pgsql wire protocol which makes it compatible with a widely available set of clients and drivers.

Note: immudb supports the pgsql wire protocol. It is not compatible with the SQL dialect. Check other topics in the Develop with SQL section to see what queries and operations are supported.

Some pgsql clients and browser application execute incompatible statements in the background or directly query the pgsql catalog. Those may not work with immudb.

immudb needs to be started with the pgsql-server option enabled (IMMUDB_PGSQL_SERVER=true).

SSL is supported, if you configured immudb with a certificate.

:::: tabs

::: tab CLI Use the psql client included with PostgreSQL. :::

::: tab C

You can use a subset of the libpq API. You will need to include:

 #include <libpq-fe.h>

and compile with gcc -o main $(pkg-config libpq --cflags --libs) main.c.

:::

::: tab Ruby

You can use the pg gem:

require 'pg'

:::

::: tab Java

Download the official JDBC driver jar artifact for PostgreSQL.

You can then compile your program:

$ javac -cp .:./postgresql-42.2.20.jar MyProgram.java

:::

::: tab PHP

Please refer to the PHP pgsql module documentation for instructions on how to enable it in your server.

:::

::::

To connect to the database:

:::: tabs

::: tab CLI

psql "host=localhost dbname=defaultdb user=immudb password=immudb sslmode=disable"
psql (13.2, server 0.0.0)
Type "help" for help.

:::

::: tab C

PGconn *conn = PQconnectdb("host=localhost user=immudb password=immudb dbname=defaultdb sslmode=disable");

if (PQstatus(conn) == CONNECTION_BAD) {
  fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
  PQfinish(conn);
  exit(1);
}

:::

::: tab Ruby

conn = PG::Connection.open("sslmode=allow dbname=defaultdb user=immudb password=immudb host=127.0.0.1 port=5432")

:::

::: tab Java

It is important to pass the preferQueryMode=simple option, as immudb pgsql server only support simple query mode.

Connection conn = 
  DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/defaultdb?sslmode=allow&preferQueryMode=simple",
    "immudb", "immudb");
System.out.println("Opened database successfully");

:::

::: tab PHP

<?php
$dbconn = pg_connect("host=localhost port=5432 sslmode=require user=immudb dbname=defaultdb password=immudb");
//...
pg_close($dbconn);
?>

:::

::::

Execute statements:

:::: tabs

::: tab CLI

defaultdb=> CREATE TABLE Orders(id INTEGER, amount INTEGER, title VARCHAR, PRIMARY KEY id);
SELECT 1
defaultdb=> UPSERT INTO Orders (id, amount, title) VALUES (1, 200, 'title1');
SELECT 1

:::

::: tab C

PGresult *res = PQexec(conn, "CREATE TABLE Orders (id INTEGER, amount INTEGER, title VARCHAR, PRIMARY KEY id)");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
  do_exit(conn, res);
}
PQclear(res);

res = PQexec(conn, "UPSERT INTO Orders (id, amount, title) VALUES (1, 200, 'title 1')");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
  do_exit(conn, res);
}
PQclear(res);

:::

::: tab Ruby

conn.exec( "CREATE TABLE Orders (id INTEGER, amount INTEGER, title VARCHAR, PRIMARY KEY id)" )
conn.exec( "UPSERT INTO Orders (id, amount, title) VALUES (1, 200, 'title 1')" )
conn.exec( "UPSERT INTO Orders (id, amount, title) VALUES (2, 400, 'title 2')" )

:::

::: tab Java

Statement stmt = conn.createStatement();

stmt.executeUpdate("CREATE TABLE people(id INTEGER, name VARCHAR, salary INTEGER, PRIMARY KEY id);");

stmt.executeUpdate("INSERT INTO people(id, name, salary) VALUES (1, 'Joe', 20000);");
stmt.executeUpdate("INSERT INTO people(id, name, salary) VALUES (2, 'Bob', 30000);");

:::

::: tab PHP

$stmt = 'CREATE TABLE people(id INTEGER, name VARCHAR, salary INTEGER, PRIMARY KEY id);';
$result = pg_query($stmt) or die('Error message: ' . pg_last_error());
$stmt = 'INSERT INTO people(id, name, salary) VALUES (1, 'Joe', 20000);';
$result = pg_query($stmt) or die('Error message: ' . pg_last_error());
$stmt = 'INSERT INTO people(id, name, salary) VALUES (2, 'Bob', 30000);';

:::

::::

Query and iterate over results:

:::: tabs

::: tab CLI

defaultdb=> SELECT id, amount, title FROM Orders;
 (defaultdb.Orders.id) | (defaultdb.Orders.amount) | (defaultdb.Orders.title)
-----------------------+---------------------------+--------------------------
                     1 |                       200 | "title1"
(1 row)

:::

::: tab C

res = PQexec(conn, "SELECT id, amount, title FROM Orders");
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
  printf("No data retrieved\n");
  PQclear(res);
  do_exit(conn, res);
}

int rows = PQntuples(res);
for(int i=0; i<rows; i++) {
  printf("%s %s %s\n", PQgetvalue(res, i, 0),
  PQgetvalue(res, i, 1), PQgetvalue(res, i, 2));
}
PQclear(res);
PQfinish(conn);

:::

::: tab Ruby

conn.exec( "SELECT id, amount, title FROM Orders" ) do |result|
  result.each do |row|
    puts row.inspect
  end
end

:::

::: tab Java

ResultSet rs = stmt.executeQuery("SELECT * FROM people");

while(rs.next()){
    System.out.print("ID: " + rs.getInt("(defaultdb.people.id)"));
    System.out.print(", Name: " + rs.getString("(defaultdb.people.name)"));
    System.out.print(", Salary: " + rs.getInt("(defaultdb.people.salary)"));
    System.out.println();
}

:::

::: tab PHP

$query = 'SELECT * FROM people';
$result = pg_query($query) or die('Error message: ' . pg_last_error());
while ($row = pg_fetch_row($result)) {
  var_dump($row);  
}

:::

::::