Directus on YugabyteDB
Tags: yugabytedb, nocode, distributed, sql
Published: 2022-04-01 16:05:02

Here is my first attempt to run [Directus](https://directus.io/) 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/](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](https://knexjs.org/). 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:
```bash
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:
```bash
npm init directus-project example-project

```

I fill-in the database connection information:
![npm init directus-project](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rrwk79gb72iqbeu4fjzr.PNG)

This, in addition to initializing the database, creates a project directory where I move to:
```bash
cd example-project

```

Now starting a YugabyteDB database:
```bash
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](https://cloud.yugabyte.com/signup)) and you will just use the connection information for it.

Copying the schema from PostgreSQL to YugabyteDB is easy with `pg_dump`:
```bash
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:
```bash
sed \
 -e '/DB_PORT=/s/5432/5433' \
 -e '/DB_USER=/s/postgres/yugabyte'
 -i .env

```

Starting Directus (I'm following the [Quickstart Guide](https://docs.directus.io/getting-started/quickstart/)):
```bash
npx directus start

```
The http port is mentioned when starting:
![Start Directus](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/smzqshxhyvw6if4o09az.PNG)

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

![Login](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pixxnfbmmiexlt634g70.PNG)

I can create a Collection, which is actually a table in the database:
![Create Collection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7qikn77rb3uf0k69viw3.PNG)
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xv469pnfzsbmj1pa20er.PNG)

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:
```sql
[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:
![Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fi1t65lc962x17amtlmn.PNG)

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.