Feature request: goose seed

[mfridman]

Opening this as more of a discussion on whether it's a good (or not) idea to have a goose seed command.

Quite often I find myself crafting the same queries over and over to populate a database with arbitrary data. Since goose knows the entire schema, I wonder if it's possible to automagically populate a database from the given schema.

... this idea needs to be flushed out a bit more, but using this issue as a placeholder.

[rquadling]

I've been using Phinx for about a decade and the seeding capability / concept is pretty much essential.

One of the issues I had with Phinx is the built in DBAL not being as expressive as we want. So I added the template system to Phinx to allow the writing of your own SQL. But I don't need the DBAL as I only use 1 RDBMS for a project. Pros/cons on using/not using DBAL.

With seeds, though I've not used Goose yet (team is and so I'll be getting there eventually), having them is important I feel.

Pro: 1 set of credential/config for incrementing (migrations) and setting (seeds) the content in a DB (actually, if you extract the DB aspect, it can be for ANY store - migrations are NOT just for RDBMS!).
Con: Keeping seeds upto date with a changing schema can be a brand new pain to be endured by the developers.

[mfridman]

Fwiw goose does support "seeding" in the sense that you can apply ad-hoc migrations without versioning.

https://pressly.github.io/goose/blog/2021/no-version-migrations/

The idea is you point goose at a separate directory and run --no-versioning which applies the migrations.

---

Maybe I mislabeled this issue, but it's more about running a single command and goose somehow figures out a sane set of data to apply to your database without you having to actually write the logic.

[rquadling]

Seeding, in general, is one of those areas that has as many ideas/issues as any other thing.

Less about running the seed, but what should the seed contain, how is it implemented/maintained.

If Goose can handle a directory of seeds, then 1 command to run the seeds after the migrations ... is that good enough?

One area that Phinx (and I think Goose) DOESN'T have is "repeatables". Views, stored procedures, triggers, UDF's, etc. Had to write my own for this. But still up against the RDBMS on this. Some don't allow you to do any sort of atomic operation on DML, so you temporary need to drop the trigger to add the replacement. Best option is to NOT use triggers!

You can drop this comment if it is going off track - I tend to go off-track!

[mfridman]

Regarding the repeatable bit, there's an issue (#472) tracking this and it's on our radar

I haven't quite figured out a nice UX for this, so if you have comments/suggestions that's a good place to drop them.

This issue is specific to some command(s) that can be used in testing code to populate the database automagically, since goose already knows the entire schema and all the types, effectively a bunch if INSERT statements to model the relationships in some semi-clever way.