MySQL persistence

How to persist data using a MySQL database.

Key takeaways

MySqlPersistence	Persistence component that stores data in MySQL databases using the official driver and implements several CRUD operations over data items.
IdentifiableMySqlPersistence	Persistence component that stores data in MySQL databases and implements several CRUD operations over data items with unique ids.
IdentifiableJsonMySqlPersistence	Persistence component that stores data in MySQL databases in JSON or JSONB fields and implements several CRUD operations over data items with unique ids.

Introduction

In this tutorial, we will see how to interact with a MySQL database in order to provide persistence to our code. First, we will see the main CRUD methods available in the three persistence components contained in the MySQL module. Then, we will see how to run SQL queries with these components. We will conclude with a summary of all the learned concepts.

MySQL persistence

Pip.Services contains a module titled MySQL, which has three components for data persistence. These components are MySqlPersistence, IdentifiablemySqlPesistence and identifialeJsonMySqlPersistence. The following sections explain their usage.

Pre-requisites

In order to work with the MySQL persistence module, we need to install it. This can be done with the following command.

npm install pip-services3-mysql-nodex --save

dotnet add package PipServices3.Mysql

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

dart pub add pip_services3_mysql

pip install pip-services3-mysql

Not available

Data object

In the examples of this tutorial, we will use the following data structure

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

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

func (d *MyData) SetId(id string) {
	d.Id = id
}

func (d MyData) GetId() string {
	return d.Id
}

func (d MyData) Clone() MyData {
	return MyData{
		Id:      d.Id,
		Key:     d.Key,
		Content: d.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 the following instances of it.

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

MySqlPersistence

This is the most basic persistence class and is used to create persistence components that store data in MySQL 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 { MySqlPersistence } from 'pip-services3-mysql-nodex';

using PipServices3.MySql.Persistence;

import (
	mysqlpersist "github.com/pip-services3-gox/pip-services3-mysql-gox/persistence"
)

import 'package:pip_services3_mysql/pip_services3_mysql.dart';

from pip_services3_mysql.persistence import MySqlPersistence

Not available

Component implementation

To implement this component, we define a class that inherits the methods from the PostgresPersistence class. Our code will look something like this, where mydata is the name of the table in our database where we are going to store our data: 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 MyMySqlPersistence extends MySqlPersistence<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 VARCHAR(32) PRIMARY KEY, `key` VARCHAR(50), `content` TEXT)');
        // create an index
        this.ensureIndex(this._tableName + '_key', { key: 1 }, { unique: true });
    }

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

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

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

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

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

public class MyMySqlPersistence : MySqlPersistence<MyData>
{
    public MyMySqlPersistence() : base("mydata") { }

    protected override void DefineSchema()
    {
        // clear all previously autogenerated schemas
        ClearSchema();
        // create a table 
        EnsureSchema($"CREATE TABLE `{_tableName}` (`id` VARCHAR(32) PRIMARY KEY, `key` VARCHAR(50), `content` TEXT, `create_time_utc` DATETIME, `sub_dummy` 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, sort, select);
    }

    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 MyMySqlPersistence struct {
	*mysqlpersist.MySqlPersistence[MyData]
}

func NewMyMySqlPersistence() *MyMySqlPersistence {
	c := &MyMySqlPersistence{}
	c.MySqlPersistence = mysqlpersist.InheritMySqlPersistence[MyData](c, "mydata")
	return c
}

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

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

	return c.MySqlPersistence.GetPageByFilter(ctx, correlationId, filter, paging, sort, selection)
}

func (c *MyMySqlPersistence) GetListByFilter(ctx context.Context, correlationId string,
	filter string, sort string, selection string) (items []MyData, err error) {

	return c.MySqlPersistence.GetListByFilter(ctx, correlationId, filter, sort, selection)
}

func (c *MyMySqlPersistence) GetCountByFilter(ctx context.Context, correlationId string, filter string) (int64, error) {
	return c.MySqlPersistence.GetCountByFilter(ctx, correlationId, filter)
}

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

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

class MyMySqlPersistence extends MySqlPersistence<MyData> {
  MyMySqlPersistence() : super('mydata', null);

