Metadata in migrations files?

[simon-brooke]

As you know I'm working on parsing Migratus migrations and using that parse-tree to build a complete application skeleton wrapping the database. Obviously also there is information required to build an application which isn't present in the raw SQL. Consequently I'm wondering about adding metadata into the files, and I want to be sure the way I'm considering doing that won't get in the way of anything you're planning.

What I'm thinking is something like this:

CREATE TABLE IF NOT EXISTS addresses (
    id serial NOT NULL PRIMARY KEY,
    address character varying(256) NOT NULL, -- meta {:distinct :user}
    postcode character varying(16), -- meta {:validation #"^([A-PR-UWYZa-pr-uwyz0-9][A-HK-Ya-hk-y0-9][AEHMNPRTVXYaehmnortvxy0-9]?[ABEHMNPRVWXYabehmnprvwxy0-9]? {1,2}[0-9][ABD-HJLN-UW-Zabd-h-jln-uw]{2}|GIR 0AA)$"}
    phone character varying(16),
    district_id integer REFERENCES district(id), -- meta {:prompt "Electoral District"}
    latitude real, -- meta {:min 54 :max 61}
    longitude real
); -- meta {:shelf-life (* 24 60 60 1000)} 
--;;

In summary, metadata would immediately follow the syntactic element it relates to, would start with the string -- meta, would be restricted to a single line, and would have a Clojure map as its value.

Would this cause any problems for you?

[yogthos]

The metadata approach is reasonable, and I'm already using -- :disable-transaction metadata hint. A single line meta that would point to some EDN seems like a clean approach.

[simon-brooke]

So it would probably be preferable to use -- :meta {:foo 5 :bar "baz"} rather than -- meta {:foo 5 :bar "baz"}

(by which I mean meta should be syntactically a Clojure keyword, :meta), would you agree?

[yogthos]

Yup, and that would also be in line with HugSQL syntax.

[dijonkitchen]

Here are the HugSQL docs on it: https://www.hugsql.org/#sql-file

Is this still being worked on?

[yogthos]

I haven't been working on it, but I'm open to the idea. There is already some ad hoc metadata parsing happening, so it would probably make sense to turn it into a multimethod. I'm also leaning towards declaring metadata at the top of the SQL statement as opposed to doing it inline. One idea could be to to just put EDN in a multi-line comment below the -- :meta decalation, e.g:

-- :meta
/*
{:foo "baz"
 :bar "baz"}
*/

Then the API would need to be extended to provide the list of migrations along with the metadata. Perhaps each migration could be represented as a map:

{:id "..." ; migration id
:meta {...} ; map with the metadata
 :status :complete ; :complete/:pending
 :sql "..." ; migration string
}