sql 2023-05-21 | Slack Archive

DrLjótsson10:05:08

My app is using a MySQL database and my test suite as well. I wonder if it possible to switch to a in-memory db for the tests. Does anyone know if the differences in syntax between MySQL and these databases is so large that I would need to have lots of checks in my queries if I’m running in test mode or not? Notably, I can’t fully leverage honey sql’s ability to format queries to a dialect, I still have a lot of queries written in HugSQL (although I am slowly porting them).

igrishaev11:05:51

Mixing two various DBs for prod and testing as a bad idea, really. Trivial queries like select * from users where id = 42 would work the same but sooner or later you'll face different behaviour. If your app uses Mysql, use Mysql everywhere, even in tests. It can be easily set up locally on any OS. Another option would be to run it in Docker which is great for CI

DrLjótsson11:05:10

Thanks, makes sense!

seancorfield16:05:03

MySQL has some unique differences from other DBs, such as the precedence of SET in an UPDATE statement, and several non-standard extensions (although it is slowly becoming more standardized over time). So I'll echo what Ivan said: don't use a different DB between dev/test and prod -- use Docker to run it in dev/test if you don't want to actually install it. We use MySQL at work very heavily and it would be a pain to migrate to a more standard DB. We use Docker to run it in dev/test/CI and then we have primary/secondary clusters in QA/production -- all the exact same version of MySQL (Percona, specifically), and we also make sure we have user accounts set up the same way with a couple of accounts that are read-only for various schemas ("database" in MySQL-speak -- but all the other DBs would call them schemas).

👍 1

Fredrik Meyer18:05:33

I am trying to understand the next.jdbc library. When I insert a new column, the result of

(with-open [c (get-connection)]
    (let [res (jdbc/execute! c “INSERT ….”
                             (-> jdbc/snake-kebab-opts (assoc :return-keys {:return-keys true})))]
      res))

is of the form [{:last-insert-rowid() 8}]. Why is there a parenthesis in the keyword name? That makes it kinda clumsy to get the generated ID out. I have to do something like (get res "last-insert-rowid()") I might be misunderstanding something 🙂

seancorfield19:05:42

Because that's what it is called in that database. Each different database has a different name for inserted generated keys.

seancorfield19:05:20

next.jdbc does no translation on this stuff: it's whatever comes back from JDBC itself.

seancorfield19:05:14

In MySQL it's GENERATED_KEY as I recall (I'm on my phone so I can't double check).

seancorfield19:05:04

A lot of people are surprised at how much variety there is in JDBC across databases...:face_with_rolling_eyes:

Fredrik Meyer19:05:58

Ah, I see, thank you. In this case it is SQLite. Yes, I code Python at work, but wanted to test a little hobby project with Clojure, and there’s been many years since I was in the Java ecosystem… Thank you for the answer

2023-05-21

Channels