pip_services3_postgres

Description:

pip services3 postgres

PostgreSQL components for Pip.Service in Dart #
This module is a part of the Pip.Services polyglot microservices toolkit. It provides a set of components to implement PostgreSQL persistence.
The module contains the following packages:

Build - Factory to create PostreSQL persistence components.
Connect - Connection component to configure PostgreSQL connection to database.
Persistence - abstract persistence components to perform basic CRUD operations.

Quick links:

Configuration
API Reference
Change Log
Get Help
Contribute

Use #
Add this to your package's pubspec.yaml file:
dependencies:
pip_services3_postgres: version
copied to clipboard
As an example, lets create persistence for the following data object.
import 'package:pip_services3_commons/pip_services3_commons.dart';

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

MyObject();

MyObject.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
MyObject clone() {
return MyObject.from(id, key, content);
}
}
copied to clipboard
The persistence component shall implement the following interface with a basic set of CRUD operations.
abstract class IMyPersistence {
Future<DataPage<MyObject>> getPageByFilter(
String? correlationId, FilterParams? filter, PagingParams? paging);

Future<MyObject?> getOneById(String? correlationId, String id);

Future<MyObject?> getOneByKey(String? correlationId, String key);

Future<MyObject?> create(String? correlationId, MyObject? item);

Future<MyObject?> update(String? correlationId, MyObject? item);

Future<MyObject?> set(String? correlationId, MyObject? item);

Future<MyObject?> deleteById(String? correlationId, String? id);
}
copied to clipboard
To implement postgresql persistence component you shall inherit IdentifiablePostgresPersistence.
Most CRUD operations will come from the base class. You only need to override getPageByFilter method with a custom filter function.
And implement a getOneByKey custom persistence method that doesn't exist in the base class.
class MyPostgresPersistence
extends IdentifiablePostgresPersistence<MyObject, String> {
MyPostgresPersistence() : super('myobjects', null) {
ensureSchema_(
"CREATE TABLE myobjects (id VARCHAR(32) PRIMARY KEY, key VARCHAR(50), content VARCHAR(255))");
ensureIndex_("myobjects_key", {'key': 1}, {'unique': true});
}

@override
void defineSchema_() {
// pass
}

String? _composeFilter(FilterParams? filter) {
filter = filter ?? FilterParams();

var criteria = [];

var id = filter.getAsNullableString('id');
if (id != null) criteria.add("id='" + id + "'");

var tempIds = filter.getAsNullableString("ids");
if (tempIds != null) {
var ids = tempIds.split(",");
criteria.add("id IN ('" + ids.join("','") + "')");
}

var key = filter.getAsNullableString("key");
if (key != null) criteria.add("key='" + key + "'");

return criteria.length > 0 ? criteria.join(" AND ") : null;
}

Future<DataPage<MyObject>> getPageByFilter(
String? correlationId, FilterParams? filter, PagingParams? paging) {
return super.getPageByFilter_(
correlationId, _composeFilter(filter), paging, null, null);
}

Future<MyObject?> getOneByKey(String? correlationId, String key) async {
var query =
"SELECT * FROM " + this.quotedTableName_() + " WHERE \"key\"=@1";
var params = {'1': key};

var res = await client_!.query(query, substitutionValues: params);

var resValues = res.isNotEmpty ? res.first[0][1] : null;

var item = this.convertToPublic_(resValues);

if (item == null)
this.logger_.trace(correlationId, "Nothing found from %s with key = %s",
[this.tableName_, key]);
else
this.logger_.trace(correlationId, "Retrieved from %s with key = %s",
[this.tableName_, key]);

item = this.convertToPublic_(item);
return item;
}
}
copied to clipboard
Alternatively you can store data in non-relational format using IdentificableJsonPostgresPersistence.
It stores data in tables with two columns - id with unique object id and data with object data serialized as JSON.
To access data fields you shall use data->'field' expression or data->>'field' expression for string values.
class MyPostgresJsonPersistence
extends IdentifiableJsonPostgresPersistence<MyObject, String> {
MyPostgresJsonPersistence() : super('myobjects_json', null) {
clearSchema();
ensureTable_(idType: "VARCHAR(32)", dataType: "JSONB");
ensureIndex_(this.tableName_! + '_json_key', {"(data->>'key')": 1},
{'unique': true});
}

@override
void defineSchema_() {
// pass
}

String? _composeFilter(FilterParams? filter) {
filter = filter ?? FilterParams();

var criteria = [];

var id = filter.getAsNullableString('id');
if (id != null) criteria.add("data->>'id'='" + id + "'");

var tempIds = filter.getAsNullableString("ids");
if (tempIds != null) {
var ids = tempIds.split(",");
criteria.add("data->>'id' IN ('" + ids.join("','") + "')");
}

var key = filter.getAsNullableString("key");
if (key != null) criteria.add("data->>'key'='" + key + "'");

return criteria.length > 0 ? criteria.join(" AND ") : null;
}

Future<DataPage<MyObject>> getPageByFilter(
String? correlationId, FilterParams? filter, PagingParams? paging) {
return super.getPageByFilter_(
correlationId, _composeFilter(filter), paging, 'id', null);
}

Future<MyObject?> getOneByKey(String? correlationId, String key) async {
var query =
"SELECT * FROM " + this.quotedTableName_() + " WHERE data->>'key'=@1";
var params = {'1': key};

var res = await client_!.query(query, substitutionValues: params);

var resValues = res.isNotEmpty ? res.first[0][1] : null;

var item = this.convertToPublic_(resValues);

if (item == null)
this.logger_.trace(correlationId, "Nothing found from %s with key = %s",
[this.tableName_, key]);
else
this.logger_.trace(correlationId, "Retrieved from %s with key = %s",
[this.tableName_, key]);

item = this.convertToPublic_(item);
return item;
}
}
copied to clipboard
Configuration for your microservice that includes postgresql persistence may look the following way.
...
{{#if POSTGRES_ENABLED}}
- descriptor: pip-services:connection:postgres:con1:1.0
connection:
uri: {{{POSTGRES_SERVICE_URI}}}
host: {{{POSTGRES_SERVICE_HOST}}}{{#unless POSTGRES_SERVICE_HOST}}localhost{{/unless}}
port: {{POSTGRES_SERVICE_PORT}}{{#unless POSTGRES_SERVICE_PORT}}5432{{/unless}}
database: {{POSTGRES_DB}}{{#unless POSTGRES_DB}}app{{/unless}}
credential:
username: {{POSTGRES_USER}}
password: {{POSTGRES_PASS}}

- descriptor: myservice:persistence:postgres:default:1.0
dependencies:
connection: pip-services:connection:postgres:con1:1.0
table: {{POSTGRES_TABLE}}{{#unless POSTGRES_TABLE}}myobjects{{/unless}}
{{/if}}
...
copied to clipboard
Now you can install package from the command line:
pub get
copied to clipboard
Develop #
For development you shall install the following prerequisites:

Dart SDK 2
Visual Studio Code or another IDE of your choice
Docker

Install dependencies:
pub get
copied to clipboard
Run automated tests:
pub run test
copied to clipboard
Generate API documentation:
./docgen.ps1
copied to clipboard
Before committing changes run dockerized build and test as:
./build.ps1
./test.ps1
./clear.ps1
copied to clipboard
Contacts #
The module is created and maintained by

Sergey Seroukhov
Danil Prisiazhnyi