smoothdb

SmoothDB

SmoothDB provides a RESTful API to PostgreSQL databases. It is mostly compatible with PostgREST, with which it shares many characteristics.

The main differences are:

• SmoothDB is in development and alpha quality. Prefer PostgREST for now, which is rock solid
• SmoothDB is faster and has a lower CPU load
• It is written in Go
• Can be used both stand-alone and as a library (my main motivation for writing this)
• It also supports DDL (create / alter / drop databases, tables, manage constraints, roles, etc)
• Supports multiple databases

See TODO.md for the many things to be completed. Please create issues to let me know your priorities.

Getting started

Install and build

go get github.com/sted/smoothdb
go build

Start

./smoothdb

Starting SmoothDB this way, it creates a configuration file named config.jsonc in the current directory, which can be edited for further customizations.

API

Here you find some examples for the API. For more detailed information, see PostgREST API.

The default Content-Type is "application/json".

Authentication

Like PostgREST (see PostgREST Authentication), smoothdb is designed to keep the database at the center of API security.

To make an authenticated request, the client must include an Authorization HTTP header with the value Bearer <jwt>. For instance:

GET /test HTTP/1.1
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoic3RlZCJ9.-XquFDiIKNq5t6iov2bOD5k_LljFfAN7LqRzeWVuv7k

We will omit this header in the following examples.

Create a database

POST /admin/databases HTTP/1.1

{ "name": "testdb" }

Create a table

POST /admin/databases/test/tables HTTP/1.1

{ 
    "name": "test",
    "columns": [
		{"name": "col1", "type": "text", "notnull": true},
		{"name": "col2", "type": "boolean"},
		{"name": "col3", "type": "integer", "default": "42", "constraints": ["CHECK (col3 > 40)"]},
		{"name": "col4", "type": "timestamp"},
		{"name": "arr", "type": "integer[]"},
		{"name": "extra", "type": "json"},
		{"name": "duration", "type": "tsrange"},
		{"name": "other", "type": "text", "constraints": ["REFERENCES test (col1)"]}
	],
	"constraints": ["PRIMARY KEY (col1)"]}

Insert records

Insert one record:

POST http://localhost:8081/api/testdb/test HTTP/1.1

{
	"col1": "",
	"col2": false,
	"extra": {
		"a": "pippo",
		"b": 4444,
		"c": [1,2,3,"d"]
	},
	"arr": "{1,2,3}",
	"duration": "['2022-12-31 11:00','2023-01-01 06:00']"
}

Insert multiple records:

POST http://localhost:8081/api/testdb/test HTTP/1.1

[
	{ "col1": "one", "col3": 43},
	{ "col1": "two", "col3": 44}
]

Select records

GET http://localhost:8081/api/testdb/test?col3.gt=42 HTTP/1.1

[
  { "col1": "one", "col2": null, "col3": 43, "col4": null, "arr": null, "extra": null, "duration": null, "other": null },
  { "col1": "two", "col2": null, "col3": 44, "col4": null, "arr": null, "extra": null, "duration": null, "other": null }
]