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

Not available

go get -u github.com/pip-services4/pip-services4-go/pip-services4-postgres-go@latest

Not available

pip install pip-services4-postgres

Not available

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

Not available

type MyData struct {
	Id      string `bson:"_id" json:"id"`
	Key     string `bson:"key" json:"key"`
	Content string `bson:"content" json:"content"`
}

Not available

from pip_services4_data.data import IStringIdentifiable

class MyData(IStringIdentifiable): 
    def __init__(self, id: str = None, key: str = None, content: str = None): 
        self.id = id
        self.key = key 
        self.content = content

Not available

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'};

Not available

data1 := MyData{Id: "1", Key: "key 1", Content: "content 1"}
data2 := MyData{Id: "2", Key: "key 2", Content: "content 2"}
data3 := MyData{Id: "3", Key: "key 3", Content: "content 3"}

Not available

data1 = MyData('1','key 1', 'content 1') 
data2 = MyData('2','key 2', 'content 2') 
data3 = MyData('3','key 3', 'content 3')

Not available

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';

Not available

import (
    persist "github.com/pip-services4/pip-services4-go/pip-services4-postgres-go"
)

Not available

from pip_services4_postgres.persistence import PostgresPersistence

Not available

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);
    }
}

Not available

import (
	"context"

	cquery "github.com/pip-services4/pip-services4-go/pip-services4-data-go/query"

	persist "github.com/pip-services4/pip-services4-go/pip-services4-postgres-go/persistence"
)

// page structure
type MyDataPage struct {
	Total *int64   `bson:"total" json:"total"`
	Data  []MyData `bson:"data" json:"data"`
}

func NewEmptyMyDataPage() *MyDataPage {
	return &MyDataPage{}
}

func NewMyDataPage(total *int64, data []MyData) *MyDataPage {
	return &MyDataPage{Total: total, Data: data}
}

type MyPostgresPersistence struct {
	*persist.PostgresPersistence[MyData]
}

func NewMyPostgresPersistence() *MyPostgresPersistence {
	c := &MyPostgresPersistence{}
	c.PostgresPersistence = persist.InheritPostgresPersistence[MyData](c, "mydata")
	return c
}

func (c *MyPostgresPersistence) DefineSchema() {
	// clear all previously autogenerated schemas
	c.ClearSchema()
	c.PostgresPersistence.DefineSchema()
	// create a table
	c.EnsureSchema("CREATE TABLE " + c.QuotedTableName() + " (\"id\" TEXT PRIMARY KEY, \"key\" TEXT, \"content\" TEXT)")
	// create an index
	c.EnsureIndex(c.TableName+"_key", map[string]string{"key": "1"}, map[string]string{"unique": "true"})
}

func (c *MyPostgresPersistence) Create(ctx context.Context, correlationId string, item MyData) (result MyData, err error) {
	return c.PostgresPersistence.Create(ctx, item)
}

func (c *MyPostgresPersistence) GetPageByFilter(ctx context.Context, correlationId string, filter string, paging *cquery.PagingParams) (page cquery.DataPage[MyData], err error) {
	return c.PostgresPersistence.GetPageByFilter(ctx, filter, *paging, "", "")
}

func (c *MyPostgresPersistence) GetOneRandom(ctx context.Context, correlationId string, filter string) (item MyData, err error) {
	return c.PostgresPersistence.GetOneRandom(ctx, filter)
}

func (c *MyPostgresPersistence) GetListByFilter(ctx context.Context, correlationId string, filter string) (items []MyData, err error) {
	return c.PostgresPersistence.GetListByFilter(ctx, filter, "", "")
}

func (c *MyPostgresPersistence) GetCountByFilter(ctx context.Context, correlationId string, filter string) (count int64, err error) {
	return c.PostgresPersistence.GetCountByFilter(ctx, filter)
}

