Sql增刪改查

本節(jié)使用knex作為sql框架，以sqlite數(shù)據(jù)庫(kù)為例

準(zhǔn)備工作

knex是一個(gè)運(yùn)行在各自數(shù)據(jù)庫(kù)Driver上的框架，因此需要安裝相應(yīng)的js版數(shù)據(jù)庫(kù)Driver，如: PostgreSQL -> pg, mysql/mariadb -> mysql, sqlite -> sqlite3…

• 安裝sqlite3依賴(lài) npm install sqlite3
• 安裝knex依賴(lài) npm install knex
• 引入依賴(lài)

const app = express();
const knex = require('knex');

• 建議安裝一款合適的數(shù)據(jù)庫(kù)界面工具，筆者使用的是Beekeeper Studio.

創(chuàng)建項(xiàng)目

拷貝第一節(jié)HelloWorld的項(xiàng)目

創(chuàng)建sqlite連接

指明client為sqlite3（剛剛安裝的sqlite3依賴(lài)），并指明要操作的sqlite數(shù)據(jù)庫(kù)路徑

const sqlite = knex({client: 'sqlite3',connection: {filename: './data.db',},
});

創(chuàng)建了一個(gè)連接實(shí)例后，會(huì)自動(dòng)創(chuàng)建一個(gè)連接池，因此初始化數(shù)據(jù)庫(kù)只會(huì)發(fā)生一次

連接配置

sqlite3默認(rèn)的是單連接，如果你希望連接池有更多的連接，創(chuàng)建時(shí)帶上pool:

const sqlite = knex({client: 'sqlite3',connection: {filename: './data.db',},pool: { min: 0, max: 7 }
});

創(chuàng)建連接池的回調(diào)

用于檢查連接池是否正常，通常不需要這步

pool: {afterCreate: function (conn, done) {//...}
}

acquireConnectionTimeout

連接超時(shí)時(shí)間

日志

knex內(nèi)置了打印警告、錯(cuò)誤、棄用和調(diào)試信息的日志函數(shù)，如果你希望自定義日志操作，可以在log項(xiàng)里重寫(xiě)它們

log: {warn(message) {},error(message) {},deprecate(message) {},debug(message) {}
}

數(shù)據(jù)表

建表

語(yǔ)法: sqlite.schema.createTable(表名, table=>{表結(jié)構(gòu)})

添加一個(gè)PUT接口，監(jiān)聽(tīng) 127.0.0.1:8080/db/:tbname
根據(jù)我們想創(chuàng)建的表名嘗試創(chuàng)建一個(gè)表，注意: sql執(zhí)行是異步的，為了得到結(jié)果，建議使用 async/await 語(yǔ)法糖（當(dāng)然你就是喜歡地獄回調(diào)也不是不行）

app.put('/db/:tbname', async function (req, res) {let resultSet = null;try {// Create a tableresultSet = await sqlite.schema.createTable(req.params.tbname, table => {table.increments('id');table.string('uname');table.string('passwd');})// Finally, add a catch statement} catch(e) {console.error(e);resultSet = e;};res.json(resultSet);
});

瞅瞅控制臺(tái):

sqlite does not support inserting default values. Set the `useNullAsDefault` flag to hide this warning. (see docs https://knexjs.org/guide/query-builder.html#insert).

嗯？sqlite不支持default？不用管他，去看數(shù)據(jù)庫(kù)，反正成功創(chuàng)建了user表，你要是加了useNullAsDefault這個(gè)flag，反而會(huì)告訴你 not supported by node-sqlite3

const sqlite = knex({client: 'sqlite3',connection: {filename: './data.db',},
});

刪表

語(yǔ)法: sqlite.schema.deleteTable(表名)

app.delete('/db/:tbname', async function (req, res) {try {// Delete a tableawait sqlite.schema.dropTable(req.params.tbname);// Finally, add a catch statement} catch(e) {console.error(e);};res.json(null);
});

表記錄crud

增

往user表里面插入一條新的記錄

app.use(express.json({type: 'application/json'}));
app.put('/db/:tbname/record', async function (req, res) {/*前端請(qǐng)求體格式:{"uname": "evanp","passwd": "iloveu"}*/let resultSet = null;try {// Insert a recordresultSet = await sqlite(req.params.tbname).insert(req.body);// Finally, add a catch statement} catch(e) {console.error(e);resultSet = e;};res.json(resultSet);
});

嘗試用api調(diào)試工具PUT 127.0.0.1:8080/db/user/record，攜帶相應(yīng)的請(qǐng)求體，將會(huì)得到[1]，這是影響的記錄數(shù)，1代表成功了

查

從user表里查詢(xún)uname=我們剛剛插入的記錄

app.get('/db/:tbname/record', async function (req, res) {//前端攜帶query: uname=evanplet resultSet = null;try {// select a record where uname=xxxresultSet = await sqlite(req.params.tbname).select('*').where('uname',req.query.uname);// Finally, add a catch statement} catch(e) {console.error(e);resultSet = e;};res.json(resultSet);
});

嘗試用api調(diào)試工具GET 127.0.0.1:8080/db/user/record?uname=evanp，將會(huì)得到:

[{"id": 1,"uname": "evanp","passwd": "iloveu"}
]

改

接下來(lái)我們修改uname=evanp這條記錄的passwd為123456

app.post('/db/:tbname/record', async function (req, res) {//前端攜帶query: uname=evanp/*前端請(qǐng)求體格式:{"passwd": "123456"}*/let resultSet = null;try {// select a record where uname=xxxresultSet = await sqlite(req.params.tbname).update(req.body).where('uname',req.query.uname);// Finally, add a catch statement} catch(e) {console.error(e);resultSet = e;};res.json(resultSet);
});

嘗試用api調(diào)試工具POST 127.0.0.1:8080/db/user/record?uname=evanp，并攜帶相應(yīng)請(qǐng)求體，將會(huì)得到: [1]，這代表影響記錄1條，成功了

刪

接下來(lái)我們刪除uname=evanp且passwd=123456的這條記錄

app.delete('/db/:tbname/record', async function (req, res) {/*前端請(qǐng)求體格式:{"uname": "evanp","passwd": "123456"}*/let resultSet = null;try {// select a record where uname=xxxresultSet = await sqlite(req.params.tbname).del().where(req.body);// Finally, add a catch statement} catch(e) {console.error(e);resultSet = e;};res.json(resultSet);
});

嘗試用api調(diào)試工具DELETE 127.0.0.1:8080/db/user/record，并攜帶相應(yīng)請(qǐng)求體，將會(huì)得到: [1]，這代表影響記錄1條，成功了

原生sql

當(dāng)然了，如果你需要直接使用sql語(yǔ)句，也是可以的，調(diào)用raw(sqlStr)即可，既可以作為某一段sql的綁定，也可以直接當(dāng)作整句sql

格式: knex.raw(sql, [bindings]

sqlite.raw("select * from user",[1]).then((resp)=>{//..})

在這里不做介紹

總結(jié)

以上給出了使用knex實(shí)現(xiàn)增刪改查的基本操作，這些方法并不是唯一的，在實(shí)際開(kāi)發(fā)中往往要應(yīng)對(duì)更復(fù)雜的場(chǎng)景，基礎(chǔ)crud也是遠(yuǎn)遠(yuǎn)不夠的
關(guān)于knex的更多拓展使用方法，請(qǐng)移步knex官方文檔https://knexjs.org/guide/

下一節(jié)-Sql-ORM增刪改查