npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

ali-rds-x

v3.4.1

Published

Aliyun RDS client

Downloads

10

Readme

ali-rds-x

NPM version build status Test coverage David deps npm download

Aliyun RDS client. Sub module of ali-sdk.

clone from https://github.com/SimbaZZX/ali-rds

增加like模糊查询等,用于npm发布

RDS Usage

RDS, Relational Database Service. Equal to well know Amazon RDS. Support MySQL, SQL Server and PostgreSQL.

MySQL Usage

Create RDS instance

const rds = require("ali-rds");

const db = rds({
  host: "your-rds-address.mysql.rds.aliyuncs.com",
  port: 3306,
  user: "your-username",
  password: "your-password",
  database: "your-database-name",

  // optional params
  // The charset for the connection.
  // This is called "collation" in the SQL-level of MySQL (like utf8_general_ci).
  // If a SQL-level charset is specified (like utf8mb4)
  // then the default collation for that charset is used. (Default: 'UTF8_GENERAL_CI')
  // charset: 'utf8_general_ci',
  //
  // The maximum number of connections to create at once. (Default: 10)
  // connectionLimit: 10,
  //
  // The maximum number of connection requests the pool will queue
  // before returning an error from getConnection.
  // If set to 0, there is no limit to the number of queued connection requests. (Default: 0)
  // queueLimit: 0,
});

Insert

  • Insert one row
let row = {
  name: 'fengmk2',
  otherField: 'other field value',
  createdAt: db.literals.now, // `now()` on db server
  // ...
};
let result = yield db.insert('table-name', row);
console.log(result);
{ fieldCount: 0,
  affectedRows: 1,
  insertId: 3710,
  serverStatus: 2,
  warningCount: 2,
  message: '',
  protocol41: true,
  changedRows: 0 }
  • Insert multi rows

Will execute under a transaction and auto commit.

let rows = [
  {
    name: 'fengmk1',
    otherField: 'other field value',
    createdAt: db.literals.now, // `now()` on db server
    // ...
  },
  {
    name: 'fengmk2',
    otherField: 'other field value',
    createdAt: db.literals.now, // `now()` on db server
    // ...
  },
  // ...
];

let results = yield db.insert('table-name', rows);
console.log(result);
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 3840,
  serverStatus: 2,
  warningCount: 2,
  message: '&Records: 2  Duplicates: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0 }

Update

  • Update a row with primary key: id
let row = {
  id: 123,
  name: 'fengmk2',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
};
let result = yield db.update('table-name', row);
console.log(result);
{ fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1 }
  • Update a row with options.where and options.columns
let row = {
  name: 'fengmk2',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
};
let result = yield db.update('table-name', row, {
  where: { name: row.name },
  columns: [ 'otherField', 'modifiedAt' ]
});
console.log(result);
{ fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1 }

Update multiple rows

  • Update multiple rows with primary key: id
let options = [{
  id: 123,
  name: 'fengmk2',
  email: '[email protected]',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
}, {
   id: 124,
  name: 'fengmk2_2',
  email: 'm@fengmk2_2.com',
  otherField: 'other field value 2',
  modifiedAt: db.literals.now, // `now()` on db server
}]
let result = yield db.updateRows('table-name', options);
console.log(result);
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 2  Changed: 2  Warnings: 0',
  protocol41: true,
  changedRows: 2 }
  • Update multiple rows with row and where properties
let options = [{
  row: {
    email: '[email protected]',
    otherField: 'other field value',
    modifiedAt: db.literals.now, // `now()` on db server
  },
  where: {
    id: 123,
    name: 'fengmk2',
  }
}, {
  row: {
    email: 'm@fengmk2_2.com',
    otherField: 'other field value2',
    modifiedAt: db.literals.now, // `now()` on db server
  },
  where: {
    id: 124,
    name: 'fengmk2_2',
  }
}]
let result = yield db.updateRows('table-name', options);
console.log(result);
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 2  Changed: 2  Warnings: 0',
  protocol41: true,
  changedRows: 2 }

Get

  • Get a row
let row = yield db.get('table-name', { name: 'fengmk2' });

=> SELECT * FROM `table-name` WHERE `name` = 'fengmk2'

Select

  • Select all rows
let rows = yield db.select('table-name');

=> SELECT * FROM `table-name`
  • Select rows with condition
let rows = yield db.select('table-name', {
  where: {
    type: 'javascript'
  },
  columns: ['author', 'title'],
  orders: [['id', 'desc']]
});

