Koa routing middleware allowing you to expose a sqlite database via RESTful CRUD
Mostly because I wanted to dig deeper into node web server code, but also because I haven't jumped onto the NoSQL bandwagon and think that web APIs are extremely useful. The result is a modest attempt at automating the CRUD boilerplate that every developer hates, while following the specs to make API consumption intuitive. I chose sqlite to keep the database side of things simple, with the intent that the API isn't serving heavy loads.
This tutorial will walk you through
By installing this library globally, you receive access to sqlite-to-rest.
The CLI currently contains one command generate-skeleton which creates an
initial bare-bones koa server from an existing sqlite database. This should
help you get started.
See sqlite-to-rest --help for more info.
require('sqlite-to-rest') returns an object with two properties.
generateSkeleton: madonna-function
-> promise(undefined)
This will usually be called from the CLI but is also made
available via the js API. Its purpose is to generate a barebones koa
server to get you up and running. In the directory it will:
npm init -f if a package.json doesn't existIt takes two properties
dir: isLadenString
isDirectory
Directory to generate the koa server.
dbPath optional: isSqliteFile
Path to your sqlite3 database.
// example
sqliteToRest
.generateSkeleton({
dir: beerApiDir,
dbPath: 'path/to/your/db.sqlite3',
})
.then(() => {
/* skeleton.js is ready to be ran */
})
getSqliteRouter: madonna-function
-> promise(koa-router)
This function generates the RESTful CRUD routing and returns the modified
koa-router instance.
It takes two properties
dbPath: isSqliteFile
Path to your sqlite3 database.
config optional: A routing config object
// example
const app = new require('koa')(),
dbPath = 'path/to/your/db.sqlite3'
getSqliteRouter({ dbPath }).then(router => {
app.use(router.routes())
// ...
})
The following is a list of the available crud operations made available by the
RESTful API in the form of pseudo examples. All assume a beer table with two
columns id INTEGER PRIMARY KEY and name which is nullable.
As noted in limitations, Be aware that unsafe methods (DELETE and POST) can only affect one row at a time.
GET This allows for the most variation. Click here for all available query operators. Keep in mind the following examples ignore proper query encoding
Headers may be specified right below urls
/beer
Requests for all rows
/beer?id=1
Where id = 1
/beer
range: rows=0-2
First three rows
/beer
range: rows=-5
Last five rows
/beer
range: rows=0-
As many rows as the server is able to provide, which in practice will be
the smaller of maxRange and total row count.
/beer
range: rows=1-
As many rows as the server is able to provide, starting from row 1.
/beer
order: name
Ordered by name ascending
/beer
order: name desc
Ordered by name descending
/beer
order: name desc,id
Contrived, but orders first by name descending, and in the case of a tie
by id ascending.
/beer?id>1
Where id > 1
/beer?id>=2&id<5
Where id >= 2 and id < 5
/beer?name_NOTNULL
Where name is not null
/beer?name_ISNULL
Where name is null
/beer?id!=5&name_LIKE'Spotted%'
Where id != 5 and name is LIKE "Spotted%" (ignore quotes)
/beer?id>=1&id<10&name_LIKE'Avery%'
order: name desc,id range: rows=2-4
Contrived for sake of example.
Get beer with ids between 1 and 9 inclusive, with name like "Avery%",
ordered first by name descending then by id ascending, getting the third
through 5th rows of the result. Or in SQL:
SELECT *
FROM beer
WHERE id >= 1
AND id <10
AND name LIKE 'Avery%'
ORDER BY name desc, id
LIMIT 3 OFFSET 2
DELETE Requires a query string with all primary keys set equal to a value. This enforces a maximum deletion of a single row.
if the beer table instead had a composite primary key of both id and name
POST create
Must not pass a query string. If a query string is passed, then POST update
is assumed. All POST requests must pass the header
content-type: application/json.
Keep in mind the body must contain all non-nullable and non INTEGER PRIMARY KEY columns. A 400 response will be sent otherwise indicating what fields were missed. Nullable columns will default to null and INTEGER PRIMARY KEY columns will automatically increment per sqlite3 specifications.
Json data will be specified right below urls
/beer
{"id":1,"name":"Serendipity"}
Creates a beer with id = 1 and name = 'Serendipity'
/beer
{"id":1}
Creates a beer with id = 1 and name = NULL
/beer
{"name":"Serendipity"}
Creates a beer with id set to the next incremented value per
sqlite3 INTEGER PRIMARY KEY specifications
/beer
{}
Creates a beer with id incremented, and name set to NULL
POST update
Must contain a query string. Without a query string, POST create is assumed.
As with POST create, the header content-type: application/json
is mandatory.
The query string must contain all primary keys to ensure only a single row gets updated. If incorrect values are passed, a 400 will be returned listing the offending keys.
The request body must contain a non-empty object and must contain valid keys corresponding to column names.
Json data will be specified right below urls
/beer?id=1
{"id":2}
Updates beer with id of 1 setting it to two.
/beer?id=1
{"name":"Two Women"}
Updates beer with id of 1 setting its name to Two Women.
if the beer table instead had a composite primary key of both id and name
{"name":"Moon Man"}Query conditions must be delimited by ampersands e.g. id>5&name!=Spotted Cow
Binary operators (require a value after)
=
!=
>=
<=
>
<
_LIKE
Unary operators (must follow a column name)
_ISNULL
_NOTNULL
isLadenPlainObject
The purpose of this object is to provide generic configuration for the sqlite
router. The following properties are allowed:
prefix: isLadenString
The string passed to koa-router's
prefix
constructor option. For example, the skeleton server doesn't specify
a prefix, allowing the beer api to be hit directly from the domain root
http://localhost:8085/beer. If you set prefix to '/api', then you
would instead send requests to http://localhost:8085/api/beer.
allTablesAndViews: A tabular configuration object
The configurations specified in this object will apply for all tables and
views, optionally overridden by the tablesAndViews property.
tablesAndViews: isLadenPlainObject
The object passed must have keys matching the database column or view
names. If not, a friendly error message will be thrown. The values for each
table and view must be a tabular configuration object
isLadenPlainObject
This object represents configurations that can be set for either views
or tables. It allows the following properties:
maxRange: isPositiveNumber
Application default: 1000
This is the maximum range your server will allow requests for. If a GET
request comes in with no range header, the spec assumes they want the entire
resource. If the number of rows resulting in that GET is greater than
maxRange, then a 416 status is returned with the custom header
max-range. The application default is purposefully
conservative in hopes that authors will set maxRange according to
their needs.
Note that 'Infinity' is a valid positive number.
flags: isLadenArray
Currently the only flag accepted is the string 'sendContentRangeInHEAD'.
When set, HEAD requests will return the available content range in the form
content-range: */<max-range>. The reason it's configurable is that
calculating max-range may be more work than its worth, depending on the load
of the server and the size of your tables.
These aren't all necessarily custom, but all their usage falls outside what's defined in the spec and thus need clarification.
GET
max-range: This header is returned when the requested number of rows
surpasses the configured maxRange. Note the
request might not specify the range header, but the number of rows
resulting in that resource will still be checked.
content-range: rfc7233 states
only the 206 (Partial Content) and 416 (Range Not Satisfiable) status codes describe a meaning for Content-Range.
When sqlite-to-rest responds with a 200 status code, the content-range
header is sent with the 206 format of <row start>-<row end>/<row count>.
When a request is sent without a range header and the number of resulting
rows surpasses maxRange, a 400 is returned
with content-range set in the 416 format of */<row count>
Note this header may be returned in a HEAD response.
accept-order: This will be returned if the request header order
had bad syntax or specified incorrect column names. For details, refer
to HEAD -> accept-order below.
HEAD
accept-order: accept-order is just a comma-delimited list of the
requested table columns, intended to tell the client the valid columns
able to be used in the request header order.
max-range: The configured maxRange
of the requested table.
content-range: This header will only be sent if the table has been
configured with the flag sendContentRangeInHEAD.
In that case, content-range is set to the 416 format of */<row count>
npm test
Content type
Image
Digest
Size
35.9 MB
Last updated
over 6 years ago
docker pull ntsurumi/sqlite-to-rest