Combining cornucopia and migration frameworks like Refinery

[dlight]

When using migration frameworks like Refinery one usually don't list the entire DB schema at once but create incremental migrations that alter parts of the schema at time. Am I right that I can't directly feed Cornucopia SQL constructs like ALTER TABLE and friends?

My thinking now is, at build time (or when running the Cornucopia cli), to apply the migrations on PostgreSQL and get back the resulting schema, and only then pass it to Cornucopia. But is this really necessary?

It would be great to have an example combining Cornucopia and some migration framework.

[LouisGariepy]

Am I right that I can't directly feed Cornucopia SQL constructs like ALTER TABLE and friends?

You could, (Cornucopia will handle pretty much any query you throw at it), but its really not designed to work this way.

The easiest way to go about it is simply to keep using refinery just like you did before using Cornucopia. Once the migrations are installed on your DB (using your method of choice), keep your DB alive and use Cornucopia CLI's live action. This will connect to your existing DB and generate your queries. cornucopia live --help will give your more information.

TLDR: You can generate a your queries against a live database managed using any tool of your choice (including refinery). Nothing special to do, really.

[LouisGariepy]

Maybe I didn't understand your question properly, though. If that's the case, please let me know :) We're always looking for new ways to improve the crate.

[dbofmmbt]

I had the same question in my mind when I read about cornucopia, about how to integrate with migration tools.

I think @LouisGariepy answered this question very well for me. I wonder if it would be worth it to set up an example or add something about it in the docs in the workflows section (or both?) as I think this would be a common way to work with cornucopia.

[jacobsvante]

For me it's been working mighty fine to just generate the cornucopia file with cornucopia --queries-path=./queries schema ./migrations/*.

But I don't use a migration toolkit. I just use a simple CLI sub command for my app that runs unapplied migrations consecutively (it just looks at the max integer revision number in a migration table to find out which ones are not applied yet). This has been working great for me and I personally don't foresee myself having a need for a tool like Refinery.

Here's the layout of my migrations directory:

> ls -1 ./migrations
001-initial-schema.sql
002-price-table.sql
[...]

[ricardodarocha]

If you have a centralized DB instance, it would be easy to maintain two versions, a development version and a production version with different schema. After updating the development database structure, you can run the cornucopia and rebuild the rust project and test it until everything is working perfectly. This seems safe because the cornucopia validates the SQL before generating the rust code. For example you remove a field that is used in SQL, cornucopia will report it.

I think the problem starts when you have many clients with different database versions, each version a schema, they are very difficult to keep everything working well. In this case, you need to create your own system to manage the database versions as well as the application version. This is the reason why some frameworks adopt a model-centered framework.

In this case model-centered you will not create the Queries that way, but you will create the models that represent the database schemas, generate the databases and also the alter table instructions. The query select insert etc are abstractions.

see SQLAlchemy, Alembic (Python), Entity Framework (C#) Spring(Java) ORMs

just a guess What would make sense, in my opinion, is that if Cornucopia adds a new layer that generates or preprocesses the queries in the /queries/ folder, for example a parameter that specifies the preprocessor script1 to run before the system cornucopia generator, you could integrate with a model-centric methodology and the same preprocessor you share between cornucopia and migration system
ricardodarocha@outlook.com

[LouisGariepy]

@dlight Looking at this thread, there seems to be little consensus on how migrations should be done, so I'm hesitant to add a specific refinery example. It could be useful, but I personally think a better solution is to explain how migrations interact with cornucopia in general, as suggested by @dbofmmbt.

Does that seem like a good path forward for this issue, or do you have another resolution in mind?

[LouisGariepy]

@ricardodarocha If this is something you'd like to see in cornucopia, please open a separate issue so we can discuss it in more details :smile:

[LouisGariepy]

@dlight How would you feel about a community-managed examples repo? User farazfazli has also expressed interest in some more specific integration examples in issue #201.

Ultimately we don't really have the throughput to maintain all these crate-specific examples to a high standard, especially considering that not all maintainers (if any) actually use these crates, but I would be thrilled if we could arrange a separate crate for such integrations. I know how important they are to onboard users.

Please let me know what you think.

[MMMikeM]

How would you feel about a community-managed examples repo?

I would love to see this happen; Cornucopia is well suited to being agnostic to migrations. A couple of examples, such as Liquibase, Flyway, Atlas etc. would make a lot of sense as most brownfield projects (or those with dedicated data teams) would already have something like this in place.