PostgreSQL persistence

How to persist data using a PostgreSQL database.

Key takeaways

PostgrePersistence Persistence component that stores data in PostgreSQL databases using the official driver.
IdentifiablePostgrePersistence Persistence component that stores data in PostgreSQL databases and implements several CRUD operations over data items with unique ids.
IdentifiableJsonPostgrePersistence Persistence component that stores data in PostgreSQL databases in JSON or JSONB fields and implements several CRUD operations over data items with unique ids.

Introduction

In this tutorial, you will understand how to create persistence components for PostgreSQL databases. First, we will see the necessary prerequisites. Then, we will move to each of the three components available in Pip.Services for this purpose. Finally, we will summarize all the explained concepts.

PostgreSQL persistence

Pip.Services offers three different components to work with PostgreSQL databases. They are PostgrePersistence, IdentifiablePostgrePersistence and IdentifiableJsonPostgrePersistence. The following sections explain how to use each of them.

Pre-requisites

In order to use this library, we need to download it from GitHub with the following command.

npm install pip-services4-postgres-node

Data object

In our examples, we will use data objects with the following structure.

import { IStringIdentifiable } from 'pip-services34-data-node';

export class MyData implements IStringIdentifiable {
    public id: string;
    public key: string;
    public content: string;
}

And with the following instances

let data1 = { id: '1', key: 'key 1', content: 'content 1'};
let data2 = { id: '2', key: 'key 2', content: 'content 2'};
let data3 = { id: '3', key: 'key 3', content: 'content 3'}; 

PostgresPersistence

This is the most basic persistence class and is used to create persistence components that store data in PostgreSQL databases using the official driver.

Pre-requisites

In order to use this component, we need to import the corresponding library with the following command.

import { PostgresPersistence } from 'pip-services4-postgres-node';


Component implementation

To implement this component, we define a class that inherits the methods from the PostgresPersistence class. In this class, we can also define the defineSchema method, which checks if the table exists or not. If not, it creates one with the defineSchema method. Here, we can use the SQL command CREATE TABLE to define the structure of the table. The example below shows how to do this, where mytable is the name of our table.

export class MyPostgresPersistence
    extends PostgresPersistence<MyData> {
    public constructor() {
        super('mydata');
    }

    protected defineSchema(): void {
        // clear all previously autogenerated schemas
        this.clearSchema();
        // create a table 
        this.ensureSchema('CREATE TABLE ' + this._tableName + ' (id TEXT PRIMARY KEY, key TEXT, content TEXT)');
        // create an index
        this.ensureIndex(this._tableName + '_key', { key: 1 }, { unique: true });
    }

    public async getOneRandom(ctx: Context, filter: any): Promise<MyData> {
        return await super.getOneRandom(ctx, filter);
    }

    public async getListByFilter(ctx: Context, filter: any, sort: any, select: any): Promise<MyData[]> {
        return await super.getListByFilter(ctx, filter, sort, select);
    }

    public async getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any, select: any): Promise<DataPage<MyData>> {
        return await super.getPageByFilter(ctx, filter, paging, sort, select);
    }

    public async getCountByFilter(ctx: Context, filter: any): Promise<number> {
        return await super.getCountByFilter(ctx, filter);
    }

    public deleteByFilter(ctx: Context, filter: any): Promise<void> {
        return super.deleteByFilter(ctx, filter);
    }
}


Now, we create an instance of this class and configure it according to our database configuration parameters. For this, we use the configure method and the ConfigParams component. Our code will be similar to this one.

import { FilterParams } from 'pip-services4-data-node';
import { ConfigParams } from 'pip-services4-components-node';

let persistence = new MyPostgresPersistence();
persistence.configure(ConfigParams.fromTuples(
    'connection.host', 'localhost',
    'connection.port', 5432,
    'connection.database', 'pip1',
    'credential.username', 'postgres',
    'credential.password', 'admin'
));
Connection

Once our component has been defined, we can connect to our database using the open() method.

result.id      // Returns '1'
result.key     // Returns 'key 1'
result.content // Returns 'content 1'

CRUD operations

This component allows us to perform many CRUD operations by using its methods. The sections below show how to do this.

Create

To create a new record, we use the create() method, which accepts the correlationId and the item to be stored as input parameters. This method returns a PostgrePersistence object containing the inserted record.

let result = await persistence.getOneRandom(null, "key='key 1'");

Where

result.id      // Returns '1'
result.key     // Returns 'key 1'
result.content // Returns 'content 1'

Retrieve

To retrieve a record, this class presents three methods namely getOneRandom(), getListByFilter() and getPageByFilter(). Additionally, it also contains the getCountByBilter() method, which returns the number of records retrieved by using a given filter. The following are examples of their usage.

