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:

  • dbConnection
    Object Specifies database connection details
  • onReady(db, _db)
    Function Callback fired after prostgles has been initialised.
    • db
      Object is the current database handler containing all user-generated views and tables. See queries
    • _db
      Object Database object from pg-promise. Used in running raw SQL
  • publish
    Publish | (socket, db) => Publish | (socket, db) => Promise<Publish> (Optional) Awaitable configuration object used to specify the tables/views and rules by which clients can interact with them. By default nothing is accessible to the clients. See publish
  • publishMethods
    Methods | (socket, db) => Methods | (socket, db) => Promise<Methods> (Optional) Awaitable non-nested object that contains functions or promises that clients can interact with. Structure: { [method_name]: (...args) => any }
  • publishRawSQL
    boolean | (socket, db) => boolean | (socket, db) => Promise<boolean> (Optional) Disabled by default. When true it allows the connected client to run raw SQL queries. Use at your own risk.
  • io
    Object (Optional) socket.io server instance used by clients to connect to the server.
  • onSocketConnect
    (socket, db) => Promise<any> (Optional) Awaitable function fired every time a socket connects. Handshake/Cookie based auth can be processed here. Throw an error to drop the socket.
  • onSocketDisconnect
    (socket, db) => Promise<any> (Optional) Awaitable function fired every time a socket disconnects
  • sqlFilePath
    string (Optional) Full path to an .sql file. This file will be run on the given connection before prostgles is fully initialised.
  • tsGeneratedTypesDir
    string (Optional) Path to the folder where Typescript definitions for the database should be saved. DBoGenerated.ts will be generated and saved in the given location. It contains DBObj which defines the structure of the db database handler. After casting db to this type all queries will then expect and return the correct data structures for each table or view.
  • transactions
    string | boolean (Optional) Enables transactions. Disabled by default. If (true | string) the db object will allow running transactions. see transactions
  • joins
    object (Optional) Enables joins. Disabled by default.See joins

Client

Installation

Dependencies:

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

Condition used to filter the records. By default all records will be returned. Examples:
                

                  { 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:

  • select
    "*" | object (Optional) Specifies which fields to return. Defaults to "*" (all fields). Fields can be included or excluded:
                         
                          db.customers.find(
                            { },
                            // Return only "id" and "email" fields   
                            { select: { id: true, email: true } }
                          );
    
      
                          db.customers.find(
                            { },
                            // Return all fields except "id" and "email"  
                            { select: { id: false, email: false } }
                          );
                        
                      
  • orderBy
    object | object[] (Optional) Specifies how result should be ordered:
                         
                          db.customers.find(
                            { },
                            // Order by "id" in ascending order
                            { orderBy: { id: true } }
                          );
    
                          db.customers.find(
                            { },
                            // Order by "id" in descending order
                            { orderBy: { id: false } }
                          );
    
                          db.customers.find(
                            { },
                            // Order by "name" in descending order and then by "id" in ascending order
                            { orderBy: [{ name: false }, { id: true }] }
                          );
                        
                      
  • limit
    number(Optional) Number of records to return. Defaults to 100
  • offset
    number(Optional) Number of records to skip/offset. Defaults to 0

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
  • onNewData(data: object[])
    Function Callback that fires once at the start and every time there has been an INSERT/UPDATE/DELETE to records that match the given filter

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
  • onNewData(data: object)
    Function Callback that fires once at the start and every time there has been an INSERT/UPDATE/DELETE to the record that matches the given filter

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

  • filter
    object Filter
  • onChange(data, delta)
    Function Callback triggered every time the data item changes. Arguments:
    • data
      object The updated data item
    • delta
      object The changes applied to the data item

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

  • data
    object | object[] (Optional) Data to be inserted. Can be an object or an array of objects
  • returning
    "*" | object (Optional) Specifies which fields to return. Defaults to nothing. See select
  • onConflictDoNothing
    boolean (Optional) Defaults to false. When set to true inserts can fail without triggering an error. See postgres docs

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

  • returning
    "*" | object (Optional) Specifies which fields to return. Defaults to nothing. See select
  • onConflictDoNothing
    boolean (Optional) Defaults to false. When set to true updates can fail without triggering an error. See postgres docs

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

  • returning
    "*" | object (Optional) Specifies which fields to return. Defaults to nothing. See select
  • onConflictDoNothing
    boolean (Optional) Defaults to false. When set to true inserts/updates can fail without triggering an error. See postgres docs

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

  • returning
    "*" | object (Optional) Specifies which fields to return. Defaults to nothing. See select

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:

db[transactions]

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

  • "*"
    string Allows the client to perform all CRUD operations on all non-system tables within the current database
  • false
    boolean Disallows the client to interact with the current database
  • { [table_name]: TableRules }
    Object Specifies custom rules for specific tables

TableRules

Defines custom rules for the given table

Values:

  • "*"
    string Allows the client to perform all CRUD operations on the given table
  • false
    boolean Disallows the client to perform any operations on the given table
  • { [operation_name]: Rules } >
    OperationRules Specifies custom rules for each operation for the given table

OperationRules

Define rules for each operation for the given table

Options:

InsertRules

Rules for inserting data into the given table

Options:

  • fields
    Select Fields allowed to be inserted
  • returningFields
    Select Fields that can be returned after an insert
  • forcedData
    Object Data to be added/overwritten for each insert

SelectRules

Defines rules for viewing data from the given table. This will enable the following queries: find findOne count subscribe

Options:

  • fields
    Select Fields that can be viewed
  • filterFields
    Select Fields that can be used in filters
  • maxLimit
    number Specifies the fields that can be returned after an insert
  • forcedFilter
    Object Filter to be added on every client call. This is useful to ensure a user can only view their own records

UpdateRules

Rules for updating data in the given table. This will enable the following queries: update upsert(if insert is also allowed)

Options:

  • fields
    Select Fields that can be updated
  • filterFields
    Select Fields that can be used in filters
  • maxLimit
    number Specifies the fields that can be returned after an insert
  • forcedFilter
    Object Filter to be added on every client call. This is useful to ensure a user can only update their own records
  • forcedData
    Object Data to be added/overwritten for each update. This is useful for adding the user ID
  • returningFields
    Select Fields that can be returned after an update

DeleteRules

Rules for deleting data in the given table. This will enable the following queries: delete

Options:

  • filterFields
    Select Fields that can be used in filters
  • forcedFilter
    Object Filter to be added on every client call. This is useful to ensure a user can only delete their own records
  • returningFields
    Select Fields that can be returned after a delete

SyncRules

Rules for synchronization and replication for the given table. Cannot be used for views. Will enable the following queries: sync

Options:

  • id_fields
    string[] Field/Fields used to identify individual rows from the table
  • synced_field
    string Numerical field used as a last updated value for each row
            
              // 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: "*",

                }
              },
            });