neighbourhoodie-nnh-logo
« Back

Structured Query Server

SQL Queries for Apache CouchDB

Product Description

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.

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.

Just to add, loving it so far, its amazing how much capability this will bring to [our product] once its fully integrated!

Our current method of working around CouchDB Query limitations are a significant performance bottleneck.

This will replace large swaths of our custom code.

Features

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

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

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

Handles millions of documents per database.
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

  1. Start up the Structured Query Server and pass in the URL of your CouchDB as an argument.
  2. 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:

  • Subqueries, including correlated subqueries.
  • Up to 64-way JOINs.
  • LEFT, RIGHT, FULL INNER, OUTER and CROSS JOINs.
  • DISTINCT, ORDER BY, GROUP BY, HAVING, LIMIT, and OFFSET.
  • UNION, UNION ALL, INTERSECT, and EXCEPT.
Buy now

Pricing

No monthly subscription fees, get your SQS licence directly via one-time-payment.

Buy now for 1490 EUR*

* VAT is added if applicable

Please consult our Software License agreement for more information.

To make sure you always get the help you need while using SQS we are offering product support separately.

See our FAQ for more information.