Page Contents

loopback-connector-postgresql

The official PostgreSQL connector for the LoopBack framework.

Connector settings

The connector can be configured using the following settings from the data source.

url: The URL to the database, such as ‘postgres://test:mypassword@localhost:5432/dev’
host or hostname (default to ‘localhost’): The host name or ip address of the PostgreSQL DB server
port (default to 5432): The port number of the PostgreSQL DB server
username or user: The user name to connect to the PostgreSQL DB
password: The password
database: The PostgreSQL database name
debug (default to false)

NOTE: By default, the ‘public’ schema is used for all tables.

The PostgreSQL connector uses node-postgres as the driver. See more information about configuration parameters, check https://github.com/brianc/node-postgres/wiki/Client#constructors.

Discovering Models

PostgreSQL data sources allow you to discover model definition information from existing postgresql databases. See the following APIs:

Model definition for PostgreSQL

The model definition consists of the following properties:

name: Name of the model, by default, it’s the camel case of the table
options: Model level operations and mapping to PostgreSQL schema/table
properties: Property definitions, including mapping to PostgreSQL column

    {"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"
        }
      }
    }}

Type Mapping

Number
Boolean
String
Object
Date
Array
Buffer

JSON to PostgreSQL Types

String JSON Text default: VARCHAR, default length is 1024
Number: INTEGER
Date: TIMESTAMP WITH TIME ZONE
Timestamp: TIMESTAMP WITH TIME ZONE
Boolean: BOOLEAN

PostgreSQL Types to JSON

BOOLEAN: Boolean
VARCHAR CHARACTER VARYING CHARACTER CHAR TEXT: String
BYTEA: Binary;

SMALLINT

INTEGER

BIGINT

DECIMAL

NUMERIC

REAL

DOUBLE

SERIAL

BIGSERIAL: Number

DATE TIMESTAMP TIME: Date
POINT: GeoPoint

Destroying Models

Destroying models may result in errors due to foreign key integrity. Make sure to delete any related models first before calling delete on model’s with relationships.

Auto Migrate / Auto Update

After making changes to your model properties you must call Model.automigrate() or Model.autoupdate(). Only call Model.automigrate() on new models as it will drop existing tables.

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

A table, for example, PRODUCT under the ‘public’ schema within the database

Running tests

The tests in this repository are mainly integration tests, meaning you will need to run them using our preconfigured test server.

Ask a core developer for instructions on how to set up test server credentials on your machine
npm test