Load structure.sql as a migration?

I’m working on a fairly mature database and writing a new Elixir app to sit on top of it. The problem is getting the migrations started. The structure.sql is a great starting point, but we’d like to have it be part of the migrations.

I tried to do something similar once before on a much smaller database and I remember eventually giving up and re-creating the database with regular Ecto migrations. But with this big/mature database, re-creating everything would be really time intensive. IIRC, one of the problems is that each Postgres “schema” (something analogous to a “folder” or partition) has to be created in its own transaction, so if we have a dozen separate schemas, we would need at least that many migrations (because each migration happens in its own transaction). It’s not the end of the world to use mix ecto.load (e.g. done as part of an alias), but a standard migration would be preferred.

Any thoughts on this? Is this maybe more difficult than it’s worth? Thanks for any pointers!

1 Like

I think the big issue is that structure.sql ALSO lists all of the migrations that you’ve done, so there’s a recursive dependency here that is sort of weird if you do it inside of a migration.

You could do a sort of custom my_structure.sql that has just a big psql_dump of things and call that from your migration, but the output of mix ecto.dump is really designed to be used with mix ecto.load

1 Like

In this case, the database had no migrations. I just have a structure dump, and it does properly represent the structure of the database. But I hear what you’re saying: the structure.sql is best handled by ecto.load.

ecto.load under the hood just does a psql to load the structure.sql into the database.
So if you have access to it on the server, you could call it yourself from a migration.

Just a note… PostgreSQL allows you to create schema objects within a transaction without problem:

127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:05:47 PM PDT 2023]
> BEGIN;
BEGIN
Time: 0.381 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:05:55 PM PDT 2023]
> CREATE SCHEMA my_schema_1;
CREATE SCHEMA
Time: 1.601 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:14 PM PDT 2023]
> CREATE SCHEMA my_schema_2;
CREATE SCHEMA
Time: 0.516 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:17 PM PDT 2023]
> CREATE SCHEMA my_schema_3;
CREATE SCHEMA
Time: 0.558 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:19 PM PDT 2023]
> COMMIT;
COMMIT
Time: 2.574 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:06:24 PM PDT 2023]
> 

New databases are not allowed to be created in an ongoing transaction:

127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:08:42 PM PDT 2023]
> BEGIN;
BEGIN
Time: 0.412 ms
127.0.0.1(from scb-muse-linuxdev).postgres.scb.5432 [Thu Sep 14 12:08:44 PM PDT 2023]
> CREATE DATABASE test_db OWNER scb;
ERROR:  CREATE DATABASE cannot run inside a transaction block
Time: 0.786 ms

FYI you can set @disable_ddl_transaction true in your ecto migration file and then this is no longer in a transaction. As @sbuttgereit notes I don’t think there is actually an issue here though.