Sql增刪改查

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

準備工作

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

• 安裝sqlite3依賴 npm install sqlite3
• 安裝knex依賴 npm install knex
• 引入依賴

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

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

創(chuàng)建項目

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

創(chuàng)建sqlite連接

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

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

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

連接配置

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

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

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

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

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

acquireConnectionTimeout

連接超時時間

日志

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

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

數(shù)據(jù)表

建表

語法: sqlite.schema.createTable(表名, table=>{表結構})

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

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

瞅瞅控制臺:

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ù)庫，反正成功創(chuàng)建了user表，你要是加了useNullAsDefault這個flag，反而會告訴你 not supported by node-sqlite3

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

刪表

語法: 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) {/*前端請求體格式:{"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調試工具PUT 127.0.0.1:8080/db/user/record，攜帶相應的請求體，將會得到[1]，這是影響的記錄數(shù)，1代表成功了

查

從user表里查詢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調試工具GET 127.0.0.1:8080/db/user/record?uname=evanp，將會得到:

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

改

接下來我們修改uname=evanp這條記錄的passwd為123456

app.post('/db/:tbname/record', async function (req, res) {//前端攜帶query: uname=evanp/*前端請求體格式:{"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調試工具POST 127.0.0.1:8080/db/user/record?uname=evanp，并攜帶相應請求體，將會得到: [1]，這代表影響記錄1條，成功了

刪

接下來我們刪除uname=evanp且passwd=123456的這條記錄

app.delete('/db/:tbname/record', async function (req, res) {/*前端請求體格式:{"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調試工具DELETE 127.0.0.1:8080/db/user/record，并攜帶相應請求體，將會得到: [1]，這代表影響記錄1條，成功了

原生sql

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

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

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

在這里不做介紹

總結

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

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