Seeding/fixtures/migration hooks

[colinhacks]

Various ideas relating to fixtures: how they are specified and how/when they are applied.

Single seed command

An initial decision to make is whether fixtures/hooks need to be associated with a particular revision. I like the idea of a single project-wide seed that evolves as the project develops, instead of a series of smaller per-migration seeding steps.

Prisma allows users to provide an arbitrary seed command in their package.json. Usually this is something like node seed.js. Whenever users run prisma db seed, prisma migrate reset, or prisma migrate dev (a dev only equivalent of edgedb migrate) the seed script is executed.

We could do something similar, where a seed command is specified in edgedb.toml. This opens up some options.

• edgedb migration seed: drops all data in the database and re-runs the seed script
• edgedb migration sync: this opens the door to potentially destructive migration commands like edgedb migration sync to clear the database, edit the migration history, and re-seed the db.

[edgedb]
server-version = 2
seed = "node seed.js"

Squash friendliness

Another reason to consider a single project-wide fixture is to accommodate any future migration squashing functionality. If each individual migration can be associated with a seed script, it's not clear squashing would work.

Independent fixtures (per Paul's comment)

• Add dbschema/fixtures
• Each subdirectory is a fixture consisting of a set of files
• Applying the fixture will apply these files in lexicographic order by filename
• .edgeql files are supported out of the gate, plus files (.js, .py) with an appropriate shebang

This may involve a new edgedb fixture command group

$ edgedb fixture apply -f benchmarks

The command above would execute the files in dbschema/fixtures/benchmarks.

Internally we have:

type sys::Fixture {
    required property filename -> str;
    required property hash -> str;
    required property timestamp -> datetime;
}

This records each fixture applied. So that edgedb fixture apply will only apply new fixtures, and warn when hash of the file changed.

Migration-coupled fixtures

Client-driven

Under this proposal, users can provide a custom "migration executor" script. That performs additional actions before after the migration itself. The content of the migration script itself can be provided in an environment variable.

dbschema/migrations/00005.js

.toml configuration

Fixture-related metadata can be supplied in a per-migration .toml file that lives next to the .edgeql file. For instance, dbschema/migrations/00005.edgeql would correspond to dbschema/migrations/00005.toml. This .toml file could contain seed data, EdgeQL queries, or a set of pre-post migration commands.

[migration]
run_queries = ['seed_users.edgeql', 'seed_posts.edgeql']
pre_hooks = ["echo 'hello'"]
post_hooks = ["node ../scripts/seedUser.js"]

dbschema
├── default.esdl
├── migrations
│   ├── 00005.edgeql
│   ├── 00005.toml
├── scripts
│   ├── seedUser.js

The appeal of this is that the seed file can be anything: a script, an EdgeQL file, etc. Users are responsible for managing any dependencies (node, etc) required to execute seed scripts.

Application

These fixtures/scripts will be executed by the CLI during edgedb migrate.

There could be some mechanism for categorizing fixtures. For instance, some fixtures could be marked as development-only and others as production fixtures (which act as data migrations). Which fixtures to apply would be specified when applying migrations: edgedb migrate --fixtures, dev,prod.

Atomicity

In this proposal, fixtures are treated as "data migrations" and should be applied atomically alongside the migration files. To do so, we need some way for seed scripts and the CLI to share a transaction. There are a couple ways this can be achieved.

• Set an environment variable with the transaction ID, e.g. _EDGEDB_TRANSACTION_ID. The underscore is to indicate that this is intended as an internal API. The clients & CLI would read this variable and respect it if defined. When defined, creating additional transactions should be disallowed.
• Proxy queries through the CLI. The connection details would also be passed to the seed scripts via environment variables (EDGEDB_DSN or similar). We would instruct users to call create_client without explicit parameters or transaction blocks.

Additional ideas/considerations

Data-only dumps

It would be cool if users could dump the contents (data only, not schema) of their database into a file (probably an .edgeql script) that could then be used for seeding. Potential workflow:

• User runs edgedb migration create-seed
• CLI reads the latest revision and generates the data dump, let's say dbschema/scripts/<revision-hash>.dump
• CLI finds the most recently appled migration (say 00005.edgeql), creates 00005.toml file (if necessary), and sets the value of run_queries to ["<revision-hash>.dump"]

Parameterized *.edgeql files

Per #4288 (comment)

Changelog:

1. Add sys::Fixture to "Independen Fixtures" section

[tailhook]

Starting with just random comments, on what's described:

1. Dumps do not need to be data-only. We can just apply dump and then apply migrations on top.
2. It would be nice to have .dump -> .edgeql, but it's quite hard in general way, and may even be impossible without turning off ACLs or without using O(number-of-db-rows) memory.
3. I think we need multiple directories: dbschema/fixtures/default, dbschema/fixtures/tests, dbschema/fixtures/benchmarks. edgedb -I bench_instance migration seed -f benchmarks. Otherwise people will need to invent their own way for fixtures for tests or need override of the full path to the directory with fixtures.
4. I think atomicity is needed only for "fixtures as data migrations" concept. And not for seeding test database.
5. I'm not sure we need to overload migration command group for fixtures, maybe we need a new one, since fixtures are not tied to migrations at least in current description.

[tailhook]

Some roadmap as I would see it:

Step 1

edgedb fixture apply that executes dbschema/fixtures/default/*.edgeql in alphabetical order (i.e we recommend naming files like 001_users.edgeql, 002_content.edgeql, etc.

Each script is processed by edgedb query -f-. So if you need transactional scripts you put START TRANSACTION and COMMIT in the script itself (and so script can have multiple transactions too).

Step 2

Then we can extend this into any binaries that are in the directory.

So .js or .py with shebang and exec bit set just work. As well as bash + cli or anything binary. And we require using configuration from environment in those scripts.

I think it should also work on windows, by associating .js/.py extension with the interpreter, right?

Step 3

The problem with (Step 2) is compiled languages, like Go and Rust (and probably Java, C# too). Direct symlink (e.g. to ../../../target/debug/command for Rust) is not cross-platform and also requires artifact to be already built. Similarly shell scripts might not work on windows. So some toml file can be introduced here dbschema/fixtures/default/003_generated_data.toml:

[fixture]
run = ["cargo", "run", "--bin", "fixture", "--", "--seed-default"]

Or:

[fixture.windows]
run = ["./dbschema/fixtures/script.ps1", "/Mode=default"]
[fixture.unix]
run = ["./dbschema/fixtures/script.sh", "--mode=default"]

Alternatively we can also use some cross-platform shell like nushell for scripts instead of toml.
Another alternative is to allow .sh/.ps1 pairs. But what is the reason if they contain same cargo run command anyway, right?

Notes

1. I don't see the reason to have "pre" and "post" scripts like described in the original discussion. Different datums are dependent on each other anyways so global order for all scripts is okay. So no need for .toml files in most cases.
2. I don't think we need a framework for "fixtures driven by client". If you don't like CLI workflow, just write in your language normally. Reading and executing .edgeql files should be easy, while you can submit the whole file to the server. And if it doesn't fit memory/protocol limits, the only escape hatch is to have a real tokenizer and pool.acquire (for correct transactions). Latter is possible to implement, but is too much maintenance work for such a niche use case. While former is too much to have in all language bindings.

[colinhacks]

I updated the original text to include an "Independent fixtures" proposal, feel free to make direct edits to the original post with any changes.

[tailhook]

Added sys::Fixture description

[tailhook]

Another idea is that we can parametherize .edgeql files, so you can do:

FOR _ in range(0..(<OPTIONAL uint32>$user_number ?? 10)) UNION (INSERT User {...})

And then:

# Create 10 users:
$ edgedb fixture apply
# Create 100 users:
$ edgedb fixture apply -Duser_number=100

[tailhook]

Also we should consider setting allow_user_specified_id := true for all fixture files.

[1st1]

Do you think stable IDs are that important? That will make fixtures significantly slower (10-100x) depending on how extensive they are.

[colinhacks]

There may be a scenario where you have a UUID in your application's route (http://localhost:3000/user/$userId), in which case it would be nice for the URL to stay constant even if you clear & reseed the db. But I don't have a super strong opinion here.

[tailhook]

Do you think stable IDs are that important?

Yes, in some cases. I think it's safe to enable here.

That will make fixtures significantly slower (10-100x) depending on how extensive they are.

I think only when ids are specified in the specific query, right? Whether we want create-seed to include ids by default is a different question (probably not).

[1st1]

@colinhacks what do we do with --yolo? geldata/gel-cli#796

[colinhacks]

It's hard to disentangle these discussions, but I just added it as a proposal in #4254

[1st1]

I like pretty much all ideas in this thread (and edgedb migration create-seed is cool)!

[colinhacks]

Except for the hyphen ☹️

[1st1]

A random thought - can seed be a top-level command?

[tailhook]

For "independent fixtures" big question is how to find out which fixtures are already applied and which ones are new, so that fixture apply (or whatever it's called) doesn't have to start from scratch at any time.

Can we have some sys::Fixture type for it?

[1st1]

Theoretically yes, what fields would that type have?

[tailhook]

filename and hash probably

[tailhook]

Added to the top comment.