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:

{% include code-caption.html content="/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:

{% include code-caption.html content="/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',
  };

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