Feature request: database migrations

[taophp]

Just in case : by migrations, I mean updating the database schema, not moving from one database to another.

Do you have an idea about how to handle migrations ?
I've just had one. Let me share with you (first draft?).

I was inspired by HyperScript init feature. It is used to launch initialisation script on page load, while preserving Locality Of Behaviour (LOB). I think we could proceed the same way and write migrations in templates, at the meaningful places for the dev. (So, potentially, migrations could be anywhere, even grouped in a dedicated template, even if that does not follow LOB design - should be documented if the dev disagree with LOB in this case).

So we should write migrations somehow like this:

{{ migration 2024032816100 .Query `ALTER TABLE user ADD last_loggin DATETIME` }}

Migrations should be run when parsing templates, when Caddy start or reload config.
The database should have a reserved table migrations (or xtemplate should try to create it when meeting the first migration). This table should have 3 columns:

• id: the migration id, should be a human friendly timestamp (2024032816100 in my previous example),
• date_run: store when the migration ran,
• log: store the queries and their output if any.

So, this table will allow xtemplate to ensure to run migration only once, and store logs about how they ran.

What do you think ? @infogulch

[taophp]

@infogulch I noticed that you are already thinking about this.
The approach described by David Rothlis have some good points, but:

• it does not follow LOB design,
• it is designed to deal efficiently with some SQLite limitations, that others DBs do not have, and this way of doing things may tie us to SQLite (which I shall disagree),
• I expected it to be very inefficient, if not dangerous, with big databases.

[infogulch]

Yes I agree that tying something like migrations too closely to a specific strategy is not a good idea, but maybe there's some useful general techniques we can use from articles like this.

In case you weren't aware, there is already functionality to run initialization routines at startup. Define a new template with a name that matches INIT .* and it will be executed once before the instance is loaded successfully. Note: the .* is there so each routine has a unique name, since templates will overwrite each other if they have the same name.

I use this inside my xrss application: (note it's using old syntax, I'll update it soon tm)

https://github.com/infogulch/xrss/blob/master/templates/.schema.html

{{define "INIT configure-schema"}}
{{$user_version := .Tx.QueryVal "PRAGMA user_version"}}
{{ .Tx.Exec (.ReadFile "schema.sql") }}
{{end}}

• Defines a new init template named INIT configure-schema
• It executes a query to get the sqlite user_version pragma, which I intended to use to select schema upgrades, but it doesn't do anything right now.
• It reads the fs file "schema.sql" and executes it to apply the schema

I will acknowledge that this is not fleshed out, but maybe you can see the basic skeleton of a usable migration process in there.

What do you think?

[taophp]

No, I was not aware of this possibility to run routines at startup. Sure, it can be useful.

Question : do all those init templates run at each startup ? Surely, if yes, I suppose we could manage conditional migrations at startup using IF EXISTS and such statements, but it should decrease booting performances, increase the amount of code... and bugs. So, it seems better to ensure that those routines, that should run only once for ever, are tracked at the module level, not self-managed.

Another point: I really like the idea of providing the migrations along side the code using it. It should upgrade LOB at the next level. I was thinking about some components that you should just copy/paste to make them work, something like HyperUI but for xtemplate/htmx/tailwind/hyperscript. This seems impossible with the init templates you propose.

BTW, I have started a project exploring my ideas about all that stuff. I called it Lazy Lob Web. I'll appreciate your comments about it, if any.

[infogulch]

Yes all init templates run on every startup. Sure you could do it all in a single SQL script like I did with xrss, but templates have plenty of expressiveness to manage the process. Off the top of my head here's how you might iterate over sql migration files in a "migrations" directory and execute new ones:

{{define "INIT apply-migrations"}}
    {{$user_version := .DB.QueryVal "PRAGMA user_version"}}
    Applying migrations after {{$user_version}}:
    {{range .FS.List "migrations"}}
        {{if gt . $user_version}}
            Applying '{{.}}':
            {{$.DB.Exec ($.FS.Read .)}}
        {{else}}
            Skipping '{{.}}'
        {{end}}
    {{end}}
{{end}}

Some further ideas:

• Extract this routine to a named template, then invoke it local to each component with a shorter invocation
• Use the .X.Template method to execute the template and write the output to a migration results file (pending writable files, wip)

My point is that it should be possible to script anything you want with templates. That said it would be understandable if you'd rather not do this inside templates (don't knock it too quickly, browsing templated migration application result files sounds pretty nice for an admin ui). If you'd rather not use templates, then perhaps a custom DotProvider would be suitable, and if not that then writing your own migration system before starting xtemplate would probably be the next step.

I'm more than happy to document various migration strategies, link to custom migration DotProviders, consider adding general features that would make migrations easier to manage, etc. But I don't see how to make migrations a built-in without tightly tying the implementation to specific databases and drivers, which I would really like to avoid.

Your LLW project looks interesting, I'll keep an eye out for how you integrate the various components. 😄

[infogulch]

So I'm updating xrss to use the new xtemplate, and I came up with a migration strategy that is general and simple enough I would consider adding to the db provider.

It requires 3 config parameters:

1. A glob pattern to find migration files
2. A regex pattern to extract the numeric migration id from the filename
3. A SQL statement to get the database's current migration id as a int64.

If these values are set, then on start the DB provider will run a migration procedure that looks like this:

• Get the current migration id
• Scan all matching migration files in lexical order, if the extracted migration id of a file is greater than the current migration id then the file is read and Exec()'d against the database in a transaction.
• Any failure aborts the process and prevents xtemplate from initializing.
• Copious debug logging.

Notes:

• The SQL statement should be valid to run on an empty database. E.g. if it needs a migrations table it should create it IF NOT EXISTS and then query it. For sqlite this is as simple as PRAGMA user_version;.
• Each migration file is expected to update the database's current migration id on its own, probably at the end of the script.

Thoughts?

[taophp]

This seems like a great design to me considering the app as a whole. It can be a great choice and if you make it yours, I will accept it and not discuss about it again after this last comment. Let me advocate my initial proposition one last time.

First point: your proposition breaks LOCB: querying the db or reading the migrations files should be required to understand the behavior of the main code. Well, it can make sense, as, most of the time, you do not need to be aware of the details of the db structure and avoiding them in the main code make it more readable...

Second point: your proposition is inaccurate considering the development of independent components to assemble for building apps. Your migrations are defined as incremental. Adding a component requires the addition of a migration file with an id related to the history of the app, which implies that as component can not be use "as it", just with a copy/paste. Well, we could expect users of xtemplate to be able to manage such process.

To conclude: your solution seems a very good one, even if I prefer mine. It's up to you now 😁

[taophp]

And, please, let me know. 😁