SQL Server persistence

How to persist data using a SQLServer database.

Key takeaways

SqlServerPersistence	Persistence component that stores data in a SQLServer database using the official driver.
IdentifiableSqlServerPersistence	Persistence component that stores data in a SQLServer database and implements several CRUD operations over data items with unique ids.
IdentifialeJsonSqlServerPersistence	Persistence component that stores data in a SQLServer database in JSON or JSONB fields and implements several CRUD operations over data items with unique ids.

Introduction

This tutorial will help you understand how to create SQL Server persistence components using Pip.Services. It begins by explaining how to install the sqlserver module and create the data structure used in the tutorial’s examples. Then, it describes each of the three persistence classes available in the module, namely SqlServerPersistence, IdentifiableSqlServerPersistence and IdentifiableJsonSqlServerPersistence. It ends with a summary of the main learned concepts.

SQLServer persistence

The Pip.Services toolkit contains the sqlserver module, which has three persistence components. These classes are SqlServerPersistence, IdentifiableSqlServerPersistence and IdentifiableJsonSqlServerPersistence. The sections below show how to use each of these components via the use of examples.

Pre-requisites

In order to use this module, we need to install it first. This can be done with the following command:

npm install pip-services4-sqlserver-node --save

Not available

pip install pip-services4-sqlserver

Not available

Data object

Throughout this tutorial, we will work with examples based on the following data structure

import { IStringIdentifiable } from 'pip-services4-data-node';

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

Not available

from pip_services4_data.data import IStringIdentifiable

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

Not available

and the following implementations of it

let data1: MyData = { id: "1", key: "key 1", content:"content 1" };
let data2: MyData ={ id: "2", key: "key 2", content: "content 2" };
let data3: MyData = { id: "3", key: "key 3", content: "content 3" };

Not available

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

Not available

SqlServerPersistence

This is the most basic persistence component for SQLServer databases and is used as a base class for the other two components. The following sections explain its main methods and their usage.

Pre-requisites

In order to use this component, we must import it with the following command:

import { SqlServerPersistence } from 'pip-services4-sqlserver-node';

Not available

from pip_services4_sqlserver.persistence import SqlServerPersistence

Not available

Component implementation

Once the component has been imported, we create a persistence class that inherits it. In our example, we will work with a table named mydata. Our code will look something like this

export class MySqlServerPersistence
    extends SqlServerPersistence<MyData> {
    public constructor() {
        super('mydata');
    }

    protected defineSchema(): void {
        this.clearSchema();
        this.ensureSchema('CREATE TABLE [' + this._tableName + '] ([id] VARCHAR(32) PRIMARY KEY, [key] VARCHAR(50), [content] VARCHAR(MAX))');
        this.ensureIndex(this._tableName + '_key', { key: 1 }, { unique: true });
    }

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

    public getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any): Promise<DataPage<MyData>> {
        return super.getPageByFilter(ctx, filter, paging, null, null);
    }

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

Not available

from typing import Optional, Any, List
from pip_services4_sqlserver.persistence import SqlServerPersistence
from pip_services4_components.context import IContext
from pip_services4_data.query import PagingParams, DataPage

class MySqlServerPersistence(SqlServerPersistence):
    def __init__(self):
        super(MySqlServerPersistence, self).__init__('mydata')

    def _define_schema(self):
        self._clear_schema()
        self._ensure_schema(
            'CREATE TABLE [' + self._table_name + \
            '] ([id] VARCHAR(32) PRIMARY KEY, [key] VARCHAR(50), [content] VARCHAR(MAX))')
        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(context, 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)

Not available

Now, we create an instance of this class and configure it according to our database’s configuration parameters via the configure() method.

import { ConfigParams } from 'pip-services4-components-node';

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

Not available

from pip_services4_components.config import ConfigParams

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

Not available

Connection

Once that our component has been configured, we can connect it to our database.

await persistence.open(null);

Not available

persistence.open(None)

Not available

CRUD operations

This component presents several methods for CRUD operations. The following sections explain the main ones.

Create

To add a new record to our table, we use the create() method. This method accepts the context and the data object containing the record to be created as input parameters, and returns a SqlServerPersistence object containing the added record. The example below illustrates its usage.

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

Not available

result = persistence.create(None, data1)

Not available

Where

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

