Toucan2 insert-returning-instance! Not Returning Inserted Record in MySQL

[AliSafiya]

I’m using Toucan2 with MySQL and running insert-returning-instance! to insert a new record while expecting it to return the inserted row. However, instead of getting the full record, the function only returns nil.

Debug Output Shows:

SELECT * FROM table_name WHERE id IN (NULL)
This suggests Toucan2 isn’t correctly capturing the generated ID from the insert.

When testing the same operation in PostgreSQL, the function correctly returns the full inserted row.

Expected Behavior:

• insert-returning-instance! should return the inserted row as a map.

Actual Behavior:

• The function doesn’t return the inserted record.
• Debug logs show that a follow-up SELECT query is being run but incorrectly attempts to fetch the row with WHERE id IN (NULL).
• My primary key (id) is auto-incremented, but Toucan2 isn’t capturing the generated key correctly.

[camsaul]

Hi, what MySQL or MariaDB JDBC driver are you using?

[AliSafiya]

@camsaul I’m using MySQL Connector/J version 8.4.0.

[marksto]

@camsaul Hi Cam! Same thing for Postgres 16, org.postgresql/postgresql 42.7.8. The insert-returning-instance! returns nil for a legit new entity/record.

May this be due to the fact that I'm doing it inside an ongoing system (DB) transaction? Nah, just checked with a proper snapshot isolation (:isolation :repeatable-read) and there's no difference.

The symptom is the same:

Parsed args: {:columns [:*], :kv-args {:toucan/pk [:in [nil]]}}
Resolved query: {}
Built: {:from [[:response]], :select [:response/*], :where [:in :response/id [nil]]}
Compiled: ["SELECT \"response\".* FROM \"response\" WHERE \"response\".\"id\" IN (NULL)"]

The \"response\".\"id\" column is defined as id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY.
The model's primary key is defined as (m/defmethod t2-m/primary-keys ::response [_] [:response/id]).

Ok, busted. Changing the primary key definition to [:id] fixes the query (and it returns a new ::response entity).
But now I end up with Assert failed: No primary key(s) were found on the realized row, which reminds me why I had these PKs qualified in the first place... but this is a separate issue, anyway.

[bolivier]

I see this with SQLite also.

I'm using org.xerial/sqlite-jdbc {:mvn/version "3.51.1.0"}.

It doesn't return pk or instance, but I found that this will return the expected value

(t2/query {:insert-into :users
           :values      [{:email    "my-email5"
                          :password "password"}]
           :returning   [:*]})