Clojurians Log v2

mysql doesn't store them as strings, and next.jdbc doesn't do any type mapping, the mysql jdbc driver does the mapping of data from over the wire to some kind of jvm data type

Ah ok. I am converting some code that expected them as strings as in sqlite. How can I have them coerce to strings then?

depending on the string formats your code accepts you'll need to use something like a simpledateformatter to turn the date into a string in the format you expect

Can I do that at the next jdbc level though?

Like is there anyway to tell next jdbc or the connector whenever it finds a timestamp convert it to string?

there are ways to calling a function on every row, but if you are porting some code from sqlite to mysql, better to switch to not expecting dates as strings

Hmm the thing is we'd like the code to be compatible with sqlite and mysql

seems like a bad idea, you are restricting yourself to the subset of sql they share, and the jdbc drivers for each database will also do different type mappings (types in sqlite basically don't exist)

Yeah thats true, definitely valid points. Our main motivation to keep sqlite compatibility is so we don't have to spin up a mysql instance every time we want to run all our tests or just do local dev work. Then in our CI/CD pipeline we'd run the tests against the prod configuration of mysql. Also, our queries right now are probably 99% compatible with both at the moment. And of course there is the benefit of being able to configure the application to run in sqlite mode or mysql mode depending on the requirements. All that being said i'm not sure if its worth it for us to support both. Having that delta between prod and dev could be concerning.

What SQL type do those fields have in SQLite?

I would have expected them to be java.sql.Timestamp even in SQLite...

Timestamps returned from the db come back as just strings for us

On sqlite

No, I mean what type are they defined as in your schema DDL?

it will depend on the sqlite jdbc driver used, there have historically have been several different sqlite drivers

TIMESTAMP

We use the xerial jdbc driver for sqlite

the native library using one, and the compiled to risc and then the risc interpreted to avoid using a native library

TIL:

(~/oss/next-jdbc)-(!2004)-> clj -A:test
Clojure 1.11.3
user=> (require '[next.jdbc :as jdbc])
nil
user=> (def ^:private test-sqlite {:dbtype "sqlite" :dbname "clojure_test_sqlite"})
#'user/test-sqlite
user=> (jdbc/execute! test-sqlite ["create table timey_wimey ( t timestamp )"])
[#:next.jdbc{:update-count 0}]
user=> (jdbc/execute! test-sqlite ["insert into timey_wimey(t) values (?)" (java.time.Instant/now)])
[#:next.jdbc{:update-count 1}]
user=> (jdbc/execute! test-sqlite ["select * from timey_wimey"])
[#:timey_wimey{:t "2024-06-12T23:13:40.126505633Z"}]
user=>

Yeah i'm leaning towards just using mysql and scrapping sqlite. I'm not so sure supporting both is worth it.

The data returned from an INSERT differs between MySQL and SQLite (and differs between versions of the Xerial driver too!). Look through the tests for next-jdbc and see the number of calls to (mysql?) and (sqlite?) for all the differences that test suite has to account for...

SQLite is a mess in terms of types. The types are plain strings and then partially matched against other strings to see if it's actually any built-in type. So IIRC providing POINT as a type will make the column of type INT because the word ends with that. Everything that isn't matched will be a plain string.

Ouch! In the next.jdbc test suite, there's some code that uses a custom builder to handle BIT and BOOL types so that you can actually get Boolean values out of SQLite instead. I didn't think to dig into date/timestamp types for tests 😞