loopback-connector-postgresql

The PostgreSQL Connector module for for loopback-datasource-juggler.

Please see the official documentation.

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:

• dataSource.discoverModelDefinitions([username], fn)
• dataSource.discoverSchema([owner], name, fn)

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.

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