  @override
  void defineSchema_() {
    // clear all previously autogenerated schemas
    clearSchema();
    // create a table
    ensureSchema_('CREATE TABLE `' +
        tableName_! +
        '` (id VARCHAR(32) PRIMARY KEY, `key` VARCHAR(50), `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 MyMySqlPersistence(MySqlPersistence):

    def __init__(self):
        super(MyMySqlPersistence, 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(32) PRIMARY KEY, `key` VARCHAR(50), `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

Once our persistence component has been defined, we need to create an instance of it and configure the connection to our MySQL database.

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

let persistence = new MyMySqlPersistence();
persistence.configure(ConfigParams.fromTuples(
    "connection.host", "localhost",
    "connection.port", 3306,
    "credential.username", "root",
    "credential.password", "",
    "connection.database", "pip"
));

using PipServices3.Commons.Config;

var persistence = new MyMySqlPersistence();
persistence.Configure(ConfigParams.FromTuples(
    "connection.host", "localhost",
    "connection.port", 3306,
    "credential.username", "user",
    "credential.password", "password",
    "connection.database", "pip"
));

import cconf "github.com/pip-services3-gox/pip-services3-commons-gox/config"

persistence := NewMyMySqlPersistence()
persistence.Configure(context.Background(), cconf.NewConfigParamsFromTuples(
	"connection.host", "localhost",
	"connection.port", 3306,
	"credential.username", "root",
	"credential.password", "",
	"connection.database", "pip",
))

import 'package:pip_services3_commons/pip_services3_commons.dart';

var persistence = MyMySqlPersistence();
persistence.configure(ConfigParams.fromTuples([
  'connection.host', 'localhost',
  'connection.port', 3306,
  'credential.username', 'user',
  'credential.password', 'password',
  'connection.database', 'pip'
]));

from pip_services3_commons.config import ConfigParams

persistence = MyMySqlPersistence()
persistence.configure(ConfigParams.from_tuples(
    "connection.host", "localhost",
    "connection.port", 3306,
    "credential.username", "user",
    "credential.password", "password",
    "connection.database", "pip"
))

Not available

Connection

After implementing our persistence component, we need to connect it to our database with the open() method.

await persistence.open(null);

await persistence.OpenAsync(null);

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

await persistence.open('123');

persistence.open(None)

Not available

CRUD operations

This class presents several methods that can be used to perform CRUD operations. The sections below show the main ones with examples.

Create

This method is used to insert a record in a database. It accepts the correlation_id and the data object as input parameters and returns a MyData object containing the stored record. The following is an example of its usage.

await persistence.create(null, data1);

await persistence.CreateAsync(null, data1);

res, err := persistence.Create(context.Background(), "123", data1)

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

Retrieve

There are four methods that can be used to retrieve records from a database. They are:

getOneRandom()

This method retrieves a random record based on a given filter and returns a MySqlPersistence object with the retrieved record. In the example below, we ask for a random record with an id value of ‘1’.

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

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

result, err := persistence.GetOneRandom(context.Background(), "123", "`key`='key 1'")

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

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

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.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()

his method returns a set of records that comply with a given filter. The result is in the form of a list of MySqlPersistence objects containing the retrieved records. The following example retrieves all records with an id equal to ‘1’.

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

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

itemsList, err := persistence.GetListByFilter(context.Background(), "123", "`key`='key 1'", "", "")

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

result = persistence.get_list_by_filter(None, "key='key1'", 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()

Similar to the previous one, this method retrieves a set of records that comply with a given filter. The input parameters are the correlation_id, a filter, and sorting and projection JSON objects. The output is in the form of a DataPage object, and the records are contained in the data field of this object. The following example shows how to extract those records with an id equal to ‘1’.

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

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

page, err := persistence.GetPageByFilter(context.Background(), "123", "`key`='key 1'", *cdata.NewEmptyPagingParams(), "", "")

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

Where

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

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

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

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

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

Not available

getCountByFilter()

This method returns an integer indicating the number of records that comply with a given filter. The example below shows how to obtain the number of records with a key equal to ‘key 1’.

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

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

count, err := persistence.GetCountByFilter(context.Background(), "123", "`key`='key 1'")

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

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

Not available

Update

This class doesn’t contain an update method. However, we can create one by using an SQL query. The Using SQL section explains how to do this.

Delete

This method deletes the record specified by the given id. The following example shows how to delete a record with an id equal to ‘1’.

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

await persistence.DeleteByFilterAsync(null, "`key`='key 1'");

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

await persistence.DeleteByFilterAsync(null, "`key`='key 1'");

persistence.delete_by_filter(None, "`key`='key 1'")

Not available

IdentifiableMySqlPersistence

This component allows us to store data in MySQL databases and implement several CRUD operations on data items with unique ids. The following sections explain how to perform each of the CRUD operations with the methods provided by this class.

Pre-requisites

In order to use this method, we must first import it. We can use the following command to do this.

import { IdentifiableMySqlPersistence } from 'pip-services3-mysql-nodex';

using PipServices3.MySql.Persistence;

import mysqlpersist "github.com/pip-services3-gox/pip-services3-mysql-gox/persistence"

import 'package:pip_services3_mysql/pip_services3_mysql.dart';

from pip_services3_mysql.persistence import IdentifiableMySqlPersistence

Not available

Component implementation

To implement an identifiable persistence component, we need to define a subclass of the IdentifiableMySqlPersistence class. Our code will look something like the one below, where mydata is the name of the table we are going to use to perform CRUD operations.

export class MyMySqlPersistence extends IdentifiableMySqlPersistence<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 VARCHAR(32) PRIMARY KEY, `key` VARCHAR(50), `content` TEXT)');
        // create an index
        this.ensureIndex(this._tableName + '_key', { key: 1 }, { unique: true });
    }

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

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

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

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

public class MyMySqlPersistence : IdentifiableMySqlPersistence<MyData, string>
{
    public MyMySqlPersistence() : base("mydata") { }

    protected override void DefineSchema()
    {
        // clear all previously autogenerated schemas
        ClearSchema();
        // create a table 
        EnsureSchema($"CREATE TABLE `{_tableName}` (`id` VARCHAR(32) PRIMARY KEY, `key` VARCHAR(50), `content` TEXT, `create_time_utc` DATETIME, `sub_dummy` 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, sort, select);
    }

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

type MyMySqlPersistence struct {
	*mysqlpersist.IdentifiableMySqlPersistence[MyData, string]
}

func NewMyMySqlPersistence() *MyMySqlPersistence {
	c := &MyMySqlPersistence{}
	c.IdentifiableMySqlPersistence = mysqlpersist.InheritIdentifiableMySqlPersistence[MyData, string](c, "mydata")
	return c
}

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

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

	return c.MySqlPersistence.GetPageByFilter(ctx, correlationId, filter, paging, sort, selection)
}

func (c *MyMySqlPersistence) GetListByFilter(ctx context.Context, correlationId string,
	filter string, sort string, selection string) (items []MyData, err error) {

	return c.MySqlPersistence.GetListByFilter(ctx, correlationId, filter, sort, selection)
}

func (c *MyMySqlPersistence) GetCountByFilter(ctx context.Context, correlationId string, filter string) (int64, error) {
	return c.MySqlPersistence.GetCountByFilter(ctx, correlationId, filter)
}

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

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

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

  @override
  void defineSchema_() {
    // clear all previously autogenerated schemas
    clearSchema();
    // create a table
    ensureSchema_('CREATE TABLE `' +
        tableName_! +
        '` (id VARCHAR(32) PRIMARY KEY, `key` VARCHAR(50), `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);
  }
}

class MyMySqlPersistence(IdentifiableMySqlPersistence):

    def __init__(self):
        super(MyMySqlPersistence, 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(32) PRIMARY KEY, `key` VARCHAR(50), `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)

Not available

Once the persistence component has been created, we configure it according to our database configuration details.

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

let persistence = new MyMySqlPersistence();
persistence.configure(ConfigParams.fromTuples(
    "connection.host", "localhost",
    "connection.port", 3306,
    "credential.username", "user",
    "credential.password", "password",
    "connection.database", "pip"
));

using PipServices3.Commons.Config;

var persistence = new MyMySqlPersistence();
persistence.Configure(ConfigParams.FromTuples(
    "connection.host", "localhost",
    "connection.port", "3306",
    "connection.database", "pip",
    "credential.username", "user",
    "credential.password", "password"
));

import cconf "github.com/pip-services3-gox/pip-services3-commons-gox/config"

persistence := NewMyMySqlPersistence()
persistence.Configure(context.Background(), cconf.NewConfigParamsFromTuples(
	"connection.host", "localhost",
	"connection.port", 3306,
	"credential.username", "user",
	"credential.password", "password",
	"connection.database", "pip",
))

import 'package:pip_services3_commons/pip_services3_commons.dart';

var persistence = MyMySqlPersistence();
persistence.configure(ConfigParams.fromTuples([
  'connection.host', 'localhost',
  'connection.port', 3306,
  'credential.username', 'user',
  'credential.password', 'password',
  'connection.database', 'pip'
]));

from pip_services3_commons.config import ConfigParams

persistence = MyMySqlPersistence()
persistence.configure(ConfigParams.from_tuples(
    "connection.host", "localhost",
    "connection.port", 3306,
    "credential.username", "user",
    "credential.password", "password",
    "connection.database", "pip"
))

Not available

Connection

Next to defining our persistence component, we connect to our database by using the open() method.

await persistence.open(null);

await persistence.OpenAsync(null);

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

await persistence.open('123');

persistence.open(None)

Not available

CRUD operations

This class presents several methods for CRUD operations. They are explained in the following sections.

Create

To create a new record in our table, we can use the create() method, which takes the correlation_id and the data object to be stored as input parameters. Once executed, it returns a MyData object containing the stored record. The following example shows how to use it.

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

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

res, err := persistence.Create(context.Background(), "123", data1)

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

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

Retrieve

This class presents several methods that can be used to retrieve one or more records from a table. They are:

getOneById()

This method returns a record from the database. It accepts the correlation_id and the id of the record as input parameters. The following example shows how to use it.

let result = await persistence.getOneById(null, "2");

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

res, err = persistence.GetOneById(context.Background(), "123", "2")

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

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

Not available

Where

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

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

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

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

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

Not available

getListById()

This method returns a set of records from a database. It accepts the correlation_id and a list containing record ids as input parameters. The result is in the form of a list of MySqlPersistence objects, each containing a record. The example below shows how to use it.

let result = await persistence.getListByIds(null, ["1", "2"]);

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

itemsList, err := persistence.GetListByIds(context.Background(), "123", []string{"1", "2"})

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

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

Not available

Where

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

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

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

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

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

Not available

Update

This class provides two different methods that can be used to update records in a database namely update() and update_partially(). The following sections show how to use both of them.

update()

This method updates a record in a database. It takes the correlation_id and a data object as input parameters. If the update was successful, it returns the updated record. Otherwise, it returns None. The following example illustrates how to use it.

let result = await persistence.update(null, { id: "2", key: "new key 2", content: "new content 2" });

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

res, err = persistence.Update(context.Background(), "123", MyData{Id: "2", Key: "new key 2", Content: "new content 2"})

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

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

Not available

Where

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

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

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

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

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

Not available

updatePartially()

This method updates one or more fields of a given record. It takes the correlation_id, the id of the record to be updated, and a dictionary containing the fields and their updated values as input parameters. If the update was successful, it returns the updated record. Otherwise, it returns None.

let result = await persistence.updatePartially(null, "3", AnyValueMap.fromTuples("content", "new content 3"));

var result = await persistence.UpdatePartially(null, "3", AnyValueMap.FromTuples("content", "new content 3"));

res, err = persistence.UpdatePartially(context.Background(), "123", "3", *cdata.NewAnyValueMapFromTuples("content", "new content 3"))

var result = await persistence.updatePartially(null, '3', AnyValueMap.fromTuples(['content', 'new content 3']));

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

Not available

Where

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

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

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

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

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

Not available

Delete

This class presents two different methods that can be used to delete records from a database. They are:

deleteById()

This method deletes a record specified by its id. It also requires the correlation_id as input parameter. If the delete was successful, it returns the deleted record. Otherwise, it returns None. The example below explains how to use it.

let result = await persistence.deleteById(null, "1");

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

res, err = persistence.DeleteById(context.Background(), "123", "1")

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

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

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

deleteByIds()

This method deletes a set of records whose ids are specified in the form of a list. It also requires the correlation_id as input parameter. After execution, it returns None. The following example shows how to use it.

await persistence.deleteByIds(null, ["2", "3"]);

await persistence.DeleteByIdsAsync(null, new string[] { "2", "3" });

err = persistence.DeleteByIds(context.Background(), "123", []string{"2", "3"})

await persistence.deleteByIds(null, ['2', '3']);

persistence.delete_by_ids(None, ['2','3'])

Not available

IdentifiableJsonMySqlPersistence

Pre-requisites

In order to use this component, we need to import it first. This can be done with the following command

import { IdentifiableJsonMySqlPersistence } from 'pip-services3-mysql-nodex';

using PipServices3.MySql.Persistence;

import mysqlpersist "github.com/pip-services3-gox/pip-services3-mysql-gox/persistence"

import 'package:pip_services3_mysql/pip_services3_mysql.dart';

from pip_services3_mysql.persistence import IdentifiableJsonMySqlPersistence

Not available

Component implementation

In order to implement this component, we create a class that inherits it. In addition, we need to define the method defineSchema(), which will allow us to use a table with two fields, namely id and data, where the data field will store the JSON values. Our class will look something like this

export class MyMySqlPersistence extends IdentifiableJsonMySqlPersistence<MyData, string> {
    public constructor() {
        super('mydata_json');
    }

    protected defineSchema(): void {
        // clear all previously autogenerated schemas
        this.clearSchema();
        // create a table 
        this.ensureTable();
        this.ensureSchema('ALTER TABLE `' + this._tableName + '` ADD `data_key` VARCHAR(50) AS (JSON_UNQUOTE(`data`->"$.key"))');
        // create an index
        this.ensureIndex(this._tableName + '_json_key', { "data_key": 1 }, { unique: true });
    }

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

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

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

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

public class MyMySqlPersistence : IdentifiableJsonMySqlPersistence<MyData, string>
{
    public MyMySqlPersistence() : base("mydata") { }

    protected override void DefineSchema()
    {
        // clear all previously autogenerated schemas
        ClearSchema();
        // create a table 
        EnsureTable();
        EnsureSchema($"ALTER TABLE `{_tableName}` ADD `data_key` VARCHAR(50) AS (JSON_UNQUOTE(`data`->\"$.key\"))");
        // create an index
        EnsureIndex($"{_tableName}_key", new Dictionary<string, bool> { { "data_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, sort, select);
    }

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

type MyMySqlPersistence struct {
	*mysqlpersist.IdentifiableJsonMysqlPersistence[MyData, string]
}

func NewMyMySqlPersistence() *MyMySqlPersistence {
	c := &MyMySqlPersistence{}
	c.IdentifiableJsonMysqlPersistence = mysqlpersist.InheritIdentifiableJsonMysqlPersistence[MyData, string](c, "mydata_json")
	return c
}

func (c *MyMySqlPersistence) DefineSchema() {
	c.ClearSchema()
	c.EnsureTable("", "")
	c.EnsureSchema("ALTER TABLE `" + c.TableName + "` ADD `data_key` VARCHAR(50) AS (JSON_UNQUOTE(`data`->\"$.key\"))")
	c.EnsureIndex(c.TableName+"_json_key", map[string]string{"data_key": "1"}, map[string]string{"unique": "true"})
}

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

	return c.MysqlPersistence.GetPageByFilter(ctx, correlationId, filter, paging, sort, selection)
}

func (c *MyMySqlPersistence) GetListByFilter(ctx context.Context, correlationId string,
	filter string, sort string, selection string) (items []MyData, err error) {

	return c.MysqlPersistence.GetListByFilter(ctx, correlationId, filter, sort, selection)
}

func (c *MyMySqlPersistence) GetCountByFilter(ctx context.Context, correlationId string, filter string) (int64, error) {
	return c.MysqlPersistence.GetCountByFilter(ctx, correlationId, filter)
}

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

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

class MyMySqlPersistence
    extends IdentifiableJsonMySqlPersistence<MyData, String> {
  MyMySqlPersistence() : super('mydata_json', null);

  @override
  void defineSchema_() {
    // clear all previously autogenerated schemas
    clearSchema();
    // create a table
    ensureTable_();
    ensureSchema_('ALTER TABLE `' +
        tableName_! +
        '` ADD `data_key` VARCHAR(50) AS (JSON_UNQUOTE(`data`->"\$.key"))');
    // 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);
  }
}

class MyMySqlPersistence(IdentifiableJsonMySqlPersistence):

    def __init__(self):
        super(MyMySqlPersistence, self).__init__('mydata_json')

    def _define_schema(self):
        # clear all previously autogenerated schemas
        self._clear_schema()
        # create a table 
        self._ensure_table()
        self._ensure_schema(
            'ALTER TABLE `' + self._table_name + '` ADD `data_key` VARCHAR(50) AS (JSON_UNQUOTE(`data`->"$.key"))')
        # create an index
        self._ensure_index(self._table_name + '_json_key', {"data_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)

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 IdentifiableMySqlPersistence component.

CRUD operations

This class inherits most of its methods from the IdentifiableMySqlPersistece 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, err := persistence.GetOneRandom(context.Background(), "123", "`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 three different MySQL persistence components, namely MySqlPersistence, IdentifiableMySqlPersistence, and IdentifiableJsonMySqlPersistence. The first is the most basic component and is the parent class of the other two. The second is aimed at identifiable objects, that is, objects that have a unique id for each record, and, the third is used to persist JSON objects. For each of these components, we learned how to implement them and perform basic CRUD operations by using their methods.