An array of TableConstraints for this table
Optional descriptionA description of the purpose of the table
If true this is an external table (not a database table)
An array of Fields in this table
Whether sync info for mobile apps is enabled for this table
Optional idThe table ID
Is this a user group? If yes it will appear as options in the ownership dropdown
Minimum role to read
Minimum role to write
The table name
Optional ownership_The ID of the ownership field
Optional ownership_A formula to denote ownership. This is a JavaScript expression which must evaluate to true if the user is the owner
Optional provider_Configuration for the table provider for this table
Optional provider_Name of the table provider for this table (not a database table)
Version history enabled for this table
Get primary key field name
get sanitized name of table
Get Table SQL Name
Returns aggregations for this table, possibly on a subset by where-expression
Optional field?: stringOptional valueOptional options: { Optional groupOptional where?: anyCount the number of rows in db table. The argument is a where-expression with conditions the counted rows should match. countRows returns the number of matching rows wrapped in a promise.
Optional where: WhereOptional opts: ForUserRequestconst bookTable = Table.findOne({name: "books"})
// Count the total number of rows in the books table
const totalNumberOfBooks = await bookTable.countRows({})
// Count the number of books where the cover_color field has the value is "Red"
const numberOfRedBooks = await bookTable.countRows({cover_color: "Red"})
// Count number of books with more than 500 pages
const numberOfLongBooks = await bookTable.countRows({pages: {gt: 500}})
Delete rows from table. The first argument is a where expression indicating the conditions for the rows to be deleted
condition
Optional user: Rowoptional user, if null then no authorization will be checked
Optional noTrigger: boolean// delete all books where author = "Friedrich Nietzsche"
await Table.findOne({name: "books"}).deleteRows({author: "Friedrich Nietzsche"})
Get rows along with joined and aggregated fields. The argument to getJoinedRows is an object
with several different possible fields, all of which are optional
where: A Where expression indicating the criterion to matchjoinFields: An object with the joinfields to retrieveaggregations: An object with the aggregations to retrieveorderBy: A string with the name of the field to order byorderDesc: If true, descending orderlimit: A number with the maximum number of rows to retrieveoffset: The number of rows to skip in the result before returning rowsOptional opts: any = {}const patients = Table.findOne({ name: "patients" });
const patients_rows = await patients.getJoinedRows({
where: { age: { gt: 65 } },
orderBy: "id",
aggregations: {
avg_temp: {
table: "readings",
ref: "patient_id",
field: "temperature",
aggregate: "avg",
},
},
joinFields: {
pages: { ref: "favbook", target: "pages" },
author: { ref: "favbook", target: "author" },
},
});
Get one row from the table in the database. The matching row will be returned in a promise - use await to read the value. If no matching rule can be found, null will be returned. If more than one row matches, the first found row will be returned.
The first argument to get row is a where-expression With the conditions the returned row should match.
The second document is optional and is an object that can modify the search. This is mainly useful
in case there is more than one matching row for the where-expression in the first argument and you
want to give an explicit order. For example, use {orderBy: "name"} as the second argument to pick
the first row by the name field, ordered ascending. {orderBy: "name", orderDesc: true} to order by name,
descending
This is however rare and usually getRow is run with a single argument of a Where expression that uniquely determines the row to return, if it exisits.
const bookTable = Table.findOne({name: "books"})
// get the row in the book table with id = 5
const myBook = await bookTable.getRow({id: 5})
// get the row for the last book published by Leo Tolstoy
const myBook = await bookTable.getRow({author: "Leo Tolstoy"}, {orderBy: "published", orderDesc: true})
Get all matching rows from the table in the database.
The arguments are the same as for getRow. The first argument is where-expression with the conditions to match, and the second argument is an optional object and allows you to set ordering and limit options. Keywords that can be used in the second argument are orderBy, orderDesc, limit and offset.
getRows will return an array of rows matching the where-expression in the first argument, wrapped in a Promise (use await to read the array).
const bookTable = Table.findOne({name: "books"})
// get the rows in the book table with author = "Henrik Pontoppidan"
const myBooks = await bookTable.getRows({author: "Henrik Pontoppidan"})
// get the 3 most recent books written by "Henrik Pontoppidan" with more that 500 pages
const myBooks = await bookTable.getRows({author: "Henrik Pontoppidan", pages: {gt: 500}}, {orderBy: "published", orderDesc: true})
Get child relations for table
Optional allow_join_aggregations: booleanGet parent relations for table
Optional allow_double: booleanOptional allow_triple: booleanget relation-data joined from a field of another table
Import CSV file to existing table
Optional options: { Optional no_Optional recalc_Optional skip_Insert row into the table. By passing in the user as the second argument, tt will check write rights. If a user object is not supplied, the insert goes ahead without checking write permissions.
Returns the primary key value of the inserted row.
This will throw an exception if the row does not conform to the table constraints. If you would like to insert a row with a function that can return an error message, use tryInsertRow instead.
Optional user: RowOptional resultCollector: objectOptional noTrigger: booleanOptional syncTimestamp: Dateawait Table.findOne("People").insertRow({ name: "Jim", age: 35 })
Get owner column name
Optional fields: null | Field[]fields list
null or owner column name
Update a single row in the table database.
The first two arguments are mandatory. The first is an object with the new values to set in the row. The second argument is the value of the primary key of the row to update. Typically this is the id field of an existing row object
columns with values to update
id value
Optional user: RowOptional noTrigger: booleanOptional resultCollector: objectOptional restore_of_version: anyOptional syncTimestamp: DateOptional additionalTriggerValues: Rowconst bookTable = Table.findOne({name: "books"})
// get the row in the book table for Moby Dick
const moby_dick = await bookTable.getRow({title: "Moby Dick"})
// Update the read field to true and the rating field to 5
await bookTable.updateRow({read: true, rating: 5}, moby_dick.id)
// if you want to update more than one row, you must first retrieve all the rows and
// then update them individually
const allBooks = await bookTable.getRows()
for(const book of allBooks) {
await bookTable.updateRow({price: book.price*0.8}, book.id)
}
Static allStatic createStatic createCreate the table structure generates a CREATE TABLE cmd with all field and runs it TODO field defaults for pg
Static create_Static findStatic findStatic find_Generated using TypeDoc
A class representing database tables and their properties.
Use this to create or delete tables and their properties, or to query or change table rows.
To query, update, insert or delete rows in an existing table, first you should find the table object with findOne.
Example
Table.findOne is synchronous (no need to await), But the functions that query and manipulate (such as insertRow, getRows, updateRow, deleteRows) rows are mostly asyncronous, so you can put the await in front of the whole expression
Example
To count the number of rows in the customer table
For further examples, see the Table test suite
Querying table rows
There are several methods you can use to retrieve rows in the database:
These functions all take
Whereexpressions which are JavaScript objects describing the criterion to match to. Some examples:{}: Match all rows{ name: "Jim" }: Match all rows with name="Jim"{ name: { ilike: "im"} }: Match all rows where name contains "im" (case insensitive){ name: /im/ }: Match all rows with name matching regular expression "im"{ age: { lt: 18 } }: Match all rows with age<18{ age: { lt: 18, equal: true } }: Match all rows with age<=18{ age: { gt: 18, lt: 65} }: Match all rows with 18<age<65{ name: { or: ["Harry", "Sally"] } }: Match all rows with name="Harry" or "Sally"{ or: [{ name: "Joe"}, { age: 37 }] }: Match all rows with name="Joe" or age=37{ not: { id: 5 } }: All rows except id=5{ id: { in: [1, 2, 3] } }: Rows with id 1, 2, or 3{ id: { not: { in: [1, 2, 3] } } }: Rows with id any value except 1, 2, or 3For further examples, see the mkWhere test suite
Updating a Row
There are two nearly identical functions for updating rows depending on how you want failures treated
Inserting a new Row
There are two nearly identical functions for inserting a new row depending on how you want failures treated
Deleting rows
Use deleteRows to delete any number (zero, one or many) of rows matching a criterion. It uses the same
whereexpression as the functions for querying rows