func (c *MyPostgresPersistence) DeleteByFilter(ctx context.Context, correlationId string, filter string) (err error) {
	return c.PostgresPersistence.DeleteByFilter(ctx, filter)
}

Not available

class MyPostgresPersistence(PostgresPersistence):
    
    def __init__(self):
        super(MyPostgresPersistence, self).__init__('mydata')
    
    def _define_schema(self):
        # clear all previously autogenerated schemas
        self._clear_schema()
        # create a table 
        self._ensure_schema('CREATE TABLE ' + self._table_name + ' (id VARCHAR(50) PRIMARY KEY, key VARCHAR(50), content VARCHAR(50))')
        # create an index
        self._ensure_index(self._table_name + '_key', {'key': 1}, {'unique': True})

    def get_one_random(self, correlation_id: Optional[str], filter: Any) -> MyData:
        return super().get_one_random(correlation_id, filter)

    def get_list_by_filter(self, correlation_id: Optional[str], filter: Any, sort: Any, select: Any) -> List[MyData]:
        return super().get_list_by_filter(correlation_id, filter, sort, select)

    def get_count_by_filter(self, correlation_id: Optional[str], filter: Any) -> int:
        return super().get_count_by_filter(correlation_id, filter)

    def get_page_by_filter(self, correlation_id: Optional[str], filter: Any, paging: PagingParams, sort: Any, select: Any) -> DataPage: 
        return super().get_page_by_filter(correlation_id, filter, paging, sort, select)

    def delete_by_filter(self, correlation_id: Optional[str], filter: Any) -> int:
        return super().delete_by_filter(correlation_id, filter)

Not available

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'
));

Not available

import (
    conf "github.com/pip-services4/pip-services4-go/pip-services4-components-go/config"
)

persistence := NewMyPostgresPersistence()
persistence.Configure(context.Background(), conf.NewConfigParamsFromTuples(
	"connection.host", "localhost",
	"connection.port", 5432,
	"connection.database", "pip1",
	"credential.username", "postgres",
	"credential.password", "admin",
))

Not available

from pip_services4_components.config import ConfigParams

persistence = MyPostgresPersistence()
persistence.configure(ConfigParams.from_tuples(
    "connection.host", "localhost",
    "connection.port", 5431,
    "connection.database", "pip1",
    "credential.username", "postgres", 
    "credential.password", "admin"
))

Not available

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'

Not available

err := persistence.Open(context.Background())

Not available

persistence.open(None)

Not available

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'");

Not available

result, _ := persistence.Create(context.Background(), data1)

Not available

result = persistence.create(None, data1)

Not available

Where

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

Not available

result.Id      // Returns '1'
result.Key     // Returns 'key 1'
result.Content // Returns 'content 1'

Not available

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

Not available

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);

Not available

result, _ := persistence.GetOneRandom(context.Background(), "", "key='key 1'")

Not available

result = persistence.get_one_random(None, "key='key 1'")

Not available

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'

Not available

result.Id      // Returns '1'
result.Key     // Returns 'key 1'
result.Content // Returns 'content 1'

Not available

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

Not available

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);

Not available

result, _ := persistence.GetListByFilter(context.Background(), "", "key='key 1'")

Not available

result = persistence.get_list_by_filter(None, "key='key 1'", None, None)

Not available

Where

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

Not available

result[0].Id       // Returns '1'
result[0].Key      // Returns 'key 1'
result[0].Content  // Returns 'content 1'

Not available

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

Not available

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

Not available

result, _ := persistence.GetPageByFilter(context.Background(), "", "key='key 1'", nil)

Not available

result = persistence.get_page_by_filter(None, "key='key 1'", None, None, None)

Not available

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'");

Not available

result.Data[0].Id       // Returns '1'
result.Data[0].Key      // Returns 'key 1'
result.Data[0].Content  // Returns 'content 5'

Not available

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

Not available

getCountByFilter()

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

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

Not available

