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 aDBGeneratedSchema
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 joinsJoin[]
- 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” rewriteEventTriggerTagFilter
- same astrue
but only on specified events"hotReloadMode"
- only rewritesDBGeneratedSchema.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:
- create a column in that table called media
- 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