I think providing a way to make SQLite and NIO play nice is valuable. However, I do not think the API in the package is the right abstraction.
- The API doesn't make it possible to efficiently insert multiple records (reusing the same prepared statement, possibly not even changing all of the bound parameters after a step).
- The Statement type queries a bunch of information from SQLite that might not be needed, and then copies it around with every Row.
- There are no ways to conditionally read some columns from query results.
- You can't customize flags the connection is opened with.
- You can't customize the busy handler, which currently just waits forever (I think this can lead to deadlocks, but I'm not quite sure right now).
Now, don't get me wrong: I'm not worried about all that functionality not being available in the package right now. What I do worry about is that the architecture doesn't seem to be designed in a way that allows for functionality like that to be slotted in later. I think this stems from the fact that the package tries to be both a swifty SQLite interface as well as a NIO adapter at the same time.
One thing that sets SQLite apart from other SQL databases (in my experience at least; I haven't worked as much with others as I have with SQLite) is that there is a bunch of functionality in the C interface that can and should be employed to both improve performance and enable advanced use-cases.
For someone looking to do more than just fire off a few non-performance-sensitive queries, looking to use even one bit of the C interface, the package as it exists right now is, sadly, completely useless: Even if going down to the C interface would be acceptable (which is awful to use directly thanks to the pervasive use of opaque types), the package doesn't provide access to the underlying handles.
To address these shortcomings, I would propose an alternative architecture:
First, a target that is a very thin (and as little overhead as possible) wrapper to make the C API a tad more usable in Swift. This should really be not much more than an apinotes overlay could provide: Make functions into methods, replace
OpaquePointer occurrences with proper types, and possibly convert result codes into thrown errors, if that can be done without too much overhead.
This target could then be used as base for all sorts of SQLite packages, related to NIO or not (crucially, this should work on all platforms that both SQLite and Swift support, even if NIO does not).
Second, a target that provides integration with NIO. From my experience using SQLite in concert with Dispatch and OperationQueue so far, it would probably for the moment suffice to have a database-queue type that has a single method
func run<T>(_ block: (Connection) -> T) -> EventLoopFuture<T>, but I think it might be possible to extend and keep the existing interface around (although maybe with deprecations sprinkled generously over it) to avoid a breaking change as well.
Any convenience APIs not directly related to NIO can then be implemented (either in this or in other packages) on top of the wrapper, which would make them usable both with the NIO adapter as well as without, enabling more code reuse across the ecosystem.
Wrt. linking SQLite statically, I think that is an interesting enhancement, especially if in the future SPM gained a feature-flag system that would allow easy customization of which parts of the amalgamation are included. I do think keeping the ability to use the system version is important though, so as long as only one is possible (or is there a SPM feature that would allow the user to choose?) the system library is probably the way to go.
When it comes to renaming the symbols to avoid collisions, I do not think that is a good idea. For one, it would make using the system provided SQLite more difficult to impossible, and it would also mean that to use SQLite extensions written in C (both those provided by the SQLite project as well as third-party), one would have to fiddle around with the sources. Just
's/sqlite3_/foobar_sqlite3_/g' also won't cut it, because there are at least constants (and maybe macros?) to deal with, as well as internal functions, types and constants that do not follow the sqlite3_* naming scheme.
The SQLite C interface also doesn't change very often and (afaik) only in backwards-compatible ways, so I'm not too worried there are issues to be solved here.