I don't really understand this. So you send SQL to the driver program, which uses the C API to talk to the database? That means for everything except SQLite it's Scheme -> Driver -> DB server -> Driver -> Scheme. Also, are you talking to the driver over a socket (TCP or local, doesn't matter) or over a pair of pipes? If the latter, you have to make sure that both sides flush frequently, or you will get deadlocks.

It would also be possible to run the standard CLI client for the database using a PTY. That solves the flushing problem, but you'd need to carefully pick an output format (or small set of formats) that all CLI clients can produce. that doesn't throw away header names or manifest typing information, and unambiguously indicates the end of the result set somehow.

On Sat, Sep 14, 2019 at 3:30 AM Lassi Kortela <xxxxxx@lassi.io> wrote:

Would somebody like to explore a simple generic protocol for sending SQL
and results over a Unix pipe? We could take the existing C libraries for
SQLite, Postgres and MySQL, and write simple driver programs in C to
speak our protocol. Then connect Scheme implementations to those drivers
using their subprocess facilities. I'd like to have this for UpScheme.

It seems that we'll have to send SQL strings over the pipe. The
databases don't expose their native ASTs at all:

* Postgres protocol: <https://www.postgresql.org/docs/current/protocol.html>

* MySQL protocol:
<https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_PROTOCOL.html>