API
A set of operation methods are available on each entity:
Returned fields
The entity operation methods accept a fields option that can specify an array of field names to be returned. If not specified, all fields will be returned.
Where clause
The entity operation methods accept a where option to allow limiting of the database rows that will be affected by the operation.
The where object's key is the field you want to check, the value is a key/value map where the key is an operator (see the table below) and the value is the value you want to run the operator against.
| Platformatic operator | SQL operator |
|---|---|
| eq | \'=\' |
| in | \'IN\' |
| nin | \'NOT IN\' |
| neq | \'<>\' |
| gt | \'>\' |
| gte | '\>=' |
| lt | \'<\' |
| lte | \'<=\' |
| like | \'LIKE\' |
Handling Null Values
When using the eq and neq operators with a null value, the comparison logic adheres to standard SQL rules for null value handling.
Examples
Selects row with id = 1
{
...
"where": {
id: {
eq: 1
}
}
}
Select all rows with id less than 100
{
...
"where": {
id: {
lt: 100
}
}
}
Select all rows with id 1, 3, 5 or 7
{
...
"where": {
id: {
in: [1, 3, 5, 7]
}
}
}
Where clause operations are by default combined with the AND operator. To combine them with the OR operator, use the or key.
Select all rows with id 1 or 3
{
...
"where": {
or: [
{
id: {
eq: 1
}
},
{
id: {
eq: 3
}
}
]
}
}
Select all rows with id 1 or 3 and title like 'foo%'
{
...
"where": {
or: [
{
id: {
eq: 1
}
},
{
id: {
eq: 3
}
}
],
title: {
like: 'foo%'
}
}
}
Select all rows where title is null
{
...
"where": {
title: {
eq: null
}
}
}
Select all rows where title is not null
{
...
"where": {
title: {
neq: null
}
}
}
Reference
find
Retrieve data for an entity from the database.
Options
| Name | Type | Description |
|---|---|---|
fields | Array of string | List of fields to be returned for each object |
where | Object | Where clause 🔗 |
orderBy | Array of Object | Object like { field: 'counter', direction: 'ASC' } |
limit | Number | Limits the number of returned elements |
offset | Number | The offset to start looking for rows from |
Usage
'use strict'
const { connect } = require('@platformatic/sql-mapper')
const { pino } = require('pino')
const pretty = require('pino-pretty')
const logger = pino(pretty())
async function main() {
const pgConnectionString = 'postgres://postgres:[email protected]/postgres'
const mapper = await connect({
connectionString: pgConnectionString,
log: logger,
})
const res = await mapper.entities.page.find({
fields: ['id', 'title',],
where: {
id: {
lt: 10
}
},
})
logger.info(res)
await mapper.db.dispose()
}
main()
count
Same as find, but only count entities.
Options
| Name | Type | Description |
|---|---|---|
where | Object | Where clause 🔗 |
Usage
'use strict'
const { connect } = require('@platformatic/sql-mapper')
const { pino } = require('pino')
const pretty = require('pino-pretty')
const logger = pino(pretty())
async function main() {
const pgConnectionString = 'postgres://postgres:[email protected]/postgres'
const mapper = await connect({
connectionString: pgConnectionString,
log: logger,
})
const res = await mapper.entities.page.count({
where: {
id: {
lt: 10
}
},
})
logger.info(res)
await mapper.db.dispose()
}
main()
insert
Insert one or more entity rows in the database.
Options
| Name | Type | Description |
|---|---|---|
fields | Array of string | List of fields to be returned for each object |
inputs | Array of Object | Each object is a new row |
Usage
'use strict'
const { connect } = require('@platformatic/sql-mapper')
const { pino } = require('pino')
const pretty = require('pino-pretty')
const logger = pino(pretty())
async function main() {
const pgConnectionString = 'postgres://postgres:[email protected]/postgres'
const mapper = await connect({
connectionString: pgConnectionString,
log: logger,
})
const res = await mapper.entities.page.insert({
fields: ['id', 'title' ],
inputs: [
{ title: 'Foobar' },
{ title: 'FizzBuzz' }
],
})
logger.info(res)
/**
0: {
"id": "16",
"title": "Foobar"
}
1: {
"id": "17",
"title": "FizzBuzz"
}
*/
await mapper.db.dispose()
}
main()
save
Create a new entity row in the database or update an existing one.
To update an existing entity, the id field (or equivalent primary key) must be included in the input object.
save actually behaves as an upsert, allowing both behaviours depending on the presence of the primary key field.
Options
| Name | Type | Description |
|---|---|---|
fields | Array of string | List of fields to be returned for each object |
input | Object | The single row to create/update |
Usage
'use strict'
const { connect } = require('@platformatic/sql-mapper')
const { pino } = require('pino')
const pretty = require('pino-pretty')
const logger = pino(pretty())
async function main() {
const connectionString = 'postgres://postgres:[email protected]/postgres'
const mapper = await connect({
connectionString: connectionString,
log: logger,
})
const res = await mapper.entities.page.save({
fields: ['id', 'title' ],
input: { id: 1, title: 'FizzBuzz' },
})
logger.info(res)
await mapper.db.dispose()
}
main()
delete
Delete one or more entity rows from the database, depending on the where option. Returns the data for all deleted objects.
Options
| Name | Type | Description |
|---|---|---|
fields | Array of string | List of fields to be returned for each object |
where | Object | Where clause 🔗 |
Usage
'use strict'
const { connect } = require('@platformatic/sql-mapper')
const { pino } = require('pino')
const pretty = require('pino-pretty')
const logger = pino(pretty())
async function main() {
const connectionString = 'postgres://postgres:[email protected]/postgres'
const mapper = await connect({
connectionString: connectionString,
log: logger,
})
const res = await mapper.entities.page.delete({
fields: ['id', 'title',],
where: {
id: {
lt: 4
}
},
})
logger.info(res)
await mapper.db.dispose()
}
main()
updateMany
Update one or more entity rows from the database, depending on the where option. Returns the data for all updated objects.
Options
| Name | Type | Description |
|---|---|---|
where | Object | Where clause 🔗 |
input | Object | The new values that want to update |
fields | Array of string | List of fields to be returned for each object |
Usage
'use strict'
const { connect } = require('@platformatic/sql-mapper')
const { pino } = require('pino')
const pretty = require('pino-pretty')
const logger = pino(pretty())
async function main() {
const connectionString = 'postgres://postgres:[email protected]/postgres'
const mapper = await connect({
connectionString: connectionString,
log: logger,
})
const res = await mapper.entities.page.updateMany({
fields: ['id', 'title',],
where: {
counter: {
gte: 30
}
},
input: {
title: 'Updated title'
}
})
logger.info(res)
await mapper.db.dispose()
}
main()