I'm evaluating SQLDelight, and there is a lot for ...

# squarelibraries

[Cies]

I'm evaluating SQLDelight, and there is a lot for me to love about it. 1) type safe, generated, functions for me to call in my code, that link to my SQL statements. 2) SQL checking at build time. But there's also a lot that makes it hard for me to use this in practice: it seems that only a subset of PG syntax is supported. And --as I now understand it-- I cannot pick only part of the deal (I'd want it to not do SQL checks, just the generated kotlin interfaces to my SQL statements as it does so much better than jdbi-kotlin) with SQLDelight: it's all or nothing. But maybe I'm wrong...

[phldavies]

The lack of support for a number of PG syntax/capabilities is currently making me question sticking with it over hand-rolling queries behind a custom interface using something like terpal-sql or just native JDBC. It is however a great tool for basic table queries and I've so far stuck with it.

It would be great to be able to annotate/hint some SQL syntax that SQLDelight doesn't natively understand to allow it to continue with generation - slightly more work on the user to add the hints but would lessen the load on SQLDelight for needing to understand all poossible syntax.

[Cies]

Yeah, some kind of "unsafe raw SQL" feature, that would certainly help me a lot too. But I read somewhere that that's not what SQLDelight wants to be: it wants to enforce it's safety (but mayby I understood or remembered it wrongly).

[Cies]

I have a JDBI test setup, it seems to work quite well. I like to have my SQL in separate files. And the @UseClasspathSqlLocator and the JDBI Jump plugin for intellij make it very usable.

Just looked into Terpal because you mentioned it. Thanks, I just might end up with that library (but still deciding)! I looks like a more Kotlinesque way of doing things compared to JDBI. It's pretty young though and JDBI has quite some momentum already.

The pick for db lib is quite a big one, especially for the type of apps I plan to build with it.

[phldavies]

I like to have my SQL in separate files

This was definitely the main draw for me to SQLDelight - I'm not a fan of DSLs/ORMs - I'd rather use SQL for SQL and code for code and SQLDelight definitely ticks that box. The downside has just been not being able to use all the SQL features.
Terpal-SQL reminds me a little of doobie from the scala world - but yes it's still young, and still a little rough around the edges.

[Cies]

The main dev has massive Scala experience

[Arjan van Wieringen]

Every 'SQL'-wrapper in every language I have ever used is by definition a subset of the SQL dialect. And it works in 95% of the cases but I ALWAYS need an exit to write raw queries.

So, not a fan. Even though these are amazing projects. For typical CRUD it works fine.

[Cies]

Though I'd not call SQLDelight, JDBI and Terpal "wrappers". SQLDelight parses only a subset though.

JOOQ's a "wrapper" (a builder), and all ORMs are.

[Arjan van Wieringen]

In the end they output SQL but wrap it in a different syntax. Though they compile it or what not... it is still a wrapper. But these are semantics. The essence is.... abstractions will never be able to encapsulate the full grammar of an SQL dialect without extreme hard work.

[Cies]

Hence I'm now with JDBI and Terpal. Both do some sort of "wrapping", but they do not try to understand my SQL (for better and for worse).

One test per query to runs it when running the tests should be enough to confirm the query is valid SQL.

[hfhbd]

You can write raw queries when you use a sqldriver.

[Cies]

I know. But 1) how to fit them in a transaction that's SQLDelight managed? 2) being limited to the subset of PGSQL that SQLDelight supports will hurt me too much (especially for schema definitions)

[curioustechizen]

Self-promotion alert: very recently I wrote a post on the topic of SQL libraries and abstractions. I concluded that SqlDelight gets this right.

At the time I did not realise that SqlDelight restricts the SQL you can use to what is supported by its "dialects". That said I still feel SqlDelight is at the right level of abstraction.

In its current form it has limitations and disadvantages but none of those are attributable to the approach itself.

http://kiranrao.in/blog/embrace-sql

[Arjan van Wieringen]

All good. But when you need anything more than basic SQL none of the solutions deliver.

Personally I do not understand why the de facto standard is to abstract SQL. For common crud it removes boilerplate and adds some form of type safety. But barely.

[Cies]

@Arjan van Wieringen terpal-sql and jdbi deliver. they do not abstract over SQL, not do they parse the SQL: they just make it easy to use SQL in your (Kotlin) code

[Arjan van Wieringen]

Yes Terpal is great. That is the sql template tag from ES into Kotlin