Database

Suitable Enviroment	SaaS
	Private Deployment

Requirements

ONES
v3.6.0+

Overview

Sometimes plugin want to persist their own data. The database ability allows each plugin to have its own independent database and provides methods to access and manipulate it.

note

This ability is compatible with mysql version 5.7.x SQL syntax

Design and usage recommendations

• Do not create tables without primary keys.
• Database names, table names, and column names do not use camel hump form.
• The database name, table name, column name, and index name do not contain special characters such as - * \/# @.
• Do not insert []byte data into character type fields.
• Do not use JSON type fields.
• Do not use NULL values as unique key constraints.
• Replace char with varchar.
• The use of INSERT requires explicit writing of the column to be inserted. For example, insert into values (1, 2); should be written as insert into (col1, col2) values (1, 2).
• Using placeholders ? to insert or update data.

Usage

Use database ability

Step 1: Installation dependency

Enter the /backend directory of the plugin project, and execute the following command for dependent installation:

npm i @ones-op/node-database

Step 2: Write SQL files

To create a database, you need to write the table creation statements of all tables in the database in advance in the SQL file and put them in the workspace directory.

Sample SQL file:

caution

The table names in the statement must be in lowercase letters, underscores _ are allowed, hyphens - are not permitted, and the table names must be enclosed in {{ }}.

CREATE TABLE IF NOT EXISTS `{{email_id_map}}`  (
    `email` varchar(128) CHARACTER SET latin1 NOT NULL COMMENT 'email',
    `id_number` varchar(128) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'Work number',
  PRIMARY KEY (`email`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

Step 3: Database initialization

Call the importSQL method to create a database table. It is recommended to initialize the table in the plugin's Install method.

Example:

backend/src/index.ts

import { importSQL } from '@ones-op/node-database'

// Lifecycle hook installation plugin
export async function Install() {
  // Initialize the database
  return importSQL('plugin.sql')
}

Step 4: Database operation

• select

  The query data uses the select method, which receives the SQL statement and returns the result of the query. If SQL fails in execution, it will throw error.

  And the format of executing SQL supports placeholder filling SQL statements. Placeholder use question marks ? , Fill in values using variable parameters. SQL statements with placeholders are supported in ONES v6.8.0+/v6.1.96+，@ones-op/node-database@0.70.1+.

  Example：

  import { select } from '@ones-op/node-database'
  
  try {
    const result1 = await select('select * from email_id_map limit 10;')
    const result2 = await select('select * from email_id_map where id_number = ?;', '001'))
  } catch (error) {
    Logger.error('ERROR: ', error)
  }

• exec

  The exec method is used to execute the sql statement, which receives the SQL operation type and the statement, and does not return a result. If SQL fails in execution, it will throw error.

  1. Currently, the supported execution operation types are：insert, update, delete, create, alter, drop.
  2. The format of executing SQL supports both raw SQL statements and placeholder filling SQL statements. Placeholder use question marks ? , Fill in values using variable parameters. SQL statements with placeholders are supported in ONES v6.2.19+/v6.1.86+,@ones-op/node-database@0.46.5+.

  Example：

  import { exec } from '@ones-op/node-database'
  
  try {
    await exec(
      'insert',
      `INSERT INTO email_id_map(email,id_number) VALUES ("plugin1@ones.com", "001");`,
    )
    await exec(
      'insert',
      `INSERT INTO email_id_map(email,id_number) VALUES (?,?);`,
      'plugin2@ones.com',
      '002',
    )
    const args = ['plugin3@ones.com', '003']
    await exec('insert', `INSERT INTO email_id_map(email,id_number) VALUES (?,?);`, ...args)
  } catch (error) {
    Logger.error('ERROR: ', error)
  }

• count

  Statistics use the count method, which receives the SQL statement and returns the statistical results. If SQL fails in execution, it will throw error.

  1. Only SQL statements that use the count method to count the number of individual columns are supported.
  2. The format of executing SQL supports placeholder filling SQL statements. Placeholder use question marks ? , Fill in values using variable parameters. SQL statements with placeholders are supported in ONES v6.8.0+/v6.1.96+，@ones-op/node-database@0.70.1+.

  Example：

  import { count } from '@ones-op/node-database'
  
  try {
    const cnt1 = await count('select count(*) from email_id_map;')
    const cnt2 = await count('select count(*) from email_id_map where id_number = ?;', '001'))
  } catch (error) {
    Logger.error('ERROR: ', error)
  }

Local development and configuration

When developing locally, in order to facilitate development and debugging, it is also supported to allow the database operations to act locally. Local debugging does not affect the use of the ability, does not need to modify the code, only needs to modify the configuration of the local database.Modify the configuration in config/local.yaml, and then re-execute the npx op invoke run command to restart the plugin

Example：

  mysql_in_local: true # 'True' to use your local database, 'false' to use the ONES system built-in database
  mysql_user_name: 'root'
  mysql_user_password: 'root123'
  mysql_database_name: 'test'
  mysql_host: '127.0.0.1'
  mysql_port: '3306'

Other

For the definition of specific parameters, please refer to: @ones-op/node-database

FAQ

How does the database migrate when the plugin is upgraded?

When the plugin is upgraded, the upgrade method in the lifecycle method will be called. The team-level plugin is Upgrade, and the organization-level plugin is OrgUpgrade. The database remains unchanged before and after the plugin is upgraded, and migration can be realized in this method.