Make it compatible with Yugabyte database

[ajunca]

Hi,

I'm trying to use kratos with yugabyte database (using the postgress api) without success. Yugabyte is a relational distributed database that have a postgress api (with still some limitations, as with in here).

The problem comes when kratos tries to create/migrate the tables. This is the kind of error I get:
An error occurred while connecting to SQL: error executing 20191100000011_courier_body_type.postgres.up.sql, sql: ALTER TABLE "courier_messages" ALTER COLUMN "body" TYPE text, ALTER COLUMN "body" SET NOT NULL;: ERROR: This ALTER TABLE command is not yet supported. (SQLSTATE 0A000)

This is an known ongoing issue with ALTER sql sentence in yugabyte, link here.

I was thinking that maybe it is kind of "easy" to replace the problematic ALTER sentences and have it working with yugabyte. Such feature would be very handy for example when high availability is required.

[zepatrik]

I guess you could migrate an postgres instance, dump the schema and use the dump to migrate your database. I'm sorry but for us it is already a lot of work to manage migrations for mysql, cockroach, postgres and sqlite, you will have to work around limitations by writing your own migrations. Please report if you succeed 😉

[ajunca]

Thank you very much for the advice. Here are my results.

First of all, I'm not very used to database management. Said that, I was able to migrate the database from postgres to yugabyte, but still unable to run Kratos (but is another problem, I will open a new issue for that).

Everything is done with docker containers. What I did was:

1. Run Kratos (migration mode) with a postgres database.
2. Dump postgres database schema using ysql_dump (the yugabyte version of pg_dump). At this point we can shutdown kratos and postgres.
3. Run Yugabyte DB.
4. Create and set role for kratos in the DB.
5. Import the previously dump using ysqlsh.
6. Run Kratos, but now pointing to the yugabyte db.

As I said, I was able to migrate the database, but still unable to run Kratos (but I can't neither run Kratos alongside with postgres, so probably is a problem related to docker-compose configuration or Kratos configuration, although I use basically the one served as example on the git repository).

One thing that still can't really picture, is what happens when the next update rolls out. I would be able to update the yugabyte schema? Or I will have to recreate everything again and re-import the data?

[zepatrik]

Depends on the migration, if it is compatible with yugabyte you should be able to apply it using the postgres driver. But as you know, not all migrations are compatible and you will have to figure out a way to work around the specific problem. Another example is sqlite, it does not allow adding columns to existing tables. We have to work around that like in this migration.
If you want to publish your migrations as it's own repo, we would be happy to feature it as a community project and might be able to help out more. You can just start off with the initial migration you have from your dump and add upcoming ones.

[aeneasr]

Yup, exactly! I'll be closing this one because compatibility with YugaByte is not something we can work on!

[jpollard-cs]

Just coming across this - Yugabyte was built using the Postgres Query layer so there's actually a very good chance there is compatibility here.

[LeeWong]

Alter commands have been improved. Is this still an issue?