result, _ := persistence.GetCountByFilter(context.Background(), "", "key='key 1'") // Returns 1

Not available

result = persistence.get_count_by_filter(None, "key='key 1'") # Returns 1

Not available

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);
    }
}

Not available

err := persistence.DeleteByFilter(context.Background(), "", "key='key 1'")

Not available

persistence.delete_by_filter(None, "key='key 3'")

Not available

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'
));

Not available

import (
    persist "github.com/pip-services4/pip-services4-go/pip-services4-postgres-go"
)

Not available

from pip_services4_postgres.persistence import IdentifiablePostgresPersistence

Not available

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);

Not available

type MyPostgresPersistence struct {
	persist.IdentifiablePostgresPersistence[MyData, string]
}

func NewMyPostgresPersistence() *MyPostgresPersistence {
	c := &MyPostgresPersistence{}
	c.IdentifiablePostgresPersistence = *persist.InheritIdentifiablePostgresPersistence[MyData, string](c, "mydata")
	return c
}

func (c *MyPostgresPersistence) DefineSchema() {
	c.ClearSchema()
	c.IdentifiablePostgresPersistence.DefineSchema()
	// Row name must be in double quotes for properly case!!!
	c.EnsureSchema("CREATE TABLE " + c.QuotedTableName() + " (\"id\" TEXT PRIMARY KEY, \"key\" TEXT, \"content\" TEXT)")
	c.EnsureIndex(c.TableName+"_key", map[string]string{"key": "1"}, map[string]string{"unique": "true"})
}

func (c *MyPostgresPersistence) Create(ctx context.Context, item MyData) (result MyData, err error) {
	return c.IdentifiablePostgresPersistence.Create(ctx, item)
}

func (c *MyPostgresPersistence) GetPageByFilter(ctx context.Context, filter string, paging cquery.PagingParams) (page cquery.DataPage[MyData], err error) {

	return c.IdentifiablePostgresPersistence.GetPageByFilter(ctx, filter, paging, "", "")
}

func (c *MyPostgresPersistence) GetOneRandom(ctx context.Context, correlationId string, filter string) (item MyData, err error) {
	return c.PostgresPersistence.GetOneRandom(ctx, filter)
}

func (c *MyPostgresPersistence) GetListByFilter(ctx context.Context, correlationId string, filter string) (items []MyData, err error) {
	return c.PostgresPersistence.GetListByFilter(ctx, filter, "", "")
}

func (c *MyPostgresPersistence) GetCountByFilter(ctx context.Context, filter string) (count int64, err error) {
	return c.PostgresPersistence.GetCountByFilter(ctx, filter)
}

func (c *MyPostgresPersistence) DeleteByFilter(ctx context.Context, filter string) (err error) {
	return c.PostgresPersistence.DeleteByFilter(ctx, filter)
}

Not available

class MyPostgresPersistence(IdentifiablePostgresPersistence):

    def __init__(self):
        super(MyPostgresPersistence, self).__init__('mydata')

    def _define_schema(self):
        # clear all previously autogenerated schemas
        self._clear_schema()
        # create a table 
        self._ensure_schema('CREATE TABLE ' + self._table_name + ' (id TEXT PRIMARY KEY, key TEXT, content TEXT)')
        # create an index
        self._ensure_index(self._table_name + '_key', {'key': 1}, {'unique': True})

    def get_one_random(self, correlation_id: Optional[str], filter: Any) -> MyData:
        return super().get_one_random(correlation_id, filter)

    def get_list_by_filter(self, correlation_id: Optional[str], filter: Any, sort: Any, select: Any) -> List[MyData]:
        return super().get_list_by_filter(correlation_id, filter, sort, select)

    def get_count_by_filter(self, correlation_id: Optional[str], filter: Any) -> int:
        return super().get_count_by_filter(correlation_id, filter)

    def get_page_by_filter(self, correlation_id: Optional[str], filter: Any, paging: PagingParams, sort: Any, select: Any) -> DataPage: 
        return super().get_page_by_filter(correlation_id, filter, paging, sort, select)
    
    def delete_by_filter(self, correlation_id: Optional[str], filter: Any) -> int:
        return super().delete_by_filter(correlation_id, filter)

