Support for async drivers

[janvladimirmostert]

Currently SQL Delight is using JDBC or other blocking drivers

Jasync has async MySQL / PostgreSQL drivers which although it's using annoying Joda time, works great otherwise
(there is a ticket there somewhere to replace Joda Time with Java Time)

Converting Joda Time is not difficult either

		when (value) {
			is java.lang.Integer -> {
				return value.toInt() as T
			}
			is java.lang.Long -> {
				return value.toLong() as T
			}
			is org.joda.time.LocalDateTime -> {
				val utc = value.toDateTime(DateTimeZone.UTC);
				val secondsSinceEpoch: Long = utc.millis / 1000
				val milliSeconds: Long = utc.millis - (secondsSinceEpoch * 1000)

				val javaTime = LocalDateTime.ofEpochSecond(
					secondsSinceEpoch,
					milliSeconds.toInt() * 1000000,
					ZoneOffset.UTC
				)
				return javaTime as T
			}

Jasync returns a Future and can easily be converted to a Coroutine allowing non-blocking queries

suspend fun Connection.execute(query: String, vararg values: Any?): QueryResult {
	return if (values.isEmpty()) {
		this.sendQuery(query).await()
	} else {
		this.sendPreparedStatement(query, values.asList().toMutableList()).await()
	}
}

suspend fun ConnectionPool<*>.executeAsync(query: String, vararg values: Any?): Deferred<QueryResult> {
	return if (values.isEmpty()) {
		this.sendQuery(query).asDeferred()
	} else {
		this.sendPreparedStatement(query, values.asList().toMutableList()).asDeferred()
	}
}

[janvladimirmostert]

I'm having a look at sqldelight/drivers
i'm assuming i'll need a jasync-postgres-driver and a jasync-mysql-driver

Jasync natively returns a Future which i can convert to a Coroutine, this driver might be slightly different to the other ones, let me hack around a bit and see if i can get it working .

[AlecKazakova]

The generated code currently assumes a synchronous API to execute the queries, you could implement the SqlDriver interface but then you’d just block on the Future you get back so I’m not sure if that helps?

We’d need to do a pretty major overhaul to have the generated code use coroutines

If the goal is just to use the Jasync runtime then I think implementing SqlDriver and blocking on the Jasync Futures is probably the way to go

[andersio]

I want to mention a potential use case: an IndexedDB driver for Web (bridged via packages like SQLWeb).

AFAIK, IndexedDB is currently the only viable database solution that supports sharing with Web Workers, especially now with Web SQL being axed. It doesn't have synchronous query API in the standard, and there is no way in ECMAScript standard to do a blocking wait on Promises that IndexedDB produces.

[janvladimirmostert]

For my use-case, blocking anywhere was a dealbreaker and it looked like an enormous refactor to get this into SQL Delight.
(not that i didn't want to do it, just that the amount for work was more than the actual project i needed it for and time is always in short supply)
Went ahead and built my own PostgreSQL -> Kotlin Jasync bindings which took slightly more than a week.
(it's pinned on my profile, not as fancy as SQL Delight, but works for what i needed)

On the JDBC side, it's probably going to be a while before LOOM makes it into JDBC, maybe it's worth having two flavours of SQL Delight: SQL Delight and SQL Delight Async which can then more easily cater for async drivers.

On the async side, you have R2DBC which supports Project Reactor and RxJava styles covering Cloud Spanner, wrapping Jasync for MySQL and PostgreSQL, native drivers for h2, MariaDB, MSSQL as well as its own drivers for MySQL and PostgreSQL.
On the Jasync side, you have Futures coming back which can be converted to Coroutines

I'm assuming an async SQL Delight would be littered with suspends everywhere which would not be ideal for an SQL Delight running on JDBC