Not available

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

Not available

Retrieve

In order to retrieve records from our table, we can use three different methods, namely getOneRandom(), getListByFilter(), and getPageByFilter(). Additionally, we can use the getCountByFilter() method to obtain the number of records that comply with a given filter’s condition.

getOneRandom()

This method retrieves a random record according to a given filter. The next example shows how to use it.

let result = await persistence.getOneRandom(null, "[key]='key 3'");

Not available

rsistence.get_one_random(None, "[key]='key 3'")

Not available

Where

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

Not available

result = persistence.get_one_random(None, "[key]='key 3'")

Not available

getListByFilter()

This method returns a set of records in accordance with a given filter. It accepts the context, a filter, and sorting and projection parameters as inputs. It returns a SqlServerPersistence object with the returned records. The following example illustrates how to use it.

let result = await persistence.getListByFilter(null, "[key]='key 3'", null, null);

Not available

result = persistence.get_list_by_filter(None, "[key]='key 3'", None, None)

Not available

Where

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

Not available

result = persistence.get_list_by_filter(None, "[key]='key 3'", None, None)

Not available

getPageByFilter()

This method retrieves a set of records that comply with a given filter’s conditions. It takes the context, paging parameters, and JSON strings for sorting and projecting as input values. It returns a DataPage object with the retrieved records in its data field. The example below explains its usage.

let page = await persistence.getPageByFilter(null, "[key]='key 3'", null, null);

Not available

result = persistence.get_list_by_filter(None, "[key]='key 3'", None, None)

Not available

Where

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

Not available

result.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 records that comply with some given conditions. The example below shows how to use it.

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

Not available

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

Not available

Update

This class doesn’t present any method to update records in a table.

Delete

This component has the deleteByFilter() method, which is used to delete one or more records in a table. It accepts the context and a filter as input parameters, and once the execution has been successfully completed, it returns None.

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

Not available

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

Not available

IdentifiableSqlServerPersistence

This persistence component stores data in SQL Server databases and implements several CRUD operations over data items with unique ids. It has several methods for CRUD operations, which are described in the following sections.

Pre-requisites

In order to use this component, we need to import it first. We use the following command to do this.

import { IdentifiableSqlServerPersistence } from 'pip-services4-sqlserver-node';

Not available

from pip_services4_sqlserver.persistence import IdentifiableSqlServerPersistence

Not available

Component implementation

Once the component has been imported, we can create a persistence component by creating a child class of IdentifiableSqlServerPersistence.

export class MySqlServerPersistence
    extends IdentifiableSqlServerPersistence<MyData, string> {
    public constructor() {
        super('mydata2');
    }

    protected defineSchema(): void {
        this.clearSchema();
        this.ensureSchema('CREATE TABLE [' + this._tableName + '] ([id] VARCHAR(32) PRIMARY KEY, [key] VARCHAR(50), [content] VARCHAR(MAX))');
        this.ensureIndex(this._tableName + '_key', { key: 1 }, { unique: true });
    }

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

    public getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any): Promise<DataPage<MyData>> {
        return super.getPageByFilter(ctx, filter, paging, null, null);
    }

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

Not available

class MySqlServerPersistence(IdentifiableSqlServerPersistence):
    def __init__(self):
        super(MySqlServerPersistence, self).__init__('mydata2')

    def _define_schema(self):
        self._clear_schema()
        self._ensure_schema(
            'CREATE TABLE [' + self._table_name + \
            '] ([id] VARCHAR(32) PRIMARY KEY, [key] VARCHAR(50), [content] VARCHAR(MAX))')
        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(context, 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)

Not available

After defining our component, we create an instance of it. Then, we configure this instance according to our database details with the configure() method.

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

Not available

from pip_services4_components.config import ConfigParams

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

Not available

Connection

Now that we have our component ready for use, we can connect it to our database by using the open() method.

await persistence.open(null);

Not available

persistence.open(None)

Not available

CRUD operations

This component presents several methods that can be used to perform CRUD operations. The main ones are explained in the following sections.

Create

To insert a new record into a table, we use the create() method. It accepts the context parameter and a data object as inputs and returns a SqlServerPersistence object containing the inserted record. The following example explains how to use it.

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

Not available

result = persistence.create(None, data1)

Not available

Where

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

Not available

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

