Bespoke Projects + Core Schema Versioning Strategy

[matthewlarkin]

Overview

While making multiple projects, I wanted to have an underlying "core schema" shared across projects. The core schema allows the inheriting of common resources that are probably stable across multiple bespoke projects (resources such as people, accounts, roles, permissions, etc). The core schema won't change from project to project, but it may get enhanced over time (such as adding a middle name to people). Maintaining a clear versioning system would be nice. So here's a straightforward approach I've come up with to ensure both compatibility and clarity.

Versioning Format

To keep track of migrations across different projects, we can adopt a simple naming convention consisting of six initial integers. These encode both our core schema and the project version: xxxxxx_description.sql:

• xxxxxx represents a six-digit number:
  • The first three digits denote the core schema version (001 means version 1).
  • The next three digits specify the project-specific version that inherits the core schema version (001 here means version 1.001 overall).

Examples

Initial Setup

A new project integrating the initial version of our core schema and adding initial project-specific migrations:

• 001000_core_schema.sql - Installs version 1.0 of core schema.
• 001001_create_users_table.sql - Adds a 'users' table to the project.

Subsequent Updates

Updating the core schema and adding further modifications:

• 002000_core_schema.sql - Updates to version 2.0 of core schema.
• 002001_add_logging_feature.sql - Adds logging capabilities to the project (project is now version 2.001 -- inheriting core schema version 2.0).

New Projects, Up-to-date Core Schema

New projects may now look like this:

• 001000_core_schema.sql
• 002000_core_schema.sql
• 003000_core_schema.sql - Installs version 3 of core schema.
• 003001_modify_users_table.sql - Makes modifications to the 'users' table (project is now version 3.001 -- denotes a core version of 3, and project sub-version of 1).

Benefits

This simple naming convention provides a few benefits:

• Clarity: The version and purpose of each migration are immediately apparent from the filename.
• Order: Migrations are still applied in a sequential order that respects SQLPage requirements.
• Flexibility: Projects can update their core system version independently of their bespoke migrations, allowing for flexible development paths, as long as the bespoke migrations don't alter the core schema tables of course.

Thoughts?

Let me know your thoughts! Have you run into similar versioning issues with bespoke projects and the desire to maintain a core schema across them? How have you handled it? I'd love to hear your thoughts and experiences!

Matthew

[lovasoa]

Hi Matthew! I hadn't thought of it, but it's a great idea 💡

Would you be interested in writing an introduction to SQL migrations for the official website? Currently we only have a few lines about it in the "getting started" guide, but I think it's a topic that would deserve a better guide in itself.

I know @DSMejantel recently had issues with migrations and ended up manually moving data from an old schema to a new one for his production application.

Having a guide with common pittfalls, tips and tricks, and examples would really help.

[matthewlarkin]

Hi @lovasoa, I would be happy to write a migration intro! I just came back from a short break and can do that this week. Should I just provide a pull request in the current documentation, or do you mean to publish this on the blog?

[lovasoa]

Yes, I think a pull request to the official site would be great!

And you can link your blog at the bottom ;)

[DSMejantel]

Hi,
It can be a good idea. For new sqlpagers, it's difficult to understand how the migrations are working. At the beginning of my project, I usually remained with all migrations. Recently, I've been having a problem with the 004_base.sql file, and I got a bad gateway error on my server. To resolve the issue, I updated my local test base on the server. However, I don't think this is a good solution.

Your method looks interesting.

[lovasoa]

Your "bad gateway" error is probably due to SQLPage refusing to start after a failed migration.

I think you could have avoided that by testing your migration locally with a backup of your production database.
You would have seen the error, had the occasion to to fix it, and push it to your server only when you knew it would work.

For large schema changes in SQLite, often you have to recreate a table from scratch? In this case, the migration should look like

create table my_table_new(...); -- create the new version of the table with a temporary name

insert into my_table_new(column1, column2) select column1, column2 from my_table; -- in the select statement, you are free to transform the data from the old table any way you want

drop table my_table; -- now that the data is copied into the new table, delete the old one

alter table my_table_new rename to my_table; -- now that the name my_table is free, give the new table its final name

and always make sure to backup your database before migrations

[DSMejantel]

Hi,
I retried on a local test server and the terminal done :
[2024-05-09T06:30:47.293Z ERROR sqlpage] Failed to apply database migrations from "migrations"
Caused by:
migration 0 was previously applied but is missing in the resolved migrations

It's because I have removed the files on the migrations folder ! So, we have to keep all the migrations files on this folder.
;-)

[lovasoa]

Yes, once a project is started and has data in production, existing migrations should never be modified or deleted. Everything should be done by adding new migrations. Even a migration that was applied by mistake should be fixed by creating a new migration, not editing the existing one.
That's why it's so important to test migrations locally. We currently do not explain any of that on the official website; we should at least give a basic introduction to migrations.