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 LO~C~B: 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 :grin:

[taophp]

And, please, let me know. 😁

[infogulch]

I've considered this for a minute; here are my thoughts as of now:

• LOB migrations is a neat idea for "componentizing" an application. Whether it works out in practice is an open question, but it seems worth a shot.
• The classic "apply centrally managed migration files in sequence" is a tried and true pattern, with the downside of manual maintenance of migration files.
• In either case any kind of builtin that manages migration tables internally is a non-starter because there is no universal SQL syntax, thus there is no way for xtemplate to maintain or query a migrations table in a universal way.
  • You could pass all the queries that a migration builtin would need but that gets annoying quickly.

Porque no los dos?

Here's a sample implementation for both migration strategies written in pure template language, specifically for the sqlite driver. You can put this in a file named e.g. .migrations.html and then invoke the migration template like {{template "migrate" (dict "DB" .DB "ID" 2024032816100 "STMT" "ALTER TABLE user ADD last_login DATETIME")}}:

{{define "create-migration-tables"}}
{{.DB.Exec `CREATE TABLE IF NOT EXISTS migration_files(id INT PRIMARY KEY, name TEXT NOT NULL) STRICT;`}}
{{.DB.Exec `CREATE TABLE IF NOT EXISTS migrations(id ANY, run_date INT NOT NULL, stmt TEXT NOT NULL, error TEXT, result TEXT, ok INT GENERATED ALWAYS AS (error IS NULL)) STRICT;`}}
{{.DB.Exec `CREATE INDEX IF NOT EXISTS successful_migrations ON migrations(id) WHERE ok;`}}
{{end}}

<!--
'migrate' accepts a dot value with fields DB, ID, and STMT. Performs a database
migration by executing the statement if a migration with the same ID has not
succeeded. Must call `create-migration-tables` once before calling this
template.

Invoke like: {{template "migrate" (dict "DB" .DB "ID" 2024032816100 "STMT" `ALTER TABLE user ADD last_login DATETIME`)}}
-->
{{define "migrate"}}
{{$applied := .DB.QueryVal `SELECT COUNT(id) FROM migrations WHERE ok AND id=?;` .ID}}
{{if eq $applied 0}}
{{$result := try .DB `Exec` .STMT}}
{{if $result.OK}}
{{$.DB.Exec `INSERT INTO migrations VALUES(?,?,?,NULL,?);` .ID now .STMT ($result.Value | toJson)}}
{{else}}
{{$.DB.Exec `INSERT INTO migrations VALUES(?,?,?,?,NULL);` .ID now .STMT ($result.Error | toJson)}}
{{fail (printf "failed to apply migration. id: %d, sqlerror: %s" .ID $result.Error)}}
{{end}}
{{end}}
{{end}}

<!--
'migrate-dir' accepts a dot value with fields DB, DIR, and RE. Performs a
database migration using the files listed in DIR that match the regex pattern
RE. The RE patternn must have a subpattern that matches a nonzero numeric string
within the file name which is the migration id. Migration IDs greater than the
current `PRAGMA user_version` are run in id order. Must call
`create-migration-tables` once before calling this template.

Invoke like {{template "migrate-dir" (dict "DB" .DB "DIR" .FS "RE" `^schema\.(\d+)\.sql$`)}}
-->
{{define "migrate-dir"}}
{{range .DIR.List "."}}
    {{$id := atoi (regexReplaceAll $.RE .Name "$1")}}
    {{if eq $id 0}}{{continue}}{{end}}
    {{.DB.Exec `INSERT INTO migration_files VALUES(?1,?2) ON CONFLICT DO UPDATE SET name = ?2;` $id .Name}}
{{end}}
{{range .DB.QueryRows `SELECT name,id FROM migration_files WHERE id > ? ORDER BY id;` (.DB.QueryVal `PRAGMA user_version;`)}}
    {{$stmt := $.DIR.Read .name}}
    {{$result := try $.DB `Exec` $stmt}}
    {{if $result.OK}}
    {{$.DB.Exec `INSERT INTO migrations VALUES(?,?,?,NULL,?);` .id now $stmt ($result.Value | toJson)}}
    {{$.DB.Exec (printf `PRAGMA user_version=%d;` .id)}}
    {{else}}
    {{$.DB.Exec `INSERT INTO migrations VALUES(?,?,?,?,NULL);` .id now $stmt ($result.Error | toJson)}}
    {{fail (printf "failed to apply migration. id: %d, sqlerror: %s" .ID $result.Error)}}
    {{end}}
{{end}}
{{end}}

This sidesteps the builtin problem: put this template anywhere in your project and customize it to match your db and desired migration strategy, then use it as you like. IMO the readability of both implementation and invocation is not perfect but is tolerable.

I'll add this to the test/example dir soon.

[taophp]

Just a point:

In either case any kind of builtin that manages migration tables internally is a non-starter because there is no universal SQL syntax, thus there is no way for xtemplate to maintain or query a migrations table in a universal way

It's a fact that there is no universal syntax. But it's another fact that, in the PHP world (where I come from), frameworks such as Symfony or Laravel, have learned to work with different databases (MariaDB, Postgres, SQL server, SQLite out of the box, Oracle and DB2 with extensions) and manage migrations. I agree that there can be problems and I have encountered some (don't try to add an index with UPDATE if you are using SQLite). But I believe it is possible to propose a "generic" solution (but not universal, neither optimal, I agree), by reducing it to common syntax elements.

Furthermore, you may be interested in Phalcon's approach, which offers its own SQL dialect, PHQL (Phalcon Query Language). Phalcon uses queries written in this dialect to "translate" them into the dialect of the database used (again: MariaDB, Postgres, SQL Server, SQLite). To achieve this, it uses a parser using the same technology as SQLite.

I understand that all that stuff is a big challenge and that it is not your priority and I understand you decided that migrations should stay out of the scope of xtemplate... for ever?

In any case, I should think about a solution to manage migrations on my side, following the valuable indications you gave in your last comment. Thanks for your help. Really.

[infogulch]

Just for scale, xtemplate is 2.5k lines of Go code, maybe 3k if you include the caddy integration. The most popular sql migration library for Go, golang-migrate/migrate alone is 18k lines not including its 185 dependencies.

I think this says two things: 1. xtemplate is pretty small for what it does, and 2. the "just implement migrations" hill is way taller than it might seem at first glance.

I might consider adding migrate as an optional feature (as a standalone func or a dot provider, not sure which) in the future. Migrate strongly guides users to the numbered-migration-files migration strategy, but appears to support running arbitrary migrations on demand (see MIGRATIONS.md, Migrate.Run).