I've been doing some research here and on the Inte...

# server

[Kevin Del Castillo]

I've been doing some research here and on the Internet, and I still have doubts about what library to use for SQL database access, I've been heavily inclined to use Ktorm but I saw some threads and messages that mention it's still a bit basic for certain scenarios, although, it supports native SQL queries. Other options I have considered are Komapper, Exposed and jOOQ (I don't fully understand this one yet). If you had the chance to create a new project from scratch in pure Kotlin, what framework for SQL access would you choose? (preferably prioritizing Kotlin-first libraries). Thanks in advance

[jw]

SQLDelight

[Kevin Del Castillo]

Thanks, I forgot to mention it, but I have been also considering SQLDelight, we'll be using PostgreSQL with JVM, so one of my biggest concerns is that the documentation mentions that its dialect is still a work in progress

[jw]

It definitely is, but it's becoming more comprehensive every day

[hfhbd]

Sqldelight with Postgres and jvm uses JDBC (or r2dbc), so you could use rawqueries if the dialect does not support your use-case. And please fill issues for missing things.

[Kevin Del Castillo]

Thanks for the insights, I'm also looking for production-ready libraries, although, we won't be launching this in the next 6 months we still want certain guarantees that SQLDelight could manage an iterative process, by this I mean that we won't be needing to consider a different library just because something isn't supported yet

[jw]

completeness and production-ready are mostly orthogonal concepts

[jw]

sqldelight has been production ready and used in production now for many years

[Kevin Del Castillo]

You're right, I should've phrased it better but I think you got the point, Philip mentioned something about rawqueries, so if I understand correctly there are still ways to workaround dialect limitations on SQLDelight, just as there's a way to write native SQL on Ktorm if a use case isn't supported by its DSL?

[jw]

yeah. i just wanted to make sure you didn't reject sqldelight on non-completeness alone!

[hfhbd]

Keep in mind, sqldelight isn't just a library, mostly it is a compiler generating typesafe Kotlin code during compiling, instead mapping some SQL queries to types during runtime.

[Kevin Del Castillo]

Yep I like the compile time checks, one of the main reasons I've also considered SQLDelight for mobile development, thanks for all your comments, I'll give it a try ✍️

[PoisonedYouth]

I have good experience using Exposed

[Jared Schlicht]

At work, we have a rather huge Kotlin spring boot app using JOOQ for everything. There's more boilerplate involved in some cases, but you've got full control over your SQL. If you're doing something advanced or may run into performance problems, it's hard to beat.

[spierce7]

@Kevin Del Castillo My team has been using sqldelight on the server for around 7 months in production. I do not recommend it. It's a fantastic library for sqlite, but the number of features I've run into that aren't supported for mysql becomes a source of severe frustration (I imagine this is worse for postgres). It becomes a clever dance of tip-toeing around what sqldelight allows you to do in the way that it allows you to do it.

It's also a bit annoying once you install the IDE plugin, you see it doing processing during your gradle sync on projects that don't even have sqldelight; a minor annoyance.

When it supports what you want, it's fantastic though.

We're in the process of migrating away from sqldelight. Our initial investigations into https://www.graphile.org/postgraphile/ have been pretty spectacular, however this is a completely non-standard way of looking at your database.

[jw]

Are you using 2.0?

[spierce7]

we're on 1.5.4

[spierce7]

Does 2.0 change anything on the server db support front?

[jw]

I also would not recommend using 1.x for anything but sqlite. Alternative databases were somewhat of a hack in 1.x. With 2.0 all variants are on equal footing and the grammar and driver support for those dialects are way up.

[spierce7]

How is support for postgres schemas in sql delight 2.0?

[jw]

I never used it in 1.x, but I'm using 2.0 on two projects with postgres and haven't run into anything unsupported yet. I am, however, dealing with someone else's schema and not writing it myself which means that it may not be a representative subset

[hfhbd]

There were plenty PR,s especially for postgres. Do you mean different schemas? foo.test and bar.test? this is not yet supported, unfortunately.

[S.]

#komapper 👍🏻

[spierce7]

@hfhbd Yeah, that's what I mean

[kevin.cianfarini]

I use 2.0 for pgsql and like it. My understanding is the mysql support is better compared to pg, which I would feel comfortable using either in daily development.

[andrewoma]

Jdbi has some nice kotlin support if you're on the native query end of the spectrum. Any query result can be bound to a kotlin data class automatically for example

[spierce7]

Does 2.0 support json I wonder?

[hfhbd]

@spierce7 2.0 understands json types but maybe there is still something we missed.