Has anyone had success with getting Ecto to use externally generated migration files? I have a directory of SQL files that I would like to migrate for my unit tests but loading each migration and executing it is causing problems with Postgres prepared statements due to multiple commands being in each SQL migration. My code to load the migrations is:
def up do
Path.wildcard("priv/repo/migrations/hasura/**/up.sql")
|> Enum.each(fn sql_file ->
sql = File.read!(sql_file)
execute("BEGIN; #{sql} COMMIT;")
end)
end
However, this gives me the error: cannot insert multiple commands into a prepared statement.
I’ve played around with the Ecto config (setting prepare: :unnamed) and wrapping each of the migration files in a BEGIN / COMMIT block (i.e. a transaction) without any luck.
I’ve resorted to using Mox to mock my Repo which works ok but I would like to test against the actual database.
Hey @Rodeoclash migrations already happen in a transaction so you shouldn’t need to add your own begin and commit. Have you tried simply doing execute(sql) ?
Hey @benwilson512 - that’s actually where I started but unfortunately I get the same cannot insert multiple commands into a prepared statement error!
Oh, I’m just realizing that you’re reading the entire file in and calling execute on the whole file. I bet that has a bunch of internal statements. Can you share a sample file?
Overall I’m not sure that ecto migrations are a good tool for this, if you’ve got a full multi-line sql file would it be better to just shell out to psql for this?
You are correct. I’ve also squashed the migrations recently which results in a large “initial” file. Here’s a head from the start of the first migration:
SET check_function_bodies = false;
CREATE FUNCTION public.set_current_timestamp_updated_at() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
_new record;
BEGIN
_new := NEW;
_new."updated_at" = NOW();
RETURN _new;
END;
$$;
CREATE TABLE public.genders (
name text NOT NULL
);
CREATE TABLE public.honorifics (
name text NOT NULL
);
CREATE TABLE public.organisation_roles (
name text NOT NULL,
comment text NOT NULL
);
I have a bunch of helper methods for populating these into the database (i.e. psql) but I’m hoping to take advantage of Ecto’s ability to restore the database after each test. That said, I realise I don’t understand the way Ecto rolls back the database between tests. I haven’t tested this, but if I got the database to the initial state I wanted before running the tests, would it be enough to just run the unit tests and rely on whatever rollback mechanism the test suite has…?
In my mind I’ve somehow linked the migrations and rollback of the unit tests but I realise this may not be the case.
Ah! yes 100%. So what Ecto does is that for each test it starts a transaction, and then all of your test data is executed within that transaction. Then at the end of the test, it just calls ROLLBACK on that transaction instead of COMMIT. So this way it doesn’t need to do any big delete of any data, because no data is ever committed.
In that cause I’d put all of that in a priv/repo/structure.sql file and then use the mix ecto.load task. So for a total reset you would mix do ecto.drop, ecto.create, ecto.load, run priv/repo/seeds.exs to drop, create, load your sql file, and run an extra seeds.
For your tests I’d do the same thing in MIX_ENV=test but just don’t run seeds.exs. From there, the normal Ecto Sandbox stuff should handle everything.
EDIT: I’d also add an ecto.migrate command after ecto.load to run any new migrations you later add, if you plan to add any.