=> SELECT `author`, `title` FROM `table-name`
 WHERE `type` = 'javascript' ORDER BY `id` DESC
  • 复杂查询

    如果你看到这里说明你和我遇到了一样的问题,egg/mysql 缺少一些实用方法,这里做了一些扩展,可能在你的使用过程中,还会存在 bug,请参考着继续完善吧。 单层CUSTOM自定义查询语句

        let rows = yield db.select('table-name', {
            where: {
                sender: { op: 'like', value: `%111%` },
                receiver: { op: 'like', value: `%222%` },
                cn_orderno_list: { op: 'like', value: `%333%` },
                OR: {
                    sender11: { op: 'like', value: `%111%` },
                    receiver22: { op: 'like', value: `%222%` },
                },
                a: 1,
                b: [11, 22],
                AND: {
                    orderno: null,
                    id: 1,
                    role: ['admin', 'guest'],
                    OR: {
                        realname: { op: 'like', value: 'simba' },
                        age: { op: '>', value: '20' },
                        nickname: { op: 'like', value: 'ace' }
                    },
                    createtime: [
                        { op: '>', value: '2020-10-13' },
                        { op: '<', value: '2020-11-22' },
                    ]
                },
            },
            columns: ['author', 'title'],
            orders: [['id', 'desc']]
        });

=> SELECT *  FROM `t_order`
    WHERE `sender` like '%111%'
    AND `receiver` like '%222%'
    AND `cn_orderno_list` like '%333%'
    AND (`sender11` like '%111%' OR `receiver22` like '%222%')
    AND `a` = 1
    AND `b` IN (11, 22)
    AND (`orderno` IS NULL AND `id` = 1
          AND `role` IN ('admin', 'guest')
          AND (`realname` like 'simba' OR `age` > '20' OR `nickname` like 'ace')
          AND (`createtime` > '2020-10-13' AND `createtime` < '2020-11-22')
        )

Delete

  • Delete with condition
let result = yield db.delete('table-name', {
  name: 'fengmk2'
});

=> DELETE FROM `table-name` WHERE `name` = 'fengmk2'

Count

  • Get count from a table with condition
let count = yield db.count('table-name', {
  type: 'javascript'
});

=> SELECT COUNT(*) AS count FROM `table-name` WHERE `type` = 'javascript';

Transactions

beginTransaction, commit or rollback

let tran = yield db.beginTransaction();

try {
  yield tran.insert(table, row1);
  yield tran.update(table, row2);
  yield tran.commit();
} catch (err) {
  // error, rollback
  yield tran.rollback(); // rollback call won't throw err
  throw err;
}

Transaction with scope

API: *beginTransactionScope(scope)

All query run in scope will under a same transaction. We will auto commit or rollback for you.

var result = yield db.beginTransactionScope(function* (conn) {
  // don't commit or rollback by yourself
  yield conn.insert(table, row1);
  yield conn.update(table, row2);
  return { success: true };
});
// if error throw on scope, will auto rollback

Transaction on koa

API: *beginTransactionScope(scope, ctx)

Use koa's context to make sure only one active transaction on one ctx.

function* foo(ctx, data1) {
  return yield db.beginTransactionScope(function* (conn) {
    yield conn.insert(table1, data1);
    return { success: true };
  }, ctx);
}

function* bar(ctx, data2) {
  return yield db.beginTransactionScope(function* (conn) {
    // execute foo with the same transaction scope
    yield foo(ctx, { foo: "bar" });
    yield conn.insert(table2, data2);
    return { success: true };
  }, ctx);
}

Raw Queries

  • Query without arguments
let rows = yield db.query('SELECT * FROM your_table LIMIT 100');
console.log(rows);
  • Query with array arguments
let rows = yield db.query('SELECT * FROM your_table WHERE id=?', [ 123 ]);
console.log(rows);
  • Query with object arguments
let rows = yield db.query('SELECT * FROM your_table WHERE id=:id', { id: 123 });
console.log(rows);

SQL Server Usage

TBD


APIs

  • * Meaning this function is yieldable.

IO queries

  • *query(sql[, values)
  • *queryOne(sql[, values)
  • *select(table, options)
  • *get(table, where, options)
  • *insert(table, row[s], options)
  • *update(table, row, options)
  • *updateRows(table, options)
  • *delete(table, where)
  • *count(table, where)

Transactions

  • *beginTransaction()
  • *beginTransactionScope(scope)

Utils

  • escape(value, stringifyObjects, timeZone)
  • escapeId(value, forbidQualified)
  • format(sql, values, stringifyObjects, timeZone)

Literals

yield db.insert('user', {
  name: 'fengmk2',
  createdAt: db.literals.now,
});

=>

INSERT INTO `user` SET `name` = 'fengmk2', `createdAt` = now()

Custom Literal

let session = new db.literals.Literal("session()");

TODO

  • [x] MySQL
    • [x] Pool
    • [ ] Cluster
  • [ ] SQL Server
  • [ ] PostgreSQL

License

MIT