Install

npm i @eggjs/mysql

Configuration

exportdefault{mysql:{enable:true,package:'@eggjs/mysql',},};

Simple database instance

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,},};

awaitapp.mysql.query(sql,values);// you can access to simple database instance by using app.mysql.

Multiple database instance

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,},};

constclient1=app.mysqls.getSingletonInstance('db1');awaitclient1.query(sql,values);constclient2=app.mysqls.getSingletonInstance('db2');awaitclient2.query(sql,values);

CRUD user guide

Create

// insertconstresult=awaitapp.mysql.insert('posts',{title:'Hello World'});constinsertSuccess=result.affectedRows===1;

Read

// 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

// 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;

Delete

constresult=awaitapp.mysql.delete('table-name',{name:'fengmk2',});

Transaction

Manual control

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;}

Automatic control: Transaction with scope

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

Advance

Custom SQL splicing

constresults=awaitapp.mysql.query('update posts set hits = (hits + ?) where id = ?',[1,postId]);

Literal

Inner Literal

awaitapp.mysql.insert(table,{create_time:app.mysql.literals.now,});// INSERT INTO `$table`(`create_time`) VALUES(NOW())

Custom literal

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"))

For the local dev

docker compose -f docker-compose.yml up -d# if you run the first time, should wait for ~20s to let mysql service init started

npmtest

docker compose -f docker-compose.yml down

Questions & Suggestions

License

Contributors