Franck Pachot for YugabyteDB Distributed PostgreSQL Database

Posted on Apr 1, 2022 • Edited on Aug 17

Directus on YugabyteDB

#yugabytedb #distributed #postgres #database

This blog post that follows is outdated. The issues mentioned are fixed.
Here is how to use Directus on YugabyteDB:

Add YugabyteDB #23393

FranckPachot posted on Aug 17, 2024

This adds YugabyteDB to the docker compose

YugabyteDB is Open Source, PostgreSQL-compatible, Distribtued SQL database. It is used with the same driver / dialect as PostgreSQL, so no changes required. More nodes can be added to scale-out (adding --join= to the command line to join the cluster dtarted by the first node). With at least 3 nodes, it is resilient to one node failure or rolling upgrade.

I changed 'PostgreSQL / Redshift' to 'PostgreSQL / Redshift / YugabyteDB' to mention it but still see the old menu item :(

Here is how I tested:

# Start YugabyteDB (webconsole visible on port 15433)
docker compose up yugabytedb -d

# build the project
nvm install v18.17.0
npm init directus-project example-project

? Ok to proceed? (y)
? Choose your database client PostgreSQL / Redshift
? Database Host: 127.0.0.1
? Port: 5114
? Database Name: directus
? Database User: yugabyte
? Database Password: yugabyte
? Enable SSL: (y/N)
? Email admin@example.com
? Password *****

cd /workspace/directus/example-project
npx directus start

View on GitHub

Here is my first attempt to run Directus on YugabyteDB.

Currently, YugabyteDB is not one of the database supported but, because we are PostgreSQL compatible, it worths a try, isn't it?

Directus is described on https://directus.io/ as:

Directus is the world's first Open Data Platform for instantly turning any SQL database into an API and beautiful no-code app

In my mind, having been working a lot with Oracle Database, this translates to an open APEX equivalent 😎

If you try to initialize a Directus project directly on a YugabyteDB you may encounter some errors like:

alter table "directus_relations" alter column "sort_field" type varchar(64) using ("sort_field"::varchar(64))
 - This ALTER TABLE command is not yet supported.

This is because, with the current version of YugabyteDB (2.13) there are some DDL which are not yet supported. The support is tracked in: https://github.com/yugabyte/yugabyte-db/issues/1124

YugabyteDB is PostgreSQL compatible by re-using PostgreSQL code for the SQL processing layer. However, maintaining the catalog in a distributed environment need more considerations and this is why the support for ALTER TABLE has to be considered one by one.

This DDL is generated by the Directus database migration scripts, which use Knex.js. I'm not a big fan replaying the life of the datamodel for each deployment - DDL is never cheap, but I understand the agility of it.

No problem, even if some DDL are not yet supported, it is easy to move from one database to the other when they are compatible. I'll initialize in PostreSQL and move it to YugabyteDB. You may ask why not staying in PostgreSQL? YugabyteDB is distributed: you can add nodes to scale out. The connections, SQL processing, read and writes, and data are automatically balanced. And replicated for High Availability with application continuity.

I start a PostgreSQL database:

podman run --name pg -d \
 -e POSTGRES_PASSWORD=secret \
 -p 5432:5432 \
 postgres

podman exec pg psql -h localhost -U postgres \
 -c "create database directus"

I initialize a Directus project:

npm init directus-project example-project

I fill-in the database connection information:

This, in addition to initializing the database, creates a project directory where I move to:

cd example-project

Now starting a YugabyteDB database:

podman run --name yb -d \
 -p 5433:5433 \
 yugabytedb/yugabyte:latest \
 bin/yugabyted start --daemon=false

podman exec yb ysqlsh -h localhost -U yugabyte \
 -c "create database directus"

Of course, you may have your database already, like in the Yugabyte cloud (free tier) and you will just use the connection information for it.

Copying the schema from PostgreSQL to YugabyteDB is easy with pg_dump:

podman exec    pg pg_dump    -h localhost -U postgres -d directus | 
podman exec -i yb bin/ysqlsh -h localhost -U yugabyte -d directus

In the project directory, a .env file holds all connection information. It was generated for the PostgreSQL database, so I change it to my YugabyteDB one:

sed \
 -e '/DB_PORT=/s/5432/5433' \
 -e '/DB_USER=/s/postgres/yugabyte'
 -i .env

Starting Directus (I'm following the Quickstart Guide):

npx directus start

The http port is mentioned when starting:

Now, welcome to the GUI where I can login (with the credentials mentioned during the initialization and stored in the database):

I can create a Collection, which is actually a table in the database:

You can choose the primary key as generated by a sequence or a UUID. Both work and are scalable, as, by default, YugabyteDB with HASH distribute on the first primary key column.

I can add Items, which are rows in the table:

All this is visible in the database. That's the beauty of it: real SQL stuff, where you may add indexes, triggers, or any PostgreSQL feature you need for optimization:

[opc@dev example-project]$ psql -h localhost -p 5433 -U yugabyte directus
psql (13.5, server 11.2-YB-2.13.0.0-b0)
Type "help" for help.

directus=# \d "Franck"

                                          Table "public.Franck"
    Column    |            Type             | Collation | Nullable |               Default
--------------+-----------------------------+-----------+----------+--------------------------------------
 id           | integer                     |           | not null | nextval('"Franck_id_seq"'::regclass)
 status       | character varying(255)      |           | not null | 'draft'::character varying
 sort         | integer                     |           |          |
 user_created | uuid                        |           |          |
 date_created | timestamp with time zone    |           |          |
 date_updated | timestamp with time zone    |           |          |
 my_date      | timestamp without time zone |           |          |
Indexes:
    "Franck_pkey" PRIMARY KEY, lsm (id HASH)
Foreign-key constraints:
    "franck_user_created_foreign" FOREIGN KEY (user_created) REFERENCES directus_users(id)

directus=# select * from "Franck";

 id | status | sort |             user_created             |        date_created        | date_updated |       my_date
----+--------+------+--------------------------------------+----------------------------+--------------+---------------------
  1 | draft  |      | 05ba2305-d788-4a6b-af35-e75ee69b862c | 2022-04-01 15:12:54.236+00 |              | 2022-04-01 17:12:00
(1 row)

directus=#

Finally, here is a simple dashboard based on the table:

This is the beauty of No Code database application development: the consistency and availability of data is guaranteed by the SQL database, and building the presentation layer on top of it is easy and nice.