Hi guys, I have question for couroutines with SQLi...

# coroutines

[Tran Thang]

Hi guys, I have question for couroutines with SQLite, So Can we using Coroutines with SQLite? and talk to me advantages and disadvantages when make that?

[Dominaezzz]

Short answer is yes. Long answer is SQLite transactions are thread local.

[gildor]

There is no advantages or disadvantages except that you can run requests on some IO dispatcher.
I would recommend to take a look on https://github.com/cashapp/sqldelight
It has coroutines adapter too

[Dominaezzz]

Also be sure to use a Semaphore(1) for writes. It will keep resource/thread usage to a minimum.

[gildor]

I wouldn't bother with semaphore until there is heavy parallelism
It depends on the type of the app of course, but I wouldn't use sqlite on a serious server applications anyway and it should be a pretty rare case when semaphore would really show visible performance improvements on a client app

[Dominaezzz]

It does indeed depend on the app but it's easy enough to just wrap the write transactions in a withPermit { INSERT } that I think everyone should just do it once concurrency might be involved.

[gildor]

I may be wrong, just my gut feeling, never really tried to measure it

[gildor]

You also can just use custom thread pool for all db operations and it would never consume to many threads, even with heavy parallelism

[Dominaezzz]

SQLite can be used for serious servers so long it's read heavy and not distributed. Once you need to have replication, failover, etc. then there are better DBs for that.

[Dominaezzz]

Is it possible to create custom dispatchers that share threads with the default one?

[gildor]

I mean concurrency of course is involved on client applications, but how much concurrency is another question

[gildor]

Now yes, with limited parallelism

[gildor]

https://kotlinlang.org/api/kotlinx.coroutines/kotlinx-coroutines-core/kotlinx.coroutines/-coroutine-dispatcher/limited-parallelism.html

[Dominaezzz]

Oh sweet, I haven't been following for a while but glad it's finally a thing.

[gildor]

Even without this, you could just have a thread executor for this, so it doesn't share threads, but at least will not consume too many

[Dominaezzz]

Having to create new threads instead of reusing the existing ones seems wasteful though.

[gildor]

SQLite can be used for serious servers so long it's read heavy and not distributed.

I would say if there is no writing from clients at all, and in this case you don't need a write semaphore 😅

[gildor]

Well, it's wasteful if you have options to share them, otherwise it's just a necessity 🙈

[Dominaezzz]

Otherwise yeah, thankfully it's not necessary with the limited parallelism.