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-services3-postgres-nodex

dotnet add package PipServices3.Postgres

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

dart pub add pip_services3_postgres

pip install pip-services3-postgres

Not available

Data object

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

import { IStringIdentifiable } from 'pip-services3-commons-nodex';

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

using System.Runtime.Serialization;
using PipServices3.Commons.Data;

[DataContract]
public class MyData: IStringIdentifiable
{
    [DataMember(Name = "id")]
    public string Id { get; set; }

    [DataMember(Name = "key")]
    public string Key { get; set; }

    [DataMember(Name = "content")]
    public string Content { get; set; }
}

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

class MyData implements IStringIdentifiable, ICloneable {
  @override
  String? id;
  String? key;
  String? content;

  MyData();

  MyData.from(this.id, this.key, this.content);

  Map<String, dynamic> toJson() {
    return <String, dynamic>{'id': id, 'key': key, 'content': content};
  }

  void fromJson(Map<String, dynamic> json) {
    id = json['id'];
    key = json['key'];
    content = json['content'];
  }

  @override
  MyData clone() {
    return MyData.from(id, key, content);
  }
}

from pip_services3_commons.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'};

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

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

var data1 = MyData.from('1', 'key 1', 'content 1');
var data2 = MyData.from('2', 'key 2', 'content 2');
var data3 = MyData.from('3', 'key 3', 'content 3');

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-services3-postgres-nodex';

using PipServices3.Postgres.Persistence;

import (
    persist "github.com/pip-services3-gox/pip-services3-postgres-gox/persistence"
)

import 'package:pip_services3_postgres/pip_services3_postgres.dart';

from pip_services3_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(correlationId: string, filter: any): Promise<MyData> {
        return await super.getOneRandom(correlationId, filter);
    }

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

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

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

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

public class MyPostgresPersistence: PostgresPersistence<MyData>
{
    public MyPostgresPersistence() : base("mydata") { }

    protected override void DefineSchema()
    {
        // clear all previously autogenerated schemas
        ClearSchema();
        // create a table 
        EnsureSchema($"CREATE TABLE {_tableName} (id TEXT PRIMARY KEY, key TEXT, content TEXT)");
        // create an index
        EnsureIndex($"{_tableName}_key", new Dictionary<string, bool> { { "key", true } }, new IndexOptions { Unique = true });
    }

    public async new Task<MyData> GetOneRandomAsync(string correlationId, string filter)
    {
        return await base.GetOneRandomAsync(correlationId, filter);
    }

    public async new Task<List<MyData>> GetListByFilterAsync(string correlationId, string filter, string sort = null, string select = null)
    {
        return await base.GetListByFilterAsync(correlationId, filter);
    }

    public async new Task<long> GetCountByFilterAsync(string correlationId, string filter)
    {
        return await base.GetCountByFilterAsync(correlationId, filter);
    }

    public async new Task<void> DeleteByFilterAsync(string correlationId, string filter)
    {
        return await base.DeleteByFilterAsync(correlationId, filter);
    }
}

import (
	"fmt"
	"context"

	conf "github.com/pip-services3-gox/pip-services3-commons-gox/config"
	cdata "github.com/pip-services3-gox/pip-services3-commons-gox/data"
	persist "github.com/pip-services3-gox/pip-services3-postgres-gox/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, correlationId, item)
}

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

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

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

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

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

class MyPostgresPersistence extends PostgresPersistence<MyData> {
  MyPostgresPersistence() : super('mydata', null);

  @override
  void defineSchema_() {
    // clear all previously autogenerated schemas
    clearSchema();
    // create a table
    ensureSchema_('CREATE TABLE ' +
        tableName_! +
        ' (id TEXT PRIMARY KEY, key TEXT, content TEXT)');
    // create an index
    ensureIndex_(tableName_! + '_key', {'key': 1}, {'unique': true});
  }

  Future<MyData?> getOneRandom(String correlationId, String filter) async {
    return await super.getOneRandom_(correlationId, filter);
  }

  Future<List<MyData>> getListByFilter(
      String correlationId, String filter, sort, select) async {
    return await super.getListByFilter_(correlationId, filter, sort, select);
  }

