DEV Community

Franck Pachot for Yugabyte

Posted on

Directus on YugabyteDB

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.
Enter fullscreen mode Exit fullscreen mode

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"

Enter fullscreen mode Exit fullscreen mode

I initialize a Directus project:

npm init directus-project example-project

Enter fullscreen mode Exit fullscreen mode

I fill-in the database connection information:
npm init directus-project

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

cd example-project

Enter fullscreen mode Exit fullscreen mode

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"

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

npx directus start

Enter fullscreen mode Exit fullscreen mode

The http port is mentioned when starting:
Start Directus

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

Login

I can create a Collection, which is actually a table in the database:
Create Collection
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:
Insert Items

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=#

Enter fullscreen mode Exit fullscreen mode

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

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.

Discussion (1)

Collapse
w0kyj profile image
Jonathan Wagner

Great entry - really appreciate the detailed walk thru. Look forward to improved support in the future :)