getOneRandom()

This method gets a random item based on a given filter. The filter is defined by a string with the following syntax: Field = “value”

An example of its usage is

let result = await persistence.getListByFilter(null, "key='key 1'", null, null);

The result is a PostgrePersistence object containing the extracted record.

result[0].id       // Returns '1'
result[0].key      // Returns 'key 1'
result[0].content  // Returns 'content 1'

getListByFilter()

This method returns a list PostgrePersistence objects with the data items retrieved according to a given filter. In the following example, we will extract all those records containing a key’s value equal to ‘key 1’.

let result = await persistence.getPageByFilter(null, "key='key 1'", null, null, null);

Where

result.data[0].id       // Returns '1'
result.data[0].key      // Returns 'key 1'
result.data[0].content  // Returns 'content 5'

getPageByFilter()

This method gets a page of data items retrieved according to a given filter and sorted according to sort parameters. In the example below, we obtain again those records with a key’s value equal to ‘key 1’.

let result = await persistence.getCountByFilter(null, "key='key 1'"); // Returns 1

The obtained result is a DataPage object, which contains the data field with the extracted records. An example of how to read the values of the extracted records is

await persistence.deleteByFilter(null, "key='key 3'");

getCountByFilter()

This method returns an integer representing the number of data items retrieved by a given filter.

import { IdentifiablePostgresPersistence } from 'pip-services4-postgres-node';


Update

Note: This component has no update method. Therefore, if we want to have it, we need to define it in our persistence class.

Delete

To delete one or more records, we can use the deleteByFilter() method. The example below shows how to delete all records with a key’s value equal to ‘key 1’.

export class MyPostgresPersistence
    extends IdentifiablePostgresPersistence<MyData, string> {
    public constructor() {
        super('mydata');
    }

    protected defineSchema(): void {
        // clear all previously autogenerated schemas
        this.clearSchema();
        // create a table 
        this.ensureSchema('CREATE TABLE ' + this._tableName + ' (id TEXT PRIMARY KEY, key TEXT, content TEXT)');
        // create an index
        this.ensureIndex(this._tableName + '_key', { key: 1 }, { unique: true });
    }

    public async getOneRandom(ctx: Context, filter: any): Promise<MyData> {
        return await super.getOneRandom(ctx, filter);
    }

    public async getListByFilter(ctx: Context, filter: any, sort: any, select: any): Promise<MyData[]> {
        return await super.getListByFilter(ctx, filter, sort, select);
    }

    public async getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any, select: any): Promise<DataPage<MyData>> {
        return await super.getPageByFilter(ctx, filter, paging, sort, select);
    }

    public async getCountByFilter(ctx: Context, filter: any): Promise<number> {
        return await super.getCountByFilter(ctx, filter);
    }

    public deleteByFilter(ctx: Context, filter: any): Promise<void> {
        return super.deleteByFilter(ctx, filter);
    }
}

IdentifiablePostgresPersistence

This class provides a persistence component that stores data in PostgreSQL databases and implements several CRUD operations over data items with unique ids.

Pre-requisites

In order to use this component, we need to import the corresponding library with the following command.

import { ConfigParams } from 'pip-services4-components-node';

let persistence = new MyPostgresPersistence();
persistence.configure(ConfigParams.fromTuples(
    'connection.host', 'localhost',
    'connection.port', 5432,
    'connection.database', 'pip1',
    'credential.username', 'postgres',
    'credential.password', 'admin'
));

Component implementation

To implement this component, we can define a class that inherits the IdentifiablePostgresPersistence class. Our code will look something like this, where mydata is the name of the table where we are going to store our data:

await persistence.open(null);

Now, we can create an instance of this class and configure it according to our database.

let result1 = await persistence.create(null, data1);

Connection

Once our component has been defined, we can connect to our database using the open() method. This method accepts the correlationId as an input parameter.

result1.id      // Returns '1'
result1.key     // Returns 'key 1'
result1.content // Returns 'content 1'

CRUD operations

This component allows us to perform several CRUD operations. The sections below show how to do this.

Create

To create a new record, we can use the create() method, which accepts the correlationId and the item to be stored as input parameters. This method returns a PostgrePersistence object containing the inserted values.

let result = await persistence.getOneById(null, '1');

where

result.id       // Returns '1'
result.key      // Returns 'key 1.1'
result.content  // Retuns 'content 1'

Retrieve

This class provides two main methods that can be used to retrieve records from a PostgreSQL database. They are getOneById() and getListByIds().

getOneById()

