How to use DuckDB as a REST API
One of the more interesting features of Scratch Data is we let you treat your DuckDB instance as a RESTful API.
We'd written a bunch of code to query DuckDB, buffer the results, and convert to JSON - something particularly cumbersome in Go. However, with named pipes, we figured out a trick to have DuckDB handle all of this for us.
Before: Creating JSON from Scratch in Go
The first iteration of performing queries was cumbersome. The first step was to get a list of columns:
rows, _ := db.Query("DESCRIBE SELECT * FROM t")
for rows.Next() {
rows.Scan(&columnName, ...)
columnNames = append(columnNames, columnName)
}
Then we used DuckDB's to_json() function as a shortcut to translate from their data types to JSON:
rows, _ = db.Query("SELECT to_json(COLUMNS(*)) FROM (SELECT * FROM t)")
Finally, we stitched it all together. Using some magic from this 10-year-old Golang mailing list thread the code looked something like this:
pointers := make([]interface{}, len(cols))
container := make([]*string, len(cols))
for i, _ := range pointers {
pointers[i] = &container[i]
}
hasNext := rows.Next()
for hasNext {
err := rows.Scan(pointers...)
}
// Create JSON by combining the columnNames and our data
This worked but was cumbersome - DuckDB, after all, already knows how to create JSON. How could we get DuckDB to output that data not to a file on disk, but to an HTTP socket?
Named Pipes to the Rescue!
I noticed that DuckDB does have the ability to write to stdout.
The syntax looks like this:
COPY (SELECT * FROM t) TO '/dev/stdout' WITH (FORMAT 'json', ARRAY true)
Does this also work with named pipes? It does! Here was a quick CLI test:
$ mkfifo p.pipe
$ echo "COPY (SELECT * FROM t) TO './p.pipe' (FORMAT 'json', ARRAY true)" | duckdb
This process blocks until the pipe is read. Running cat p.pipe
worked as expected. Now to wire it up
to our Go program:
func QueryHandler(w http.ResponseWriter, r *http.Request) {
// Have DuckDB write to our pipe. This will block until data is read, so run
// it in a separate goroutine
go func() {
db.Exec("COPY (SELECT * FROM t) TO 'p.pipe' (FORMAT JSON, ARRAY true)")
}()
// Open pipe for reading
pipe, _ := os.OpenFile("p.pipe", os.O_CREATE|os.O_RDONLY, os.ModeNamedPipe)
// Copy data directly from pipe (where duckdb is writing) to HTTP handler
io.Copy(w, pipe)
}
Magic in only 3 lines of glue code!
Conclusion
DuckDB continously delights, as its support for pipes really simplifies our JSON code. One of the fun things about building this software is we get to pull all our Linux tricks out of the bag. If you have any comments or feedback, I'd love to hear them!