Migrations do not work on postgres

[miikargh]

Hi,

Problem

I'm setting up a luminus project with postgresql but I can't get the database migrations to work. When I run lein run migrate I get the following error:

2020-03-21 18:57:39,783 [main] INFO  migratus.core - Starting migrations
2020-03-21 18:57:40,226 [main] INFO  migratus.core - Ending migrations
Syntax error (PSQLException) compiling at (/private/var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/form-init6713705334548032261.clj:1:125).
ERROR: relation "schema_migrations" does not exist
  Position: 15

Full report at:
/var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/clojure-1434244382898877516.edn

Steps to reproduce:

1. Install postgresql with docker:

   docker run --rm --name test_db_dev \
   -e POSTGRES_USER=admin \
   -e POSTGRES_PASSWORD=admin \
   -e POSTGRES_DB=test_db_dev \
   -d -p 5432:5432 postgres:11.7

2. Install a fresh luminus project from template

   lein new luminus testproject +postgres +service +swagger +auth

3. Add the database-url to dev-config.edn

   :database-url "postgresql://localhost:5432/test_db_dev?user=admin&password=admin"

4. Run project

   lein run

5. Cider-jack-in to repl on localhost:7000, mount to db and add a table

   user> (mount.core/start #'testproject.db.core/*db*)
   ;; => {:started []}
   user>(user/create-migration "add-users-table")
   ;; => nil

6. Add the following SQL to resources/migrations/20200321171627-add-users-table.up.sql

   CREATE TABLE users (id INT, name VARCHAR(25));

7. Add the following SQL to resources/migrations/20200321171627-add-users-table.down.sql

   DROP TABLE users;

8. Run migration:

   
   ❯ lein run migrate
   2020-03-21 19:17:28,431 [main] INFO  migratus.core - Starting migrations
   2020-03-21 19:17:28,767 [main] INFO  migratus.core - Ending migrations
   Syntax error (PSQLException) compiling at (/private/var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/form-init4803780192902124334.clj:1:125).
   ERROR: relation "schema_migrations" does not exist
   Position: 15

Full report at: /var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/clojure-8247198488770846901.edn

Here are the contents of the full report:

{:clojure.main/message "Syntax error (PSQLException) compiling at (/private/var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/form-init4803780192902124334.clj:1:125).\nERROR: relation \"schema_migrations\" does not exist\n Position: 15\n", :clojure.main/triage {:clojure.error/phase :compile-syntax-check, :clojure.error/line 1, :clojure.error/column 125, :clojure.error/source "form-init4803780192902124334.clj", :clojure.error/path "/private/var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/form-init4803780192902124334.clj", :clojure.error/class org.postgresql.util.PSQLException, :clojure.error/cause "ERROR: relation \"schema_migrations\" does not exist\n Position: 15"}, :clojure.main/trace {:via [{:type clojure.lang.Compiler$CompilerException, :message "Syntax error compiling at (/private/var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/form-init4803780192902124334.clj:1:125).", :data {:clojure.error/phase :compile-syntax-check, :clojure.error/line 1, :clojure.error/column 125, :clojure.error/source "/private/var/folders/8p/w2c0ng4j2pdb97mbcjd8pcg80000gn/T/form-init4803780192902124334.clj"}, :at [clojure.lang.Compiler load "Compiler.java" 7648]} {:type org.postgresql.util.PSQLException, :message "The database returned ROLLBACK, so the transaction cannot be committed. Transaction failure cause is <<ERROR: relation \"schema_migrations\" does not exist\n Position: 15>>", :at [org.postgresql.core.v3.QueryExecutorImpl processResults "QueryExecutorImpl.java" 2209]} {:type org.postgresql.util.PSQLException, :message "ERROR: relation \"schema_migrations\" does not exist\n Position: 15", :at [org.postgresql.core.v3.QueryExecutorImpl receiveErrorResponse "QueryExecutorImpl.java" 2578]}], :trace [[org.postgresql.core.v3.QueryExecutorImpl receiveErrorResponse "QueryExecutorImpl.java" 2578] [org.postgresql.core.v3.QueryExecutorImpl processResults "QueryExecutorImpl.java" 2313] [org.postgresql.core.v3.QueryExecutorImpl execute "QueryExecutorImpl.java" 331] [org.postgresql.jdbc.PgStatement executeInternal "PgStatement.java" 448] [org.postgresql.jdbc.PgStatement execute "PgStatement.java" 369] [org.postgresql.jdbc.PgPreparedStatement executeWithFlags "PgPreparedStatement.java" 159] [org.postgresql.jdbc.PgPreparedStatement executeQuery "PgPreparedStatement.java" 109] [clojure.java.jdbc$execute_query_with_params invokeStatic "jdbc.clj" 1062] [clojure.java.jdbc$execute_query_with_params invoke "jdbc.clj" 1056] [clojure.java.jdbc$db_query_with_resultsetSTAR invokeStatic "jdbc.clj" 1079] [clojure.java.jdbc$db_query_with_resultsetSTAR invoke "jdbc.clj" 1065] [clojure.java.jdbc$query invokeStatic "jdbc.clj" 1155] [clojure.java.jdbc$query invoke "jdbc.clj" 1117] [clojure.java.jdbc$query invokeStatic "jdbc.clj" 1133] [clojure.java.jdbc$query invoke "jdbc.clj" 1117] [migratus.database$table_existsQMARK$fn16186 invoke "database.clj" 157] [clojure.java.jdbc$db_transactionSTAR invokeStatic "jdbc.clj" 789] [clojure.java.jdbc$db_transactionSTAR invoke "jdbc.clj" 759] [clojure.java.jdbc$db_transactionSTAR invokeStatic "jdbc.clj" 772] [clojure.java.jdbc$db_transactionSTAR invoke "jdbc.clj" 759] [migratus.database$table_existsQMARK invokeStatic "database.clj" 154] [migratus.database$table_existsQMARK invoke "database.clj" 145] [migratus.database$init_schemaBANG invokeStatic "database.clj" 217] [migratus.database$init_schemaBANG invoke "database.clj" 209] [migratus.database.Database connect "database.clj" 270] [migratus.core$run invokeStatic "core.clj" 25] [migratus.core$run invoke "core.clj" 22] [migratus.core$migrate invokeStatic "core.clj" 83] [migratus.core$migrate invoke "core.clj" 78] [luminus_migrations.core$fn__16367 invokeStatic "core.clj" 44] [luminus_migrations.core$fn16367 invoke "core.clj" 41] [luminus_migrations.core$migrate invokeStatic "core.clj" 97] [luminus_migrations.core$migrate invoke "core.clj" 83] [testproject.core$_main invokeStatic "core.clj" 70] [testproject.core$_main doInvoke "core.clj" 57] [clojure.lang.RestFn invoke "RestFn.java" 408] [clojure.lang.Var invoke "Var.java" 384] [user$eval22635 invokeStatic "form-init4803780192902124334.clj" 1] [user$eval22635 invoke "form-init4803780192902124334.clj" 1] [clojure.lang.Compiler eval "Compiler.java" 7177] [clojure.lang.Compiler eval "Compiler.java" 7167] [clojure.lang.Compiler load "Compiler.java" 7636] [clojure.lang.Compiler loadFile "Compiler.java" 7574] [clojure.main$load_script invokeStatic "main.clj" 475] [clojure.main$init_opt invokeStatic "main.clj" 477] [clojure.main$init_opt invoke "main.clj" 477] [clojure.main$initialize invokeStatic "main.clj" 508] [clojure.main$null_opt invokeStatic "main.clj" 542] [clojure.main$null_opt invoke "main.clj" 539] [clojure.main$main invokeStatic "main.clj" 664] [clojure.main$main doInvoke "main.clj" 616] [clojure.lang.RestFn applyTo "RestFn.java" 137] [clojure.lang.Var applyTo "Var.java" 705] [clojure.main main "main.java" 40]], :cause "ERROR: relation \"schema_migrations\" does not exist\n Position: 15", :phase :compile-syntax-check}}

Any help greatly appreciated!

P.S.
Big thanks for the valuable work you are doing here!

[miikargh]

Oh, and here are my java and lein versions:

❯ lein --version
Leiningen 2.9.3 on Java 1.8.0_242 OpenJDK 64-Bit Server VM

[yogthos]

Hi, the problem is caused by the latest Postgres driver and migratus. It's been fixed here. I just pushed out a template with luminus-migrations 0.6.7 that uses the latest migratus. Let me know if that fixes it.

[miikargh]

I bumbed up the luminus-migrations version to the latest and migrations now work. Thanks! ☺️

[yogthos]

👍