I'm considering pitching a package that wraps SQLite's C APIs in a NIO friendly API. This package is called SQLiteNIO.
It has a very similar user-facing API to PostgresNIO but what it does internally is much different. Instead of using a wire protocol over TCP like Postgres, libsqlite is in process and offers a blocking C API. This package puts all calls to libsqlite on a NIOThreadPool and then dispatches back to the event loop as needed.
If the working group / community seems interested, I will do a full pitch / proposal for this.
So I don’t have a lot of knowledge about this. Was the different approach because it was easier to write, or are there other benefits/considerations?
The approach was different because the way MySQL and Postgres work is fundamentally different to SQLite. MySQL and Postgres (and most other DBs) work by having a server running that you connect to over a network. These DBs have "wire protocols", or formats for communicating with each other over TCP. This is most of what MySQLNIO and PostgresNIO do: parsing and serializing messages in their respective wire protocols.
SQLite is different. It is completely local / in-process. There's no networking. There's no wire protocol or "server". Everything that SQLite does is done by the C library with data being stored either in memory or in a local file. Because of this, there's no way to circumvent the C library like we can do with MySQLNIO / PostgresNIO. The only option is to try and make the C library easy to use with NIO.
This is not ideal performance-wise since using blocking libraries defeats the point of SwiftNIO being non-blocking, but for SQLite's use cases this seems fine. It's generally used for applications where performance isn't critical anyway, like testing.
I am on the fence here. While I understand the importance for Vapor to have a NIO wrapper around SQLite, I think it is a bit unfortunate to add a hard NIO dependency for the use-cases that do not benefit from asynchrony. SQLite is extremely valuable in a number of use cases, most of which are not concurrent web server based (as the idiom goes, “It’s not a replacement for Postgres, it’s a replacement for fopen.”)
I think I’d like to see the SSWG first tackle building a really nice idiomatic blocking SQLite library that wraps the unsafety of libsqlite3 in something nicer and much more Swift like. Then we can worry about whether we should also adopt one that wraps SQLite in a NIO thread pool.