This method returns a record according to a given id. The record is contained in a PostgrePersistence object.

let idsList = ['2', '3']
let result = await persistence.getListByIds(null, idsList);

Where

let idsList = ['2', '3']
let result = await persistence.getListByIds(null, idsList);

getListByIds()

Given a list of ids, this method returns a list containing the extracted records in the form of PostgrePersistence objects. An example of its usage is

let result = await persistence.update(null, { id: '1', key: 'key 1.1', content: 'new content 1'});

Where

result.id       // Returns '1'
result.key      // Returns 'key 1.1'
result.content  // Returns 'new content 1'

Update

This class presents two different update methods: update() and update_partially().

update()

This method updates a data item. As input parameters, it requires the correlationId and an item to be updated. It returns a PostgrePersistence object containing the updated record.

let result = await persistence.updatePartially(null, '1', new AnyValueMap({ 'content': 'new content 1.1' }));

Where

result.id      // Returns '1'
result.key     // Returns 'key 1.1'
result.content // Returns 'new content 1.1'

updatePartially()

This method updates the specified fields only. It takes three input parameters namely the correlationId, the id of the item to be updated, and a dictionary containing the fields to be updated. It returns a PostgrePersistance object with the updated record.

await persistence.deleteById(null, '1');

Where

let idsList = ['5', '6'];
await persistence.deleteByIds(null, idsList);

Delete

To delete stored records, we have two different methods: deleteById() and deleteByIds().

deleteById()

This method deletes a record specified by a given id.

import { IdentifiableJsonPostgresPersistence } from 'pip-services4-postgres-node';


deleteByIds()

This method deletes records specified by a list of ids.

export class MyPostgresPersistence 
    extends IdentifiableJsonPostgresPersistence<MyData, string>
{
    public constructor() {
        super('mydata_json2');
    }

    protected defineSchema(): void {
        // clear all previously autogenerated schemas
        this.clearSchema();
        // create a table 
        this.ensureTable();
        // create an index
        this.ensureIndex(this._tableName + '_json_key', { "(data->>'key')": 1 }, { unique: true });
    }

    public async getOneRandom(ctx: Context, filter: any): Promise<MyData> {
        return await super.getOneRandom(ctx, filter);
    }

    public async getListByFilter(ctx: Context, filter: any, sort: any, select: any): Promise<MyData[]> {
        return await super.getListByFilter(ctx, filter, sort, select);
    }

    public async getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any, select: any): Promise<DataPage<MyData>> {
        return await super.getPageByFilter(ctx, filter, paging, sort, select);
    }

    public async getCountByFilter(ctx: Context, filter: any): Promise<number> {
        return await super.getCountByFilter(ctx, filter);
    }

    public deleteByFilter(ctx: Context, filter: any): Promise<void> {
        return super.deleteByFilter(ctx, filter);
    }
}

IdentifiableJsonPostgresPersistence

This class provides a persistence component that stores data in PostgreSQL in JSON or JSONB fields and implements several CRUD operations over data items with unique ids. It inherits from IdentifialePostgrePersistence and thus also from PostgrePersistence.

Pre-requisites

In order to use this component, we need to import the corresponding library with the following command.

let result = await persistence.getOneRandom(null, "key='key 1'");

Component implementation

To implement this component, we can define a class that inherits the IdentifiableJsonPostgresPersistence class. In this class, we will include the _defineSchema() method, where we will define a table with two fields, namely id and data. The second field will include our data in JSON format, as defined in the data object section. Our code will look something like this:

result.id      // Returns '1'
result.key     // Returns 'key 1'
result.content // Returns 'content 1'

Once that this class has been defined, we can create an instance of it, configure its connection parameters and connect it to our database in the same manner as we did with the IdentifiablePostgresPersistence component.

CRUD operations

This class inherits most of its methods from the IdentifiablePostgrePersistece class. As a result, these operations are implemented in the same manner as explained for the parent class.

Returned objects

In general, CRUD operations return an object with the same fields that were passed to the persistence component and the fields can be accessed in the same way as in the original object. For example, if we use the getOneRandom() method,

await persistence.open(null);

we can obtain the record values as

let result = await persistence.create(null, data1);

Wrapping up

In this tutorial, we have seen how to create persistence objects for PostgreSQL databases. First, we saw how to perform CRUD operations with the PostgrePersistence component, which is the parent class for the other two persistence components available in this library.

Then, we understood how to perform CRUD operations with the IdentifiablePostgresPersistence and the IdentifiableJsonPersitence components. The first component is used to work with data objects that contain a unique identifier, and the second to persist identifiable data objects in JSON format.

Finally, we learned how to read records stored in these persistence classes.