Use actix-web to serve a REST API for your PostgreSQL database.
use actix_web::{App, HttpServer};
use postgres_rest_actix::{Config};
use tokio_postgres::NoTls;
fn main() {
let ip_address = "127.0.0.1:3000";
// start 1 server on each cpu thread
HttpServer::new(move || {
App::new().service(
// appends an actix-web Scope under the "/api" endpoint to app.
Config::new("postgresql://postgres@0.0.0.0:5432/postgres", NoTls)
.generate_scope("/api"),
)
})
.bind(ip_address)
.expect("Can not bind to port 3000")
.run()
.unwrap();
println!("Running server on {}", ip_address);
}TBD.
You can use dots (.) to easily walk through foreign keys and retrieve values of rows in related tables!
Assume the following schema:
-- DB setup
CREATE TABLE public.company (
id BIGINT CONSTRAINT company_id_key PRIMARY KEY,
name TEXT
);
CREATE TABLE public.school (
id BIGINT CONSTRAINT school_id_key PRIMARY KEY,
name TEXT
);
CREATE TABLE public.adult (
id BIGINT CONSTRAINT adult_id_key PRIMARY KEY,
company_id BIGINT,
name TEXT
);
ALTER TABLE public.adult ADD CONSTRAINT adult_company_id FOREIGN KEY (company_id) REFERENCES public.company(id);
CREATE TABLE public.child (
id BIGINT CONSTRAINT child_id_key PRIMARY KEY,
parent_id BIGINT,
school_id BIGINT,
name TEXT
);
ALTER TABLE public.child ADD CONSTRAINT child_parent_id FOREIGN KEY (parent_id) REFERENCES public.adult(id);
ALTER TABLE public.child ADD CONSTRAINT child_school_id FOREIGN KEY (school_id) REFERENCES public.school(id);
INSERT INTO public.company (id, name) VALUES (100, 'Stark Corporation');
INSERT INTO public.school (id, name) VALUES (10, 'Winterfell Tower');
INSERT INTO public.adult (id, company_id, name) VALUES (1, 100, 'Ned');
INSERT INTO public.child (id, name, parent_id, school_id) VALUES (1000, 'Robb', 1, 10);
Running the GET operation:
GET "/api/child?columns=id,name,parent_id.name,parent_id.company_id.name"
# | ------------------------------------------------ column names
# ^^^^^ {table} valueWill return the following JSON:
[
{
"id": 1000,
"name": "Robb",
"parent_id.name": "Ned",
"parent_id.company_id.name": "Stark Corporation"
}
]Change the previous API endpoint:
Before:
/api/child?columns=id,name,parent_id.name,parent_id.company_id.name
After:
/api/child?columns=id,name,parent_id.name as parent_name,parent_id.company_id.name as parent_company_name
or
/api/child?columns=id,name,parent_id.name parent_name,parent_id.company_id.name parent_company_name
Now, the response will return the aliased fields instead:
[
{
"id": 1000,
"name": "Robb",
"parent_name": "Ned",
"parent_company_name": "Stark Corporation"
}
]- Your tables & columns only contain letters, numbers, and underscore. We are converting query parameters/body parameters into an SQL abstract syntax tree (AST) before finally executing an SQL query in the background; there is no schema/model configuration (like in Diesel), so this restriction makes data easier to validate & secure.
- You don’t need to query for
HStore,bit, orvarbit(technical limitations for now). actix-web >= 1.0.0.- Rust >= 1.39 (that means nightly, for now).
The Config struct contains the configuration options used by this library.
use postgres_rest_actix::Config;
use tokio_postgres::NoTls;
let config = Config::new("postgresql://postgres@0.0.0.0:5432/postgres", NoTls);
let scope = config.generate_scope("/api");To see all options, visit the API docs.
Displays a list of all available endpoints and their descriptions + how to use instructions.
Queries {table} with given parameters using SELECT. If no columns are provided, column stats for {table} are returned. DOT (.) syntax can be used in columns, distinct, where, group_by, and order_by.
A comma-separated list of column names for which values are retrieved. Example: col1,col2,col_infinity.
A comma-separated list of column names for which rows that have duplicate values are excluded. Example: col1,col2,col_infinity.
The WHERE clause of a SELECT statement. Remember to URI-encode the final result. Example: (field_1 >= field_2 AND id IN (1,2,3)) OR field_2 > field_1.
Comma-separated list representing the field(s) on which to group the resulting rows. Example: name, category.
Comma-separated list representing the field(s) on which to sort the resulting rows. Example: date DESC, id ASC.
The maximum number of rows that can be returned. Default: 10000.
The number of rows to exclude. Default: 0.
Inserts new records into the table. Returns the number of rows affected. Optionally, table columns of affected rows can be returned using the returning_columns query parameter (see below).
The ON CONFLICT action to perform (can be update or nothing).
Comma-separated list of columns that determine if a row being inserted conflicts with an existing row. Example: id,name,field_2.
Comma-separated list of columns to return from the INSERT operation. Example: id,name,field_2. Unfortunately PostgreSQL has no native foreign key functionality for RETURNING columns, so only columns that are on the table being inserted can be returned.
An array of objects where each object represents a row and whose key-values represent column names and their values.
POST /api/child
{
"id": 1001,
"name": "Sansa",
"parent_id": 1,
"school_id": 10
}
returns { "num_rows": 1 }.
Assuming the “Simple Insert” example above was run:
POST /api/child?conflict_action=nothing&conflict_target=id
{
"id": 1001,
"name": "Arya",
"parent_id": 1,
"school_id": 10
}
returns { "num_rows": 0 }.
Assuming the “Simple Insert” example above was run:
POST /api/child?conflict_action=update&conflict_target=id
{
"id": 1001,
"name": "Arya",
"parent_id": 1,
"school_id": 10
}
returns { "num_rows": 1 }. name: "Sansa" has been replaced with name: "Arya".
POST /api/child?returning_columns=id,name
{
"id": 1002,
"name": "Arya",
"parent_id": 1,
"school_id": 10
}
returns [{ "id": 1002, "name": "Arya" }].
Updates existing records in {table}. Returns the number of rows affected. Optionally, table columns of affected rows can be returned using the returning_columns query parameter (see below). DOT (.) syntax can be used in where, returning_columns, as well as the request body (see examples).
The WHERE clause of an UPDATE statement. Remember to URI-encode the final result. Example: (field_1 >= field_2 AND id IN (1,2,3)) OR field_2 > field_1.
Comma-separated list of columns to return from the UPDATE operation. Example: id,name,field_2.
An object whose key-values represent column names and the values to set. String values must be contained inside quotes or else they will be evaluated as expressions and not strings.
Assume the following database schema for these examples:
CREATE TABLE IF NOT EXISTS public.coach (
id BIGINT CONSTRAINT coach_id_key PRIMARY KEY,
name TEXT
);
CREATE TABLE IF NOT EXISTS public.team (
id BIGINT CONSTRAINT team_id_key PRIMARY KEY,
coach_id BIGINT,
name TEXT
);
CREATE TABLE IF NOT EXISTS public.player (
id BIGINT CONSTRAINT player_id_key PRIMARY KEY,
team_id BIGINT,
name TEXT
);
ALTER TABLE public.player ADD CONSTRAINT player_team_reference FOREIGN KEY (team_id) REFERENCES public.team(id);
ALTER TABLE public.team ADD CONSTRAINT team_coach_reference FOREIGN KEY (coach_id) REFERENCES public.coach(id);
INSERT INTO public.coach (id, name) VALUES
(2, 'Doc Rivers'),
(4, 'Bill Donovan'),
(5, 'Mike D''Antoni');
INSERT INTO public.team (id, coach_id, name) VALUES
(2, 2, 'LA Clippers'),
(4, 4, 'OKC Thunder'),
(5, 5, 'Houston Rockets');
INSERT INTO public.player
(id, name, team_id)
VALUES
(3, 'Garrett Temple', 2),
(4, 'Wilson Chandler', 2),
(5, 'Russell Westbrook', 4);
PUT /api/player?where=id%3D5
{ "team_id": 5 }
Result:
{ "num_rows": 1 }
Russell Westbrook’s team_id is now 5.
PUT /api/player?where=id%3D5&returning_columns=name,team_id
{ "team_id": 5 }
Result:
[{ "name": "Russell Westbrook", "team_id": 5 }]
Russell Westbrook’s team_id is now 5.
PUT /api/player?where=name%3D'Russell Westbrook'&returning_columns=name
^-----------------^ Notice the quotes used to pass a string value
Body:
{ "name": "'Chris Paul'" }
^----------^ Notice the quotes used to pass a string value
Result:
[{ "name": "Chris Paul" }]
Russell Westbrook’s name has been changed to 'Chris Paul'.
PUT /api/player?where=team_id.name%3D'LA Clippers'&returning_columns=id, name, team_id.name, team_id.coach_id.name
{ "name": "team_id.coach_id.name" }
^---------------------^ No inner quotes in the string means that the value is an expression.
Result:
[
{
"id": 3,
"name": "Doc Rivers",
"team_id.name": "LA Clippers",
"team_id.coach_id.name": "Doc Rivers"
},
{
"id": 4,
"name": "Doc Rivers",
"team_id.name": "LA Clippers",
"team_id.coach_id.name": "Doc Rivers"
}
]
Garrett Temple and Wilson Chandler have been renamed to Doc Rivers.
Obviously this request didn’t produce the most useful results, but it shows the possibilities of bulk updates.
Delete records in {table}. Returns the number of rows affected. Optionally, table columns of deleted rows can be returned using the returning_columns query parameter (see below). DOT (.) syntax can be used in where and returning_columns (see examples).
Required in order to process the DELETE operation. The endpoint returns a 400 error response otherwise.
The WHERE clause of an DELETE statement. Remember to URI-encode the final result. Example: (field_1 >= field_2 AND id IN (1,2,3)) OR field_2 > field_1.
Comma-separated list of columns to return from the DELETE operation. Example: id,name, field_2.
Assume the following database schema for these examples:
CREATE TABLE IF NOT EXISTS public.delete_b (
id BIGINT CONSTRAINT delete_b_id_key PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS public.delete_a (
id BIGINT CONSTRAINT delete_a_id_key PRIMARY KEY,
b_id BIGINT
);
CREATE TABLE IF NOT EXISTS public.delete_simple (
id BIGINT CONSTRAINT delete_simple_id_key PRIMARY KEY
);
ALTER TABLE public.delete_a ADD CONSTRAINT delete_a_b_reference FOREIGN KEY (b_id) REFERENCES public.delete_b(id);
INSERT INTO public.delete_b (id) VALUES
(1),
(2),
(3),
(4);
INSERT INTO public.delete_a
(id, b_id)
VALUES
(1, 1),
(2, 2),
(3, 2),
(4, 3),
(5, 4);
INSERT INTO public.delete_simple (id) VALUES (1), (2), (3);
DELETE /api/delete_simple?confirm_delete
Result:
{ "num_rows": 3 }
All rows are deleted from the table `delete_simple`.
DELETE /api/delete_simple?confirm_delete&whereid%3D1
Result:
{ "num_rows": 1 }
The first row is deleted from the table `delete_simple`.
Note that this does not remove rows from the table referenced by the foreign key.
DELETE /api/delete_a?confirm_delete&whereid%3D3&returning_columns=b_id.id
Result:
{ "b_id.id": 2 }
The second row (with id = 3) is deleted from the table `delete_a`.
Note that this does not remove rows from the table referenced by the foreign key.
DELETE /api/delete_a?confirm_delete&whereid%3D3&returning_columns=b_id.id b_id
Result:
{ "b_id": 2 }
The second row (with id = 3) is deleted from the table `delete_a`.
Runs any passed-in SQL query (which is dangerous). This is here in case the above endpoints aren’t sufficient for complex operations you might need. Be careful if/how you expose this endpoint (honestly it should never be publicly exposed and if used, only used internally with hardcoded or extremely sanitized values). By default, this endpoint is disabled and must be enabled by setting .enable_custom_sql_url() in Config.
A plain-text string with the SQL query to run. A Content-Type header value of text/plain must be sent to this endpoint or else a 400 error will be returned.
Example: SELECT * FROM a_table;.
Pass in this parameter in order to return row data. Note that this is also needed for SELECT statements to return rows. Otherwise, the endpoint only returns the number of rows affected by the query. This is due to a limitation of the parser library we are using.
Assume the following database schema for these examples:
CREATE TABLE IF NOT EXISTS public.company (
id BIGINT CONSTRAINT company_id_key PRIMARY KEY,
name TEXT
);
INSERT INTO public.company (id, name) VALUES (1, 'Stark Corporation');
POST /api/sql
SELECT * FROM company;
Result:
{ "num_rows": 1 }
POST /api/sql?is_return_rows
SELECT * FROM company;
Result:
{ "id": 1, "name": "Stark Corporation" }
See source.
- HStore (
rust-sqlparserdoesn't support it). Use JSON/JSONB instead. - Bit and Varbit types (the
B'0101'syntax in postgres is not supported byrust-sqlparser) - Exclusion and Trigger constraints
BETWEEN(see Postgres wiki article)
You will need docker-compose to run tests. In one terminal, run docker-compose up to start the postgres docker image.
In another terminal, run cargo test.
- benchmark
- flatbuffers
- brotli|gzip-compress table stats cache (so it doesn't hog memory for large db's)
- GraphQL API
- Optimization: Convert Strings to &str / statics.
- CSV, XML for REST API (nix for now?)
- Eventually support dot syntax in INSERT: See this forum post
- Maybe use Diesel's parser instead of SQLParser in order to support HStore, bit/varbit, and RETURNING (would eliminate need for
is_returning_rows)?