StrongLoop is pleased to announce the 1.0.0 release of the LoopBack PostgreSQL Connector, that enables LoopBack powered applications to access PostgreSQL databases. The PostgreSQL connector is a new member of the LoopBack connector family. It supports the same model APIs as other LoopBack database connectors for create, read, update, and delete (CRUD) operations, synchronization, and discovery.
What’s LoopBack? It’s an open-source API server for Node.js applications. It enables mobile apps to connect to enterprise data through models that use pluggable data sources and connectors. Connectors provide connectivity to backend systems such as databases. Models are in turn exposed to mobile devices through REST APIs and client SDKs.
This blog will explore this new PostgreSQL connector using a simple example.
Prerequisites
First, make sure you have the StrongLoop command-line tool, slc, installed.
$ npm install -g strong-cli
Get example application
The LoopBack database example application is available at https://github.com/strongloop-community/loopback-example-database. The project contains examples to demonstrate five LoopBack connectors:
You can switch between the databases by updating datasources.json and models.json. No code change is required. In the following steps, you’ll use PostgreSQL as the example. By default, the application connects to a PostgreSQL server running on demo.strongloop.com.
$ git clone git@github.com:strongloop-community/loopback-example-database.git
$ cd loopback-example-database
$ git checkout postgresql
The last command ensures that you’re connecting to the PostgreSQL server instead of Oracle, MongoDB, or MySQL.
Now install dependencies:
$ npm install
Run the example application
Run the sample application as usual:
$ node .
Now load the API explorer in your browser at http://0.0.0.0:3000/explorer. You can check out the REST API that the application exposes.
Click on the GET endpoint labeled Find all instances of the model matched by filter from the data source, then click “Try it out!”
You’ll see that there are two account records in the database, returned in JSON format.
Create a new LoopBack application
Now you’ll create a new application similar to the example using the slc command:
$ slc lb project my-postgres-example
$ cd my-postgres-example
$ slc lb datasource accountDB --connector postgresql
$ slc lb model account -i --data-source accountDB
Follow the prompts to create your model with the following properties:
The properties will be saved to models.json.
Install dependencies
Now add the loopback-connector-postgresql module and install the dependencies.
$ npm install loopback-connector-postgresql --save
NOTE: Don’t worry if you see errors like this:
...
/bin/sh: pg_config: command not found
gyp: Call to 'pg_config --libdir' returned exit status 127.
gyp ERR! configure error
...
The PostgreSQL connector looks for the native PostgreSQL client, but if it’s not installed, it falls back to a pure JavaScript solution so you can ignore these errors.
Configure the data source
The generated data source uses the memory connector by default. To connect to PostgreSQL instead, edit datasources.json and look for the data source configuration for PostgreSQL:
"accountDB": {
"connector": "postgresql"
}
Replace the above lines with:
"accountDB": {
"connector": "postgresql",
"host": "demo.strongloop.com",
"port": 5432,
"database": "demo",
"username": "demo",
"password": "L00pBack"
}
This will connect the application to the PostgreSQL server running on demo.strongloop.com.
Create the table and add test data
Now you have an account model in LoopBack, do you need to run some SQL statements to create the corresponding table in the PostgreSQL database?
Sure, but LoopBack provides Node.js APIs to do so automatically. To make things easy, use some pre-written code in loopback-example-database/create-test-data.js.
Copy this file into your application directory. Then enter this command to run the script:
$ node create-test-data
Look at the code:
dataSource.automigrate('account', function (err) {
accounts.forEach(function(act) {
Account.create(act, function(err, result) {
if(!err) {
console.log('Record created:', result);
}
});
});
});
dataSource.automigrate() creates or re-creates the table in PostgreSQL based on the model definition for account. Please note this function will drop the table if it exists and your data will be lost. If you need to keep existing data, use dataSource.autoupdate() instead.
Account.create() inserts two sample records to the PostgreSQL table.
Run the application
$ node .
Now open your browser. To view all accounts, go to http://localhost:3000/api/accounts. This is the JSON response:
[
{
"email": "[email protected]",
"level": 10,
"created": "2013-10-15T21:34:50.000Z",
"modified": "2013-10-15T21:34:50.000Z",
"id": 1
},
{
"email": "[email protected]",
"level": 20,
"created": "2013-10-15T21:34:50.000Z",
"modified": "2013-10-15T21:34:50.000Z",
"id": 2
}
]
To get an account by ID, go to http://localhost:3000/api/accounts/1.
{
"email": "[email protected]",
"level": 10,
"created": "2013-10-15T21:34:50.000Z",
"modified": "2013-10-15T21:34:50.000Z",
"id": "1"
}
You can explore all the REST APIs at: http://0.0.0.0:3000/explorer/<code></code>
Use the discovery to create model
Now you have the account table in PostgreSQL, you can use the LoopBack discovery APIs to create the LoopBack model from the database. Copy the script from loopback-example-database/discover.js.
Run the script:
$ node discover
First, you’ll see output as the script creates the account model in JSON format.
{
"name": "Account",
"options": {
"idInjection": false,
"postgresql": {
"schema": "public",
"table": "account"
}
},
"properties": {
"email": {
"type": "String",
"required": false,
"length": 1073741824,
"precision": null,
"scale": null,
"postgresql": {
"columnName": "email",
"dataType": "character varying",
"dataLength": 1073741824,
"dataPrecision": null,
"dataScale": null,
"nullable": "YES"
}
},
...,
"id": {
"type": "Number",
"required": false,
"length": null,
"precision": 32,
"scale": 0,
"postgresql": {
"columnName": "id",
"dataType": "integer",
"dataLength": null,
"dataPrecision": 32,
"dataScale": 0,
"nullable": "NO"
}
}
}
}
Then it uses the model to find all accounts from PostgreSQL:
[ { id: 1,
email: '[email protected]',
level: 10,
created: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT),
modified: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT) },
{ id: 2,
email: '[email protected]',
level: 20,
created: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT),
modified: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT) } ]
Examine the code in discover.js too: it’s surprisingly simple! The dataSource.discoverSchema() method returns the model definition based on the account table schema. dataSource.discoverAndBuildModels() goes one step further by making the model classes available to perform CRUD operations.
dataSource.discoverSchema('account', {schema: 'demo'}, function (err, schema) {
console.log(JSON.stringify(schema, null, ' '));
});
dataSource.discoverAndBuildModels('account', {schema: 'demo'}, function (err, models) {
models.Account.find(function (err, act) {
if (err) {
console.error(err);
} else {
console.log(act);
}
});
});
As you have seen, the PostgreSQL connector for LoopBack enables applications to work with data in PostgreSQL databases. It can be new data generated by mobile devices that need to be persisted, or existing data that need to be shared between mobile clients and other backend applications. No matter where you start, LoopBack makes it easy to handle your data with PostgreSQL.