Server
Installation
Dependencies:
- Node version 8.0.0 or higher
- PostgreSQL version 9.5 or higher
- express (Optional)
- socket.io (Optional) version 2.2.0 or higher. Required to allow client-server communication
Minimal installation
npm i prostgles-server
Full installation
npm i prostgles-server socket.io express
To create a new postgres account for your server:
sudo -su postgres createuser -P --superuser api
Usage
Basic local usage. db object exposes all user-generated tables and views found in the database
const prostgles = require('prostgles-server');
prostgles({
dbConnection: {
host: "localhost",
port: "5432",
database: "postgres",
user: "api",
password: "yourpassword"
},
onReady: async (db) => {
// Assuming the "customer" table exists in the database
const customers = await db.customers.find();
}
});
Client-server usage. All tables and views are available to the client
const path = require('path');
const socketIO = require('socket.io');
const express = require('express');
const app = express();
const io = socketIO.listen(app.listen(3000));
const prostgles = require('prostgles-server');
prostgles({
dbConnection: {
host: "localhost",
port: "5432",
database: "postgres",
user: "api",
password: "yourpassword"
},
io,
publish: "*",
onReady: async (db) => {
const customers = await db.customers.find();
}
});
prostgles(options);
options:
Client
Installation
Dependencies:
- socket.io-client version 2.2.0 or higher
As module
npm i prostgles-client socket.io-client
Or
From CDN
<head>
<script src="https://unpkg.com/socket.io-client@latest/dist/socket.io.slim.js" type="text/javascript"></script>
<script src="https://unpkg.com/prostgles-client@latest/dist/index.js" type="text/javascript"></script>
</head>
Usage
The client can only access the tables and views specified in the server publish config object. For a quick start on non-sensitive data you can set the publish to "*" to expose all user-generated tables. Otherwise you can set custom rules and only the allowed tables and operations will exist within the db handler below
prostgles({
socket: io(),
onReady: async (db) => {
const posts = await db.posts.find({});
},
onConnect: (err, res) => { },
onDisconnect: (err, res) => { }
});
Queries (client & server)
find
Retrieve records from a table or view
Returns a promise that resolves to an array of objects
db.table_name.find(filter, options) => Promise <Object[]>
db.customers.find(
{ address: { $ilike: "Fake%" } },
{ orderBy: { id: -1 } }
);
/*
[
{ id: 97, name: "John", address: "Fake Drive 31" },
{ id: 65, name: "Jane", address: "Fake Drive 319" },
...
]
*/
filter
{ name: "John", age: 24 }
// WHERE name = 'John' AND age = 24
{ $or: [{ name: "John" }, { age: 24 }] }
// WHERE name = 'John' OR age = 24
{ name: "John" }
// WHERE name = 'John'
{ address: null }
// WHERE address IS NULL
{ address: { $not: null } }
// WHERE address IS NOT NULL
{ name: { $not: "John" } }
// WHERE name != 'John'
{ address: { $ilike: "Fake%" } }
// WHERE address::text ILIKE 'Fake%'
{ address: { $notIlike: "Fake%" } }
// WHERE address::text NOT ILIKE 'Fake%'
{ address: { $like: "Fake%" } }
// WHERE address::text LIKE 'Fake%'
{ address: { $notLike: "Fake%" } }
// WHERE address::text NOT LIKE 'Fake%'
{ name: { $in: ["John", "Jon"] } }
// WHERE name IN ('John', 'Jon')
{ name: { $nin: ["John", "Jon"] } }
// WHERE name NOT IN ('John', 'Jon')
// Full text search
{ address: { $tsQuery: ["East"] } }
// WHERE address @@ to_tsquery('East')
{ address: { "=": "Fake%" } }
// WHERE address = 'Fake%'
{ size: { ">": 456 } }
{ size: { $gt: 456 } }
// WHERE size > 456
{ size: { ">=": 456 } }
{ size: { $gte: 456 } }
// WHERE size >= 456
{ size: { "<": 456 } }
{ size: { $lt: 456 } }
// WHERE size < 456
{ size: { "<=": 456 } }
{ size: { $lte: 456 } }
// WHERE size <= 456
// Array operations
// https://www.postgresql.org/docs/current/functions-array.html
{ tags: { "@>": ["red", "blue"] } }
// WHERE tags @> ARRAY['red', 'blue'] -- "tags" contains 'red' AND 'blue'
{ tags: { "<@": ["red", "blue"] } }
// WHERE tags <@ ARRAY['red', 'blue'] -- "tags" contains only 'red' AND 'blue'
{ tags: { "&&": ["red", "blue"] } }
// WHERE tags && ARRAY['red', 'blue'] -- "tags" contains 'red' OR 'blue'
Options:
findOne
Retrieve a record from a table or view
Returns a promise that resolves to an object
db.table_name.findOne(filter, options) => Promise <Object>
db.customers.findOne(
{ address: { $ilike: "Fake%" } },
{ orderBy: { id: -1 } }
);
/*
{ id: 97, name: "John", address: "Fake Drive 31" }
*/
See filter, options
count
Retrieve the total number of records from a table or view that match the given condition
Returns a promise that resolves to a number
db.table_name.count(filter) => Promise <number>
db.customers.count(
{ id: { $lt: 12 } }
);
/*
11
*/
See filter
subscribe
Retrieve records from a table or view every time they change
Returns a subscription handler. Can be used on tables only.
db.table_name.subscribe(filter, options, onNewData) => Promise<{ unsubscribe: Function, update: Function }>
const sub = db.orders.subscribe(
{ price: { $gt: 100 } },
{ },
orders => {
console.log(orders)
}
);
/*
[
{ id: 97, product: "TV", price: 560 }
]
*/
See filter, options
subscribeOne
Retrieve first matched record from a table every time it changes
Returns a subscription handler. Can be used on tables only.
db.table_name.subscribeOne(filter, options, onNewData) => Promise<{ unsubscribe: Function, update: Function }>
const sub = db.orders.subscribeOne(
{ price: { $gt: 100 } },
{ },
order => {
console.log(order)
}
);
/*
{ id: 97, product: "TV", price: 560 }
*/
See filter, options
sync (Experimental)
Maintain a set of records synchronized with the server on the client-side
Returns a promise
db.table_name.sync(filter, onChange, handlesOnData) => <Object>
const shapeSync = db.shapes.sync(
{ },
shape => {
console.log(shape);
console.log(shapeSync);
},
true
);
/*
[
{ id: 197, x: 43, y: 876, type: "circle", $update: Function, $delete: Function }
...
]
{ unsync: Function, upsert: Function }
*/
Arguments
syncOne (Experimental)
Maintain a record synchronized with the server on the client-side
Returns a promise
db.table_name.syncOne(filter, onChange, handlesOnData) => <Object>
const shapeSync = db.shapes.syncOne(
{ id: 197 },
shape => {
console.log(shape)
},
true
);
/*
// shape
{ id: 197, x: 43, y: 876, type: "circle", $update, $delete }
// shapeSync
{ unsync, update, delete }
*/
Arguments
insert
Insert a record/records
Returns a promise
db.table_name.insert(data, options) => Promise<object[]>
const res = db.orders.insert(
{ price: 87, product: "abc" },
{ returning: "*" }
);
/*
{ id: 97, product: "abc", price: 87 }
*/
Options
update
Update records
Returns a promise
db.table_name.update(filter, newData, options) Promise <Object[]>
const res = db.orders.update(
{ id: 97 },
{ price: 187 },
{ returning: "*" }
);
/*
{ id: 97, product: "abc", price: 187 }
*/
Options
upsert
Update or insert a record. If no records match the given filter the record is inserted otherwise it is updated
Returns a promise
db.table_name.upsert(filter, newData, options) Promise <Object>
const res = db.orders.upsert(
{ id: 197 },
{ id: 197, price: 187, product: "xyz" },
{ returning: "*" }
);
/*
{ id: 197, product: "xyz", price: 187 }
*/
Options
delete
Delete record/records
Returns a promise
db.table_name.delete(filter, options) Promise <Object[]>
const res = db.orders.delete(
{ id: 197 },
{ returning: "*" }
);
/*
{ id: 197, product: "xyz", price: 187 }
*/
Options
Joins (Experimental)
When a valid joins configuration object is present find and findOne enable querying related tables.
prostgles(
...
joins: [
{
tables: ["users", "posts"],
on: { id: "user_id" },
type: "one-many"
},
{
tables: ["posts", "comments"],
on: { id: "post_id" },
type: "one-many"
}
],
onReady: async (dbo: DBObj, db) => {
/* Get user fields and comments */
await dbo.user.findOne({ id: 1 }, {
select: {
id: 1,
first_name: 1,
last_name: 1,
comments: {
id: 1,
content: 1
}
}
});
/*
{
id: 1,
first_name: "John",
first_name: "Doe",
comments: [
{ id: 22, content: "of course" },
{ id: 123, content: "haha" },
....
]
}
*/
},
...
)
Aggregations (Experimental)
The select parameter from find and findOne queries allows performing aggregate functions on table fields
prostgles(
...
onReady: async (dbo: DBObj, db) => {
/*
Get list of user counts grouped by first_name and last_name.
Equivalent to:
SELECT first_name, last_name, COUNT(id) as count
FROM users
GROUP BY first_name, last_name
*/
await dbo.user.find({}, {
select: {
first_name: 1,
last_name: 1,
count: { $count: "id" }
}
});
/*
[
{ first_name: "John", first_name: "Doe", count: 71312 },
{ first_name: "Jane", first_name: "Doe", count: 6312 },
...
]
*/
},
...
)
Transactions (server only)
The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions. If some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.
Prostgles exposes this functionality when a truthy value for transactions config option is specified. If true then the db handler will expose the "tx" method to run transactions. A string can be specified instead and it will be used as the transaction method name:
In the example below any client can view data (find, findOne, subscribe, count) from the "products" table. Only "name" and "price" fields will be returned and only the subset of records where "available" is equal to true can be viewed.
prostgles(
...
transactions: true,
onReady: async (dbo: DBObj, db) => {
await dbo.items.delete({});
dbo.tx(async t => {
await t.items.insert({ name: "tr" });
console.log(await t.items.find());
// [{ name: 'tr', id: 1 }]
throw "err";
// Any errors will revert all data-changing commands called from "t"
});
console.log(await dbo.items.find());
// [] -> No items present due to transaction block error
},
...
)
Publish
Prostgles server configuration object defining the rules under which the client can interact with the tables and views. Client socket and the db object are passed on every call to allow custom logic tailored for each client/request type
In the example below any client can view data (find, findOne, subscribe, count) from the "products" table. Only "name" and "price" fields will be returned and only the subset of records where "available" is equal to true can be viewed.
...
publish: async (socket, db) => ({
// [table_name]
products: {
// [operation_name]
select: {
// [Rules]
fields: { name: 1, price: 1 },
forcedFilter: { available: true }
}
}
}),
...
Values
TableRules
Defines custom rules for the given table
Values:
OperationRules
Define rules for each operation for the given table
Options:
InsertRules
Rules for inserting data into the given table
Options:
SelectRules
Defines rules for viewing data from the given table. This will enable the following queries: find findOne count subscribe
Options:
UpdateRules
Rules for updating data in the given table. This will enable the following queries: update upsert(if insert is also allowed)
Options:
DeleteRules
Rules for deleting data in the given table. This will enable the following queries: delete
Options:
SyncRules
Rules for synchronization and replication for the given table. Cannot be used for views. Will enable the following queries: sync
Options:
// Example
// Logged-in clients can insert into the "orders" table. "user_id" field will always contain the client id
publish: async (socket, db) => ({
// Any client can perform all CRUD operations on table "products"
products: "*",
// Only logged-in clients can interact with "orders"
orders: !socket.user? false : {
// "user_id" field will always contain the client id
insert: {
forcedData: { user_id: socket.user.id }
},
select: {
fields: { some_col: false },
forcedFilter: { user_id: socket.user.id }
}
}
})
prostgles({
...
publish: async (socket, db) => {
return {
products: "*",
}
},
});