- Notifications
You must be signed in to change notification settings - Fork60
eggjs/mysql
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
MySQL plugin for Egg.js
npm i @eggjs/mysql
MySQL Plugin foregg@4, support egg application access to MySQL database.
If you're using
egg@3, please useegg-mysql@5instead.
This plugin based on@eggjs/rds, if you want to know specific usage, you should refer to the document of@eggjs/rds.
Change${app_root}/config/plugin.ts to enable MySQL plugin:
exportdefault{mysql:{enable:true,package:'@eggjs/mysql',},};
Configure database information in${app_root}/config/config.default.ts:
exportdefault{mysql:{// database configurationclient:{// hosthost:'mysql.com',// portport:'3306',// usernameuser:'test_user',// passwordpassword:'test_password',// databasedatabase:'test',},// load into app, default is `true`app:true,// load into agent, default is `false`agent:false,},};
Usage:
awaitapp.mysql.query(sql,values);// you can access to simple database instance by using app.mysql.
exportdefault{mysql:{clients:{// clientId, access the client instance by app.mysql.get('clientId')db1:{// hosthost:'mysql.com',// portport:'3306',// usernameuser:'test_user',// passwordpassword:'test_password',// databasedatabase:'test',},// ...},// default configuration for all databasesdefault:{},// load into app, default is openapp:true,// load into agent, default is closeagent:false,},};
Usage:
constclient1=app.mysqls.getSingletonInstance('db1');awaitclient1.query(sql,values);constclient2=app.mysqls.getSingletonInstance('db2');awaitclient2.query(sql,values);
// insertconstresult=awaitapp.mysql.insert('posts',{title:'Hello World'});constinsertSuccess=result.affectedRows===1;
// getconstpost=awaitapp.mysql.get('posts',{id:12});// queryconstresults=awaitapp.mysql.select('posts',{where:{status:'draft'},orders:[['created_at','desc'],['id','desc'],],limit:10,offset:0,});
// update by primary key ID, and refreshconstrow={id:123,name:'fengmk2',otherField:'other field value',modifiedAt:app.mysql.literals.now,// `now()` on db server};constresult=awaitapp.mysql.update('posts',row);constupdateSuccess=result.affectedRows===1;
constresult=awaitapp.mysql.delete('table-name',{name:'fengmk2',});
- adventage:
beginTransaction,commitorrollbackcan be completely under control by developer - disadventage: more handwritten code, Forgot catching error or cleanup will lead to serious bug.
constconn=awaitapp.mysql.beginTransaction();try{awaitconn.insert(table,row1);awaitconn.update(table,row2);awaitconn.commit();}catch(err){// error, rollbackawaitconn.rollback();// rollback call won't throw errthrowerr;}
- API：
async 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
constresult=awaitapp.mysql.beginTransactionScope(asyncconn=>{// don't commit or rollback by yourselfawaitconn.insert(table,row1);awaitconn.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
constresults=awaitapp.mysql.query('update posts set hits = (hits + ?) where id = ?',[1,postId]);
If you want to call literals or functions in mysql , you can useLiteral.
- NOW(): The database system time, you can obtain by
app.mysql.literals.now.
awaitapp.mysql.insert(table,{create_time:app.mysql.literals.now,});// INSERT INTO `$table`(`create_time`) VALUES(NOW())
The following demo showed how to callCONCAT(s1, ...sn) function in mysql to do string splicing.
constLiteral=app.mysql.literals.Literal;constfirst='James';constlast='Bond';awaitapp.mysql.insert(table,{id:123,fullname:newLiteral(`CONCAT("${first}", "${last}"`),});// INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))
Run docker compose to start test mysql service
docker compose -f docker-compose.yml up -d# if you run the first time, should wait for ~20s to let mysql service init startedRun the unit tests
npmtestStop test mysql service
docker compose -f docker-compose.yml down
Please open an issuehere.
Made withcontributors-img.
About
MySQL plugin for egg
Topics
Resources
License
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Uh oh!
There was an error while loading.Please reload this page.
Contributors13
Uh oh!
There was an error while loading.Please reload this page.