MySQL persistence
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-services4-mysql-node --save
go get -u github.com/pip-services4/pip-services4-go/pip-services4-mysql-go/persistence@latest
pip install pip-services4-mysql
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;
}
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,
}
}
from pip_services4_data.data import IStringIdentifiable
class MyData(IStringIdentifiable):
def __init__(self, id: str = None, key: str = None, content: str = None):
self.id = id
self.key = key
self.content = content
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" };
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"}
data1 = MyData('1','key 1', 'content 1')
data2 = MyData('2','key 2', 'content 2')
data3 = MyData('3','key 3', 'content 3')
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-services4-mysql-node';
import (
mysqlpersist "github.com/pip-services4/pip-services4-go/pip-services4-mysql-go/persistence"
)
from pip_services4_mysql.persistence import MySqlPersistence
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(ctx: Context, filter: any): Promise<MyData> {
return super.getOneRandom(ctx, filter);
}
public getListByFilter(ctx: Context, filter: any, sort: any, select: any): Promise<MyData[]> {
return super.getListByFilter(ctx, filter, sort, select);
}
public getCountByFilter(ctx: Context, filter: any): Promise<number> {
return super.getCountByFilter(ctx, filter);
}
public getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any, select: any): Promise<DataPage<MyData>> {
return super.getPageByFilter(ctx, filter, paging, sort, select);
}
public deleteByFilter(ctx: Context, filter: any): Promise<void> {
return super.deleteByFilter(ctx, filter);
}
}
import (
"context"
mysqlpersist "github.com/pip-services4/pip-services4-go/pip-services4-mysql-go/persistence"
cquery "github.com/pip-services4/pip-services4-go/pip-services4-data-go/query"
)
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, filter string,
paging cquery.PagingParams, sort string, selection string) (page cquery.DataPage[MyData], err error) {
return c.MySqlPersistence.GetPageByFilter(ctx, filter, paging, sort, selection)
}
func (c *MyMySqlPersistence) GetListByFilter(ctx context.Context, filter string,
sort string, selection string) (items []MyData, err error) {
return c.MySqlPersistence.GetListByFilter(ctx, filter, sort, selection)
}
func (c *MyMySqlPersistence) GetCountByFilter(ctx context.Context, filter string) (int64, error) {
return c.MySqlPersistence.GetCountByFilter(ctx, filter)
}
func (c *MyMySqlPersistence) GetOneRandom(ctx context.Context, filter string) (item MyData, err error) {
return c.MySqlPersistence.GetOneRandom(ctx, filter)
}
func (c *MyMySqlPersistence) DeleteByFilter(ctx context.Context, filter string) (err error) {
return c.MySqlPersistence.DeleteByFilter(ctx, filter)
}
from typing import Optional, Any, List
from pip_services4_data.query import PagingParams, DataPage
from pip_services4_components.context import IContext
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, context: Optional[IContext], filter: Any) -> MyData:
return super().get_one_random(correlation_id, filter)
def get_list_by_filter(self, context: Optional[IContext], 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, context: Optional[IContext], filter: Any) -> int:
return super().get_count_by_filter(correlation_id, filter)
def get_page_by_filter(self, context: Optional[IContext], 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, context: Optional[IContext], filter: Any) -> int:
return super().delete_by_filter(context, filter)
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-services4-components-node';
let persistence = new MyMySqlPersistence();
persistence.configure(ConfigParams.fromTuples(
"connection.host", "localhost",
"connection.port", 3306,
"credential.username", "root",
"credential.password", "",
"connection.database", "pip"
));
import cconf "github.com/pip-services4/pip-services4-go/pip-services4-components-go/config"
persistence := NewMyMySqlPersistence()
persistence.Configure(context.Background(), cconf.NewConfigParamsFromTuples(
"connection.host", "localhost",
"connection.port", 3306,
"credential.username", "root",
"credential.password", "",
"connection.database", "pip",
))
from pip_services4_components.config import ConfigParams
persistence = MyMySqlPersistence()
persistence.configure(ConfigParams.from_tuples(
"connection.host", "localhost",
"connection.port", 3306,
"credential.username", "root",
"credential.password", "password",
"connection.database", "pip"
))
Connection
After implementing our persistence component, we need to connect it to our database with the open() method.
result.id // Returns '1'
result.key // Returns 'key 1'
result.content // Returns 'content 1'
err := persistence.Open(context.Background())
persistence.open(None)
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.
let result = await persistence.getOneRandom(null, "`key`='key 1'");
res, err := persistence.Create(context.Background(), data1)
result = persistence.create(None, data1)
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'
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.getListByFilter(null, "`key`='key 1'", null, null);
result, err := persistence.GetOneRandom(context.Background(), "`key`='key 1'")
result = persistence.get_one_random(None, "`key`='key 1'")
Where
result[0].id // Returns '1'
result[0].key // Returns 'key 1'
result[0].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'
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.getPageByFilter(null, "`key`='key 1'", null, null, null);
itemsList, err := persistence.GetListByFilter(context.Background(), "`key`='key 1'", "", "")
result = persistence.get_list_by_filter(None, "`key`='key 1'", None, None)
Where
result.data[0].id // Returns '1'
result.data[0].key // Returns 'key 1'
result.data[0].content // Returns 'content 1'
itemsList[0].Id // Returns '1'
itemsList[0].Key // Returns 'key 1'
itemsList[0].Content // Returns 'content 1'
result[0].id # Returns '1'
result[0].key # Returns 'key 1'
result[0].content # Returns 'content 1'
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.getCountByFilter(null, "`key`='key 1'");
page, err := persistence.GetPageByFilter(context.Background(), "`key`='key 1'", *cquery.NewEmptyPagingParams(), "", "")
result = persistence.get_page_by_filter(None, "`key`='key 1'", None, None, None)
Where
await persistence.deleteByFilter(null, "`key`='key 1'");
itemsList[0].Id // Returns '1'
itemsList[0].Key // Returns 'key 1'
itemsList[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'
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’.
await persistence.deleteByFilter(null, "`key`='key 1'");
itemsList[0].Id // Returns '1'
itemsList[0].Key // Returns 'key 1'
itemsList[0].Content // Returns 'content 1'
result = persistence.get_count_by_filter(None, "`key`='key 1'") # Returns 1
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’.
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(ctx: Context, filter: any): Promise<MyData> {
return super.getOneRandom(ctx, filter);
}
public getListByFilter(ctx: Context, filter: any, sort: any, select: any): Promise<MyData[]> {
return super.getListByFilter(ctx, filter, sort, select);
}
public getCountByFilter(ctx: Context, filter: any): Promise<number> {
return super.getCountByFilter(ctx, filter);
}
public getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any, select: any): Promise<DataPage<MyData>> {
return super.getPageByFilter(ctx, filter, paging, sort, select);
}
}
err = persistence.DeleteByFilter(context.Background(), "`key`='key 1'")
persistence.delete_by_filter(None, "`key`='key 1'")
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 { ConfigParams } from 'pip-services4-components-node';
let persistence = new MyMySqlPersistence();
persistence.configure(ConfigParams.fromTuples(
"connection.host", "localhost",
"connection.port", 3306,
"credential.username", "user",
"credential.password", "password",
"connection.database", "pip"
));
import (
mysqlpersist "github.com/pip-services4/pip-services4-go/pip-services4-mysql-go/persistence"
)
from pip_services4_mysql.persistence import IdentifiableMySqlPersistence
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.
await persistence.open(null);
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, filter string,
paging cquery.PagingParams, sort string, selection string) (page cquery.DataPage[MyData], err error) {
return c.MySqlPersistence.GetPageByFilter(ctx, filter, paging, sort, selection)
}
func (c *MyMySqlPersistence) GetListByFilter(ctx context.Context, filter string,
sort string, selection string) (items []MyData, err error) {
return c.MySqlPersistence.GetListByFilter(ctx, filter, sort, selection)
}
func (c *MyMySqlPersistence) GetCountByFilter(ctx context.Context, filter string) (int64, error) {
return c.MySqlPersistence.GetCountByFilter(ctx, filter)
}
func (c *MyMySqlPersistence) GetOneRandom(ctx context.Context, filter string) (item MyData, err error) {
return c.MySqlPersistence.GetOneRandom(ctx, filter)
}
func (c *MyMySqlPersistence) DeleteByFilter(ctx context.Context, filter string) (err error) {
return c.MySqlPersistence.DeleteByFilter(ctx, filter)
}
from typing import Optional, Any, List
from pip_services4_data.query import PagingParams, DataPage
from pip_services4_components.context import IContext
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, context: Optional[IContext], filter: Any) -> MyData:
return super().get_one_random(correlation_id, filter)
def get_list_by_filter(self, context: Optional[IContext], filter: Any, sort: Any, select: Any) -> List[MyData]:
return super().get_list_by_filter(context, filter, sort, select)
def get_count_by_filter(self, context: Optional[IContext], filter: Any) -> int:
return super().get_count_by_filter(context, filter)
def get_page_by_filter(self, context: Optional[IContext], filter: Any, paging: PagingParams, sort: Any, select: Any) -> DataPage:
return super().get_page_by_filter(context, filter, paging, sort, select)
Once the persistence component has been created, we configure it according to our database configuration details.
let result = await persistence.create(null, data1);
import cconf "github.com/pip-services4/pip-services4-go/pip-services4-components-go/config"
persistence := NewMyMySqlPersistence()
persistence.Configure(context.Background(), cconf.NewConfigParamsFromTuples(
"connection.host", "localhost",
"connection.port", 3306,
"credential.username", "root",
"credential.password", "",
"connection.database", "pip",
))
from pip_services4_components.config import ConfigParams
persistence1 = MyMySqlPersistence()
persistence1.configure(ConfigParams.from_tuples(
"connection.host", "localhost",
"connection.port", 3306,
"credential.username", "root",
"credential.password", "password",
"connection.database", "pip1"
))
Connection
Next to defining our persistence component, we connect to our database by using the open() method.
result.id // Returns '1'
result.key // Returns 'key 1'
result.content // Returns 'content 1'
err := persistence.Open(context.Background())
persistence.open(None)
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.getOneById(null, "2");
import (
mysqlpersist "github.com/pip-services4/pip-services4-go/pip-services4-mysql-go/persistence"
)
result = persistence.create(None, data1)
Where
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 '1'
result.key # Returns 'key 1'
result.content # Returns 'content 1'
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.getListByIds(null, ["1", "2"]);
res, err = persistence.GetOneById(context.Background(), "123", "2")
result = persistence.get_one_by_id(None, '2')
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.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'
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.update(null, { id: "2", key: "new key 2", content: "new content 2" });
itemsList, err := persistence.GetListByIds(context.Background(), "123", []string{"1", "2"})
result = persistence.get_list_by_ids(None, ['2','3'])
Where
result.id // Returns '2'
result.key // Returns 'key 2'
result.content // Returns 'content 2'
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'
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.updatePartially(null, "3", AnyValueMap.fromTuples("content", "new content 3"));
res, err = persistence.Update(context.Background(), "123", MyData{Id: "2", Key: "new key 2", Content: "new content 2"})
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'
Where
result.id // Returns '3'
result.key // Returns 'key 3'
result.content // Returns 'content 3'
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'
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.deleteById(null, "1");
res, err = persistence.UpdatePartially(context.Background(), "123", "3", *cdata.NewAnyValueMapFromTuples("content", "new content 3"))
result = persistence.update_partially(None, '3', AnyValueMap({'content':'new content 3'}))
Where
result.id // Returns '1'
result.key // Returns 'key 1'
result.content // Returns 'content 1'
result.id # Returns '3'
result.key # Returns 'key 3'
result.content # Returns 'content 3'
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.
await persistence.deleteByIds(null, ["2", "3"]);
res, err = persistence.DeleteById(context.Background(), "123", "1")
result = persistence.delete_by_id(None, '1')
Where
import { IdentifiableJsonMySqlPersistence } from 'pip-services4-mysql-node';
result.Id // Returns '1'
result.Key // Returns 'key 1'
result.Content // Returns 'content 1'
result = persistence.delete_by_id(None, '1')
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.
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(ctx: Context, filter: any): Promise<MyData> {
return super.getOneRandom(ctx, filter);
}
public getListByFilter(ctx: Context, filter: any, sort: any, select: any): Promise<MyData[]> {
return super.getListByFilter(ctx, filter, sort, select);
}
public getCountByFilter(ctx: Context, filter: any): Promise<number> {
return super.getCountByFilter(ctx, filter);
}
public getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any, select: any): Promise<DataPage<MyData>> {
return super.getPageByFilter(ctx, filter, paging, sort, select);
}
}
err = persistence.DeleteByIds(context.Background(), "123", []string{"2", "3"})
persistence.delete_by_ids(None, ['2','3'])
IdentifiableJsonMySqlPersistence
Pre-requisites
In order to use this component, we need to import it first. This can be done with the following command
let result = await persistence.getOneRandom(null, "`key`='key 1'");
import (
mysqlpersist "github.com/pip-services4/pip-services4-go/pip-services4-mysql-go/persistence"
)
from pip_services4_mysql.persistence import IdentifiableJsonMySqlPersistence
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
result.id // Returns '1'
result.key // Returns 'key 1'
result.content // Returns 'content 1'
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, filter string,
paging cquery.PagingParams, sort string, selection string) (page cquery.DataPage[MyData], err error) {
return c.MySqlPersistence.GetPageByFilter(ctx, filter, paging, sort, selection)
}
func (c *MyMySqlPersistence) GetListByFilter(ctx context.Context, filter string,
sort string, selection string) (items []MyData, err error) {
return c.MySqlPersistence.GetListByFilter(ctx, filter, sort, selection)
}
func (c *MyMySqlPersistence) GetCountByFilter(ctx context.Context, filter string) (int64, error) {
return c.MySqlPersistence.GetCountByFilter(ctx, filter)
}
func (c *MyMySqlPersistence) GetOneRandom(ctx context.Context, filter string) (item MyData, err error) {
return c.MySqlPersistence.GetOneRandom(ctx, filter)
}
func (c *MyMySqlPersistence) DeleteByFilter(ctx context.Context, filter string) (err error) {
return c.MySqlPersistence.DeleteByFilter(ctx, filter)
}
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, context: Optional[IContext], filter: Any) -> MyData:
return super().get_one_random(correlation_id, filter)
def get_list_by_filter(self, context: Optional[IContext], filter: Any, sort: Any, select: Any) -> List[MyData]:
return super().get_list_by_filter(context, filter, sort, select)
def get_count_by_filter(self, context: Optional[IContext], filter: Any) -> int:
return super().get_count_by_filter(context, filter)
def get_page_by_filter(self, context: Optional[IContext], filter: Any, paging: PagingParams, sort: Any, select: Any) -> DataPage:
return super().get_page_by_filter(context, filter, paging, sort, select)
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,
await persistence.open(null);
result, err := persistence.GetOneRandom(context.Background(), "`key`='key 1'")
result = persistence.get_one_random(None, "`key`='key 1'")
we can obtain the record values as
await persistence.create(null, data1);
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'
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.