Table Methods
The table object provides a number of methods for generating and running queries on table rows. These use the table query builder to automatically generate and run queries for you based on the data you provide as arguments.
There are numerous methods that provide minor variations on other methods. These all conform to a naming convention where the first part of the name is the action (e.g. insert, update), followed by the number of rows that it operates on (e.g. one, any, all). The Row suffix (e.g. insertOneRow()) indicates that that reload option is automatically set and the method returns the complete row (or rows) reloaded from the database. The Record suffix (e.g. insertOneRecord()) indicates that the record option is automatically set and the methods reloads the row (or rows) and returns it as a record object (or array or objects).
As per the previous examples, we'll assume the table definition looks something like this:
// define the users table and the columns it contains
const db = connect({
database: 'sqlite://test.db',
tables: {
users: {
columns: 'id name:required email:required'
}
}
});
// fetch the users table
const users = await db.table('users');Insert Methods
insert(data, options)
The insert() method will construct and run an INSERT SQL query to insert a row from the column data that you provide.
const result = await users.insert({
name: 'Brian Badger',
email: 'brian@badgerpower.com'
});
console.log('Rows changed:', result.changes);
console.log('Generated id:', result.id);The SQL query generated will look like this for Sqlite and Mysql:
INSERT INTO users (name, email)
VALUES (?, ?)Note the use of value placeholders ? to prevent SQL injection attacks.
The format for placeholders in Postgres is slightly different but has the exact same effect:
INSERT INTO users (name, email)
VALUES ($1, $2)The result returned from the insert() method is an object containing the number of rows affected as changes and the generated id, where applicable in id. If you have defined a different id field (e.g. user_id) then this will be returned instead. Other data returned by the database engine may also be defined.
You can insert multiple rows by passing an array of objects to the method.
const results = await users.insert([
{
name: 'Bobby Badger',
email: 'bobby@badgerpower.com'
},
{
name: 'Brian Badger',
email: 'brian@badgerpower.com'
}
]);
console.log('Generated id #1:', results[0].id )
console.log('Generated id #2:', results[1].id )The return value will be an array of results the same as those returned by calling the method to insert a single row.
In some cases you may want to immediately fetch the inserted row back out of the database. This can be the case when you have columns with default values that will be generated by the database (e.g. a created timestamp) that you want to inspect.
You could easily do it yourself - the insert() method will return a result containing the generated id (or other id field) which you can then use to fetch the record. But why do it yourself when we can do it for you? Pass a second argument to the method as an object containing the reload option set to a true value.
After inserting a row the table insert() method will immediately reload it from the database and return the data for the row.
const frank = await users.insert(
{
name: 'Frank Ferret',
email: 'frank@ferrets-r-us.com'
},
{ reload: true }
);
console.log(frank.id); // e.g. 3
console.log(frank.name); // Frank Ferret
console.log(frank.email); // frank@ferrets-r-us.comThe same thing happens if you insert multiple rows and specify the reload options. The only difference is that the return value will be an array of rows.
const animals = await users.insert(
[
{
name: 'Frank Ferret',
email: 'frank@ferrets-r-us.com'
},
{
name: 'Simon Stoat',
email: 'simon@stoats-r-superb.com'
},
],
{ reload: true }
);
console.log(animals.length); // 2
console.log(animals[0].id); // e.g. 4
console.log(animals[0].name); // Frank Ferret
console.log(animals[0].email); // frank@ferrets-r-us.com
console.log(animals[1].id); // e.g. 5
console.log(animals[1].name); // Simon Stoat
console.log(animals[1].email); // simon@stoats-r-superb.comAnother option that is supported by the insert methods is record. This will reload the row from the database (as per the reload option) and return it as a record object. We'll be talking more about records shortly but for now you should know that it exists.
const frank = await users.insert(
{
name: 'Frank Ferret',
email: 'frank@ferrets-r-us.com'
},
{ record: true }
);
// frank is a record object but it still behaves like a row
console.log(frank.id); // e.g. 3
console.log(frank.name); // Frank Ferret
console.log(frank.email); // frank@ferrets-r-us.comThe final option is pick. If you try and insert data containing fields that aren't defined as table columns then a ColumnValidationError error will be throw.
const frank = await users.insert(
{
name: 'Frank Ferret',
email: 'frank@ferrets-r-us.com',
feet: 4
}
);
// -> throws ColumnValidationError: Unknown "feet" column in the users tableIf you like to throw caution to the wind then you can specify the pick option to override this. With it set to a true value, the method will pick out the values that are defined as columns and silently ignore everything else.
const frank = await users.insert(
{
name: 'Frank Ferret',
email: 'frank@ferrets-r-us.com',
feet: 4
},
{ pick: true }
);
// -> silently ignores invalid "feet" columninsertOne(data, options)
Internally, the insert() method calls either insertAll(), if the value passed is an array, or insertOne() if it's a single data object. You can call these methods directly if you prefer.
const result = await users.insertOne({
name: 'Brian Badger',
email: 'brian@badgerpower.com'
});insertAll(array, options)
Here's an example explicitly calling the insertAll() method. It's exactly the same as calling insert() with an array of rows to insert.
const results = await users.insertAll([
{
name: 'Frank Ferret',
email: 'frank@badgerpower.com'
}
{
name: 'Simon Stoat',
email: 'simon@badgerpower.com'
}
]);insertOneRow(data, options)
This is a wrapper around the insertOne() method which automatically sets the reload option for you. The result returned will be the inserted row reloaded from the database instead of a result object.
const row = await users.insertOneRow({
name: 'Brian Badger',
email: 'brian@badgerpower.com'
});
console.log(row.id); // e.g. 123
console.log(row.name); // Brian Badger
console.log(row.email); // brian@badgerpower.comThe insertRow() method is provided as an alias for this method.
insertAllRows(array, options)
This is a wrapper around the insertAll() method which automatically sets the reload option for you. The result returned will be an array of rows reloaded from the database instead of an array of results.
const rows = await users.insertAllRows([
{
name: 'Bobby Badger',
email: 'bobby@badgerpower.com'
},
{
name: 'Brian Badger',
email: 'brian@badgerpower.com'
}
]);
// array of rows returned
console.log(rows.length); // 2
console.log(rows[0].id); // e.g. 123
console.log(rows[0].name); // Bobby Badger
console.log(rows[0].email); // bobby@badgerpower.com
console.log(rows[1].id); // e.g. 124
console.log(rows[1].name); // Brian Badger
console.log(rows[1].email); // brian@badgerpower.comThe insertRows() method is provided as an alias for this method.
insertOneRecord(data, options)
This is a wrapper around the insertOne() method which automatically sets the record option for you. The result returned will be a record object instead of a result object.
const record = await users.insertOneRecord({
name: 'Brian Badger',
email: 'brian@badgerpower.com'
});
// a record object still behaves like a row
console.log(record.name); // Brian Badger
console.log(record.email); // brian@badgerpower.comThe insertRecord() method is provided as an alias for this method.
insertAllRecords(array, options)
This is a wrapper around the insertAll() method which automatically sets the record option for you. The result returned will be an array of record objects instead of an array of results.
const records = await users.insertAllRecords([
{
name: 'Bobby Badger',
email: 'bobby@badgerpower.com'
},
{
name: 'Brian Badger',
email: 'brian@badgerpower.com'
}
]);
// array of records returned, but they behave like rows
console.log(records.length); // 2
console.log(records[0].name); // Bobby Badger
console.log(records[0].email); // bobby@badgerpower.com
console.log(records[1].name); // Brian Badger
console.log(records[1].email); // brian@badgerpower.comThe insertRecords() method is provided as an alias for this method.
Update Methods
update(set, where, options)
The update() method, as the name suggests, allows you to update rows. It is an alias for the updateAll() method.
await users.update(
{ name: 'Brian "The Brains" Badger' }, // SET...
{ email: 'brian@badgerpower.com' } // WHERE...
);The first argument is an object containing the changes you want to make. The second optional argument is the WHERE clause identifying the rows you want to update. You can omit the second argument if you want to update all rows.
The SQL generated for the method call shown above will look something like this:
UPDATE users
SET name=?
WHERE email=?Again, the format for Postgres is slightly different, using $1 and $2 for placeholders instead of ?, but works exactly the same.
If you want to use comparison operators (other than the default =) in the WHERE clause then specify the value as an array of [operator, value].
For example, to update all rows where the email address isn't brian@badgerpower.com (I know, I know, this is a terrible example), then you could write:
await users.update(
{ name: "He's not the Messiah, he's a very naughty boy" },
{ email: ['!=', 'brian@badgerpower.com'] }
);The SQL generated for this example will look something like this:
UPDATE users
SET name=?
WHERE email!=?Any single value SQL operator can be used, e.g. =, !=, <, <=, >, >=. For the in and not in operators you should define the candidate values as an array.
await users.update(
{ name: "He's not a badger, he's a very naughty boy" },
{ animal: ['in', ['Ferret', 'Stoat']] }
);The query builder will add placeholders for each of the values:
UPDATE users
SET name=?
WHERE animal IN (?,?)This method, and the other update methods, will throw a ColumnValidationError if you specify a column in either the set or where data that isn't defined as a table column. The pick option can be used to override this. When set, the methods will pick out the data items that do have table column definitions and silently ignore the rest.
await users.update(
// "feet" is not a valid column, but the pick
// option tells the method to ignore it
{ name: 'Brian "The Brains" Badger', feet: 4 },
{ email: 'brian@badgerpower.com' },
{ pick: true }
);A ColumnValidationError will also be thrown if you attempt to update a column that is marked as readonly or fixed.
updateOne(set, where, options)
This is a variant of the update() / updateAll() method that has an additional assertion check that exactly one row is updated. If zero or more rows are updated then an UnexpectedRowCount error will be thrown with a message of the form N rows were updated when one was expected.
This method also supports the reload option. When set, the method will automatically reload the row from the database after performing the update. This can be useful if you've got a column which is automatically set when the record is updated, e.g. a modified column, which you want to inspect.
const row = await users.updateOne(
{ name: 'Brian "The Brains" Badger' },
{ email: 'brian@badgerpower.com' },
{ reload: true }
);
console.log('updated row:', row);The where clause defaults to using the equality operator, =, but as described in the update() method, you can use other comparison operators by specifying the value as an an array, e.g. { year: ['>', 2000] } to match all records where the year is greater than 2000.
One thing to note: this uses the modification and selection criteria specified to reload the data. If, for example, you change the email address of a row then it will correctly reload the record using the new email address.
const row = await users.updateOne(
{ email: 'brian-badger@badgerpower.com' },
{ email: 'brian@badgerpower.com' },
{ reload: true }
);
console.log('new email address:', row.email); // brian-badger@badgerpower.comHowever, there are edge cases where it's not possible to reload the same row that was modified, based on the criteria provided. Consider this somewhat contrived example: if the users table has a friends column and there is exactly one record where the friends count is set to 0. You feel sorry for the poor user and decide to modify their friends count to be 1. You'll be their friend, right?
await users.updateOne(
{ friends: 1 },
{ friends: 0 },
{ reload: true }
);While there may have been exactly one user with friends set to 0 before the update, once the update has been applied there may be multiple rows which have the friends count set to 1. The reload will fail with an UnexpectedRowCount error. In these cases you should always provide some other unique attribute to ensure that the correct row can be identified and reloaded:
await users.updateOne(
{ friends: 1 },
{ email: 'bobby@badger.com' },
{ reload: true }
);updateAny(set, where, options)
This is a variant of the update() / updateAll() method that has an additional assertion check that no more than one row is updated. If more than one rows are updated then an UnexpectedRowCount error will be thrown with a message of the form N rows were updated when one was expected.
This also supports the reload option. If a row is updated then the complete row data will be returned. Otherwise it will return undefined.
const row = await users.updateAny(
{ name: 'Brian "The Brains" Badger' },
{ email: 'brian@badgerpower.com' },
{ reload: true }
);
if (row) {
console.log("updated row:", row);
}
else {
console.log("could not update row - Brian not found!")
}You can also use other comparison operator as per the update() method, e.g. { year: ['>', 2000] } to match all records where the year is greater than 2000.
updateAll(set, where, options)
The update() method is an alias for the updateAll() method. If you want additional checks to be performed to ensure that you're only updating one row, or if you want to automatically reload a row after an update then you can use the updateOne() or updateAny() methods.
updateOneRow(set, where, options)
This is a wrapper around the updateOne() method which automatically sets the reload option for you. The result returned will be the updated row reloaded from the database instead of a result object.
const row = await users.updateOneRow(
{ name: 'Brian "The Brains" Badger' },
{ email: 'brian@badgerpower.com' },
);
console.log('updated row:', row);The updateRow() method is provided as an alias for this method.
updateAnyRow(set, where, options)
This is a wrapper around the updateAny() method which automatically sets the reload option for you. The result returned will be the updated row reloaded from the database instead of a result object.
const row = await users.updateAnyRow(
{ name: 'Brian "The Brains" Badger' },
{ email: 'brian@badgerpower.com' },
);
if (row) {
console.log("updated row:", row);
}
else {
console.log("could not update row - Brian not found!")
}Delete Method
delete(where)
You can probably guess what the delete() method does.
await users.delete({
email: 'brian@badgerpower.com'
});The object passed as the only argument identifies the rows that you want to delete.
The SQL generated will look something like this:
DELETE FROM users
WHERE email=?You can omit the selection criteria if you want to delete all rows in the table.
await users.delete()Naturally, you should use this method with caution.
You can also use other comparison operator in the where clause, as per the update() method, e.g. { year: ['>', 2000] } to match all records where the year is greater than 2000.
If you include any columns in the where specification that aren't recognised as table columns then the method will throw a ColumnValidationError. Use the pick option to override this behaviour.
await users.delete(
// "feet" is not a valid table column, but the
// pick option tells the method to ignore it
{ email: 'brian@badgerpower.com', feet: 4 },
{ pick: true }
);Fetch Methods
fetch(where, options)
There are three different methods for fetching rows from the table using selection criteria. The fetchOne() method is used when you're expecting to get exactly one row returned. The fetchAny() method is for when you're expecting to get a single row that may or may not exist. The fetchAll() method returns an array of all matching rows that may be zero or more.
The fetch() method is an alias for the fetchAll() method.
All of the methods will throw a ColumnValidationError if you specify a data item in the where criteria that isn't defined as a table column. Use the pick option to override this behavious, as per the insert(), update() and delete() methods.
fetchOne(where, options)
The fetchOne() method will return a single row. If the row isn't found or multiple rows match the criteria then an UnexpectedRowCount error will be thrown with a message of the form N rows were returned when one was expected.
// returns a single row or throws an error
const brian = await users.fetchOne({
email: 'brian@badgerpower.com'
});
console.log('Brian:', brian);The where clause default to using the equality operator, =, but like the update() and delete() methods, you can use other comparison operators, e.g. { year: ['>', 2000] } to match all records where the year is greater than 2000.
You can pass a second argument which can contain various options to modify the selection. For example, the columns option can be used to specify the columns that you want to select. They can be specified as a string containing the columns names separated by whitespace:
const brian = await users.fetchOne(
{ email: 'brian@badgerpower.com' },
{ columns: 'id name' }
);Or as an array:
const brian = await users.fetchOne(
{ email: 'brian@badgerpower.com' },
{ columns: ['id', 'name'] }
);The record option can be specified if you want the data returned as a record instead of a row.
const brian = await users.fetchOne(
{ email: 'brian@badgerpower.com' },
{ record: true }
);The generated SQL for this method (and also fetchAny() and fetchAll() will look something like this:
SELECT "users"."id", "users"."name" FROM users
WHERE email=?fetchAny(where, options)
The fetchAny() method will return a single row if it exists or undefined if it doesn't.
// returns a single row or undefined
const brian = await users.fetchAny({
email: 'brian@badgerpower.com'
});
if (brian) {
console.log('Brian:', brian);
}
else {
console.log('Brian Badger was not found');
}The where clause default to using the equality operator, =, but like numerous other methods described above, you can use other comparison operators e.g. { year: ['>', 2000] } to match all records where the year is greater than 2000.
As per fetchOne() you can pass an additional object containing options. For example, to specify the columns you want returned:
const brian = await users.fetchAny(
{ email: 'brian@badgerpower.com' },
{ columns: 'id email' }
);Or to return the row as a record object:
const brian = await users.fetchAny(
{ email: 'brian@badgerpower.com' },
{ record: true }
);fetchAll(where, options)
The fetchAll() method will return an array of all matching rows.
// returns an array of all rows (possibly empty)
const bobbies = await users.fetchAll(
{ name: 'Bobby Badger' }
);
if (bobbies.length) {
console.log("Fetched %s users called 'Bobby Badger':", bobbies.length);
}
else {
console.log("There aren't any users called 'Bobby Badger'");
}If you want to return all matching rows then you can omit the criteria or specify an empty object.
const allUsers = await users.fetchAll();const allUsers = await users.fetchAll({ });It shouldn't surprise you to learn that you can use other comparison operators in the where clause, e.g. { year: ['>', 2000] } to match all records where the year is greater than 2000.
As per fetchOne() you can pass an additional objects containing options. It supports the columns and record options. You can also provide order (or orderBy if you prefer to use a naming convention as close as possible to the SQL equivalent of ORDER BY) to specify the order in which rows should be returned:
The order can contain multiple columns and each will be automatically quoted.
const allUsers = await users.fetchAll(
{ }, // you can specify selection criteria or use an empty object to fetch all rows
{ order: 'name, id' } // -> ORDER BY "name", "id"
);If you want to use a raw SQL order then define it as an object with a single sql property:
const allUsers = await users.fetchAll(
{ }, // you can specify selection criteria or use an empty object to fetch all rows
{ order: { sql: 'name DESC' } }
);Or use the sql function to generate it for you from a tagged template literal.
import { sql } from '@abw/badger-database'
const allUsers = await users.fetchAll(
{ }, // you can specify selection criteria or use an empty object to fetch all rows
{ order: sql`name DESC` }
);fetchOneRecord(where, options)
This method is a wrapper around fetchOne() which returns the row as a record object. It effectively sets the record option for you.
Read more about records here.
The fetchRecord() method is provided as an alias for this method.
fetchAnyRecord(where, options)
This method is a wrapper around fetchAny() which returns the row as a record object.
fetchAllRecords(where, options)
This method is a wrapper around fetchAll() which returns the rows as an array of record objects.
The fetchRecords() method is provided as an alias for this method.
Record Methods
loaded(row, options)
This method is called whenever a row is loaded from the database (or reloaded in the case of updated rows when the reload option is set). If the options.record flag is set then it passes the row to the record() method to convert it to a record object. Otherwise the row is returned.
You can redefine this method in your own table classes to perform any processing of the loaded data. For example, if your database stores boolean values as numbers and you want them to be converted to boolean values whenever a record is loaded then you could do something like this.
import { Table } from '@abw/badger-database'
export class Users extends Table {
loaded(row, options) {
row.admin = Boolean(row.admin);
return super.loaded(row, options);
}
}You should always call the super.loaded() method after any processing and return the value that it returns, as show in this example.
record(row)
This methods converts a row of data to a record object.
records(rows)
This methods converts an array of rows of data to a an array of record objects.
Where Next?
In the section we'll look at how you can define your own custom table queries.