  Future<int> getCountByFilter(String correlationId, String filter) async {
    return await super.getCountByFilter_(correlationId, filter);
  }

  Future<DataPage<MyData>> getPageByFilter(String correlationId, String filter,
      PagingParams paging, sort, select) async {
    return await super
        .getPageByFilter_(correlationId, filter, paging, sort, select);
  }

  Future<void> deleteByFilter(String correlationId, String filter) async {
    return await super.deleteByFilter_(correlationId, filter);
  }
}

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 { ConfigParams, FilterParams } from 'pip-services3-commons-nodex';

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

using PipServices3.Commons.Config;

var persistence = new MyPostgresPersistence();
persistence.Configure(ConfigParams.FromTuples(
    "host", "localhost",
    "port", 5432,
    "connection.database", "pip1",
    "credential.user", "postgres",
    "credential.password", "admin"
));

await persistence.OpenAsync(null)

import (
	conf "github.com/pip-services3-gox/pip-services3-commons-gox/config"
)

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

import 'package:pip_services3_commons/pip_services3_commons.dart';

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

from pip_services3_commons.config import ConfigParams

persistence = MyPostgresPersistence()
persistence.configure(ConfigParams.from_tuples(
    "connection.host", "localhost",
    "connection.port", 5432,
    "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.

await persistence.open(null);

await persistence.OpenAsync(null);

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

await persistence.open(null);

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.create(null, data1);

var result = await persistence.CreateAsync(null, data1);

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

var result = await persistence.create('123', data1);

result = persistence.create(None, data1)

Not available

Where

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

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

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

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

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.getOneRandom(null, "key='key 1'");

var result = await persistence.GetOneRandomAsync(null, "key='key 1'");

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

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

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

Not available

The result is a PostgrePersistence object containing the extracted record.

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

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

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

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

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.getListByFilter(null, "key='key 1'", null, null);

var result = await persistence.GetListByFilterAsync(null, "key='key 1'");

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

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

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

Not available

Where

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

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

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

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

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.getPageByFilter(null, "key='key 1'", null, null, null);

var result = await persistence.GetPageByFilterAsync(null, "key='key 1'");

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

var result = await persistence.getPageByFilter('123', "key='key 1'", PagingParams(), null, null);

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

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

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

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

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

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.

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

var result = persistence.GetCountByFilterAsync(null, "key='key 1'"); // Returns 1

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

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

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’.

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

persistence.DeleteByFilterAsync(null, "key='key 3'").Wait();

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

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

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 { IdentifiablePostgresPersistence } from 'pip-services3-postgres-nodex';

using PipServices3.Postgres.Persistence;

import (
	persist "github.com/pip-services3-gox/pip-services3-postgres-gox/persistence"
)

import 'package:pip_services3_postgres/pip_services3_postgres.dart';

from pip_services3_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:

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(correlationId: string, filter: any): Promise<MyData> {
        return await super.getOneRandom(correlationId, filter);
    }

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

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

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

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

public class MyPostgresPersistence: IdentifiablePostgresPersistence<MyData, string>
{
    public MyPostgresPersistence() : base("mydata") { }

    protected override void DefineSchema()
    {
        // clear all previously autogenerated schemas
        ClearSchema();
        // create a table 
        EnsureSchema($"CREATE TABLE {_tableName} (id TEXT PRIMARY KEY, key TEXT, content TEXT)");
        // create an index
        EnsureIndex($"{_tableName}_key", new Dictionary<string, bool> { { "key", true } }, new IndexOptions { Unique = true });
    }

    public async new Task<MyData> GetOneRandomAsync(string correlationId, string filter)
    {
        return await base.GetOneRandomAsync(correlationId, filter);
    }

    public async new Task<List<MyData>> GetListByFilterAsync(string correlationId, string filter, string sort = null, string select = null)
    {
        return await base.GetListByFilterAsync(correlationId, filter);
    }

    public async new Task<long> GetCountByFilterAsync(string correlationId, string filter)
    {
        return await base.GetCountByFilterAsync(correlationId, filter);
    }

    public async new Task<void> DeleteByFilterAsync(string correlationId, string filter)
    {
        return await base.DeleteByFilterAsync(correlationId, filter);
    }
}

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

func NewMyPostgresPersistence() *MyPostgresPersistence {
	c := &MyPostgresPersistence{}
	c.IdentifiablePostgresPersistence = *persist.InheritIdentifiablePostgresPersistence(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, correlationId string, item MyData) (result MyData, err error) {
	return c.IdentifiablePostgresPersistence.Create(ctx, correlationId, item)
}

func (c *MyPostgresPersistence) GetPageByFilter(correlationId string, filter string, paging *cdata.PagingParams) (page cdata.DataPage[MyData], err error) {

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

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

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

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

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

class MyPostgresPersistence extends IdentifiablePostgresPersistence<MyData, String> {
  MyPostgresPersistence() : super('mydata', null);

  @override
  void defineSchema_() {
    // clear all previously autogenerated schemas
    clearSchema();
    // create a table
    ensureSchema_('CREATE TABLE ' +
        tableName_! +
        ' (id TEXT PRIMARY KEY, key TEXT, content TEXT)');
    // create an index
    ensureIndex_(tableName_! + '_key', {'key': 1}, {'unique': true});
  }

  Future<MyData?> getOneRandom(String correlationId, String filter) async {
    return await super.getOneRandom_(correlationId, filter);
  }

  Future<List<MyData>> getListByFilter(
      String correlationId, String filter, sort, select) async {
    return await super.getListByFilter_(correlationId, filter, sort, select);
  }

  Future<int> getCountByFilter(String correlationId, String filter) async {
    return await super.getCountByFilter_(correlationId, filter);
  }

  Future<DataPage<MyData>> getPageByFilter(String correlationId, String filter,
      PagingParams paging, sort, select) async {
    return await super
        .getPageByFilter_(correlationId, filter, paging, sort, select);
  }

  Future<void> deleteByFilter(String correlationId, String filter) async {
    return await super.deleteByFilter_(correlationId, filter);
  }
}

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.

import { ConfigParams } from 'pip-services3-commons-nodex';

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

using PipServices3.Commons.Config;

var persistence = new MyPostgresPersistence();

persistence.Configure(ConfigParams.FromTuples(
    "connection.host", "localhost",
    "connection.port", "5432",
    "connection.database", "pip1",
    "credential.username", "postgres",
    "credential.password", "admin"
));

import (
	conf "github.com/pip-services3-gox/pip-services3-commons-gox/config"
)

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

import 'package:pip_services3_commons/pip_services3_commons.dart';

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

from pip_services3_commons.config import ConfigParams

persistence = MyPostgresPersistence()

persistence.configure(ConfigParams.from_tuples(
    "connection.host", "localhost",
    "connection.port", 5432,
    "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.

await persistence.open(null);

await persistence.OpenAsync(null);

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

await persistence.open('123');

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 result1 = await persistence.create(null, data1);

var result1 = await persistence.CreateAsync(null, data1);

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

var result1 = await persistence.create('123', data1);

result1 = persistence.create(None, data1)

Not available

where

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

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

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

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

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 result = await persistence.getOneById(null, '1');

var result = await persistence.GetOneByIdAsync(null, "1");

result, _ := persistence.GetOneById(ctx context.Context, "", "1")

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

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

Not available

Where

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

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

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

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

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 idsList = ['2', '3']
let result = await persistence.getListByIds(null, idsList);

var idsList = new string[] { "1", "2", "3" };
var result = await persistence.GetListByIdsAsync(null, idsList);

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

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

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

Not available

Where

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

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'

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'

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'

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.update(null, { id: '1', key: 'key 1.1', content: 'new content 1'});

var result = await persistence.UpdateAsync(null, new MyData { Id="1", Key="key 1.1", Content="new content 1" });

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

var result = await persistence.update(null, MyData.from('1', 'key 1.1', 'new content 1'));

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'

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

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

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

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.

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

var result = await persistence.UpdatePartially(null, "1", new AnyValueMap { { "content", "new content 1.1" } });

import (
    cdata "github.com/pip-services3-gox/pip-services3-commons-gox/data"
)

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

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

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

Not available

Where

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

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

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

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

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.

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

await persistence.DeleteByIdAsync(null, "3");

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

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

persistence.delete_by_id(None, "3")

Not available

deleteByIds()

This method deletes records specified by a list of ids.

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

var idsList = new string[] { "5", "6" };
await persistence.DeleteByIdsAsync(null, idsList);

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

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

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.

import { IdentifiableJsonPostgresPersistence } from 'pip-services3-postgres-nodex';

using PipServices3.Postgres.Persistence;

import (
	persist "github.com/pip-services3-gox/pip-services3-postgres-gox/persistence"
)

import 'package:pip_services3_postgres/pip_services3_postgres.dart';

from pip_services3_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:

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(correlationId: string, filter: any): Promise<MyData> {
        return await super.getOneRandom(correlationId, filter);
    }

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

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

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

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

public class MyPostgresPersistence: IdentifiableJsonPostgresPersistence<MyData, string>
{
    public MyPostgresPersistence() : base("mydata_json2") { }

    protected override void DefineSchema()
    {
        // clear all previously autogenerated schemas
        ClearSchema();
        // create a table 
        EnsureTable();
        // create an index
        EnsureIndex($"{_tableName}_key", new Dictionary<string, bool> { { "(data->>'key')", true } }, new IndexOptions { Unique = false });
    }

    public async new Task<MyData> GetOneRandomAsync(string correlationId, string filter)
    {
        return await base.GetOneRandomAsync(correlationId, filter);
    }

    public async new Task<List<MyData>> GetListByFilterAsync(string correlationId, string filter, string sort = null, string select = null)
    {
        return await base.GetListByFilterAsync(correlationId, filter);
    }

    public async new Task<long> GetCountByFilterAsync(string correlationId, string filter)
    {
        return await base.GetCountByFilterAsync(correlationId, filter);
    }

    public async new Task<void> DeleteByFilterAsync(string correlationId, string filter)
    {
        return await base.DeleteByFilterAsync(correlationId, filter);
    }
}

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

func NewMyPostgresPersistence() *MyPostgresPersistence {
	c := &MyPostgresPersistence{}
	c.IdentifiableJsonPostgresPersistence = *persist.InheritIdentifiableJsonPostgresPersistence(c, "mydata_json2")
	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, correlationId string, item MyData) (result MyData, err error) {
	return c.IdentifiableJsonPostgresPersistence.Create(ctx, correlationId, item)
}

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

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

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

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

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

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

class MyPostgresPersistence
    extends IdentifiableJsonPostgresPersistence<MyData, String> {
  MyPostgresPersistence() : super('mydata_json2', null);

  @override
  void defineSchema_() {
    // clear all previously autogenerated schemas
    clearSchema();
    // create a table
    ensureTable_();
    // create an index
    ensureIndex_(
        tableName_! + '_json_key', {"(data->>'key')": 1}, {'unique': true});
  }

  Future<MyData?> getOneRandom(String correlationId, String filter) async {
    return await super.getOneRandom_(correlationId, filter);
  }

  Future<List<MyData>> getListByFilter(
      String correlationId, String filter, sort, select) async {
    return await super.getListByFilter_(correlationId, filter, sort, select);
  }

  Future<int> getCountByFilter(String correlationId, String filter) async {
    return await super.getCountByFilter_(correlationId, filter);
  }

  Future<DataPage<MyData>> getPageByFilter(String correlationId, String filter,
      PagingParams paging, sort, select) async {
    return await super
        .getPageByFilter_(correlationId, filter, paging, sort, select);
  }

  Future<void> deleteByFilter(String correlationId, String filter) async {
    return await super.deleteByFilter_(correlationId, filter);
  }
}

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,

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

var result = await persistence.GetOneRandomAsync(null, "key='key 1'");

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

var result = await persistence.getOneRandom('123', "key='key 1'");

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

Not available

we can obtain the record values as

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

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

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

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

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.