What is the best way to handle schema migrations?

[mindreframer]

Currently the triggers are only attached during the start phase of marmot. Individual schema changes (new tables / columns) are not reflected at runtime and require either a manual way to apply them on all DBs in the cluster + restarting each marmot node to update triggers and changelog tables.

Are there some recommendations how to approach it in a straightforward and maintainable way?

Best,
Roman

[maxpert]

Yes, right now what you have described is the case and the only way to handle schema changes is via stopping Marmot, dropping a snapshot on all nodes, and restarting the Marmot process. The major reason to not implement schema changes propagation is because:

• Messages can arrive out of order in a sharded configuration. So it's quite possible that some of the logs will start failing because they have new schema changes while the table DDL has not been applied yet.
• I personally believe just like when you deploy your app with new schema in SQLite you have general tendency to deploy new code-base and restart the app. Schema changes are not that frequent, so like any DBMS when you run migrations you can get a down time, it should be expected here as well.

Having said that, here are my recommendations:

• Have some scripting/tooling (I have a dirty version in Python version that I can probably post in a gist) that can:
  • Stop Marmot
  • Performs Marmot cleanup (-cleanup) so that trigger tables are dropped.
  • Takes DB snapshot. You can either use Marmot (-save-snapshot) or use your own script to do so.
  • Applies the migration from some .sql file (Read below on questions I have on how people get their migration scripts)
  • If schema migration was successful, take a snapshot again!
  • Starts Marmot
• Ideally this script lives along with your code base, and can be part of your CD job.

I've been brain-storm about this topic, and I have questions that I believe community can answer. It will really help me develop a solution that works really well for community:

• How do you maintain / run migrations today with SQLite? Specially when you have made changes that can be breaking changes.
• If you have 5 nodes with same DB running what is preferred distribution mechanism? I am imagining some sort of rolling update CD job that brings up a node, runs migration on a snapshot, before plugging it into traffic. Is that true?
• Would it matter if Marmot introduces a dedicated topic in NATS (Stream) for migrations. That means you will have to use Marmot some internal HTTP endpoint enqueue an upgrade script, which will effectively let Marmot know update is here, and with some topic name versioning tricks it should be able to get you to new schema, without restarting Marmot. It's a bigger change that will need some work, but I am curious if community will prefer that approach. I can actually expose HTTP endpoint in that case, my biggest fear in this case is that people will end up in situations where they have not updated their code but ran the script (but that's true for today's RDBMses too).

Again I am open to suggestions and contributions on this topic.

[mindreframer]

@maxpert thanks for the elaborate response!

The algorithms that you describe involves downtime. Also, if I understand correctly, the migration runs on a single marmot node , and the distribution to other nodes happens via snapshots, so that other nodes would be required to download the snapshot to be up-to-date? If that is the case, it would be problematic with bigger database (100s of GB), and the migration would involve a long time.

How it could work:

maybe it would be possible to have a special table, that marmot would use to signal the state of the migration. A rolling update is good, and having some way to see the progress of the roll-out through the cluster would be great.
Topic in NATS for migration:

• this would be great! We could mark each database as being in-progress for migration (possibly lock it for modification), stop accepting writes, drain the pending changes from the changelog tables and then run the migration script in parallel on all nodes. Once the complete cluster is finished, we mark tables as ready for writes and release the lock.
  The application could check status and display a friendly maintenance page during migration.

