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.

postgresql

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.

loopback_logo

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:

  • LoopBack PostgreSQL connector
  • LoopBack MongoDB connector
  • LoopBack Oracle connector
  • LoopBack MySQL connector
  • LoopBack Microsoft SQL Server (MSSQL) connector
  • 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:

  • email: string – The email address for the account.
  • level: number – Your game level.
  • created: date – The date your account was created.
  • modified: date – The date your account was updated.
  • 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": "foo@bar.com",
           "level": 10,
           "created": "2013-10-15T21:34:50.000Z",
           "modified": "2013-10-15T21:34:50.000Z",
           "id": 1
         },
         {
           "email": "bar@bar.com",
           "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": "foo@bar.com",
         "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: 'foo@bar.com',
       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: 'bar@bar.com',
       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.

    What’s next?

  • Install LoopBack with a simple npm command
  • What’s in the upcoming Node v0.12 version? Big performance optimizations, read the blog by Ben Noordhuis to learn more.
  • Need performance monitoring, profiling and cluster capabilities for your Node apps? Check out StrongOps!