UOS Func Stateless CRUD MySQL Nodejs SDK

用法

1.安装 UOS Func Stateless CRUD MySQL Nodejs SDK

npm install func-stateless-mysql-sdk-nodejs

2.引用本 SDK 并连接 MySQL Client

context.database = require('func-stateless-mysql-sdk-nodejs').database;
// If you do not specify a databaseId, the sdk will automatically search for and connect to an available database.
// 如果没有指定 databaseId 的话，sdk 会自动获取可用的数据库进行连接。
// Please note: if uos is authorized to store your database password in an encrypted form, this SDK will automatically fill in the password, if not authorized, you will need to specify the database password in your code.
// 注意：如果uos被授权以加密形式存储您的数据库密码，本sdk将自动填入密码，如果未被授权，您需要在代码中指定数据库密码。
// Please note: if there are multiple available mysql databases, you must use the databaseId to specify the desired database you wish to connect to.
// 注意：如果有多个可用的 mysql 数据库的话，请在代码中指定要用于连接的数据库的 databaseId。
try {
  const databaseId = '';
  const databasePassword = '';
  const database = await context.database(databaseId, databasePassword);
  const client = await database.connection();
} catch (error) {
  console.error(error);
}

3.使用 MySQL Client 操作数据库

例：

try {
  const databaseName = 'test';
  await connection.query(`CREATE DATABASE IF NOT EXISTS \`${databaseName}\``);
  console.log(`Database ${databaseName} ensured.`);

  await connection.query(`USE \`${databaseName}\``);
  console.log(`Using database ${databaseName}`);

  const createTableQuery = `
         CREATE TABLE IF NOT EXISTS users (
           id INT AUTO_INCREMENT PRIMARY KEY,
           name VARCHAR(255) NOT NULL,
           email VARCHAR(255) NOT NULL UNIQUE
         )
       `;
  await connection.query(createTableQuery);
  console.log('table created successfully');

  const insertQuery = `INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')`;
  await connection.query(insertQuery);
  console.log('rows inserted successfully');

  const [rows] = await connection.query('SELECT * FROM users');
  console.log('selected rows:', rows);

  const deleteTableQuery = `
         DROP TABLE IF EXISTS users
       `;
  await connection.query(deleteTableQuery);
  console.log('table deleted successfully');
} catch (error) {
  console.error('error while setting up the database:', error);
} finally {
  // Please note: mysql connection should be released manually if you successfully check it out, regardless of whether there was an error with the queries you ran on the client.
  // 注意：mysql 客户端需要被手动释放，无论查询过程是否发生错误
  await connection.release();

  //test end pool
  await database.endConnection();
  console.log('closed the connection pool');
}

4.完整操作示例

/*
环境变量(自动生成)：
process.env['UOS_APP_ID'] = 8972b2ba-ffb4-4211-bbef-1274083a2c09
process.env['UOS_APP_SECRET'] = 9e131ad6-e73e-4e47-bc9d-936df2b1e70a
process.env['UOS_APP_SERVICE_SECRET'] = f4f57f44-273c-4005-ab70-d469d7b45053
*/

exports.main = async (event, context) => {
  context.database = require('func-stateless-mysql-sdk-nodejs').database;
  // If you do not specify a databaseId, the system will automatically search for and connect to an available database.
  // 如果没有指定 databaseId 的话，sdk 会自动获取可用的数据库进行连接。
  // Please note: if uos is authorized to store your database password in an encrypted form, this SDK will automatically fill in the password, if not authorized, you will need to specify the database password in your code.
  // 注意：如果uos被授权以加密形式存储您的数据库密码，本sdk将自动填入密码，如果未被授权，您需要在代码中指定数据库密码。
  // Please note: if there are multiple available mysql databases, you must use the databaseId to specify the desired database you wish to connect to.
  // 注意：如果有多个可用的 mysql 数据库的话，请在代码中指定要用于连接的数据库的 databaseId
  const databaseId = '';
  const databasePassword = '';
  try {
    const database = await context.database(databaseId, databasePassword);
    const connection = await database.connection();
    try {
      // 如果在创建数据库实例时已经指定DATABASE则无需使用CREATE DATABASE命令，并且此时不推荐使用USE命令切换SCHEMA
      const databaseName = 'test';
      await connection.query(
        `CREATE DATABASE IF NOT EXISTS \`${databaseName}\``
      );
      console.log(`Database ${databaseName} ensured.`);

      await connection.query(`USE \`${databaseName}\``);
      console.log(`Using database ${databaseName}`);

      const createTableQuery = `
         CREATE TABLE IF NOT EXISTS users (
           id INT AUTO_INCREMENT PRIMARY KEY,
           name VARCHAR(255) NOT NULL,
           email VARCHAR(255) NOT NULL UNIQUE
         )
       `;
      await connection.query(createTableQuery);
      console.log('table created successfully');

      const insertQuery = `INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')`;
      await connection.query(insertQuery);
      console.log('rows inserted successfully');

      const [rows] = await connection.query('SELECT * FROM users');
      console.log('selected rows:', rows);

      const deleteTableQuery = `
         DROP TABLE IF EXISTS users
       `;
      await connection.query(deleteTableQuery);
      console.log('table deleted successfully');
    } catch (error) {
      console.error('error while setting up the database:', error);
    } finally {
      // Please note: mysql connection should be released manually if you successfully check it out, regardless of whether there was an error with the queries you ran on the client.
      // 注意：mysql 客户端需要被手动释放，无论查询过程是否发生错误
      await connection.release();
    }

    //test end pool
    await database.endConnection();
    console.log('closed the connection pool');
  } catch (error) {
    console.error(error);
  }
};