eggjs / Egg Mysql
Licence: mit
MySQL plugin for egg
Stars: ✭ 276
Programming Languages
javascript
184084 projects - #8 most used programming language
Labels
Projects that are alternatives of or similar to Egg Mysql
egg-parameters
Merge all parameters (ctx.params, ctx.request.query, ctx.request.body) into ctx.params like Rails application.
Stars: ✭ 24 (-91.3%)
Mutual labels: egg, egg-plugin
egg-elasticsearch
elasticsearch client for egg.js
Stars: ✭ 22 (-92.03%)
Mutual labels: egg, egg-plugin
egg-view-vue-ssr
Egg Vue Server Side Render (SSR) Plugin
Stars: ✭ 90 (-67.39%)
Mutual labels: egg, egg-plugin
egg-vue-webpack-dev
基于egg + vue2 + webpack2 的前后端集成开发编译构建插件
Stars: ✭ 29 (-89.49%)
Mutual labels: egg, egg-plugin
egg-y-validator
☯️ Egg Magic Validator (Egg 魔法验证工具)
Stars: ✭ 30 (-89.13%)
Mutual labels: egg, egg-plugin
egg-aop
AOP plugin for eggjs, add DI, AOP support.
Stars: ✭ 44 (-84.06%)
Mutual labels: egg, egg-plugin
egg-mysql
Aliyun rds client(support mysql portocal) for egg framework
Install
$ npm i egg-mysql --save
MySQL Plugin for egg, support egg application access to MySQL database.
This plugin based on ali-rds, if you want to know specific usage, you should refer to the document of ali-rds.
Configuration
Change ${app_root}/config/plugin.js
to enable MySQL plugin:
exports.mysql = {
enable: true,
package: 'egg-mysql',
};
Configure database information in ${app_root}/config/config.default.js
:
Simple database instance
exports.mysql = {
// database configuration
client: {
// host
host: 'mysql.com',
// port
port: '3306',
// username
user: 'test_user',
// password
password: 'test_password',
// database
database: 'test',
},
// load into app, default is open
app: true,
// load into agent, default is close
agent: false,
};
Usage:
app.mysql.query(sql, values); // you can access to simple database instance by using app.mysql.
Multiple database instance
exports.mysql = {
clients: {
// clientId, access the client instance by app.mysql.get('clientId')
db1: {
// host
host: 'mysql.com',
// port
port: '3306',
// username
user: 'test_user',
// password
password: 'test_password',
// database
database: 'test',
},
// ...
},
// default configuration for all databases
default: {
},
// load into app, default is open
app: true,
// load into agent, default is close
agent: false,
};
Usage:
const client1 = app.mysql.get('db1');
client1.query(sql, values);
const client2 = app.mysql.get('db2');
client2.query(sql, values);
CRUD user guide
Create
// insert
const result = yield app.mysql.insert('posts', { title: 'Hello World' });
const insertSuccess = result.affectedRows === 1;
Read
// get
const post = yield app.mysql.get('posts', { id: 12 });
// query
const results = yield app.mysql.select('posts',{
where: { status: 'draft' },
orders: [['created_at','desc'], ['id','desc']],
limit: 10,
offset: 0
});
Update
// update by primary key ID, and refresh
const row = {
id: 123,
name: 'fengmk2',
otherField: 'other field value',
modifiedAt: app.mysql.literals.now, // `now()` on db server
};
const result = yield app.mysql.update('posts', row);
const updateSuccess = result.affectedRows === 1;
Delete
const result = yield app.mysql.delete('table-name', {
name: 'fengmk2'
});
Transaction
Manual control
- adventage:
beginTransaction
,commit
orrollback
can be completely under control by developer - disadventage: more handwritten code, Forgot catching error or cleanup will lead to serious bug.
const conn = yield app.mysql.beginTransaction();
try {
yield conn.insert(table, row1);
yield conn.update(table, row2);
yield conn.commit();
} catch (err) {
// error, rollback
yield conn.rollback(); // rollback call won't throw err
throw err;
}
Automatic control: Transaction with scope
- API:
*beginTransactionScope(scope, ctx)
-
scope
: A generatorFunction which will execute all sqls of this transaction. -
ctx
: The context object of current request, it will ensures that even in the case of a nested transaction, there is only one active transaction in a request at the same time.
-
- adventage: easy to use, as if there is no transaction in your code.
- disadvantage: all transation will be successful or failed, cannot control precisely
const result = yield app.mysql.beginTransactionScope(function* (conn) {
// don't commit or rollback by yourself
yield conn.insert(table, row1);
yield conn.update(table, row2);
return { success: true };
}, ctx); // ctx is the context of current request, access by `this.ctx`.
// if error throw on scope, will auto rollback
Advance
Custom SQL splicing
const results = yield app.mysql.query('update posts set hits = (hits + ?) where id = ?', [1, postId]);
Literal
If you want to call literals or functions in mysql , you can use Literal
.
Inner Literal
- NOW(): The database system time, you can obtain by
app.mysql.literals.now
.
yield app.mysql.insert(table, {
create_time: app.mysql.literals.now
});
// INSERT INTO `$table`(`create_time`) VALUES(NOW())
Custom literal
The following demo showed how to call CONCAT(s1, ...sn)
funtion in mysql to do string splicing.
const Literal = app.mysql.literals.Literal;
const first = 'James';
const last = 'Bond';
yield app.mysql.insert(table, {
id: 123,
fullname: new Literal(`CONCAT("${first}", "${last}"`),
});
// INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))
Questions & Suggestions
Please open an issue here.
License
Note that the project description data, including the texts, logos, images, and/or trademarks,
for each open source project belongs to its rightful owner.
If you wish to add or remove any projects, please contact us at [email protected].