Your code can can manipulate rows in the database, manipulate files, interact 
with remote APIs, or issue directives for the user's display.

Your code can use await at the top level, and should do so whenever calling 
database queries or other aynchronous code (see example below)

The variable `table` is the associated table (if any; note lowercase). If you want to access a different table,
use the `Table` variable (note uppercase) to access the Table class of table.

Example:

await table.insertRow({name: "Alex", age: 43})
const otherTable = Table.findOne({name: "Orders"})
await otherTable.deleteRows({id: order})


You can use the Table class to access database tables. 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({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 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:

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

Querying table rows

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

countRows: 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.

countRows(where?): Promise<number>
Count amount of rows in db table

Parameters
Optional where: Where
Returns Promise<number>

Example of using countRows: 
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}})

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


getRows(where?, selopts?): Promise<Row[]>
Get rows from Table in db

Parameters
where: Where = {}
selopts: SelectOptions & ForUserRequest = {}
Returns Promise<Row[]>

Example of using getRows:

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

getRow: 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.

getRow(where?, selopts?): Promise<null | Row>
Get one row from table in db

Parameters
where: Where = {}
selopts: SelectOptions & ForUserRequest = {}
Returns Promise<null | Row>

Example of using getRow:
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})

getJoinedRows: To retrieve rows together with joinfields and aggregations

getJoinedRows(opts?): Promise<Row[]>
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
Optional opts: any = {}
Returns Promise<Row[]>

Example of using getJoinedRows: 

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" },
     },
});

These functions all take "Where expressions" which are JavaScript objects describing 
the criterion to match to. Some examples:

{ 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

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

updateRow Update a row in the database table, throws an exception if update is invalid

updateRow(v_in, id, user?, noTrigger?, resultCollector?, restore_of_version?, syncTimestamp?): Promise<string | void>
Update row

Parameters
v_in: any. columns with values to update

id: number. id value, table primary key

Optional user: Row
Optional noTrigger: boolean
Optional resultCollector: object
Optional restore_of_version: any
Optional syncTimestamp: Date

Example of using updateRow: 

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 in the retrieved row.
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)
}

tryUpdateRow Update a row, return an error message if update is invalid

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

insertRow insert a row, throws an exception if it is invalid
insertRow(v_in, user?, resultCollector?, noTrigger?, syncTimestamp?): Promise<any>
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.

Parameters
v_in: Row
Optional user: Row
Optional resultCollector: object
Optional noTrigger: boolean
Optional syncTimestamp: Date
Returns Promise<any>

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

tryInsertRow insert a row, return an error message if it is invalid

Use 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
deleteRows(where, user?, noTrigger?): Promise<void>
Delete rows from table

Parameters
where: Where
condition

Optional user: Row
optional user, if null then no authorization will be checked

Optional noTrigger: boolean
Returns Promise<void>

The following tables are present in the database:

{{# for (const table of tables) { }}
{{ table.name }} table with name = "{{ table.name }}" which has the following fields:
{{# for (const field of table.fields) { }} - {{ field.name }} of type {{ field.pretty_type.replace("Key to","ForeignKey referencing") }}
{{# } }}
{{# } }}

The code may run in a context of a single row. In that case the variable called `row` 
can be used to access its data as a JavaScript object. The primary key value for the current row
can be accessed with row.id

In addition to `table` and `Table`, you can use other functions/variables:

sleep: A small utility function to sleep for certain number of milliseconds. Use this with await

Example: `await sleep(1000)`

`fetch` and `fetchJSON`: Use these to make HTTP API calls. `fetch` is the standard JavaScript `fetch` (provided by 
[node-fetch](https://www.npmjs.com/package/node-fetch#common-usage)). `fetchJSON` performs a fetch 
and then reads its reponse to JSON

Example: 

const response = await fetch('https://api.github.com/users/github');
const data = await response.json();

which is the same as

const data = await fetchJSON('https://api.github.com/users/github');

The logged-in user Is available in the `user` variable as a JavaScript object. The primary key for the 
logged-in user is accessed as `user.id` and their email address as `user.email`.

Your code can with its return value give directives to the current page. 
Valid return values are:

notify: Send a pop-up notification indicating success to the user. Example: return { notify: "Order completed!" }

error: Send a pop-up notification indicating error to the user. Example: return { error: "Invalid command!" }

goto: Navigate to a different URL. Example: return { goto: "https://saltcorn.com" }

reload_page: Request a page reload with the existing URL. Example: return { reload_page: true }

popup: Open a URL in a popup. Example: return { popup: `/view/Orders?id=${parent}`}

set_fields: If triggered from an edit view, set fields dynamically in the form. The 
value should be an object with keys that are field variable names. Example:

return { set_fields: {
    zidentifier: `${name.toUpperCase()}-${id}`
  }
}