SQLite2REST; Soul; Feature-rich Postgres CRUD

When prototyping a new app or data repository, it can be useful to have a basic CRUD REST API shell available that encapsulates network-based programmatic access to and operations on the data. Today we’ll look at two different ones that wrap SQLite databases and one that does the same for Postgres using tools from three different language ecosystems. Regardless of your preference, you’ll be up and running (or, is it, RESTing?) in no time.

SQLite2REST (Python)

SQLite2REST simplifies the process of creating a RESTful API from a SQLite database using Python’s Flask web framework. It automagically reads the schema of a SQLite database and generates endpoints for each table, allowing for Create, Read, Update, and Delete (CRUD) operations. The library also generates an OpenAPI specification for the API.

This is the way to get it working on your system:

$ mkdir sqlite2rest
$ cd sqlite2rest
$ virtualenv s2r
$ source ./s2r/bin/activate
$ python3 -m pip install sqlite2rest
$ curl -so chinook.db https://github.com/nside/sqlite2rest/raw/main/data/chinook.db
$ ./s2r/bin/sqlite2rest serve chinook.db

and, this will get you all the Employees from the good ol’ “chinook” example database:

$ curl -s http://localhost:5000/Employee | jq '.[0]'
{
  "Address": "11120 Jasper Ave NW",
  "BirthDate": "1962-02-18 00:00:00",
  "City": "Edmonton",
  "Country": "Canada",
  "Email": "andrew@chinookcorp.com",
  "EmployeeId": 1,
  "Fax": "+1 (780) 428-3457",
  "FirstName": "Andrew",
  "HireDate": "2002-08-14 00:00:00",
  "LastName": "Adams",
  "Phone": "+1 (780) 428-9482",
  "PostalCode": "T5K 2N1",
  "ReportsTo": null,
  "State": "AB",
  "Title": "General Manager"
}

It has all the core CRUD ops:

• GET /<table>: Get all records from the table.

• GET /<table>/<id>: Returns the record with the given ID from the table.

• POST /<table>: Create a new record in the table. The data for the record should be provided as JSON in the request body.

• PUT /<table>/<id>: Update an existing record in the table. The data for the record should be provided as JSON in the request body.

• DELETE /<table>/<id>: Delete an existing record from the table.

Soul (JavaScript)

Soul is a JavaScript-based SQLite REST and real-time server with scads more functionality than the simple Python tool mentioned in the first section. It drops in just as cleanly:

$ npm i -g soul-cli

And you fire up the REST API with:

$ soul -d chinook.db

The http://localhost:8000/api/docs/ endpoint has full, interactive Swagger docs on the API endpoints.

Here’s how to get a list of tables:

curl -s http://localhost:8000/api/tables | jq '.data[]|.name'
"Artist"
"Customer"
"Employee"
"Genre"
"Invoice"
"InvoiceLine"
"MediaType"
"Playlist"
"PlaylistTrack"
"Track"

and, here’s a similar example, pull of the deets on Mr. Adams:

curl -s 'http://localhost:8000/api/tables/Employee/rows?_page=1&_limit=1' | jq
{
  "data": [
    {
      "EmployeeId": 1,
      "LastName": "Adams",
      "FirstName": "Andrew",
      "Title": "General Manager",
      "ReportsTo": null,
      "BirthDate": "1962-02-18 00:00:00",
      "HireDate": "2002-08-14 00:00:00",
      "Address": "11120 Jasper Ave NW",
      "City": "Edmonton",
      "State": "AB",
      "Country": "Canada",
      "PostalCode": "T5K 2N1",
      "Phone": "+1 (780) 428-9482",
      "Fax": "+1 (780) 428-3457",
      "Email": "andrew@chinookcorp.com"
    }
  ],
  "total": 8,
  "next": "/tables/Employee/rows?_limit=1&_page=2",
  "previous": null
}

If you start it up in “studio” mode:

soul --studio --database=./chinook.db

You get a full-featured GUI to all your tables and records:

You can even tap into the Websocket to watch for POST, PUT, and DELETE ops for a given table. (You can install the wscat utility used in those examples via npm i -g wscat).

As if that’s not enough to catch your interest, Soul also sports and extension framework.

It’s super lightweight and very well-designed.

Feature-rich Postgres CRUD (Golang)

pREST (PostgreSQL RESTful Engine) is an open-source, low-code solution designed to simplify and accelerate the development of PostgreSQL applications by exposing your database in a RESTful API format. It supports PostgreSQL version 9.5 or higher and aims to make data access fast, secure, and scalable without the need for complex software development.

It offers a wide range of features and capabilities, including:

• automatic generation of RESTful APIs based on a database schema

• support for “migrations”, which are a way of propagating database changes such as adding a field, deleting a model table, or modifying data in bulk. Migrations are designed to be mostly automatic (you’ll need to know when to perform them and how to handle common issues).

• using query strings — similar to Soul — to apply filtering, sorting, pagination, and other operations to API queries.

• JWT Token generation based on two fields (e.g., user and password), allowing you to use an existing table from your database for authentication.

• configuration of read, write, and delete permissions for each table in pREST using a prest.toml configuration file.

• the ability to create custom endpoints by writing shared libraries that can be loaded when starting the server.

• a handy Docker image (with examples)

Their docs have all you need to get up and running.

FIN

It’s hard to believe tomorrow is WPE day already! ☮