Documentation
    Preparing search index...

    Class Table

    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 Table.findOne.

    Table.findOne({name: "Customers"}) // find the table with name "Customers"
    Table.findOne("Customers") // find the table with name "Customers" (shortcut)
    Table.findOne({ id: 5 }) // find the table with id=5
    Table.findOne(5) // find the table with id=5 (shortcut)

    Table.findOne is synchronous (no need to await), But the functions that query and manipulate (such as Table.insertRow, Table.getRows, Table.updateRow, Table.deleteRows) rows are mostly asyncronous, so you can put the await in front of the whole expression

    To count the number of rows in the customer table

    const nrows = await Table.findOne("Customers").countRows()

    For further examples, see the Table test suite

    There are several methods you can use to retrieve rows in the database:

    These functions all take Where expressions 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 3

    For further examples, see the mkWhere test suite

    There are two nearly identical functions for updating rows depending on how you want failures treated

    There are two nearly identical functions for inserting a new row depending on how you want failures treated

    Use Table.deleteRows to delete any number (zero, one or many) of rows matching a criterion. It uses the same where expression as the functions for querying rows

    Implements

    Index

    Constructors

    constructor

    Properties

    constraints description? external fields has_sync_info id? is_user_group min_role_read min_role_write name ownership_field_id? ownership_formula? provider_cfg? provider_name? versioned

    Accessors

    composite_pk_names pk_name pk_type santized_name sql_name to_json

    Methods

    aggregationQuery check_table_constraints compress_history countRows delete delete_url deleteRows distinctValues dump_history_to_json dump_to_json enable_fkey_constraints field_options get_child_relations get_history get_join_field_options get_models get_parent_relations get_relation_data get_relation_options getField getFields getForeignKeys getForeignTables getFormulaExamples getJoinedQuery getJoinedRow getJoinedRows getRow getRows getTags import_csv_file import_json_file import_json_history_file insert_history_row insertRow is_owner latestSyncInfo latestSyncInfos move_include_fts_to_search_context normalise_error_message normalise_fkey_values owner_fieldname owner_fieldname_from_fields ownership_formula_where ownership_options parse_json_fields processSelectOptions read_state_strict redo_row_changes rename repairCompositePrimary resetSequence restore_row_version slug_options stringify_json_fields to_provided_table toggleBool tryInsertRow tryUpdateRow undo_row_changes update updateRow updateWhereWithOwnership allSlugOptions create create_from_csv createInDb find find_with_external findOne state_refresh

    Constructors

    Properties

    constraints: TableConstraint[]

    An array of TableConstraints for this table

    description?: string

    A description of the purpose of the table

    external: boolean

    If true this is an external table (not a database table)

    fields: Field[]

    An array of Fields in this table

    has_sync_info: boolean

    Whether sync info for mobile apps is enabled for this table

    id?: number

    The table ID

    is_user_group: boolean

    Is this a user group? If yes it will appear as options in the ownership dropdown

    min_role_read: number

    Minimum role to read

    min_role_write: number

    Minimum role to write

    name: string

    The table name

    ownership_field_id?: number | null

    The ID of the ownership field

    ownership_formula?: string

    A formula to denote ownership. This is a JavaScript expression which must evaluate to true if the user is the owner

    provider_cfg?: Row

    Configuration for the table provider for this table

    provider_name?: string

    Name of the table provider for this table (not a database table)

    versioned: boolean

    Version history enabled for this table

    Accessors

    • get to_json(): {
          external: boolean;
          fields: any[];
          id: number
          | undefined;
          min_role_read: number;
          min_role_write: number;
          name: string;
          ownership_field_id: number | null | undefined;
          ownership_formula: string | undefined;
          provider_cfg: Row | undefined;
          provider_name: string | undefined;
          versioned: boolean;
      }

      Returns {
          external: boolean;
          fields: any[];
          id: number | undefined;
          min_role_read: number;
          min_role_write: number;
          name: string;
          ownership_field_id: number | null | undefined;
          ownership_formula: string | undefined;
          provider_cfg: Row | undefined;
          provider_name: string | undefined;
          versioned: boolean;
      }

    Methods

    • Returns aggregations for this table, possibly on a subset by where-expression

      Parameters

      • aggregations: { [nm: string]: { aggregate: string; field?: string; valueFormula?: string } }
      • Optionaloptions: { groupBy?: string | string[]; where?: Where } & ForUserRequest

      Returns Promise<Row>

    • Check table constraints against a row object. Will return a string With an error message if the table constraints are violated, undefined if the row does not violate any constraints

      Parameters

      Returns string | undefined

    • Compress history by minimal interval and/or deleting unchanged rows. Can be called with options object, or just minimal interval for legacy code

      Parameters

      • options: number | { delete_unchanged?: boolean; interval_secs?: number }

      Returns Promise<void>

    • Count 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.

      Parameters

      Returns Promise<number>

      const 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

      Parameters

      • where: Where

        condition

      • Optionaluser: AbstractUser

        optional user, if null then no authorization will be checked

      • OptionalnoTrigger: boolean
      • OptionalresultCollector: any

      Returns Promise<void>

      // delete all books where author = "Friedrich Nietzsche"
      await Table.findOne({name: "books"}).deleteRows({author: "Friedrich Nietzsche"})

    • Parameters

      • nrecurse: number = 0
      • fieldWhere: (f: Field) => boolean = ...
      • prefix: string = ""

      Returns Promise<string[]>

    • Parameters

      • opts: any = {}

      Returns Promise<
          Partial<JoinOptions> & {
              notAuthorized?: boolean;
              sql?: string;
              values?: Value[];
          },
      >

    • 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 match
      • joinFields: An object with the joinfields to retrieve
      • aggregations: An object with the aggregations to retrieve
      • orderBy: A string with the name of the field to order by
      • orderDesc: If true, descending order
      • limit: A number with the maximum number of rows to retrieve
      • offset: The number of rows to skip in the result before returning rows

      Parameters

      • Optionalopts: any = {}

      Returns Promise<Row[]>

      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.

      Parameters

      Returns Promise<Row | null>

      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).

      Parameters

      Returns Promise<Row[]>

      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 than 500 pages
      const myBooks = await bookTable.getRows({author: "Henrik Pontoppidan", pages: {gt: 500}}, {orderBy: "published", orderDesc: true})

    • Import CSV file to existing table

      Parameters

      • filePath: string
      • Optionaloptions: {
            delimiter?: string;
            extra_row_values?: Row;
            method?: "Auto" | "copy" | "row-by-row";
            no_table_write?: boolean;
            no_transaction?: boolean;
            overwrite_csv_fields?: boolean;
            recalc_stored?: boolean;
            skip_first_data_row?: boolean;
        }

      Returns Promise<ResultMessage>

    • Insert row into the table. By passing in the user as the second argument, it 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 Table.tryInsertRow instead.

      Parameters

      • v_in0: Row
      • Optionaluser: AbstractUser
      • OptionalresultCollector: object
      • OptionalnoTrigger: boolean
      • OptionalsyncTimestamp: Date

      Returns Promise<any>

      await Table.findOne("People").insertRow({ name: "Jim", age: 35 })

    • 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

      Parameters

      • v_in: Row

        columns with values to update

      • id_in: Row | PrimaryKeyValue
      • Optionaluser: AbstractUser

        user

      • OptionalnoTrigger:
            | boolean
            | {
                additionalTriggerValues?: Row;
                autoRecalcIterations?: number;
                extraArgs?: any;
                noTrigger?: boolean;
                restore_of_version?: number;
                resultCollector?: object;
                syncTimestamp?: Date;
            }
      • OptionalresultCollector: object
      • Optionalrestore_of_version: number
      • OptionalsyncTimestamp: Date
      • OptionaladditionalTriggerValues: Row
      • OptionalautoRecalcIterations: number
      • OptionalextraArgs: any

      Returns Promise<string | void>

      const 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)
      }

    Settings

    Member Visibility

    On This Page

    Querying table rowsUpdating a RowInserting a new RowDeleting rows
    Constructors
    Properties
    Accessors
    Methods
    aggregationQuerycheck_table_constraintscompress_historycountRowsdeletedelete_urldeleteRowsdistinctValuesdump_history_to_jsondump_to_jsonenable_fkey_constraintsfield_optionsget_child_relationsget_historyget_join_field_optionsget_modelsget_parent_relationsget_relation_dataget_relation_optionsgetFieldgetFieldsgetForeignKeysgetForeignTablesgetFormulaExamplesgetJoinedQuerygetJoinedRowgetJoinedRowsgetRowgetRowsgetTagsimport_csv_fileimport_json_fileimport_json_history_fileinsert_history_rowinsertRowis_ownerlatestSyncInfolatestSyncInfosmove_include_fts_to_search_contextnormalise_error_messagenormalise_fkey_valuesowner_fieldnameowner_fieldname_from_fieldsownership_formula_whereownership_optionsparse_json_fieldsprocessSelectOptionsread_state_strictredo_row_changesrenamerepairCompositePrimaryresetSequencerestore_row_versionslug_optionsstringify_json_fieldsto_provided_tabletoggleBooltryInsertRowtryUpdateRowundo_row_changesupdateupdateRowupdateWhereWithOwnershipallSlugOptionscreatecreate_from_csvcreateInDbfindfind_with_externalfindOnestate_refresh