Database Replacement

To prepare for a production environment, replace the PostgreSQL* database with that of another provider.

To replace the database, update these services:

• MPS
• RPS

What You'll Do¶

This guide focuses on updating the RPS with an MS SQL Server (MSSQL) relational database.

Here are the main tasks:

• Review DB Schema
• Add DB Client Dependency
• Update Configuration
• Implement the Code

Review DB Schema¶

The diagrams below illustrates the database schema and relationships.

RPS¶

MPS¶

Add DB Client¶

Add the database client library you will use to connect to your database. To support MSSQL, this example uses the Microsoft SQL Server client* for Node.js, node-mssql.

To add the database:

Open a Terminal or Command Prompt and navigate to a directory of your choice for development:

npm install node-mssql --save

Update Configuration¶

Update the connection string and a folder name for your db either in your ENV or .rc file.

To modify the configuration:

"db_provider":"mssql", //This will be the name of the folder you create in the next section.
"connection_string":"Server=localhost,1433;Database=database;User Id=username;Password=password;Encrypt=true'",

Implement the Code¶

To support the new database:

1. Create a new folder in ./src/data. The name of the new folder should be the name you supplied for the db_provider property, which is mssql in the example above.

   

2. Create a file called index.ts that implements our IDB interface. Below is an example interface and query method:

   Interface

   export interface IDB {
     ciraConfigs: ICiraConfigTable
     domains: IDomainsTable
     profiles: IProfilesTable
     wirelessProfiles: IWirelessProfilesTable
     profileWirelessConfigs: IProfilesWifiConfigsTable
     query: (text: string, params?: any) => Promise<any>
   }
   

   Query Method

   This query function is responsible for taking in the query parameters and performing the execution.

   async query <T>(text: string, params?: any): Promise<mssql.IResult<T>> {
      let result
      const start = Date.now()
      return await new Promise((resolve, reject) => {
        this.sqlPool.connect(async (err) => {
          if (err) {
            this.log.error(err)
            reject(err)
          }
          result = await this.sqlPool.request().query(text)
          const duration = Date.now() - start
          this.log.verbose(`executed query: ${JSON.stringify({ text, duration, rows: result.recordset.length })}`)
          resolve(result)
          })
      })
   }
   

3. Implement each of the table interfaces. The base interface looks like this:

   export interface ITable<T> {
     getCount: (tenantId?: string) => Promise<number>
     get: (limit: number, offset: number, tenantId?: string) => Promise<T[]>
     getByName: (name: string, tenantId?: string) => Promise<T>
     delete: (name: string, tenantId?: string) => Promise<boolean>
     insert: (item: T) => Promise<T>
     update: (item: T) => Promise<T>
   }
   

   There are interfaces for each table in the ./interfaces/database which adds specific functions on top of the base ITable<> interface.

   Here's an example of the get implementation for Domains:

   /**
    * @description Get all Domains from DB
    * @param {number} top
    * @param {number} skip
    * @returns {AMTDomain[]} returns an array of AMT Domain objects from DB
    */
   async get (top: number = DEFAULT_TOP, skip: number = DEFAULT_SKIP, tenantId: string = ''): Promise<AMTDomain[]> {
      const results = await this.db.query(`
      SELECT name as  profileName, domain_suffix as  domainSuffix, provisioning_cert as  provisioningCert, provisioning_cert_storage_format as  provisioningCertStorageFormat, provisioning_cert_key as  provisioningCertPassword, tenant_id tenantId
      FROM domains 
     ORDER BY name`)
     return result
    }
   

4. Complete all the queries for each table's functions to finish the implementation.