Using PostgreSQL from Prolog

April 3, 2022, 11:32 a.m. by

PostgreSQL is probably one of the most advanced databases in the relational world. Let's see how can we access it from Scryer Prolog.

There are many kinds of databases in our world. Relational ones, however, remain dominant, and it's expected that sooner or later we need to access one from our Prolog code. Let's see how we can connect to PostgreSQL, one of the best open-source relational databases, from Prolog.

We'll use a library called postgresql-prolog. It only supports Scryer Prolog at the moment, but contributions are welcome! The good thing about this library is that it doesn't require any native code, it tries to implement the PostgreSQL protocol in Prolog and only needs a sockets library available.

Setting up PostgreSQL

Current PostgreSQL versions work. In this example, I'm going to test using PostgreSQL 14.2. However, at the moment only one method of authentication works well. It is password. Other methods are not implemented yet. Note that password can only be used in trusted networks because it sends the password in clear text. After you've checked that password authentication is enabled in pg_hba.conf, let's go to the Prolog side.

If you're using Docker/Docker Compose, this is a valid configuration:

version: "3.6"
services:
  postgres:
    image: postgres:14.2-alpine
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: postgres
      POSTGRES_HOST_AUTH_METHOD: password
    ports:
    - 5432:5432

Installing postgresql-prolog

The library is made of several files. To install it on your project there are various methods.

If you're already using Logtalk packs, there's one for postgresql-prolog! Add the aarroyoc-packs repository to make it available.

Otherwise, you can just download the folder (or make a Git submodule) and keep the .pl files.

You must be able to load the library and have two new predicates available: connect/6 and query/3.

:- use_module(postgresql).

Using postgresql-prolog

To connect to the database use the connect/6 predicate. The arguments are the following:

connect(+User, +Password, +Host, +Port, +Database, -Connection)

Basically, you need to provide a user with a password, a host with a port, and a database name. It will give you a connection to the database. We can use this connection to make queries.

?- connect("user", "password", '127.0.0.1', 5432, "postgres", Conn).
 Conn = postgresql('$stream'(0x55bf885d6158))

If the connection details are wrong, the predicate will fail.

Now we can do a query. The query/3 predicate can be used to do both SELECT and INSERT/UPDATE/DELETE/... statements.

query(+Connection, +Query, -Result)

The result will be different depending on the query. INSERT/UPDATE/... queries will unify with ok if successful, while SELECT queries will give data. Both will unify with error(Error) if an error happens.

?- connect("user", "password", '127.0.0.1', 5432, "postgres", Conn),
query(Conn, "CREATE TABLE test_table (id serial, name text)", Result).
    Conn = postgresql('$stream'(0x55bfae7e63f8)), Result = ok

Let's add some data:

?- connect("user", "password", '127.0.0.1', 5432, "postgres", Conn),
query(Conn, "INSERT INTO test_table (name) VALUES ('test1')", Result1),
query(Conn, "INSERT INTO test_table (name) VALUES ('test2')", Result2).
    Conn = postgresql('$stream'(0x55bfbeb3ffc8)), Result1 = ok, Result2 = ok

Now, you may want to ask about the security of the queries here. It should be noted that this interface does not do any check to prevent SQL injection attacks as it is considered to be a raw interface. Most secure predicates probably will be added in the future.

Let's SELECT something.

?- connect("user", "password", '127.0.0.1', 5432, "postgres", Conn), query(Conn, "SELECT * FROM test_table", Result).
   Conn = postgresql('$stream'(0x565437244618)), Result = data(["id","name"],[["1","test1"],["2","test2"]])
;  ... .

?- connect("user", "password", '127.0.0.1', 5432, "postgres", Conn), query(Conn, "SELECT name,id FROM test_table", Result).
   Conn = postgresql('$stream'(0x5654372080d8)), Result = data(["name","id"],[["test1","1"],["test2","2"]])
;  ... .

If the query was successful a data structure is returned. First, it contains the names of the fields in a list. Then, it contains a list of every row in the same order.

With these basic building blocks, we can start using PostgreSQL from Prolog. As mentioned before, the project is open to contributions!