Repository files navigation

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

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'spackage.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.

For LoopBack 4 users, use the LoopBack 4Command-line interface to generate a DataSource with PostgreSQL connector to your LB4 application. Runlb4 datasource, it will prompt for configurations such as host, post, etc. that are required to connect to a PostgreSQL database.

After setting it up, the configuration can be found undersrc/datasources/<DataSourceName>.datasource.ts, which would look like this:

constconfig={name:'db',connector:'postgresql',url:'',host:'localhost',port:5432,user:'user',password:'pass',database:'testdb',};

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

You can also specify connection pool settings in<DataSourceName>.datasource.ts ( ordatasources.json for LB3 users). 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 ofdb.datasource.ts:

constconfig={name:'db',connector:'postgresql',url:'',host:'localhost',port:5432,user:'user',password:'pass',database:'testdb',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:

constconfig={name:'db',connector:'postgresql',url:'',host:'/var/run/postgresql/',port:5432,user:'user',password:'pass',database:'testdb',debug:true};

LoopBack allows you to specify some database settings through the model definition and/or the property definition. These definitions would be mapped to the database. Please check out the CLIlb4 model for generating LB4 models. The following is a typical LoopBack 4 model that specifies the schema, table and column details through model definition and property definitions:

@model({settings:{postgresql:{schema:'public',table:'inventory'}},})exportclassInventoryextendsEntity{  @property({type:'number',required:true,scale:0,id:1,postgresql:{columnName:'id',dataType:'integer',dataLength:null,dataPrecision:null,dataScale:0,nullable:'NO',},})id:number;  @property({type:'string',postgresql:{columnName:'name',dataType:'text',dataLength:null,dataPrecision:null,dataScale:null,nullable:'YES',},})name?:string;  @property({type:'boolean',required:true,postgresql:{columnName:'available',dataType:'boolean',dataLength:null,dataPrecision:null,dataScale:null,nullable:'NO',},})available:boolean;constructor(data?:Partial<User>){super(data);}}

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

To learn more about specifying database settings, please check the sectionData Mapping Properties.

See LoopBack 4 types (orLoopBack 3 types) fordetails on LoopBack's data types.

Besides the basic LoopBack types, as we introduced above, you can also specify the database type for model properties. It would be mapped to the database (seeData Mapping Properties). For example, we would like the propertyprice to have database typedouble precision in the corresponding table in the database, we have specify it as following:

  @property({type:'number',postgresql:{dataType:'double precision',},})price?:number;

"properties":{// .."price":{"type":"Number","postgresql":{"dataType":"double precision",}},

{% include warning.html content="Not all database types are supported for operating CRUD operations and queries with filters. For example, type Array cannot be filtered correctly, see GitHub issues:# 441 and# 342." %}

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:

  @property({type:'number',postgresql:{dataType:'double precision',},})price?:number;

You can query the nested fields with dot notation:

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

PostgreSQL supports the following PostgreSQL-specific operators:

• contains
• containedBy
• containsAny
• match

Please note extended operators are disabled by default, you must enablethem at datasource level or model level by settingallowExtendedOperators totrue.

Thecontains operator allow you to query array properties and pick onlyrows where the stored value contains all of the items specified by the query.

The operator is implemented using PostgreSQLarray operator@>.

Note The fields you are querying must be setup to use the postgresql array data type - seeDefining models above.

Assuming a model such as this:

@model({settings:{allowExtendedOperators:true,}})classPost{  @property({type:['string'],postgresql:{dataType:'varchar[]',},})categories?:string[];}

You can query the tags fields as follows:

constposts=awaitpostRepository.find({where:{{categories:{'contains':['AA']},}}});

Inverse of thecontains operator, thecontainedBy operator allow you to query array properties and pick onlyrows where the all the items in the stored value are contained by the query.

The operator is implemented using PostgreSQLarray operator<@.

Note The fields you are querying must be setup to use the postgresql array data type - seeDefining models above.

Assuming a model such as this:

@model({settings:{allowExtendedOperators:true,}})classPost{  @property({type:['string'],postgresql:{dataType:'varchar[]',},})categories?:string[];}

You can query the tags fields as follows:

constposts=awaitpostRepository.find({where:{{categories:{'containedBy':['AA']},}}});

ThecontainsAnyOf operator allow you to query array properties and pick onlyrows where the any of the items in the stored value matches any of the items in the query.

The operator is implemented using PostgreSQLarray overlap operator&&.

Note The fields you are querying must be setup to use the postgresql array data type - seeDefining models above.

Assuming a model such as this:

@model({settings:{allowExtendedOperators:true,}})classPost{  @property({type:['string'],postgresql:{dataType:'varchar[]',},})categories?:string[];}

You can query the tags fields as follows:

constposts=awaitpostRepository.find({where:{{categories:{'containsAnyOf':['AA']},}}});

Thematch operator allows you to perform afull text search using the@@ operator in PostgreSQL.

Assuming a model such as this:

@model({settings:{allowExtendedOperators:true,}})classPost{  @property({type:'string',})content:string;}

You can query the content field as follows:

constposts=awaitpostRepository.find({where:{{content:{match:'someString'},}}});

The PostgreSQL connector supportsmodel discovery that enables you to create LoopBack modelsbased on an existing database schema. Once you defined your datasource:

• LoopBack 4 users could use the commendlb4 discover to discover models.
• For LB3 users, please checkDiscovering models from relational databases.

(Seedatabase discovery API for related APIs information)

The PostgreSQL connector also supportsauto-migration that enables you to create a database schemafrom LoopBack models.

For example, based on the following model, the auto-migration method would create/alter existingcustomer table underpublic schema in the database. Tablecustomer would have two columns:name andid, whereid is also the primary key and has the default valueSERIAL as it has definition oftype: 'Number' andgenerated: true:

@model()exportclassCustomerextendsEntity{  @property({id:true,type:'Number',generated:true})id:number;  @property({type:'string'})name:string;}

By default, tables generated by the auto-migration are underpublic schema and named in lowercase.

Besides the basic model metadata, LoopBack allows you to specify part of the database schema definition via theproperty definition, which would be mapped to the database.

For example, based on the following model, after running the auto-migration script, a table namedCUSTOMER under schemamarket will be created. Moreover, you can also have different names for your property and the corresponding column. In the example, by specifying the column name, the propertyname will be mapped to thecustomer_name column. This is useful when your database has a different naming convention than LoopBack (camelCase).

@model(settings:{postgresql:{schema:'market',table:'CUSTOMER'},})exportclassCustomerextendsEntity{  @property({id:true,type:'Number',generated:true})id:number;  @property({type:'string',postgresql:{columnName:'customer_name'}})name:string;}

For how to run the script and more details:

• For LB4 users, please checkDatabase Migration
• For LB3 users, please checkCreating a database schema from models

(SeeLoopBack auto-migrate method for related APIs information)

Here are some limitations and tips:

• If you definedgenerated: true in the id property, it generates integers by default. For auto-generated uuid, seeAuto-generated id property
• Only the id property supports the auto-generation settinggenerated: true for now
• Auto-migration doesn't create foreign key constraints by default. But they can be defined through the model definition. SeeAuto-migrate with foreign keys
• Destroying models may result in errors due to foreign key integrity. First delete any related models by calling delete on models with relationships.

Foreign key constraints can be defined in the model definition.

Note: The order of table creation is important. A referenced table must exist before creating a foreign key constraint.

Define your models and the foreign key constraints as follows:

customer.model.ts:

@model()exportclassCustomerextendsEntity{  @property({id:true,type:'Number',generated:true})id:number;  @property({type:'string'})name:string;}

order.model.ts:

@model({settings:{foreignKeys:{fk_order_customerId:{name:'fk_order_customerId',entity:'Customer',entityKey:'id',foreignKey:'customerId',onDelete:'CASCADE',onUpdate:'SET NULL'},},})exportclassOrderextendsEntity{  @property({id:true,type:'Number',generated:true})id:number;  @property({type:'string'})name:string;  @property({type:'Number'})customerId:number;}

({"name":"Customer","options": {"idInjection":false  },"properties": {"id": {"type":"Number","id":1    },"name": {"type":"String","required":false    }  }},{"name":"Order","options": {"idInjection":false,"foreignKeys": {"fk_order_customerId": {"name":"fk_order_customerId","entity":"Customer","entityKey":"id","foreignKey":"customerId","onDelete":"CASCADE","onUpdate":"SET NULL"      }    }  },"properties": {"id": {"type":"Number""id":1    },"customerId": {"type":"Number"    },"description": {"type":"String","required":false    }  }})

Auto-migrate supports the automatic generation of property values for the id property. For PostgreSQL, the default id type isinteger. Thus if you havegenerated: true in the id property, it generates integers by default:

{id:true,type:'Number',required:false,generated:true// enables auto-generation}

It is common to use UUIDs as the primary key in PostgreSQL instead of integers. You can enable it with either the following ways:

• use uuid that isgenerated by your LB application by settingdefaultFn: uuid:

  @property({id:true,type:'string'defaultFn:'uuid',// generated: true,  -> not needed})  id:string;

• use PostgreSQL built-in (extension and) uuid functions:

  @property({id:true,type:'String',required:false,// settings below are neededgenerated:true,useDefaultIdType:false,postgresql:{dataType:'uuid',},})  id:string;

The setting usesuuid-ossp extension anduuid_generate_v4() function as default.

If you'd like to use other extensions and functions, you can do:

  @property({id:true,type:'String',required:false,// settings below are neededgenerated:true,useDefaultIdType:false,postgresql:{dataType:'uuid',extension:'myExtension',defaultFn:'myuuid'},})  id:string;

WARNING: It is the users' responsibility to make sure the provided extension and function are valid.

This module adopts theModule Long Term Support (LTS) policy, with the following End Of Life (EOL) dates:

Learn more about our LTS plan indocs.

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