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
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent