Overview

Prostgles allows connecting to a PostgreSQL database to get a realtime view of the data and schema changes. By configuring “tsGeneratedTypesDir” the database schema types are generated automatically allowing full end-to-end type safety

Installation

To install the package, run:

npm install prostgles-server

Configuration

To get started, you need to provide a configuration object to the server.

Minimal configuration:

import prostgles from "prostgles-server";
import { DBGeneratedSchema } from "./DBGeneratedSchema";
prostgles<DBGeneratedSchema>({
  dbConnection: {
    host: "localhost",
    port: 5432,
    database: "postgres"
    user: process.env.PRGL_USER,
    password: process.env.PRGL_PWD
  },
  tsGeneratedTypesDir: __dirname,
  onReady: async ({ dbo }) => {
    try {
      await dbo.items.insert({ name: "a" });
      console.log(await dbo.items.find());
    } catch(err) {
      console.error(err)
    }
  },
});

To allow clients to connect an express server with socket.io needs to be configured:

import prostgles from "prostgles-server";
import { DBGeneratedSchema } from "./DBGeneratedSchema";
import express from "express";
import path from "path";
import http from "http";
import { Server } from "socket.io";
 
const app = express();
const httpServer = http.createServer(app);
httpServer.listen(30009);
const io = new Server(httpServer, {
  path: "/prgl-api",
});
 
prostgles<DBGeneratedSchema>({
  dbConnection: {
    host: "localhost",
    port: 5432,
    database: "postgres"
    user: process.env.PRGL_USER,
    password: process.env.PRGL_PWD
  },
  io,
  publish: () => {
    return {
      items: "*",
    }
  },
  tsGeneratedTypesDir: __dirname,
  onReady: async ({ dbo }) => {
    try {
      await dbo.items.insert({ name: "a" });
      console.log(await dbo.items.find());
    } catch(err) {
      console.error(err)
    }
  },
});

Configuration options

• dbConnection required DbConnection

  Database connection details and options

• onReady required OnReadyCallback

  Called when the prostgles server is ready to accept connections. It waits for auth, tableConfig and other async configurations to complete before executing

• tsGeneratedTypesDir optional string

  Path to the directory where the generated types (DBGeneratedSchema.d.ts) will be saved. This file exports a DBGeneratedSchema type which contains types for the database tables and can be used as a generic type input for the prostgles instances to ensure type safety

• io optional Server<DefaultEventsMap, DefaultEventsMap, DefaultEventsMap, any> | undefined

  Socket.IO server instance object required to allow clients to connect through websockets

• restApi optional RestApiConfig

  Rest API configuration. The REST API allows interacting with the database similarly to the websocket connection. with the exception of subscriptions and realtime features.

  POST Routes:

  • /api/db/:tableName/:command
  • /api/db/sql
  • /api/methods/:method
  • /api/schema

  Example request:

  const res = await fetch(`http://127.0.0.1:3001/api/db/items/findOne`, {
    method: "POST",
    headers: new Headers({
      Authorization: `Bearer ${Buffer.from(token, "utf-8").toString("base64")}`,
      Accept: "application/json",
      "Content-Type": "application/json",
    }),
    body: JSON.stringify([{ id: 1 }]),
  });

  • expressApp required Express

    Express server instance

  • routePrefix required string

    Defaults to “/api”

• disableRealtime optional boolean | undefined

  If true then schema watch, subscriptions and syncs will be disabled. No prostgles schema will be created which is needed for the realtime features. This is useful when you want to connect to a database and prevent any changes to the schema

• publish optional Publish

  Data access rules applied to clients. By default, nothing is allowed.

• publishRawSQL optional (params: PublishParams<S, SUser>) => boolean | "" | Promise<boolean | "">

  If defined and resolves to true then the connected client can run SQL queries

• publishMethods optional PublishMethods

  Server-side functions that can be invoked by the client

• testRulesOnConnect optional boolean | undefined

  If true then will test all table methods on each socket connect. Not recommended for production

• joins optional Joins

  Allows defining table relationships that can then be used in filters and data inserts:

  • infered - uses the foreign keys to infer the joins
  • Join[] - specifies the joins manually

• schemaFilter optional Record<string, 1> | Record<string, 0> | undefined

  If defined then the specified schemas are included/excluded from the prostgles schema. By default only current_schema() is included.

• sqlFilePath optional string

  Path to a SQL file that will be executed on startup (but before onReady)

• transactions optional boolean | undefined

  If true then will allow transactions on the server through the db.tx method:

  db.tx(async (t) => {
    await t.items.insert({ name: "a" });
    throw new Error("rollback");
  });

• onSocketConnect optional (args: AuthRequestParams<S, SUser> & { socket: PRGLIOSocket; }) => void | Promise<void>

  Called when a socket connects Use for connection verification. Will disconnect socket on any errors

• onSocketDisconnect optional (args: AuthRequestParams<S, SUser> & { socket: PRGLIOSocket; }) => void | Promise<void>

  Called when a socket disconnects

• auth optional Auth

  Auth configuration. Supports email and OAuth strategies

  • sidKeyName optional string

    Name of the cookie or socket hadnshake query param that represents the session id. Defaults to “session_id”

  • responseThrottle optional number

    Response time rounding in milliseconds to prevent timing attacks on login. Login response time should always be a multiple of this value. Defaults to 500 milliseconds

  • expressConfig optional ExpressConfig

    Will setup auth routes /login /logout /magic-link/:id

    • app required Express

      Express app instance. If provided Prostgles will attempt to set sidKeyName to user cookie

    • cookieOptions optional AnyObject | undefined

      Options used in setting the cookie after a successful login

    • disableSocketAuthGuard optional boolean | undefined

      False by default. If false and userRoutes are provided then the socket will request window.location.reload if the current url is on a user route.

    • publicRoutes optional string[] | undefined

      If provided, any client requests to NOT these routes (or their subroutes) will be redirected to loginRoute (if logged in) and then redirected back to the initial route after logging in If logged in the user is allowed to access these routes

    • use optional ExpressMiddleware<S, SUser> | undefined

      Will attach a app.use listener and will expose getUser Used in UI for blocking access

    • onGetRequestOK optional ((req: ExpressReq, res: ExpressRes, params: AuthRequestParams<S, SUser>) => any) | undefined

      Will be called after a GET request is authorised This means that

    • magicLinks optional { check: (magicId: string, dbo: DBOFullyTyped<S>, db: DB, client: LoginClientInfo) => Awaitable<BasicSession | undefined>; } | undefined

      If defined, will check the magic link id and log in the user and redirect to the returnUrl if set

    • registrations optional AuthRegistrationConfig<S> | undefined

  • getUser required (sid: string | undefined, dbo: DBOFullyTyped<S>, db: DB, client: AuthClientRequest & LoginClientInfo) => Awaitable<AuthResult<…>>

    undefined sid is allowed to enable public users

  • login optional (params: LoginParams, dbo: DBOFullyTyped<S>, db: DB, client: LoginClientInfo) => Awaitable<BasicSession>

  • logout optional (sid: string | undefined, dbo: DBOFullyTyped<S>, db: DB) => any

  • cacheSession optional { getSession: (sid: string | undefined, dbo: DBOFullyTyped<S>, db: DB) => Awaitable<BasicSession>; }

    If provided then session info will be saved on socket.__prglCache and reused from there

    • getSession required (sid: string | undefined, dbo: DBOFullyTyped<S>, db: DB) => Awaitable<BasicSession>

• DEBUG_MODE optional boolean | undefined

  Used internally for debugging

• onQuery optional (error: any, ctx: IEventContext<IClient>) => void

  Callback called when a query is executed. Useful for logging or debugging

• watchSchemaType optional “DDL_trigger” | “prostgles_queries” | undefined

  What schema change watcher to use when watchSchema is enabled:

  • "DDL_trigger" - (default) - Use a database event trigger for schema changes. Requires superuser.
  • "prostgles_queries" - Check db.sql() initiated queries for schema changes. Any other queries are ignored.

• watchSchema optional boolean | EventTriggerTagFilter | “hotReloadMode” | OnSchemaChangeCallback | undefined

  Reloads schema on schema change. Either calls the provided callback or triggers “onReady” on both the server and any connected clients when schema changes and also updates DBGeneratedSchema.d.ts if enabled. Options:

  • true - “onReady” call and “DBGeneratedSchema” rewrite
  • EventTriggerTagFilter - same as true but only on specified events
  • "hotReloadMode" - only rewrites DBGeneratedSchema.d.ts. Used in development when server restarts on file change.
  • OnSchemaChangeCallback - custom callback to be fired. Nothing else triggered Useful for development

• onNotice optional (notice: AnyObject, message?: string | undefined) => void

  Called when a notice is received from the database

• fileTable optional FileTableConfig

  Enables file storage and serving. Currently supports saving files locally or to AWS S3. By designating a file table files can be inserted through the table handler:

  const file = await db.files.insert(
    { file: new Buffer("file content"), name: "file.txt" },
    { returnType: "*" }
  );
   
  const fileUrl = file.url;

  • tableName optional string

    Name of the table that will contain the file metadata. Defaults to “files”

  • fileServeRoute optional string

    GET path used in serving media. defaults to /${tableName}

  • delayedDelete optional { deleteAfterNDays: number; checkIntervalHours?: number | undefined; }

    If defined the the files will not be deleted immediately Instead, the “deleted” field will be updated to the current timestamp and after the day interval provided in “deleteAfterNDays” the files will be deleted “checkIntervalMinutes” is the frequency in hours at which the files ready for deletion are deleted

    • deleteAfterNDays required number

      Minimum amount of time measured in days for which the files will not be deleted after requesting delete

    • checkIntervalHours optional number

      How freuquently the files will be checked for deletion delay

  • expressApp required ExpressApp

    Express server instance

  • referencedTables optional { [tableName: string]: { type: “column”; referenceColumns: Record<string, FileColumnConfig>; }; }

    Used to specify which tables will have a file column and allowed file types.

    Specifying referencedTables will:

    1. create a column in that table called media
    2. create a lookup table lookupmedia{referencedTable} that joins referencedTable to the media table

  • imageOptions optional ImageOptions

    • keepMetadata optional boolean | undefined
    • compression optional ImageCompressionOptions | undefined

  • cloudClient optional CloudClient

    Callbacks for file upload and download. Used for custom file handling.

    • upload required (file: FileUploadArgs) => Promise<void>
    • downloadAsStream required (name: string) => Promise<Readable>
    • delete required (fileName: string) => Promise<void>
    • getSignedUrlForDownload required (fileName: string, expiresInSeconds: number) => Promise<string>

  • localConfig optional LocalConfig

    Local file storage configuration.

    • localFolderPath required string

      example: path.join(__dirname+‘/media’) note that this location will be relative to the compiled file location

    • minFreeBytes optional number

      Minimum amount of free bytes available to allow saving files Defaults to 100MB

• tableConfig optional TableConfig

  Define tables through a JSON-schema like object. Allows adding runtime JSONB validation and type safety. Should be used with caution because it tends to revert any changes made to the database schema through SQL queries

• tableConfigMigrations optional TableConfigMigrations

  Migration logic used when the new tableConfig version is higher than the one in the database. By default server will fail to start if the tableConfig schema changes cannot be applied without errors

  • silentFail optional boolean | undefined

    If false then prostgles won’t start on any tableConfig error true by default

  • version required number

    Version number that must be increased on each schema change.

  • versionTableName optional string

    Table that will contain the schema version number and the tableConfig Defaults to schema_version

  • onMigrate required OnMigrate

    Script executed before tableConfig is loaded and IF an older schema_version is present. Any data conflicting with the new schema changes should be resolved here.

• onLog optional (evt: EventInfo) => Promise<void>

  Usefull for logging or debugging

Isomorphic Methods

The following table/view methods are available on the client and server db object

getInfo(lang?: string): Promise<TableInfo>

Retrieves the table/view info

Parameters

• lang optional string

  Language code for i18n data. “en” by default

Return type

TableInfo

• oid required number

  OID from the postgres database Useful in handling renamed tables

• comment optional string

  Comment from the postgres database

• isFileTable optional FileTableConfig

  Defined if this is the fileTable

  • allowedNestedInserts optional { table: string; column: string; }

• hasFiles optional boolean

  True if fileTable is enabled and this table references the fileTable Used in UI

• isView optional boolean

  True if this is a view. Table methods (insert, update, delete) are undefined for views

• fileTableName optional string

  Name of the fileTable (if enabled) Used in UI

• dynamicRules optional { update?: boolean | undefined; }

  Used for getColumns in cases where the columns are dynamic based on the request. See dynamicFields from Update rules

  • update optional boolean

• info optional { label?: string | undefined; }

  Additional table info provided through TableConfig

  • label optional string

• uniqueColumnGroups required string[][] | undefined

  List of unique column indexes/constraints. Column groups where at least a column is not allowed to be viewed (selected) are omitted.

getColumns(lang?: string, params?: GetColumnsParams): Promise<ValidatedColumnInfo[]>

Retrieves columns metadata of the table/view

Parameters

• lang optional string

  Language code for i18n data. “en” by default

• params optional GetColumnsParams

  Dynamic/filter based rules (dynamicFields) allow specifying which columns can be updated based on the target record. Useful when the same user can update different fields based on the record state.

  • rule required “update”

    Only “update” is supported at the moment

  • filter required FullFilter

    Filter specifying which records are to be updated

Return type

ValidatedColumnInfo

• name required string

• label required string

  Column display name. Will be first non empty value from i18n data, comment, name

• comment required string | undefined

  Column description (if provided)

• ordinal_position required number

  Ordinal position of the column within the table (count starts at 1)

• is_nullable required boolean

  True if column is nullable

• is_updatable required boolean

• is_generated required boolean

  If the column is a generated column (converted to boolean from ALWAYS and NEVER)

• data_type required string

  Simplified data type

• udt_name required PG_COLUMN_UDT_DATA_TYPE

  Postgres data type name. Array types start with an underscore

• element_type required string | undefined

  Element data type

• element_udt_name required string | undefined

  Element data type name

• is_pkey required boolean

  PRIMARY KEY constraint on column. A table can have a multi column primary key

• references optional ReferenceTable

• has_default required boolean

  true if column has a default value Used for excluding pkey from insert

• column_default optional any

  Column default value

• min optional string | number | undefined

  Extracted from tableConfig Used in SmartForm

• max optional string | number | undefined

• hint optional string

• jsonbSchema optional JSONBSchema

  JSONB schema (a simplified version of json schema) for the column (if defined in the tableConfig) A check constraint will use this schema for runtime data validation and apropriate TS types will be generated

• file optional FileColumnConfig

  If degined then this column is referencing the file table Extracted from FileTable config Used in SmartForm

• tsDataType required “string” | “number” | “boolean” | “any” | “number[]” | “boolean[]” | “string[]” | “any[]”

  TypeScript data type

• select required boolean

  Can be viewed/selected Based on access rules and postgres policies

• orderBy required boolean

  Can be ordered by Based on access rules

• filter required boolean

  Can be filtered by Based on access rules

• insert required boolean

  Can be inserted Based on access rules and postgres policies

• update required boolean

  Can be updated Based on access rules and postgres policies

• delete required boolean

  Can be used in the delete filter Based on access rules

find(filter?: FullFilter, selectParams?: SelectParams): Promise<SelectReturnType>

Retrieves a list of matching records from the view/table

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

SelectReturnType

findOne(filter?: FullFilter, selectParams?: SelectParams): Promise<SelectReturnType<S, P, T, false> | undefined>

Retrieves a record from the view/table

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

SelectReturnType<S, P, T, false> | undefined

subscribe(filter: FullFilter, params: SelectParams, onData: SubscribeCallback, onError?: SubscribeOnError): Promise<SubscriptionHandler>

Retrieves a list of matching records from the view/table and subscribes to changes

Parameters

• filter required FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• params required SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

• onData required SubscribeCallback

  Callback fired once after subscribing and then every time the data matching the filter changes

• onError optional SubscribeOnError

  Error handler that may fire due to schema changes or other post subscribe issues Column or filter issues are thrown during the subscribe call

Return type

SubscriptionHandler

• unsubscribe required () => Promise<any>
• filter required {} | FullFilter<void, void>

subscribeOne(filter: FullFilter, params: SelectParams, onData: SubscribeOneCallback, onError?: SubscribeOnError): Promise<SubscriptionHandler>

Retrieves first matching record from the view/table and subscribes to changes

Parameters

• filter required FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• params required SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

• onData required SubscribeOneCallback

  Callback fired once after subscribing and then every time the data matching the filter changes

• onError optional SubscribeOnError

  Error handler that may fire due to schema changes or other post subscribe issues Column or filter issues are thrown during the subscribe call

Return type

SubscriptionHandler

• unsubscribe required () => Promise<any>
• filter required {} | FullFilter<void, void>

count(filter?: FullFilter, selectParams?: SelectParams): Promise<number>

Returns the number of rows that match the filter

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

number

size(filter?: FullFilter, selectParams?: SelectParams): Promise<string>

Returns result size in bits

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

string

update(filter: FullFilter, newData: Partial, params?: SelectParams): Promise<UpdateReturnType<P, T, S> | undefined>

Updates a record in the table based on the specified filter criteria

• Use { multi: false } to ensure no more than one row is updated

