Mutations

Creating a record

const id = await Film.createOne({
title: 'Ghostbusters',
description: 'Who you gonna call?'
}).execute();

This method returns the primary key value of the created record if the database supports returning the inserted data (Postgres, MariaDB). Otherwise, the auto-incremented id value (based on LAST_INSERT_ID) is returned, if available.

Creating multiple records

const ids = await Film.createMany([
{
title: 'Jaws',
description: 'You\'ll never go in the water again.',
},
{
title: 'Catch Me if You Can',
description: 'The true story of a real fake',
}
]).execute();

This method returns an array of the primary key values of the created records if the database supports returning the inserted data (Postgres, MariaDB). Otherwise, an array of the auto-incremented id values (based on LAST_INSERT_ID) is returned, if available.

Updating a record by id

const wasUpdated = await Film.updateById(
102,
{ description: 'With great power comes great responsibility.' }
).execute();

This method returns a boolean indicating whether the record was updated. If the record could not be found, it will return false.

Updating multiple records

const numRowsUpdated = await Film.updateMany({ description: '[Redacted]' })
.where({ description: { iLike: '%heck%' } })
.execute();

This method returns the number of rows that were successfully updated.

Deleting a record by id

const wasDeleted = await Film.deleteById(102).execute();

This method returns a boolean indicating whether the record was deleted. If the record could not be found, it will return false.

Deleting multiple records

const numRowsDeleted = await Film.deleteMany()
.where({ stars: { lessThanOrEqual: 1.5 } })
.execute();

This method returns the number of rows that were successfully deleted.

Transactions

Sqlmancer allows you to utilize a single transaction for multiple queries, ensuring that an operation takes place only if all operations in the transaction are also successful:

const results = await client.transaction(trx => {
const id1 = await Film.createOne({ title: 'Ghostbusters' })
.transaction(trx)
.execute();
const id2 = await Film.createOne({ title: 'Ghostbusters 2' })
.transaction(trx)
.execute();
return Film.findMany().where({ id: { in: [id1, id2] } })
.transaction(trx)
.execute();
});