Build a SQL connector

Use the loopback-connector module to build a LoopBack connector to back-end data sources such as databases or web services. There are many existing connectors for the most popular data sources; see:

To build a new data source connector that doesn't yet exist, see Building a connector.

Base Connector

Class: Connector()

Base class for LoopBack connector. This is more a collection of useful methods for connectors than a super class

Class: Connector Instance Methods

connector.define(modelDefinition)

Hook to be called by DataSource for defining a model

Arguments
Name Type Description
modelDefinition Object

The model definition

connector.defineProperty(model, propertyName, propertyDefinition)

Hook to be called by DataSource for defining a model property

Arguments
Name Type Description
model String

The model name

propertyName String

The property name

propertyDefinition Object

The object for property definition

connector.disconnect([cb])

Disconnect from the connector

Arguments
Name Type Description
[cb] Function

Callback function

connector.execute(command, [params], [options], [callback])

Execute a command with given parameters

Arguments
Name Type Description
command String or Object

The command such as SQL

[params] Array

An array of parameter values

[options] Object

Options object

[callback] Function

The callback function

connector.generateUniqueId(modelName)

Generate random id. Each data source model must override this method.

Arguments
Name Type Description
modelName String

Model name

Returns
Name Type Description
result

Data type varies from model to model,

connector.getConnectorSpecificSettings(modelName)

Get connector specific settings for a given model, for example,

{
  "postgresql": {
    "schema": "xyz"
  }
}
Arguments
Name Type Description
modelName String

Model name

Returns
Name Type Description
result Object

The connector specific settings

connector.getDataAccessObject()

Return the DataAccessObject interface implemented by the connector

Returns
Name Type Description
result Object

An object containing all methods implemented by the connector that can be mixed into the model class. It should be considered as the interface.

connector.getDataSource(model)

Look up the data source by model name

Arguments
Name Type Description
model String

The model name

Returns
Name Type Description
result DataSource

The data source

connector.getIdValue(model, data)

Get the id value for the given model

Arguments
Name Type Description
model String

The model name

data Object

The model instance data

Returns
Name Type Description
result

The id value

connector.getMetadata()

Get the metadata for the connector

Class Properties
Name Type Description
type String

The type for the backend

defaultIdType Function

The default id type

[isRelational] Boolean

If the connector represents a relational database

schemaForSettings Object

The schema for settings object

Returns
Name Type Description
result Object

The metadata object

connector.getModelDefinition(modelName)

Get the model definition by name

Arguments
Name Type Description
modelName String

The model name

Returns
Name Type Description
result ModelDefinition

The model definition

connector.getPropertyDefinition(modelName, propName)

Get model property definition

Arguments
Name Type Description
modelName String

Model name

propName String

Property name

Returns
Name Type Description
result Object

Property definition

connector.getTypes()

Get types associated with the connector

Returns
Name Type Description
result Array.<String>

The types for the connector

connector.id(model, prop)

Get the id index (sequence number, starting from 1)

Arguments
Name Type Description
model String

The model name

prop String

The property name

Returns
Name Type Description
result Number

The id index, undefined if the property is not part of the primary key

connector.idName(model)

Get the id property name

Arguments
Name Type Description
model String

The model name

Returns
Name Type Description
result String

The id property name

connector.idNames(model)

Get the id property names

Arguments
Name Type Description
model String

The model name

Returns
Name Type Description
result Array.<String>

The id property names

connector.isNullable(prop)

Test if a property is nullable

Arguments
Name Type Description
prop Object

The property definition

Returns
Name Type Description
result boolean

true if nullable

connector.isRelational()

Check if the connector is for a relational DB

Returns
Name Type Description
result Boolean

true for relational DB

connector.setIdValue(model, data, value)

Set the id value for the given model

Arguments
Name Type Description
model String

The model name

data Object

The model instance data

value

The id value

SQL Connector

Class: SQLConnector()

Base class for connectors that connect to relational databases using SQL

Class: SQLConnector Instance Methods

sQLConnector._replace(model, where, data, options, cb)

Replace all instances that match the where clause with the given data

Arguments
Name Type Description
model String

The model name

where Object

The where object

data Object

The property/value object representing changes to be made

options Object

The options object

cb Function

The callback function

sQLConnector.all(model, filter, [cb])

Find matching model instances by the filter

Please also note the name all is confusing. Model.find is to find all matching instances while Model.findById is to find an instance by id. On the other hand, Connector.prototype.all implements Model.find while Connector.prototype.find implements Model.findById due to the bad naming convention we inherited from juggling-db.

Arguments
Name Type Description
model String

The model name

filter Object

The filter

[cb] Function

The cb function

sQLConnector.alterTable(model, fields, indexes, cb)

Alters a table

Arguments
Name Type Description
model String

The model name

fields Object

Fields of the table

indexes Object

Indexes of the table

cb Function

The callback function

sQLConnector.applyPagination(model, stmt, filter)

Build a new SQL statement with pagination support by wrapping the given sql

Arguments
Name Type Description
model String

The model name

stmt ParameterizedSQL

The sql statement

filter Object

The filter object from the query

sQLConnector.automigrate([models], [cb])

Recreate the tables for the given models

Arguments
Name Type Description
[models] String or Array.<String>

A model name or an array of model names, if not present, apply to all models defined in the connector

[cb] Function

The callback function

sQLConnector.autoupdate([models], [cb])

Perform autoupdate for the given models

Arguments
Name Type Description
[models] Array.<String>

A model name or an array of model names. If not present, apply to all models

[cb] Function

The callback function

sQLConnector.buildColumnNames(model, filter)

Build a list of escaped column names for the given model and fields filter

Arguments
Name Type Description
model string

Model name

filter object

The filter object

Returns
Name Type Description
result string

Comma separated string of escaped column names

sQLConnector.buildDelete(model, where, options)

Build the DELETE FROM SQL statement

Arguments
Name Type Description
model String

The model name

where Object

The where object

options Object

Options object

Returns
Name Type Description
result ParameterizedSQL

The SQL DELETE FROM statement

sQLConnector.buildExpression(columnName, operator, columnValue, propertyValue)

Build SQL expression

Arguments
Name Type Description
columnName String

Escaped column name

operator String

SQL operator

columnValue

Column value

propertyValue

Property value

Returns
Name Type Description
result ParameterizedSQL

The SQL expression

sQLConnector.buildFields(model, data, excludeIds)

Build an array of fields for the database operation

Arguments
Name Type Description
model String

Model name

data Object

Model data object

excludeIds Boolean

Exclude id properties or not, default to false

Returns
Name Type Description
result [object Object]

sQLConnector.buildFieldsForReplace(model, data, excludeIds)

Build the SET clause for database replace through update query.

Arguments
Name Type Description
model String

Model name.

data Object

The model data object.

excludeIds Boolean

Exclude id properties or not, default to true.

Returns
Name Type Description
result string

The list of fields for update query.

sQLConnector.buildFieldsForUpdate(model, data, excludeIds)

Build the SET clause for database update.

Arguments
Name Type Description
model String

Model name.

data Object

The model data object.

excludeIds Boolean

Exclude id properties or not, default to true.

Returns
Name Type Description
result string

The list of fields for update query.

sQLConnector.buildInsert(model, data, options)

Build INSERT SQL statement

Arguments
Name Type Description
model String

The model name

data Object

The model data object

options Object

The options object

Returns
Name Type Description
result string

The INSERT SQL statement

sQLConnector.buildInsertDefaultValues(model, data, options)

Build the clause for default values if the fields is empty

Arguments
Name Type Description
model String

The model name

data Object

The model data object

options Object

Options object

Returns
Name Type Description
result string

'DEFAULT VALUES'

sQLConnector.buildInsertInto(model, fields, options)

Build the the INSERT INTO statement

Arguments
Name Type Description
model String

The model name

fields Object

Fields to be inserted

options Object

Options object

Returns
Name Type Description
result ParameterizedSQL

sQLConnector.buildInsertReturning(model, data, options)

Build the clause to return id values after insert

Arguments
Name Type Description
model String

The model name

data Object

The model data object

options Object

Options object

Returns
Name Type Description
result string

sQLConnector.buildOrderBy(model, order)

Build the ORDER BY clause

Arguments
Name Type Description
model string

Model name

order Array.<string>

An array of sorting criteria

Returns
Name Type Description
result string

The ORDER BY clause

sQLConnector.buildPropertyType(columnDefinition, options)

Map the property type from database to loopback

Arguments
Name Type Description
columnDefinition Object

The columnDefinition of the table/schema

options Object

The options for the connector

sQLConnector.buildQuerySchemas(options)

Build sql for listing schemas

Arguments
Name Type Description
options Object

Options for discoverDatabaseSchemas

sQLConnector.buildReplace(model, where, data, options, cb)

Build the UPDATE statement for replacing

Arguments
Name Type Description
model String

The model name

where Object

The where object

data Object

The data to be changed

options Object

The options object

cb Function

The callback function

Returns
Name Type Description
result ParameterizedSQL

The UPDATE SQL statement for replacing fields

sQLConnector.buildReplaceFields(model, data, excludeIds)

Build an array of fields for the replace database operation

Arguments
Name Type Description
model String

Model name

data Object

Model data object

excludeIds Boolean

Exclude id properties or not, default to false

Returns
Name Type Description
result [object Object]

sQLConnector.buildSelect(model, filter, options)

Build a SQL SELECT statement

Arguments
Name Type Description
model String

Model name

filter Object

Filter object

options Object

Options object

Returns
Name Type Description
result ParameterizedSQL

Statement object {sql: ..., params: ...}

sQLConnector.buildUpdate(model, where, data, options, cb)

Build the UPDATE statement

Arguments
Name Type Description
model String

The model name

where Object

The where object

data Object

The data to be changed

options Object

The options object

cb Function

The callback function

Returns
Name Type Description
result ParameterizedSQL

The UPDATE SQL statement

sQLConnector.buildWhere(model, where)

Build the SQL WHERE clause for the where object

Arguments
Name Type Description
model string

Model name

where object

An object for the where conditions

Returns
Name Type Description
result ParameterizedSQL

The SQL WHERE clause

sQLConnector.column(model, property)

Get the column name for the given model property. The column name can be customized at the model property definition level as column or columnName. For example,

"name": {
  "type": "string",
  "mysql": {
    "column": "NAME"
  }
}
Arguments
Name Type Description
model String

The model name

property String

The property name

Returns
Name Type Description
result String

The column name

sQLConnector.columnEscaped(model, property)

Get the escaped column name for a given model property

Arguments
Name Type Description
model String

The model name

property String

The property name

Returns
Name Type Description
result String

The escaped column name

sQLConnector.columnMetadata(model, property)

Get the column metadata for the given model property

Arguments
Name Type Description
model String

The model name

property String

The property name

Returns
Name Type Description
result Object

The column metadata

sQLConnector.count(model, where, options, cb)

Count all model instances by the where filter

Arguments
Name Type Description
model String

The model name

where Object

The where object

options Object

The options object

cb Function

The callback function

sQLConnector.create(model, data, options, [callback])

Create the data model in MySQL

Arguments
Name Type Description
model String

The model name

data Object

The model instance data

options Object

Options object

[callback] Function

The callback function

sQLConnector.createTable(model, [cb])

Create the table for the given model

Arguments
Name Type Description
model String

The model name

[cb] Function

The callback function

sQLConnector.destroyAll(model, where, options, cb)

Delete all matching model instances

Arguments
Name Type Description
model String

The model name

where Object

The where object

options Object

The options object

cb Function

The callback function

sQLConnector.discoverDatabaseSchemas(options, [cb])

Discover database schemas

Arguments
Name Type Description
options Object

Options for discovery

[cb] Function

The callback function

sQLConnector.discoverExportedForeignKeys(table, options, [cb])

Discover foreign keys that reference to the primary key of this table

Arguments
Name Type Description
table String

The table name

options Object

The options for discovery

[cb] Function

The callback function

sQLConnector.discoverForeignKeys(table, options, [cb])

Discover foreign keys for a given table

Arguments
Name Type Description
table String

The table name

options Object

The options for discovery

[cb] Function

The callback function

sQLConnector.discoverModelDefinitions(options, [cb])

Discover model definitions

Arguments
Name Type Description
options Object

Options for discovery

[cb] Function

The callback function

sQLConnector.discoverModelProperties(table, options, [cb])

Discover model properties from a table

Arguments
Name Type Description
table String

The table name

options Object

The options for discovery

[cb] Function

The callback function

sQLConnector.discoverPrimaryKeys(table, options, [cb])

Discover primary keys for a given table

Arguments
Name Type Description
table String

The table name

options Object

The options for discovery

[cb] Function

The callback function

sQLConnector.dropTable(model, [cb])

Drop the table for the given model from the database

Arguments
Name Type Description
model String

The model name

[cb] Function

The callback function

sQLConnector.escapeName(name)

Escape the name for the underlying database

Arguments
Name Type Description
name String

The name

Returns
Name Type Description
result String

An escaped name for SQL

sQLConnector.escapeValue(value)

Escape the name for the underlying database

Arguments
Name Type Description
value String

The value to be escaped

Returns
Name Type Description
result

An escaped value for SQL

sQLConnector.execute(sql, An, [options], [callback])

Execute a SQL statement with given parameters.

Arguments
Name Type Description
sql String

The SQL statement

An Array

array of parameter values

[options] Object

Options object

[callback] Function

The callback function

sQLConnector.executeSQL(sql, [params], [options], [callback])

Execute a SQL statement with given parameters

Arguments
Name Type Description
sql String

The SQL statement

[params] Array

An array of parameter values

[options] Object

Options object

[callback] Function

The callback function

sQLConnector.exists(model, id, options, cb)

Check if a model instance exists for the given id value

Arguments
Name Type Description
model String

The model name

id

The id value

options Object

Options object

cb Function

The callback function

sQLConnector.fromColumnValue(propertyDef, {*) value Column value)

Convert the data from database column to model property

Arguments
Name Type Description
propertyDef object

Model property definition

{*) value Column value
Returns
Name Type Description
result

Model property value

sQLConnector.fromRow(model, rowData)

Transform the row data into a model data object

Arguments
Name Type Description
model string

Model name

rowData object

An object representing the row data from DB

Returns
Name Type Description
result object

Model data object

sQLConnector.getCountForAffectedRows(model, info)

Parse the result for SQL UPDATE/DELETE/INSERT for the number of rows affected

Arguments
Name Type Description
model String

Model name

info Object

Status object

Returns
Name Type Description
result Number

Number of rows affected

sQLConnector.getDefaultIdType(prop)

Get the default data type for ID

Arguments
Name Type Description
prop

Property definition Returns {Function}

sQLConnector.getDefaultSchema(options)

Discover default schema of a database

Arguments
Name Type Description
options Object

The options for discovery

sQLConnector.getDefaultSchemaName()

Get the default database schema name

Returns
Name Type Description
result string

The default schema name, such as 'public' or 'dbo'

sQLConnector.getInsertedId(model, info)

Parse the result for SQL INSERT for newly inserted id

Arguments
Name Type Description
model String

Model name

info Object

The status object from driver

Returns
Name Type Description
result

The inserted id value

sQLConnector.getPlaceholderForIdentifier(key)

Get the place holder in SQL for identifiers, such as ??

Arguments
Name Type Description
key String

Optional key, such as 1 or id

Returns
Name Type Description
result String

The place holder

sQLConnector.getPlaceholderForValue(key)

Get the place holder in SQL for values, such as :1 or ?

Arguments
Name Type Description
key String

Optional key, such as 1 or id

Returns
Name Type Description
result String

The place holder

sQLConnector.getTableStatus(model, cb)

Get the status of a table

Arguments
Name Type Description
model String

The model name

cb Function

The callback function

sQLConnector.getTypes()

Get types associated with the connector Returns {String[]} The types for the connector

sQLConnector.idColumn(model)

Get the id column name

Arguments
Name Type Description
model String

The model name

Returns
Name Type Description
result String

The id column name

sQLConnector.idColumnEscaped(model)

Get the escaped id column name

Arguments
Name Type Description
model String

The model name

Returns
Name Type Description
result String

the escaped id column name

sQLConnector.idColumnValue(model, idValue)

Convert the id value to the form required by database column

Arguments
Name Type Description
model String

The model name

idValue

The id property value

Returns
Name Type Description
result

The escaped id column value

sQLConnector.invokeSuper(methodName)

Invoke a prototype method on the super class

Arguments
Name Type Description
methodName String

Method name

sQLConnector.isActual([models], [cb])

Check if the models exist

Arguments
Name Type Description
[models] Array.<String>

A model name or an array of model names. If not present, apply to all models

[cb] Function

The callback function

sQLConnector.paginateSQL(sql, orderBy, options)

Paginate the results returned from database

Arguments
Name Type Description
sql String

The sql to execute

orderBy Object

The property name by which results are ordered

options Object

Options for discoverDatabaseSchemas

sQLConnector.parameterize(ps)

Replace ? with connector specific placeholders. For example,

{sql: 'SELECT * FROM CUSTOMER WHERE NAME=?', params: ['John']}
==>
{sql: 'SELECT * FROM CUSTOMER WHERE NAME=:1', params: ['John']}

LIMITATION: We don't handle the ? inside escaped values, for example, SELECT * FROM CUSTOMER WHERE NAME='J?hn' will not be parameterized correctly.

Arguments
Name Type Description
ps ParameterizedSQL or Object

Parameterized SQL

Returns
Name Type Description
result ParameterizedSQL

Parameterized SQL with the connector specific placeholders

sQLConnector.propertyName(model, column)

Get the corresponding property name for the given column name

Arguments
Name Type Description
model String

The model name

column String

The column name

Returns
Name Type Description
result String

The property name for a given column

sQLConnector.save(model, data, options, cb)

Save the model instance into the database

Arguments
Name Type Description
model String

The model name

data Object

The model instance data

options Object

Options object

cb Function

The callback function

sQLConnector.schema(model)

Get the database schema name for the given model. The schema name can be customized at model settings or connector configuration level as schema or schemaName. For example,

"Customer": {
  "name": "Customer",
  "mysql": {
    "schema": "MYDB",
    "table": "CUSTOMER"
  }
}
Arguments
Name Type Description
model String

The model name

Returns
Name Type Description
result String

The database schema name

sQLConnector.serializeObject(obj)

Serialize an object into JSON string or other primitive types so that it can be saved into a RDB column

Arguments
Name Type Description
obj Object

The object value

Returns
Name Type Description
result

sQLConnector.setDefaultOptions(options)

Set default options for the connector

Arguments
Name Type Description
options Object

The options for discovery

sQLConnector.setNullableProperty(property)

Set the nullable value for the property

Arguments
Name Type Description
property Object

The property to set nullable

sQLConnector.showFields(model, cb)

Get fields from a table

Arguments
Name Type Description
model String

The model name

cb Function

The callback function

sQLConnector.showIndexes(model, cb)

Get indexes from a table

Arguments
Name Type Description
model String

The model name

cb Function

The callback function

sQLConnector.table(model)

Get the table name for the given model. The table name can be customized at model settings as table or tableName. For example,

"Customer": {
  "name": "Customer",
  "mysql": {
    "table": "CUSTOMER"
  }
}

Returns the table name (String).

Arguments
Name Type Description
model String

The model name

sQLConnector.tableEscaped(model)

Get the escaped table name

Arguments
Name Type Description
model String

The model name

Returns
Name Type Description
result String

the escaped table name

sQLConnector.toColumnValue(propertyDef, value)

Converts a model property value into the form required by the database column. The result should be one of following forms:

  • {sql: "point(?,?)", params:[10,20]}
  • {sql: "'John'", params: []}
  • "John"
Arguments
Name Type Description
propertyDef Object

Model property definition

value

Model property value

Returns
Name Type Description
result ParameterizedSQL

Database column value.

sQLConnector.update(model, where, data, options, cb)

Update all instances that match the where clause with the given data

Arguments
Name Type Description
model String

The model name

where Object

The where object

data Object

The property/value object representing changes to be made

options Object

The options object

cb Function

The callback function

Class: ParameterizedSQL(sql, params)

A class for parameterized SQL clauses

Arguments
Name Type Description
sql String or Object

The SQL clause. If the value is a string, treat it as the template using ? as the placeholder, for example, (?,?). If the value is an object, treat it as {sql: '...', params: [...]}

params Array

An array of parameter values. The length should match the number of placeholders in the template

Returns
Name Type Description
result ParameterizedSQL

A new instance of ParameterizedSQL

Class: ParameterizedSQL Static Methods

ParameterizedSQL.append(currentStmt, stmt, [separator])

Append the statement into the current statement

Arguments
Name Type Description
currentStmt Object

The current SQL statement

stmt Object

The statement to be appended

[separator] String

Separator, default to

Returns
Name Type Description
result

The merged statement

ParameterizedSQL.join(sqls, [separator])

Join multiple parameterized SQLs into one

Arguments
Name Type Description
sqls Array.<Object>

An array of parameterized SQLs

[separator] String

Separator, default to

Returns
Name Type Description
result ParameterizedSQL

Class: ParameterizedSQL Instance Methods

parameterizedSQL.merge(ps, [separator])

Merge the parameterized sqls into the current instance

Arguments
Name Type Description
ps Object or Array.<Object>

A parametered SQL or an array of parameterized SQLs

[separator] String

Separator, default to

Returns
Name Type Description
result ParameterizedSQL

The current instance