How are migration executed in general is a very broad question, since there are so many tools to manage migrations.
Some framework specific (Rails / Phoenix / Django), some language specific (https://atlasgo.io/, https://github.com/amacneil/dbmate, https://flywaydb.org/). I guess relying on those instruments would make support very hard / impossible, so the best way would be an internal simple way to apply a SQL script on all DBs in the cluster in a controlled fashion.

I guess the simplest would be to post a SQL file with 2 attributes: name + timestamp as identifier for this particular migration. We could start with only forward migrations, because supporting reversible migrations does not really work in production. We would also need to store applied migrations in a table, maybe "__marmot_migrations"?

Maybe we should collect those ideas in a documents (could be a markdown file in the repo), with arguments why those design decisions where preferred. From that document we could distill the minimal feature set, that would just solve the issue at hand.

[maxpert]

@maxpert thanks for the elaborate response!

The algorithms that you describe involves downtime. Also, if I understand correctly, the migration runs on a single marmot node , and the distribution to other nodes happens via snapshots, so that other nodes would be required to download the snapshot to be up-to-date? If that is the case, it would be problematic with bigger database (100s of GB), and the migration would involve a long time.

No we are not restoring snapshots, I am using snapshots as safety measure. You actually run the migration scripts everywhere.

How it could work:

maybe it would be possible to have a special table, that marmot would use to signal the state of the migration. A rolling update is good, and having some way to see the progress of the roll-out through the cluster would be great. Topic in NATS for migration:

• this would be great! We could mark each database as being in-progress for migration (possibly lock it for modification), stop accepting writes, drain the pending changes from the changelog tables and then run the migration script in parallel on all nodes. Once the complete cluster is finished, we mark tables as ready for writes and release the lock.
  The application could check status and display a friendly maintenance page during migration.

How are migration executed in general is a very broad question, since there are so many tools to manage migrations. Some framework specific (Rails / Phoenix / Django), some language specific (https://atlasgo.io/, https://github.com/amacneil/dbmate, https://flywaydb.org/). I guess relying on those instruments would make support very hard / impossible, so the best way would be an internal simple way to apply a SQL script on all DBs in the cluster in a controlled fashion.

I guess the simplest would be to post a SQL file with 2 attributes: name + timestamp as identifier for this particular migration. We could start with only forward migrations, because supporting reversible migrations does not really work in production. We would also need to store applied migrations in a table, maybe "__marmot_migrations"?

Excellent you are essentially converging to same idea of having migration script essentially being published on NATS, and then applying them everywhere.

Maybe we should collect those ideas in a documents (could be a markdown file in the repo), with arguments why those design decisions where preferred. From that document we could distill the minimal feature set, that would just solve the issue at hand.

I can introduce a RFC category under discussion, but in long run we might have to move to different repo like https://github.com/reactjs/rfcs

[mindreframer]

No we are not restoring snapshots, I am using snapshots as safety measure. You actually run the migration scripts everywhere.

Awesome, it was not clear to me, that we run the migration on each node.

Excellent you are essentially converging to same idea of having migration script essentially being published on NATS, and then applying them everywhere.

Yes, I think this is a great idea to make the orchestration for the cluster-wide migration the responsibility of marmot. It is a critically important part of every application in production, and it should be a clear and straightforward algorithm what does not depend on frameworks / languages.

I can introduce a RFC category under discussion, but in long run we might have to move to different repo like https://github.com/reactjs/rfcs

Great idea! I think those could be complementary, discussion under discussions and the final summary in a markdown file. I guess a rfc-folder with a couple files would be enough for the start. Similarly to the docs folder.

[gedw99]

Here is an alternative way that solves all of the above issues raised and also gives extra features

Design to make it scale well and upgradable with no downtime.

1. Caddy L4 as a SQL Proxy so that all IO to SQLite Db(s) is able to be inspected, and all clients are just standard Sqlite clients.

why ?

• Serverless: if there is NO DB, you can start one up and then let the IO through. So you can scale to zero !

  • On Fly or Hetzner you have Automation API to do this, so you can add or delete servers and hav hem boot the right Docker image.
  • If you don't want to use Docker, you can use NATS object store to place the DB on the new server.

• DB Schema migrations: You can listen for these in the Caddy proxy, and then use Marmot / NATS to upgrade them using progressive Lame duck mode. Lame duck mode is the same way that you upgrade NATS Server binary.

  • Now we can also upgrade SQLite itself as well as the Schema.

  • Also clients can be connected to only those with the latest Schema using introspection, thus requiring no downtime due to clients hitting DB's with old schema. Requires a minimum of 3 Sqlite DB's.

• LB and HA so that calls to the Sqlite DB are balanced.

• Multi tenant. Now it's easy to do multi tenant SQLite, because we can see what DB they are trying to connect to.

• Caddy itself can be LB, so you can run this multi region. It works on FLy too.

• Users can run many Schema versions of their DB. Dev and Prod. Marmot can manage the upgrade for them.

Changes:

• Caddy compiled with Marmot Caddy Module This runs even if there are no live DBS.

• Marmot needs to store the Schema version per DB, so that we can ensure clients only call DB's that match the required Schema version.

• NATS cluster self running. Do not have NATS embedded in the Marmot Caddy Module. HA for DB is only sensible if NATS is also HA.

• Caddy Storage plugin using NATS KV, so that Certs are also stored in NATS. I prefer to use NATS KV and NATS object store for all data rather than S3, because we need it anyway, so there is no point in adding S3 as it just makes things more complicated for zero gain. Less is more ! There is a ADR PR for NATS to be able to manage Certs, just like how Caddy can manage Certs already, so we can just use Caddy to also manage the NATS Certs.

• Caddy certs will be stored in NATS using
  https://github.com/HeavyHorst/certmagic-nats. See: https://caddyserver.com/docs/json/storage/

• Caddy L4 for SQL DB proxy. Links:

https://github.com/mholt/caddy-l4

https://caddy.community/t/cant-connect-to-database-behind-layer4-server/16168/5

https://medium.com/@panda1100/how-to-setup-layer-4-reverse-proxy-to-multiplex-tls-traffic-with-sni-routing-a226c8168826

This all results in one binary because Caddy can be compiled with the Caddy Marmto Module. Writing Caddy Modules is not complex and the APi is quite stable.

---

You could use NATS as the proxy, but then the client also needs NATS, which is a leaky abstraction. Hence why the Caddy L4 proxy.

[maxpert]

IDK if I full comprehended the solution or not. But the idea of sitting as API layer on top of DB is exactly what I don't want Marmot to be a compulsory feature. I've thought long and hard about this, I don't think anything beats SQL as DSL.

[gedw99]

You still use sql as dsl .

[maxpert]

It's long and painful story, maybe worth a conference speaker talk; but rest assured when you need full control on every inch of performance, and solid five nines nobody uses ORM based query systems. Everybody switches to object mappers and hand written queries. I've got scars to prove it!

[gedw99]

The caddy l4 just passes the sql through it. It does nothing else unless you want it to .

So it’s not maybe what you think . Not an orm .

[gedw99]

Ok so forget the caddy idea . It’s too complex.

i agree with the sentiment of using nats to do the schema migration because it’s a no downtime solution and we already have it , so no new parts to be added.

If devs want to integration it with CI then they could call out to nats from their ci.

[mindreframer]

There are couple projects that implement zero-downtime migrations for Postgres:

• https://github.com/xataio/pgroll (golang)
• https://github.com/fabianlindfors/reshape (rust)

There are also couple links with ideas:

References
This is a list of projects and articles that helped as inspiration, or otherwise are similar to pgroll:

• Reshape by Fabian Lindfors
• PgHaMigrations
• PostgreSQL at Scale: Database Schema Changes Without Downtime
• Zero downtime schema migrations in highly available databases
• Expand and contract pattern

Maybe there is something that we could apply also for Sqlite + Marmot.

[maxpert]

Interesting seems like it enforces a full framework of how migration should be done. I've been working with couple of people on how they handle migrations and so far for SQLite specially people had their typical flow of run a script to alter tables. I can may be inspire page or two out of pgroll to see how that can be mapped into SQLite workflows.

[gedw99]

Yes it does.

The only snafu will be deletes . It’s why cascade deletes don’t work.

this is because any crdt can never know if it should delete yet . Kind of like garbage collection. This problem is all over the crdt / Martin Kleppman writings.

I think phantom deletes is the best work around, but have not testing it yet

[dragonballa]

any progress on this?

[gedw99]

I have been reading up on this problem space and found a system that is like marmot

https://vlcn.io/

https://github.com/vlcn-io

it’s SQLite with crdt .

They are able to do live sql migrations by modifying the tracking tables , so as to not break the sync tables. That’s a tough problem to solve.

there are 3 working examples too .

@maxpert I am guessing you have read all their stuff ?

i personally think that NATS is required for all these db sync systems. The reason is because I always end up have a farm if SQLite db and I want to do a sql subscription and I want to make sure that only 1 of the clients handles that subscription feed event . This is a classic global pattern for me and almost every other system. Only once ( or at least once ).

So nats is needed for that anyway.

This stuff is HARD :)