Clojurians Log v2

Hi, what is your dev workflow when working with database migrations? 1. What tool do you use? 2. Are you using repl to generate migration scripts, as well as when running them? 3. Do you seed data? How? 4. How do you do it in production?

flywaydb

https://git.sr.ht/~dharrigan/startrek/tree/master/src/startrek/migration.clj

https://git.sr.ht/~dharrigan/startrek/tree/master/resources/db/migration/postgresql/_1/_0/_0

@jaime.sangcap we have beein using migratus for a quiet a while. https://github.com/yogthos/migratus no complains at all We normally run migrations when the app starts in prod. however not when started via the repl.

seed data normally is also part of the migrations for us.

thanks for sharing. I like the idea of using the flyway api instead of command due to file size IMO.

cool. I'm also looking into migratus. do you have the seed data in dev folder?

I'm thinking of creating migration.clj file and expose -main function for cli, since I'm using deps.edn. I Need to read parse-opts though

we have seed data together with migrations, so on resoruces/migrations

Trying to insert a record with org.clojure/java.jdbc into Postgres and it keeps complaining that I’m not specifying an id.

Execution error (PSQLException) at org.postgresql.core.v3.SimpleParameterList/checkAllParametersSet (SimpleParameterList.java:257).
No value specified for parameter 1.

(jdbc/execute! db ["INSERT INTO CLASS_SONGS (class_id,track_number,song_id,start_time) VALUES (?, ?, ?, ?)"]
                 {:class_id (:id class)                                         
                  :track_number track                                           
                  :song_id (:id song)                                           
                  :start_time start-time})

(jdbc/insert! db :class_songs {:class_id (:id class)                                         
                  :track_number track                                           
                  :song_id (:id song)                                           
                  :start_time start-time})

Column    |  Type   | Collation | Nullable |                 Default
--------------+---------+-----------+----------+-----------------------------------------
 id           | integer |           | not null | nextval('class_songs_id_seq'::regclass)
 class_id     | integer |           | not null |
 track_number | integer |           |          |
 song_id      | integer |           | not null |
 start_time   | integer |           |          |

do you have the error from the insert! call? the error you posted looks like it is from the execute! call, and that error is because you are not passing in the values for the parameterized query correctly

for execute! like that you can't pass in a map like that (it is treating that map as the options map) you need to pass in each value matching a '?' in the vector, similar to with query

Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2477).
ERROR: null value in column "id" violates not-null constraint
  Detail: Failing row contains (null, 8487, 1, null, 0).

are you sure your insert! is as shown?

you have 5 columns and five values in the row there, but insert! doesn't actually know how many a columns a table it is inserting to has (it doesn't do any reading of the table metadata, etc) so it wouldn't know to insert 5 values unless you gave it five

Yes, just double checked.

Interesting. In Go folks on the team aren’t specifying anything for id and Postgres happily runs the nextval function.

right, and I think somehow you are specifying an id

is it a literal map that is being inserted?

🤦

It’s the song that’s missing the id.

So the error is weird, it should complain about song_id being null.

Once I generated the map literal it was obvious.

Thanks for the help Kevin! 🙂

do you have fk constraints or triggers or something, it could be reporting the error based on the song table (where presumably the column is named id)

🙂

Foreign-key constraints:
    "class_songs_class_id_fkey" FOREIGN KEY (class_id) REFERENCES classes(id)
    "class_songs_song_id_fkey" FOREIGN KEY (song_id) REFERENCES songs(id)

Yup, that’s it.