The PostgreSQL connector enables LoopBack applications to connect to PostgreSQL data sources.
Page Contents

Installation

In your application root directory, enter this command to install the connector:

$ npm install loopback-connector-postgresql --save

This will install the module from npm and add it as a dependency to the application’s package.json file.

Creating a data source

Use the Data source generator to add a PostgreSQL data source to your application. 

The entry in the application’s server/datasources.json will look like this:

/server/datasources.json

"mydb": {
  "name": "mydb",
  "connector": "postgresql"
}

Edit datasources.json to add other properties that enable you to connect the data source to a PostgreSQL database.

Properties

Property Type Description
connector String

Connector name, either "loopback-connector-postgresql" or "postgresql"
database String Database name
debug Boolean If true, turn on verbose mode to debug database queries and lifecycle.
host String Database host name
password String Password to connect to database
port Number Database TCP port
url String Use instead of the host, port, user, password, and database properties.  For example: 'postgres://test:mypassword@localhost:5432/dev'.
username String Username to connect to database
ssl Boolean or Object Connect to database using SSL. Set to true for username/password authentication. You can also add a TLS object to establish a cert based connection (you must also omit the password property). See TLS (SSL) of nodejs for more info.

Connecting to UNIX domain socket

A common PostgreSQL configuration is to connect to the UNIX domain socket /var/run/postgresql/.s.PGSQL.5432 instead of using the TCP/IP port. For example:

{
  "postgres": {
    "host": "/var/run/postgresql/",
    "port": "5432",
    "database": "dbname",
    "username": "dbuser",
    "password": "dbpassword",
    "name": "postgres",
    "debug": true,
    "connector": "postgresql"
  }
}

Defining models

The model definition consists of the following properties.

Property Default Description
name Camel-case of the database table name Name of the model.
options N/A Model level operations and mapping to PostgreSQL schema/table
properties N/A Property definitions, including mapping to PostgreSQL column

For example:

/common/models/model.json

{
  "name": "Inventory",
  "options": {
    "idInjection": false,
    "postgresql": {
      "schema": "strongloop",
      "table": "inventory"
    }
  },
  "properties": {
    "id": {
      "type": "String",
      "required": false,
      "length": 64,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "id",
        "dataType": "character varying",
        "dataLength": 64,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "NO"
      }
    },
    "productId": {
      "type": "String",
      "required": false,
      "length": 20,
      "precision": null,
      "scale": null,
      "id": 1,
      "postgresql": {
        "columnName": "product_id",
        "dataType": "character varying",
        "dataLength": 20,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "locationId": {
      "type": "String",
      "required": false,
      "length": 20,
      "precision": null,
      "scale": null,
      "id": 1,
      "postgresql": {
        "columnName": "location_id",
        "dataType": "character varying",
        "dataLength": 20,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "available": {
      "type": "Number",
      "required": false,
      "length": null,
      "precision": 32,
      "scale": 0,
      "postgresql": {
        "columnName": "available",
        "dataType": "integer",
        "dataLength": null,
        "dataPrecision": 32,
        "dataScale": 0,
        "nullable": "YES"
      }
    },
    "total": {
      "type": "Number",
      "required": false,
      "length": null,
      "precision": 32,
      "scale": 0,
      "postgresql": {
        "columnName": "total",
        "dataType": "integer",
        "dataLength": null,
        "dataPrecision": 32,
        "dataScale": 0,
        "nullable": "YES"
      }
    }
  }
}

Destroying models

If you destroy models,  you may get errors due to foreign key integrity. Make sure to delete any related models first before calling delete() on models that have relationships.

Auto-migrate and auto-update

REVIEW COMMENT from Raymond via Rand

Removed temporarily. Is any of this still true?

The PostgreSQL connector creates the following schema objects for a given model:

  • A table, for example, PRODUCT.
  • A sequence for the primary key, for example, PRODUCT_ID_SEQUENCE.
  • A trigger to generate the primary key from the sequnce, for example, PRODUCT_ID_TRIGGER.

After making changes to your model properties, you must call Model.automigrate() or Model.autoupdate(). Call Model.automigrate() only on new models since it will drop existing tables. These methods will

  • Define a primary key for the properties whose id property is true (or a positive number).
  • Create a column with ‘SERIAL’ type if the generated property of the id property is true.

See Creating a database schema from models for more information.

Type mapping

See LoopBack types for details on LoopBack’s data types.

LoopBack to PostgreSQL types

LoopBack Type PostgreSQL Type
String
JSON
Text
Default

VARCHAR2

Default length is 1024
Number INTEGER
Date TIMESTAMP WITH TIME ZONE
Boolean BOOLEAN

PostgreSQL types to LoopBack

PostgreSQL Type LoopBack Type
BOOLEAN Boolean

VARCHAR
CHARACTER VARYING
CHARACTER
CHAR
TEXT

 String
BYTEA Node.js Buffer object
SMALLINT
INTEGER
BIGINT
DECIMAL
NUMERIC
REAL
DOUBLE
SERIAL
BIGSERIAL		 Number
DATE
TIMESTAMP
TIME 		 Date
POINT GeoPoint

Discovery methods

LoopBack provides a unified API to create models based on schema and tables in relational databases. The same discovery API is available when using connectors for Oracle, MySQL, PostgreSQL, and SQL Server. For more information, see Database discovery API.

Tags: connectors