sqlrest

Automatically generate a REST API for a SQL database. Uses sqlalchemy to talk to databases, bottle to talk to web clients.

Usage

You can initialize sqlrest independently...

# start server on port 8000 mapped to a MySQL instance
$ python -m sqlrest.server \
  --frontend.port 8000 \
  --frontend.host '0.0.0.0' \
  --db.uri "mysql://root:@localhost:3306/kittendb"

...or by attaching it to another app,

import bottle
from configurati import attrs
from sqlrest.server import attach_routes

app = bottle.Bottle()

# attach other routes here
@app.get("/")
def hello_world():
  return "Hello, World!"

# attach sqlrest routes, with URLS prefixed by "/sqlrest". e.g.
# /sqlrest/kittens/columns, /sqlrest/kittens/select, and
# /sqlrest/kittens/aggregate to access table `kittens`
app = attach_routes(attrs({'uri': "mysql://root:@localhost:3306/kittendb"}), app=app, prefix="/sqlrest")

# start serving content
app.run(...)

Querying

Using the wonderful httpie project,

# Get all tables available
$ http get localhost:8000/tables

# Get column names in table `kittens`
$ http get localhost:8000/kittens/columns

# SELECT breed, location, count(*), min(birthday) FROM KITTENS
#   WHERE date(birthday) <= "2009-08-01" AND date(birthday) >= "2009-09-01"
#   GROUP BY breed, location
#   ORDER BY count(*) DESC
#   LIMIT 0, 10;
$ http get localhost:8000/kittens/aggregate <<< '{
    "groupby": ["breed", "location"],
    "filters": {
      "date(birthday)": ["2009-08-01", "2009-09-01"]
    },
    "aggregate": ["count(*)", "min(birthday)"],
    "orderby": ["count(*)", "descending"],
    "page": 0,
    "page_size": 10
  }'

# SELECT name, location FROM kittens
#   WHERE (name = "Mittens") AND
#     (breed = "Calico" OR breed = "Persian") AND
#     (birthday >= "2009-08-01" AND birthday <= "2009-09-01");
#   ORDER BY name
#   LIMIT 50, 60;
$ http get localhost:8000/kittens/select <<< '{
    "filters": {
      "name": "Mittens",
      "breed": ["Calico", "Persian"],
      "birthday": ["2009-08-01", "2009-09-01"]
    },
    "columns": ["name", "location"],
    "orderby": "name"
    "page": 5,
    "page_size": 10
  }'

Filters

Both aggregate and select endpoints can take an argument filters, an object where keys are column names and values are either arrays or single elements.

Let's take the following example,

{
  ...
  'filters': {
     "name": "Mittens",
     "breed": ["Calico", "Persian"],
     "date(birthday)": ["2009-08-01", "2009-09-01"]
  },
  ...
}

In this scenario, only rows where name == "Mittens", breed is one of "Calico" or "Persian", and date(birthday) is after "2009-08-01" but before "2009-09-01" are included. To select one of a finite set of date(birthday)ss, you must simply use a value with more than 2 values, e.g.

{
  ...
  'filters': {
    ...
    "date(birthday)": ["2009-08-01", "2009-09-01", "2009-10-01"]
    ...
  },
  ...
}

Create, Update, Delete

# INSERT INTO kittens
#   (name, breed, birthday)
#   VALUES
#     ("Mittens", "Persian", "2009-08-01"),
#     ( "Tigger",  "Calico", "2009-09-01");
$ http PUT localhost:8000/kittens <<< '{
    "rows": [
      {
        "name": "Mittens",
        "breed": "Persian",
        "birthday": "2009-08-01"
      },
      {
        "name": "Tigger",
        "breed": "Calico",
        "birthday": "2009-09-01"
      }
    ]
  }'

# DELETE FROM kittens
#   WHERE name = "Tigger";
$ http DELETE localhost:8000/kittens <<< '{
    "filters": { "name": "Tigger" }
  }'

# UPDATE kittens
#   SET birthday = "2009-08-01"
#   WHERE name = "Mittens";
$ http PATCH localhost:8000/kittens <<< '{
    "filters" : { "name"     : "Mittens"   },
    "values"  : { "birthday" : "2009-08-01"}
  }'

Caching

sqlrest supports caching via Redis. By default, caching is disabled, but it can be enabled by adding settings caching.enabled = True in your config. For example,

$ redis-server &
$ python -m sqlrest.server                         \
  --db.uri "mysql://root:@localhost:3306/kittendb" \
  --caching.enabled true                           \
  --caching.config.port 6379                       \
  --caching.config.host localhost                  \
  --caching.timeouts '{"select": 300, "aggregate": 900}'

In the event that caching is enabled and sqlrest is unable to reach Redis, it will issue a log warning but will continue serving as if caching were disabled.

Configuration

Configuration in sqlrest is handled by configurati with the following specification,

config.py

frontend = {
  'port'   : optional(type=int, default=8000),
  'host'   : optional(type=str, default='0.0.0.0'),
  'prefix' : optional(type=str, default='')
}

db = {
  'uri': required(type=str)
}

caching = {
  'enabled' : optional(type=bool, default=False),

  # I don't really want to copy/paste all of redis.StrictRedis's
  # arguments, so I'll just leave this as a dict
  'config'  : optional(type=dict, default={}),

  'timeouts' : {
    'tables'    : optional(type=int, default=99999),
    'columns'   : optional(type=int, default=99999),
    'select'    : optional(type=int, default=60 * 5),
    'aggregate' : optional(type=int, default=60 * 60 * 24),
  }
}

# enable create, update, delete endpoints
editing = optional(type=bool, default=False)

A configuration file can be used via the --config command line parameter,

$ python -m sqlrest.server --config config.py