Not available

Retrieve

This class contains several methods to retrieve records from a database. They are:

getOneById()

This method retrieves a record according to a given id. It accepts the context and the record’s id as input parameters and returns a SqlServerPersistence object with the retrieved record. The following example explains its usage.

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

Not available

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

Not available

Where

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

Not available

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

Not available

getListByIds()

This method retrieves a set of records according to a set of given ids. It takes the context and a list with the ids of the records to be retrieved as input parameters. It returns a list of SqlServerPersistence objects, each containing a retrieved record. The example below explains how to use it.

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

Not available

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

Not available

Where

for (let item of result) {
    console.log(`${item.id} ${item.key} ${item.content}`);
}

// Prints
// 1 new key 1 content 1
// 3 key 3 content 3
// 2 key 2 content 2

Not available

for item in result:
    print(item.id, item.key, item.content)

# Prints
# 1 new key 1 content 1
# 3 key 3 content 3
# 2 key 2 content 2

Not available

Update

This class has two methods that can be used to update records. These methods are:

update()

This method updates a complete record. It takes the context and a data object as input parameters. It returns a SqlServerPersistence object with the updated record. The following example illustrates its usage.

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

Not available

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

Not available

Where

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

Not available

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

Not available

updatePartially()

This method updates one or more given fields in a record. It accepts the context, the record’s id, and a dictionary containing the fields to be updated as input parameters. It returns a SqlServerPersistence object containing the updated record. The following example explains how to use it.

let result = await persistence.updatePartially(null, "1", AnyValueMap.fromTuples("key", "new key 1.1"));

Not available

result = persistence.update_partially(None, '1', AnyValueMap.from_tuples("key", "new key 1.1"))

Not available

Where

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

Not available

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

Not available

Delete

This class contains two methods that can be used to delete records. These are:

deleteById()

This method deletes a record according to a given id. It accepts the context and the id of the record to be deleted as input parameters, and returns a SqlServerPersistence object with the deleted record. The following example shows how to use it.

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

Not available

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

Not available

Where

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

Not available

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

Not available

deleteByIds()

This method deletes a set of records from a table according to a given list of ids. It accepts the context and a list containing the ids of the records to be deleted as input parameters. Once it has executed the query, it returns None.

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

Not available

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

Not available

IdentifiableJsonSqlServerPersistence

This component is similar to the previous one, but considers identifiable JSON objects. It stores data in SQL Server databases and implements several CRUD operations over data items with unique ids.

Pre-requisites

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

import { IdentifiableJsonSqlServerPersistence } from 'pip-services4-sqlserver-node';

Not available

from pip_services4_sqlserver.persistence import IdentifiableJsonSqlServerPersistence

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 MySqlServerPersistence
    extends IdentifiableJsonSqlServerPersistence<MyData, string> {
    public constructor() {
        super('mydata_json');
    }

    protected defineSchema(): void {
        this.clearSchema();
        this.ensureTable();
        this.ensureSchema("ALTER TABLE [" + this._tableName + "] ADD [data_key] AS JSON_VALUE([data],'$.key')")
        this.ensureIndex(this._tableName + '_key', { data_key: 1 }, { unique: true });
    }

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

    public getPageByFilter(ctx: Context, filter: any, paging: PagingParams, sort: any): Promise<DataPage<MyData>> {
        return super.getPageByFilter(ctx, filter, paging, null, null);
    }

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

Not available

class MySqlServerPersistence(IdentifiableJsonSqlServerPersistence):
    def __init__(self):
        super(MySqlServerPersistence, 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(context, 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)

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

CRUD operations

This class inherits most of its methods from the IdentifiableSqlServerPersistece 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 3'");

Not available

result = persistence.get_one_random(None, "[key]='key 3'")

Not available

we can obtain the record values as

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

Not available

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

Not available

Wrapping up

In this tutorial, we have seen how to create and use three different components for SQL Server persistence. The first was SqlServerPersistence, which is a basic persistence component that is used as a parent to the other two components. The second was IdentifiableSqlPersistence, which is a persistence component for objects that can be uniquely identifiable via an id field. The last one was IdentifiableJsonSqlServerPersistence, which is similar to the previous one but considers identifiable JSON objects. For each of them, we saw how to perform CRUD operations using the methods available in each class.