RWSERVE plugin to implement a join-free MySQL REST API
MIT License
It is a common practice when developing web-based software to store and retrieve database records using a REST API over AJAX. Of course every application's needs will be different, but functionality to create new records, update and delete existing records, and retrieve records one at a time or in groups, are common needs.
This plugin allows you to fulfill those needs without any customization: everything necessary to build a simple REST API for one or more database tables can be declaratively specified in the server's configuration.
The RWSERVE MySQL REST
plugin allows you to perform these types of HTTP
requests:
Create new records, one at a time, by sending JSON data containing column names
and column values, using the HTTP PUT
method.
Update an existing record or multiple records, which match specified conditions,
by sending JSON data containing column names and column values, using the HTTP PATCH
method.
Delete an existing record or multiple records, which match specified conditions,
using the HTTP DELETE
method.
Retrieve selected record(s) from a single table, using the HTTP GET
method, with
these filtering capabilities:
Determining the number of records matching a specified set of conditions using
the count(*)
column name.
This plugin is open source and can be extended by you to provide functionality beyond what's described above, such as:
Other Read Write Tools HTTP/2 Server
built-in modules can complement this
plugin's feature set, when enabled, providing:
The plugin module is available from NPM
. Before proceeding, you should already have Node.js
and RWSERVE
configured and
tested.
This module should be installed on your web server in a well-defined place, so
that it can be discovered by RWSERVE
. The standard place for public domain
plugins is /srv/rwserve-plugins
.
Make the software available by declaring it in the plugins
section of your
configuration file. For detailed instructions on how to do this, refer to the plugins
documentation on the Read Write Tools HTTP/2 Server
website.
The config
settings require some explanation.
The connection
section specifies classic MySQL connection parameters. Refer to
the MySQL docs for more about each of those.
The maxrows
option limits the number of rows that can be retrieved in a single
SQL SELECT query. When a LIMIT parameter is provided in a request, that limit
will be honored, but only if it is less than or equal to the maxrows
value.
The schema
section declares the names of tables and columns that the REST API
can access. In the example above, customers
is a table name; oid
, schema_number
,
customer_number
and email_address
are column names. There are no limits to the
number of tables or columns that can be specified in the schema. Note that no
other column metadata — such as data type, data length, is null, etc. — is
specified here.
A full configuration file with typical settings for a server running on
localhost port 7443, is included in this NPM module at etc/mysql-rest-config
. To
use this configuration file, adjust these variables if they don't match your
server setup:
The discussion below uses a MySQL database created with these statements:
The plugin uses HTTP methods to determine which type of SQL query to perform; it uses URL query string parameters to determine how to perform the query; it uses the HTTP request body to determine what columns and values to use in the query; and it uses the HTTP response body to provide the results of the query.
The request body is used with PUT
and PATCH
methods. It should have content-type
of "application/json" containing an object with key-value pairs, where each key
is an SQL column name, and each value is its corresponding data.
The response body will always have content-type
of "application/json". For the PUT
method it will contain the single value insertID
. For the PATCH
and DELETE
methods
it will contain the single value affectedRows
. For the GET
method it will
contain an array of objects, each containing the columns and values requested.
The columns
query-string parameter is used only with the GET
method. It should
be in JSON that is urlencoded. The JSON should be an array containing the column
names to retrieve. This parameter is optional, and when omitted, the query will
retrieve all columns of the specified table.
The special column count(*)
, or its aliased version count(*) AS cnt
, is also
allowed, and is the best way to determine how many rows match the specified
conditions.
The table
query-string parameter is required for all methods. It is plain-text
that is urlencoded.
The where
query-string parameter should be provided for GET
, PATCH
, and DELETE
methods.
It should be in JSON that is urlencoded. The JSON may use implicit style,
explicit style, or multi style.
["oid", "1234"]
,WHERE oid = 1234
.["email_address", "LIKE", "%.example.com"]
WHERE email_address LIKE "%.example.com"
.[["email_address", "LIKE", "%.example.com", "AND"], ["account_type","=","expired"]]
WHERE email_address LIKE "%.example.com" AND account_type = "expired"
The orderby
query-string parameter may be provided for the GET
method only. It
should be in JSON that is urlencoded. The JSON is either in single column form
or multi column form.
"customer_number"
establishesORDER BY customer_number
. The JSON value may also include the"customer_number DESC"
establishes the SQL clause ORDER BY customer_number DESC
["account_type ASC", "customer_number DESC"]
establishes theORDER BY account_type ASC, customer_number DESC
.The limit
query-string parameter is a simple numeric value, not using JSON, and
not needing urlencoding. For example limit=100
establishes the SQL clause LIMIT 100
.
The offset
query-string parameter is a simple numeric value, not using JSON, and
not needing urlencoding. For example offset=200
establishes the SQL clause OFFSET 200
.
These examples simulate AJAX requests using CURL commands. (The examples are shown using multiple lines for visual purposes, but of course CURL expects a single line containing all of its arguments.)
Create a single new record.
This responds with status code 200
and a JSON response body containing the
auto-increment number assigned to the primary key oid
:
Update all records matching the conditions specified in the URL's where
query-string.
The request body contains a JSON string containing column names and column
values that should be updated. (Omit any columns that do not need to be
updated.)
This example reassigns all records with account_type
of "subscriber" to have the
new account_type
of "member". The WHERE conditions follow the rules described
above, so the JSON should be ["account_type","subscriber"]
, and its urlencoded
equivalent should be %5B%22account_type%22%2C%22subscriber%22%5D
.
This responds with status code 200
and a JSON response body containing the
number of rows affected.
Delete all records matching the conditions specified in the URL's where
query-string.
The request body is empty.
This example deletes all records with account_type
of "expired". The WHERE
conditions follow the rules described above, so the JSON should be ["account_type","expired"]
, and its urlencoded equivalent should be %5B%22account_type%22%2C%22expired%22%5D
.
This responds with status code 200
and a JSON response body containing the
number of rows affected.
Retrieve the column values that match the specified conditions, sort criteria, limit and offest rules, which are specified in the URL as query string variables. The request body is empty.
This example gets the customer_number
and email_address
of the 20th through the
29th customer records, alphabetically ordered by customer_number
, where the account_type
is "verified". These are the URL's query string variables:
["customer_number", "email_address"]
%5B%22customer_number%22%2C%20%22email_address%22%5D
["account_type","verified"]
, and%5B%22account_type%22%2C%22verified%22%5D
."customer_number ASC"
, and its%22customer_number%20ASC%22
.This responds with status code 200
and a JSON response body containing an array
of objects, each containing the columns and values requested:
When the request is not properly prepared or when MySQL is unable to execute the
query, the HTTP status code is 400
. Examine the HTTP header rw-mysql-rest
for
the reason.
Once you've tested the plugin and are ready to go live, adjust your production
web server's configuration in /etc/rwserve/rwserve.conf
and restart it using systemd
. . .
. . . then monitor its request/response activity with journald
.
This is a plugin for the Read Write Tools HTTP/2 Server, which works on Linux platforms.
The rwserve-mysql-rest plugin is licensed under the MIT License.