One to many sqlite problem in luminus

Hi.

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?

I don’t have experience with migratus, but I did notice this issue on the GitHub page: Multiple statements not supported in SQLite init · Issue #167 · yogthos/migratus · GitHub

Are you by any chance putting your table migrations in an init.sql file? It seems like that would break multiple statements in the case of SQLite.

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.

good work, try to create the following:
create table foo (foo_id integer primary key) ;
create table bar (bar_id integer primary key, foreign key(foo_id) references foo(foo_id));
create table bar2 (bar2_id integer primary key, foreign key(foo_id) references foo(foo_id)) ;

Does that mean multi statement migrations work for you now, but the foreign keys are an issue?

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.

ok, I solved the problem. This may be useful for someone else, so this is what I did:

remove the old database file and remove the migration file.
then create another migration with:

(create-migration "last")

in that “last” migration file create all tables and don’t forget to include one row for the foreign key, and separate every table with --;;

create table address
(address_id integer primary key,
street text,
number integer,
city text,
country text);
--;;
create table person
(person_id integer primary key,
address_id integer,
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,
address_id integer,
vname text,
vmail text,
vpage text,
foreign key(address_id) references address(address_id));

then run (migrate)

Cool, happy to hear that you managed to solve your problem.