Structured Query Server

SQL Queries for Apache CouchDB: Structured Query Server (SQS) is an SQL query engine for CouchDB, letting you use complex SQL SELECT statements to extract information from a CouchDB database with extreme flexibility.

The demo video gave me several “wow” moments, so I decided to give it a try. With SQS I can select a subset of attributes from my documents, making queries faster and lighter while still retaining richer data in the background. Honestly, I couldn’t think of an easier way — SQS makes the whole process remarkably simple.

Pedro León

Use Cases

Developers can build more complex software on top of CouchDB faster without sacrificing performance or CouchDB’s unique replication feature.

SQS seamlessly complements CouchDB’s built-in querying mechanisms and allows for complex queries that have not been possible with your CouchDB data before, including finding documents by multiple criteria, sorting by another value, and complex JOINS.

Data analysts can now query your CouchDB with the tools they are used to without any additional infrastructure to maintain.

Instead of having to learn CouchDB’s idiosyncrasies compared to SQL databases, SQS gives your data analysts full access to your CouchDB data.

Features

Example of a curl command with authentication used to query a database.

HTTP interface that returns JSON: query with anything, from anywhere, no SQL client necessary.

Diagram showing SQS running parallel with CouchDB.

SQS runs in parallel with CouchDB and keeps itself up to date.

JSON schema example defining fields for a movie database.

Works out of the box with most database designs and node configurations.

Stacks of paper files and folders in an office environment.

Handles millions of documents per database.

Bar chart comparing indexing speed of SQS and JS View.

Fast indexing (on average: on par with CouchDB JS views)

Structured Query Server is designed to feel as easy to use as the CouchDB views you already know:

Update a design doc to change your schema and SQS automatically picks it up.
SQS indexes are stored as hashes of the schema content, so you can “warm up” indexes in production before using them.
Supports _purge for strict data removal, for example to ensure GDPR compliance.
Includes _stats that show useful runtime statistics.
Can be configured with different log levels for everything from request logging to debugging and logs to stdout, a file or syslog.
Correctly handles single-node and clustered uses of CouchDB, including changes-feed rewinds.
Index compaction is handled automatically and can be switched to manual if needed.
Crash-proof design, just like CouchDB.

Installation and Usage

Structured Query Server is delivered as a binary package and is simple to set up and use.

Two-Step Installation

Start up the Structured Query Server and pass in the URL of your CouchDB as an argument.
In each CouchDB database you wish to query, add a special design document that defines your schema and how your CouchDB documents should map to tables and rows.

Usage

Send SQL SELECT queries to the Structured Query Server via simple HTTP requests:

curl http://user:pass@couchdb.example.com:4984/db/_design/sqs -X POST \
-d 'SELECT one_field FROM table WHERE other_field = 'value' AND third_field = 42 ORDER BY yet_another_field DESC LIMIT 5'

SQS is currently available for Linux x86_64, Mac x86_64, and Windows x86_64, with support for more architectures on the way.

To help you get the best out of SQS we offer a support package which allows you to get in touch with our CouchDB Support team if you need assistance with your production use of Structured Query Server

Technical Details

Structured Query Server is built in Node.js and uses SQLite for persistence and querying. Node.js’s event based runtime is perfectly suited to implement SQS, which loads data from CouchDB over the network and stores the results in on-disk indexes, while also allowing high-concurrency and fast HTTP requests for SQL queries.

SQS is a HTTP server that automatically inherits your CouchDB security setup. No additional access control management is required.

Supports all SQLite features: