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 Static Methods

Connector.getNestedPropertyDefinition(definition, propPath)

Helper function to get nested property definition

Arguments
Name Type Description
definition Object

Model name

propPath Array
Returns
Name Type Description
result Object

Property definition

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.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.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.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.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.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.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.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.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.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.getDefaultIdType(prop)

Get the default data type for ID

Arguments
Name Type Description
prop

Property definition Returns {Function}

sQLConnector.getDefaultSchemaName()

Get the default database schema name

Returns
Name Type Description
result string

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

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