Parameters

• filter required FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• newData required Partial

  Make all properties in T optional

• params optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

UpdateReturnType<P, T, S> | undefined

updateBatch(data: [FullFilter<T, S>, Partial<UpsertDataToPGCast>][], params?: SelectParams): Promise<void | UpdateReturnType<P, T, S>>

Updates multiple records in the table in a batch operation.

• Each item in the data array contains a filter and the corresponding data to update.

Parameters

• data required [FullFilter<T, S>, Partial<UpsertDataToPGCast<T>>][]

• params optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

void | UpdateReturnType<P, T, S>

insert(data: UpsertDataToPGCast | UpsertDataToPGCast[], params?: SelectParams): Promise<InsertReturnType>

Inserts a new record into the table.

Parameters

• data required InsertData

• params optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

InsertReturnType

Nothing is returned by default. returning must be specified to return the updated records. If an array of records is inserted then an array of records will be returned otherwise a single record will be returned.

upsert(filter: FullFilter, newData: Partial, params?: SelectParams): Promise<UpdateReturnType<P, T, S> | undefined>

Inserts or updates a record in the table.

• If a record matching the filter exists, it updates the record.
• If no matching record exists, it inserts a new record.

Parameters

• filter required FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• newData required Partial

  Make all properties in T optional

• params optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

UpdateReturnType<P, T, S> | undefined

delete(filter?: FullFilter, params?: SelectParams): Promise<UpdateReturnType<P, T, S> | undefined>

Deletes records from the table based on the specified filter criteria.

• If no filter is provided, all records may be deleted (use with caution).

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• params optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

UpdateReturnType<P, T, S> | undefined

Client installation

To install the package, run:

npm install prostgles-client

Configuration

Example react configuration and usage:

import prostgles from "prostgles-client";
import { DBGeneratedSchema } from "./DBGeneratedSchema";
 
export const App = () => {
  const prgl = useProstglesClient("/ws-api");
 
  if (prgl.isLoading) return <div>Loading...</div>;
  return <MyComponent prgl={prgl} />;
};

Example configuration:

import prostgles from "prostgles-client";
import { DBGeneratedSchema } from "./DBGeneratedSchema";
import io from "socket.io-client";
const socket = io({ path: "/ws-api" });
 
const prostglesClient = prostgles<DBGeneratedSchema>
  socket,
  onReady: async (dbs, methods, schema, auth) => {
    console.log(dbs.items.find());
  }
})

Configuration options

InitOptions

• socket required Socket<DefaultEventsMap, DefaultEventsMap>

  Socket.io client instance

• onReload optional () => void

  Execute this when requesting user reload (due to session expiring authGuard) Otherwise window will reload

• onSchemaChange optional () => void

  Callback called when schema changes. “onReady” will be called after this callback

• onReady required OnReadyCallback

  Callback called when:

  • the client connects for the first time
  • the schema changes
  • the client reconnects
  • server requests a reload

• onReconnect optional (socket: any, error?: any) => void

  Custom handler in case of websocket re-connection. If not provided will fire onReady

• onDisconnect optional () => void

  On disconnect handler. It is recommended to use this callback instead of socket.on(“disconnect”)

• onDebug optional (event: DebugEvent) => void | Promise<void>

  Awaited debug callback. Allows greater granularity during debugging.

Client-only Methods

The following table/view methods are available on the client.

useSync(basicFilter: EqualityFilter, syncOptions: SyncOptions): AsyncResult

Retrieves rows matching the filter and keeps them in sync

• use { handlesOnData: true } to get optimistic updates method: $update
• any changes to the row using the $update method will be reflected instantly to all sync subscribers that were initiated with the same syncOptions

Parameters

• basicFilter required EqualityFilter

  Filter used for data synchronization, where all specified columns must match the given values.

  Columns are combined using an AND condition.

  Example: { department: 'd1', name: 'abc' } would match records where department is ‘d1’ AND name is ‘abc’.

• syncOptions required SyncOptions

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred

sync(basicFilter: EqualityFilter, options: SyncOptions, onChange: OnChange, onError?: OnErrorHandler): Promise<SyncHandler>

Parameters

• basicFilter required EqualityFilter

  Filter used for data synchronization, where all specified columns must match the given values.

  Columns are combined using an AND condition.

  Example: { department: 'd1', name: 'abc' } would match records where department is ‘d1’ AND name is ‘abc’.

• options required SyncOptions

