Repository files navigation

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

For more information, see thedocumentation.

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

💥 In case you are making a commit for this package repository,MAKE SURE TO READ AND UNDERSTAND THE FOLLOWING TOPICS:

1. Every commit that runs on themaster branch runs through the Publish Github Package Workflow on Github Actions. Sobe sure to check if your code is well written and tested, since it'll be published if the code passes the Continuous Integration (CI) unit tests.

2. If the commit passes through the Github Actions workflow, the module will be released as a package in the Github Packages Registry. This workflow has anunderlying command thatincrements/bumps the version from the latest release based on commit messages, such as:

• If the string "BREAKING CHANGE" or "major" is found anywhere in any of the commit messages or descriptions, themajor version will be incremented (i.e. 1.X.X).

• If a commit message begins with the string "feat" or includes "minor" then theminor version will be increased (i.e. X.1.X). This works for most common commit metadata for feature additions: "feat: new API" and "feature: new API".

• All other changes will increment thepatch version (i.e. X.X.1).

3. Furthermore, the workflow has also an underlying command that deploys automatically a new release when a success test/deployment takes places. These releases can be foundhere.

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

$ npm install @letsfullstack/loopback-connector-postgresql

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 runnpm install for you.

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 settingsrequired to connect to a PostgreSQL database. It will also run thenpm install command above for you.

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

"mydb":{"name":"mydb","connector":"postgresql""host":"mydbhost","port":5432,"url":"postgres://admin:admin@mydbhost:5432/db1?ssl=false","database":"db1","password":"admin","user":"admin","ssl":false}

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

You can also specify connection pool settings indatasources.json. For instance you can specify the minimum and the maximum pool size, and the maximum pool client's idle time before closing the client.

Example ofdatasource.json:

{  "mypostgresdb": {    "host": "mydbhost",    "port": 5432,    "url": "postgres://admin:password1@mydbhost:5432/db1?ssl=false",    "database": "db1",    "password": "password1",    "name": "mypostgresdb",    "user": "admin",    "connector": "postgresql",    "min": 5,    "max": 200,    "idleTimeoutMillis": 60000,    "ssl": false  }}

Check outnode-pg-pool andnode postgres pooling example for more information.

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

The PostgreSQL connector usesnode-postgres as the driver. For moreinformation about configuration parameters, seenode-postgres documentation.

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

The model definition consists of the following properties.

For example:

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

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

| LoopBack Type | PostgreSQL Type ||---|---|---|| String/JSON/Text/Default | VARCHAR2 - Default length is 1024 || Number | INTEGER || Date | TIMESTAMP WITH TIME ZONE || Boolean | BOOLEAN |

| PostgreSQL Type | LoopBack Type ||---|---|---|| BOOLEAN | Boolean || VARCHAR/CHARACTER VARYING/CHARACTER/CHAR/TEXT | String || BYTEA | Node.jsBuffer object || SMALLINT/INTEGER/BIGINT/DECIMAL/NUMERIC/REAL/DOUBLE/SERIAL/BIGSERIAL | Number || DATE/TIMESTAMP/TIME | Date || POINT |GeoPoint |

NOTE: Thenode.js driver for postgres by default castsNumeric type as a string onGET operation. This is to avoiddata precision loss sinceNumeric types in postgres cannot be safely converted to JavaScriptNumber.

For details, see the correspondingdriver issue.

NOTE: The fields you are querying should be setup to use the JSON postgresql data type - see Defining models

Assuming a model such as this:

{"name":"Customer","properties": {"address": {"type":"object","postgresql": {"dataType":"json"      }    }  }}

You can query the nested fields with dot notation:

Customer.find({where:{'address.state':'California'},order:'address.city'})

The PostgreSQL connector supportsmodel discovery that enables you to create LoopBack modelsbased on an existing database schema using the unified database discovery API. For more information on discovery, seeDiscovering models from relational databases.

The PostgreSQL connector also supportsauto-migration that enables you to create a database schemafrom LoopBack models using theLoopBack 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 givenmodel: 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 thegenerated property of theid 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.

If you have a local or remote PostgreSQL instance and would like to use that to run the test suite, use the following command:

• Linux

POSTGRESQL_HOST=<HOST> POSTGRESQL_PORT=<PORT> POSTGRESQL_USER=<USER> POSTGRESQL_PASSWORD=<PASSWORD> POSTGRESQL_DATABASE=<DATABASE> CI=true npmtest

• Windows

SET POSTGRESQL_HOST=<HOST> SET POSTGRESQL_PORT=<PORT> SET POSTGRESQL_USER=<USER> SET POSTGRESQL_PASSWORD=<PASSWORD> SET POSTGRESQL_DATABASE=<DATABASE> SET CI=true npmtest

If you do not have a local PostgreSQL instance, you can also run the test suite with very minimal requirements.

• Assuming you haveDocker installed, run the following script which would spawn a PostgreSQL instance on your local:

source setup.sh<HOST><PORT><USER><PASSWORD><DATABASE>

where<HOST>,<PORT>,<USER>,<PASSWORD> and<DATABASE> are optional parameters. The default values arelocalhost,5432,root,pass andtestdb respectively.

• Run the test:

npmtest