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"

3. Run project

lein run

4. 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

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

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

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

DROP TABLE users;

7. 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_resultset_STAR_
    invokeStatic
    "jdbc.clj"
    1079]
   [clojure.java.jdbc$db_query_with_resultset_STAR_
    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_exists_QMARK_$fn__16186
    invoke
    "database.clj"
    157]
   [clojure.java.jdbc$db_transaction_STAR_ invokeStatic "jdbc.clj" 789]
   [clojure.java.jdbc$db_transaction_STAR_ invoke "jdbc.clj" 759]
   [clojure.java.jdbc$db_transaction_STAR_ invokeStatic "jdbc.clj" 772]
   [clojure.java.jdbc$db_transaction_STAR_ invoke "jdbc.clj" 759]
   [migratus.database$table_exists_QMARK_
    invokeStatic
    "database.clj"
    154]
   [migratus.database$table_exists_QMARK_ invoke "database.clj" 145]
   [migratus.database$init_schema_BANG_
    invokeStatic
    "database.clj"
    217]
   [migratus.database$init_schema_BANG_ 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$fn__16367 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]

👍