I am currently trying to migrate a database in luminus that has a one to many relation:
create table address
(address_id integer primary key,
street text,
number integer,
city text,
country text);
create table person
(person_id integer primary key,
fname text(40),
lname text(40),
email text(40),
mobil integer(50),
foreign key(address_id) references address(address_id));
create table venue
(venue_id integer primary key,
vname text,
vmail text,
vpage text,
foreign key(address_id) references address(address_id));
when running migration it only creates the first database:
user=> (migrate)
2022-05-05 17:15:00,817 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] INFO migratus.core - Starting migrations
2022-05-05 17:15:00,818 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] INFO migratus.database - creating migration table 'schema_migrations'
2022-05-05 17:15:00,821 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] DEBUG migratus.migrations - Looking for migrations in #object[java.io.File 0x4d4ee515 /Users/jonas/Dropbox/prog/web/clojure/luminus/kammem/resources/migrations]
2022-05-05 17:15:00,822 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] INFO migratus.core - Running up for [20220503142710 20220504090351]
2022-05-05 17:15:00,822 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] INFO migratus.core - Up 20220503142710-tables1
2022-05-05 17:15:00,823 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] DEBUG
2022-05-05 17:15:00,825 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] INFO migratus.core - Up 20220504090351-people
2022-05-05 17:15:00,826 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] DEBUG migratus.migration.sql - found 1 up migrations
2022-05-05 17:15:00,826 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] DEBUG migratus.database - marking 20220504090351 complete
2022-05-05 17:15:00,827 [nREPL-session-4f2dbcd9-90d4-4317-99fc-7b3608ff75a7] INFO migratus.core - Ending migrations
jonas@Jonass-MBP:~/Dropbox/prog/web/clojure/luminus/kammem$ sqlite3 kammem_dev.db
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .tables
address schema_migrations
sqlite>
That is, it has created address but not the other two tables, person and venue. I have tried to separate them with --;; but with no success. any ideas how to migrate all tables?
no, I create a migration file with (create-migration) and put everything there. Some suggestions include putting --;; between tables, but I need one table to reference another table.
Do you have a public repo which shows this unexpected behaviour; or can you make one? I would like to help you further, but without that I can only keep guessing.
no I don’t have any public repo. But it is eays, just create a luminus project with +sqlite like:
lein new luminus test +sqlite
and try to create several tables whtin one migration file with a relation between them.
But the problem really boils down to create several tables in one file. Otherwise one has to make a file for every table. And there must be a way to create relations across tables. otherwise it becomes very limited.
I installed leiningen, created the test project like you described, opened a repl, and ran
(user/restart)
(user/create-migration "test")
Then I set the contents of the created migration file (resources/migrations/20220505202309-test.up.sql) to
create table foo (id integer primary key);
--;;
create table bar (id integer primary key);
After that, (user/migrate) detected the migration and ran it. The sqlite database now contains both tables foo and bar.
The question now is, can you spot the difference from what you did? A guess could be that it maybe doesn’t like extra line breaks around the --;; line, but I haven’t tested that.
no, it means that the first table will be created and then the next table will not be created because it does not recognize the reference to the first one. try and see what happens
Okay. In that case, for SQLite, I don’t think you have other options than putting related tables in separate migrations. I know that Postgres is able to handle your case correctly, as I’ve done that before (with a different migrations tool, but still), but SQLite probably has a different way of handling DDL in transactions (if it can even do that).
I don’t think this is a limitation of migratus, but rather of SQLite.