Not available

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

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

Not available

import (
    conf "github.com/pip-services4/pip-services4-go/pip-services4-components-go/config"
)

persistence := NewMyPostgresPersistence()
persistence.Configure(context.Background(), conf.NewConfigParamsFromTuples(
	"connection.host", "localhost",
	"connection.port", 5432,
	"connection.database", "pip1",
	"credential.username", "postgres",
	"credential.password", "admin",
))

Not available

from pip_services4_components.config import ConfigParams

persistence = MyPostgresPersistence()

persistence.configure(ConfigParams.from_tuples(
    "connection.host", "localhost",
    "connection.port", 5431,
    "connection.database", "pip1",
    "credential.user", "postgres", 
    "credential.password", "admin"
))

Not available

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'

Not available

err := persistence.Open(context.Background())

Not available

persistence.open(None)

Not available

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');

Not available

result, _ := persistence.Create(context.Background(), data1)

Not available

result1 = persistence.create(None, data1)

Not available

where

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

Not available

result1.Id      // Returns '1'
result1.Key     // Returns 'key 1'
result1.Content // Returns 'content 1'

Not available

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

Not available

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);

Not available

result, _ = persistence.GetOneById(context.Background(), "1")

Not available

result = persistence.get_one_by_id(None,'1')

Not available

Where

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

Not available

result1.Id      // Returns '1'
result1.Key     // Returns 'key 1'
result1.Content // Returns 'content 1'

Not available

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

Not available

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'});

Not available

idsList := []string{"2", "3"}
result1, _ := persistence.GetListByIds(context.Background(), idsList)

Not available

ids_list = ['2','3']
result = persistence.get_list_by_ids(None,ids_list)

Not available

Where

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

Not available

result[0].Id        // Returns '3'
result[0].Key       // Returns 'key 3'
result[0].Content   // Returns 'content 3'
result[1].Id        // Returns '2'
result[1].Key       // Returns 'key 2'
result[1].Content   // Returns 'content 2'

Not available

result[0].id        # Returns '3'
result[0].key       # Returns 'key 3'
result[0].content   # Returns 'content 3'
result[1].id        # Returns '2'
result[1].key       # Returns 'key 2'
result[1].content   # Returns 'content 2'

Not available

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' }));

Not available

result, _ = persistence.Update(context.Background(), MyData{Id: "1", Key: "key 1.1", Content: "new content 1"})

Not available

result = persistence.update(None, MyData('1','key 1.1', 'new content 1') )

Not available

Where

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

Not available

result.Id       // Returns '1'
result.Key      // Returns 'key 1.1'
result.Content  // Returns 'new content 1'

Not available

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

Not available

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');

Not available

import (
    cdata "github.com/pip-services4/pip-services4-go/pip-services4-commons-go/data"
)

result, _ = persistence.UpdatePartially(context.Background(), "1", *cdata.NewAnyValueMap(map[string]interface{}{"content": "new content 1.1"}))

Not available

result = persistence.update_partially(None, '1', AnyValueMap({'content': 'new content 1.1'}))

Not available

Where

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

Not available

result.Id      // Returns '1'
result.Key     // Returns 'key 1.1'
result.Content // Returns 'new content 1.1'

Not available

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';

Not available

result, _ := persistence.DeleteById(context.Background(), "1")

Not available

persistence.delete_by_id(None, "3")

Not available

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);
    }
}

Not available

idsList := []string{"4", "5"}
err := persistence.DeleteByIds(context.Background(), idsList)

Not available

id_list = ['5','6']
persistence.delete_by_ids(None,id_list)

Not available

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'");

Not available

