Skip to content

Running SQL queries

Introduction

Under the hood, Sheetlabs stores data tables in PostgreSQL, which is a powerful relational database. Users with more complex use cases may wish to run SQL queries directly against their tables, which allows for the full power (and complexity!) of SQL.

This may be useful for:

  1. Joining multiple tables together.
  2. Performing more complex filtering or manipulation of data.
  3. Allowing you to treat Sheetlabs more like a database, and less like a series of APIs.

Getting started

To make an SQL query, send a POST request to http://sheetlabs.com/query/{organisation}, with your SQL query as the body. You must also add an HTTP header of Content-type: text/plain.

The table names are the same as those listed on https://app.sheetlabs.com/#/tables, except they are prefixed with your organisation code followed by an underscore. For example, "oldestdomains" would become "ACME_oldestdomains". The column names are the same listed in the data tables in the Sheetlabs web interface.

Here is a quick example of running an SQL query using cURL:

curl \
   -X POST \
   --data "SELECT * FROM ACME_oldestdomains" \
   -u "user@example.com:token" \
   -H "Content-type: text/plain" \
    http://sheetlabs.com/query/ACME

Of course, you can send far more complex SQL queries than this. You can use the full PostgreSQL query language.

Limitations

SQL queries via Sheetlabs are subject to the following limitations:

  1. Only SELECT queries are supported.
  2. You can only query tables in your organisation; you cannot join or query from other organisations.
  3. Queries are limited to 5 seconds execution time.
  4. You can only run one query per request; you cannot send a batch of queries.
  5. Authentication is required when running SQL queries, and only users with Admin level access are able to access them. We may relax this in the future, if desired by users.