• onChange required OnChange

  Creates a local synchronized table

• onError optional OnErrorHandler

Return type

SyncHandler

• $unsync required () => void
• $upsert required (newData: T[]) => void | Promise<void>
• getItems required () => T[]

syncOne(basicFilter: Partial, options: SyncOneOptions, onChange: OnchangeOne, onError?: OnErrorHandler): Promise<SingleSyncHandles>

Parameters

• basicFilter required Partial

  Make all properties in T optional

• options required SyncOneOptions

  • onChange optional MultiChangeListener

    Data change listener. Called on first sync and every time the data changes

  • skipFirstTrigger optional boolean

    If true then the first onChange trigger is skipped

  • select optional AnyObject | ”*” | undefined

  • storageType optional “object” | “array” | “localStorage” | undefined

    Default is “object”. “localStorage” will persist the data

  • patchText optional boolean

    If true then only the delta of the text field is sent to server. Full text is sent if an error occurs

  • patchJSON optional boolean

  • onReady optional () => void

  • handlesOnData optional boolean

• onChange required OnchangeOne

• onError optional OnErrorHandler

Return type

SingleSyncHandles

CRUD handles added if initialised with handlesOnData = true

• $get required () => T | undefined
• $find required (idObj: Partial<T>) => T | undefined
• $unsync required () => any
• $delete required () => void
• $update required <OPTS extends $UpdateOpts>(newData: OPTS extends { deepMerge: true; } ? DeepPartial<T> : Partial<T>, opts?: OPTS | undefined) => any
• $cloneSync required CloneSync
• $cloneMultiSync required CloneMultiSync

useSyncOne(basicFilter: EqualityFilter, syncOptions: SyncOneOptions): AsyncResult

Retrieves the first row matching the filter and keeps it in sync

• use { handlesOnData: true } to get optimistic updates method: $update
• any changes to the row using the $update method will be reflected instantly to all sync subscribers that were initiated with the same syncOptions

Parameters

• basicFilter required EqualityFilter

  Filter used for data synchronization, where all specified columns must match the given values.

  Columns are combined using an AND condition.

  Example: { department: 'd1', name: 'abc' } would match records where department is ‘d1’ AND name is ‘abc’.

• syncOptions required SyncOneOptions

  • onChange optional MultiChangeListener

    Data change listener. Called on first sync and every time the data changes

  • skipFirstTrigger optional boolean

    If true then the first onChange trigger is skipped

  • select optional AnyObject | ”*” | undefined

  • storageType optional “object” | “array” | “localStorage” | undefined

    Default is “object”. “localStorage” will persist the data

  • patchText optional boolean

    If true then only the delta of the text field is sent to server. Full text is sent if an error occurs

  • patchJSON optional boolean

  • onReady optional () => void

  • handlesOnData optional boolean

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred

useSubscribe(filter?: FullFilter, options?: SubscribeParams): AsyncResult

Retrieves a list of matching records from the view/table and subscribes to changes

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• options optional SubscribeParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

  • throttle optional number

    If true then the subscription will be throttled to the provided number of milliseconds

  • throttleOpts optional { skipFirst?: boolean | undefined; }

    • skipFirst optional boolean

      False by default. If true then the first value will be emitted at the end of the interval. Instant otherwise

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred

useSubscribeOne(filter?: FullFilter, options?: SubscribeParams): AsyncResult

Retrieves a matching record from the view/table and subscribes to changes

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• options optional SubscribeParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

  • throttle optional number

    If true then the subscription will be throttled to the provided number of milliseconds

  • throttleOpts optional { skipFirst?: boolean | undefined; }

    • skipFirst optional boolean

      False by default. If true then the first value will be emitted at the end of the interval. Instant otherwise

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred

useFind(filter?: FullFilter, selectParams?: SelectParams): AsyncResult

Retrieves a list of matching records from the view/table

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred

useFindOne(filter?: FullFilter, selectParams?: SelectParams): AsyncResult

Retrieves first matching record from the view/table

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred

useCount(filter?: FullFilter, selectParams?: SelectParams): AsyncResult

Returns the total number of rows matching the filter

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred

useSize(filter?: FullFilter, selectParams?: SelectParams): AsyncResult

Returns result size in bits matching the filter and selectParams

Parameters

• filter optional FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• selectParams optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

AsyncResult

Async result type:

• data: the expected data
• isLoading: true when data is being fetched (initially or on subsequent filter/option changes)
• error: any error that occurred