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

loopback-connector-postgresql

PostgreSQL, is a popular open-source object-relational database. The loopback-connector-postgresql module is the PostgreSQL connector for the LoopBack framework.

For more information, see the documentation.

NOTE: The PostgreSQL connector requires PostgreSQL 8.x or 9.x.

Installation

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

$ npm install loopback-connector-postgresql --save

This installs the module from npm and adds it as a dependency to the application’s package.json file.

If you create a PostgreSQL data source using the data source generator as described below, you don’t have to do this, since the generator will run npm install for you.

Creating a data source

Use the Data source generator to add a PostgreSQL data source to your application.
The generator will prompt for the database server hostname, port, and other settings required to connect to a PostgreSQL database. It will also run the npm install command above for you.

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

/server/datasources.json

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

  "host": "mydbhost",
      "port": 5432,
      "url": "postgres://admin:admin@myhost/db",
      "database": "db1",
      "password": "admin",
      "user": "admin"
}

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 thehost,port,user,password, anddatabaseproperties. For example:'postgres://test:mypassword@localhost:5432/dev'.
username String Username to connect to database

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

The PostgreSQL connector uses node-postgres as the driver. For more information about configuration parameters, see node-postgres documentation.

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

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 and auto-migration

Model discovery

The PostgreSQL connector supports model discovery that enables you to create LoopBack models based on an existing database schema using the unified database discovery API. For more information on discovery, see Discovering models from relational databases.

Auto-migratiion

The PostgreSQL connector also supports auto-migration that enables you to create a database schema from LoopBack models using the LoopBack automigrate method.

For more information on auto-migration, see Creating a database schema from models for more information.

LoopBack PostgreSQL connector creates the following schema objects for a given model: a table, for example, PRODUCT under the ‘public’ schema within the database.

The auto-migrate method:

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

Destroying models may result in errors due to foreign key integrity. First delete any related models by calling delete on models with relationships.

Running tests

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

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

If you wish to run the tests using your own test database instance,

Set up the database

  1. Go to pgAdmin.
    By default, the local database is one of the servers under Server Groups > Servers.
  2. Under Login Roles, add a user called strongloop.

Change configuration for database connection

In test\init.js, change the value of config to be pointing to the local database. For example,

  var config = {
    host: 'localhost',
    port: '5432',
    database:'strongloop',
    username: 'postgres',
    password: 'postgres',
  };
  1. (Linux Only) CI=true PGHOST=localhost PGPORT=<pgport> PGDATABASE=<dbname> PGUSER=<username> PGPASSWORD=<password> npm test

Troubleshooting

When running npm test, it runs the pretest.js which eventually runs schema.sql to set up the database and tables. If there is problem, you can run the schema.sql manually. To do this:

  1. Go to SQL Shell (psql)
  2. Run: ``` \i «file path»

For example on Windows, \i c:\somepath\test\schema.sql ```

Tags: connectors