cashapp / sqldelight

SQLDelight - Generates typesafe Kotlin APIs from SQL
https://cashapp.github.io/sqldelight/
Apache License 2.0
6.04k stars 501 forks source link

Make it possible to insert raw SQL types to cast if I really know what I'm doing and SQLDelight does not #5332

Open digitalheir opened 1 week ago

digitalheir commented 1 week ago

Description

Consider #5331. I am so close to making my database work with SQLDelight, except for the one XML column for which I know for sure it is correct.

I'm fine if SQLDelight treats the XML as a String. I do too and Postgres does too for most purposes except data validation.

But I NEED to cast it! Postgresql will throw if I try to insert a Text value into an XML column.

It would be nice to have a little escape hatch when SQLDelight development lags behind database capabilities. I propose something like:

CREATE TABLE IF NOT EXISTS my_table(xml TEXT) -- fake TEXT type, the actual database has XML type
insert:
INSERT INTO my_table(xml) VALUES(CAST('<root></root>' AS `XML`));

When the type is put in backticks, SQLDelight may forgo type checking and just dump the SQL as is.

griffio commented 1 week ago

Currently the escape hatch to run ad-hoc queries is to use driver.execute directly - same api the compiler uses in the generated Queries

e.g

 driver.execute(-1, "INSERT INTO my_table(xml) VALUES(?)", 1) {
        check(this is JdbcPreparedStatement)
        bindObject(0, "<root></root>", SQLXML) // assumes the actual table is XML column type
    }