simple-dao v1.0.19

Simple-dao is a simple wrapper for handling MySQL database

1. Install

$ npm install --save simple-dao

2. Query data

Code in promise style:

const dao = require("simple-dao")({
    "database": "your-database",
    "user": "root",
    "password": "your-password",
    "host": "127.0.0.1",
    "port": "3306"
});

dao.query("SELECT * FROM user LIMIT 10").then((data) => {
    console.log("data of table user: ", data);
}).catch((err) => {
    console.error(err);
});

Or Code in callback style:

const dao = require("simple-dao")({
    "database": "your-database",
    "user": "root",
    "password": "your-password",
    "host": "127.0.0.1",
    "port": "3306"
});

dao.query("SELECT * FROM user LIMIT 10", (data) => {
    console.log("data of table user: ", data);
}, (err) => {
    console.error(err);
});

3. Query One line data

Code in promise style:

dao.queryOne("SELECT COUNT(*) c FROM user").then((data) => {
    console.log("data count of table user: ", data["c"]);
}).catch((err) => {
    console.error(err);
});

Or Code in callback style:

dao.queryOne("SELECT COUNT(*) c FROM user", (data) => {
    console.log("data count of table user: ", data["c"]);
}, (err) => {
    console.error(err);
});

4. Update data

Code in promise style:

const tableName = "user";
// dao.now() Can get current time, whose format looks like '2017-09-09 12:23:56'
const updateFields = {username: "newUsername", nickname: "newNickname", update_time: dao.now()};
const whereFields = {userId: 12345};
dao.update(tableName, updateFields, whereFields).then(() => {
    console.log("update user whose userId is 12345 successfully";
}).catch((err) => {
    console.error(err);
});

Or Code in callback style:

const tableName = "user";
// dao.now() Can get current time, whose format looks like '2017-09-09 12:23:56'
const updateFields = {username: "newUsername", nickname: "newNickname", update_time: dao.now()};
const whereFields = {userId: 12345};
dao.update(tableName, updateFields, whereFields, () => {
    console.log("update user whose userId is 12345 successfully";
}, (err) => {
    console.error(err);
});

5. Insert data

Code in promise style:

const tableName = "user";
// dao.now() Can get current time, whose format looks like '2017-09-09 12:23:56'
const data = {username: "hello", nickname: "world", create_time: dao.now()};
dao.insert(tableName, data).then((insertId) => {
    console.log("Auto generate insertId: ", insertId);
}).catch((err) => {
    console.error(err);
});

Or Code in callback style:

const tableName = "user";
// dao.now() Can get current time, whose format looks like '2017-09-09 12:23:56'
const data = {username: "hello", nickname: "world", create_time: dao.now()};
dao.insert(tableName, data, (insertId) => {
    console.log("Auto generate insertId: ", insertId);
}, (err) => {
    console.error(err);
});

6. Insert data in batch

Code in promise style:

const tableName = "user";
// dao.now() Can get current time, whose format looks like '2017-09-09 12:23:56'
const data = [
    {username: "username1", nickname: "nickname1", create_time: dao.now()},
    {username: "username2", nickname: "nickname2", create_time: dao.now()}
];
dao.batchInsert(tableName, data).then((insertIds) => {
    console.log("Auto generate insertIds: ", insertIds);
}).catch((err) => {
    console.error(err);
});

Or Code in callback style:

const tableName = "user";
// dao.now() Can get current time, whose format looks like '2017-09-09 12:23:56'
const data = [
    {username: "username1", nickname: "nickname1", create_time: dao.now()},
    {username: "username2", nickname: "nickname2", create_time: dao.now()}
];
dao.batchInsert(tableName, data, (insertIds) => {
    console.log("Auto generate insertIds: ", insertIds);
}, (err) => {
    console.error(err);
});

7. Insert or update data

Code in promise style:

/*
 * If there is user whose userId is 1234, update its username and nickname. If not, insert a user with userId 1234.
 */
const tableName = "user";
const data = [{userId: 1234, username: "username1", nickname: "nickname1"}];
dao.insertOnDuplicateKeyUpdate(tableName, data).then(() => {
    console.log("save user data successfully");
}).catch((err) => {
    console.error(err);
});

Or Code in callback style:

/*
 * If there is user whose userId is 1234, update its username and nickname. If not, insert a user with userId 1234.
 */
const tableName = "user";
const data = [{userId: 1234, username: "username1", nickname: "nickname1"}];
dao.insertOnDuplicateKeyUpdate(tableName, data, () => {
    console.log("save user data successfully");
}, (err) => {
    console.error(err);
});

8. Remove data

Code in promise style:

const whereFields = {"userId": 12345};
dao.remove(tableName, whereFields).then(() => {
    console.log("delete user whose id is 12345 successfully");
}).catch((err) => {
    console.error(err);
});

Or Code in callback style:

const whereFields = {"userId": 12345};
dao.remove(tableName, whereFields, () => {
    console.log("delete user whose id is 12345 successfully");
}, (err) => {
    console.error(err);
});

9. Add event listener

const dao = require("simple-dao")({
    "database": "your-database",
    "user": "root",
    "password": "your-password",
    "host": "127.0.0.1",
    "port": "3306"
}).on("afterQuery", (err, data) => {
    console.log("execute query:", data.sql);
});

dao.query("SELECT * FROM user LIMIT 10").then((data) => {
    // do something
}).catch((err) => {
    console.error(err);
});
/*
 * The code above will print:
 * execute query: SELECT * FROM user LIMIT 10
 */