import (
    persist "github.com/pip-services4/pip-services4-go/pip-services4-postgres-go"
)

Not available

from pip_services4_postgres.persistence import IdentifiableJsonPostgresPersistence

Not available

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'

Not available

type MyPostgresPersistence struct {
	persist.IdentifiableJsonPostgresPersistence[MyData, string]
}

func NewMyPostgresPersistence() *MyPostgresPersistence {
	c := &MyPostgresPersistence{}
	c.IdentifiableJsonPostgresPersistence = *persist.InheritIdentifiableJsonPostgresPersistence[MyData, string](c, "mydata")
	return c
}

func (c *MyPostgresPersistence) DefineSchema() {
	c.ClearSchema()
	c.IdentifiableJsonPostgresPersistence.DefineSchema()
	c.EnsureTable("", "")
	c.EnsureIndex(c.TableName+"_key", map[string]string{"(data->'key')": "1"}, map[string]string{"unique": "true"})
}

func (c *MyPostgresPersistence) Create(ctx context.Context, item MyData) (result MyData, err error) {
	return c.IdentifiableJsonPostgresPersistence.Create(ctx, item)
}

func (c *MyPostgresPersistence) GetPageByFilter(ctx context.Context, filter string, paging *cquery.PagingParams) (page cquery.DataPage[MyData], err error) {

	return c.IdentifiableJsonPostgresPersistence.GetPageByFilter(ctx,
		filter, *paging,
		"", "")
}

func (c *MyPostgresPersistence) GetOneRandom(ctx context.Context, filter string) (item MyData, err error) {
	return c.PostgresPersistence.GetOneRandom(ctx, filter)
}

func (c *MyPostgresPersistence) GetListByFilter(ctx context.Context, filter string) (items []MyData, err error) {
	return c.PostgresPersistence.GetListByFilter(ctx, filter, "", "")
}

func (c *MyPostgresPersistence) GetCountByFilter(ctx context.Context, filter string) (count int64, err error) {
	return c.PostgresPersistence.GetCountByFilter(ctx, filter)
}

func (c *MyPostgresPersistence) DeleteByFilter(ctx context.Context, filter string) (err error) {
	return c.PostgresPersistence.DeleteByFilter(ctx, filter)
}

Not available

class MyPostgresPersistence(IdentifiableJsonPostgresPersistence):

    def __init__(self):
        super(MyPostgresPersistence, self).__init__('mydata_json2')

    def _define_schema(self):
        # clear all previously autogenerated schemas
        self._clear_schema()
        # create a table 
        self._ensure_table()
        # create an index
        self._ensure_index(self._table_name + '_json_key', {"(data->>'key')": 1}, {'unique': None})

    def get_one_random(self, correlation_id: Optional[str], filter: Any) -> MyData:
        return super().get_one_random(correlation_id, filter)

    def get_list_by_filter(self, correlation_id: Optional[str], filter: Any, sort: Any, select: Any) -> List[MyData]:
        return super().get_list_by_filter(correlation_id, filter, sort, select)

    def get_count_by_filter(self, correlation_id: Optional[str], filter: Any) -> int:
        return super().get_count_by_filter(correlation_id, filter)

    def get_page_by_filter(self, correlation_id: Optional[str], filter: Any, paging: PagingParams, sort: Any, select: Any) -> DataPage: 
        return super().get_page_by_filter(correlation_id, filter, paging, sort, select)

    def delete_by_filter(self, correlation_id: Optional[str], filter: Any) -> int:
        return super().delete_by_filter(correlation_id, filter)

Not available

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);

Not available

result, _ := persistence.GetOneRandom(context.Background(), "", "key='key 1'")

Not available

result = persistence.get_one_random(None, "key='key 1'")

Not available

we can obtain the record values as

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

Not available

result.Id      // Returns '1'
result.Key     // Returns 'key 1'
result.Content // Returns 'content 1'

Not available

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

Not available

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.