nodejs內建sqlite

sqlite3是一個專為nodejs設計的，在nodejs上面生态健壯，幾乎支援所有版本的nodejs，同時也可以和nwjs內建。這裡簡單介紹一下sqlite3的用法

正在物色node上面的輕量級嵌入式資料庫，作為嵌入式資料庫的代表，sqlite無疑是個理想的選擇方案。npm上內建sqlite的庫主要有兩個——sqlite3和realm。

realm是一個理想的選擇方案，它最初是為移動app設計的，在node也可以運作的，但是不支援Windows系統。sqlite3是一個專為nodejs設計的，在nodejs上面生态更健壯，是以最終選擇sqlite3。

sqlite3幾乎支援所有版本的nodejs，同時也可以和nwjs內建。

安裝

基于npm安裝

npm install sqlite3
      

這樣除了安裝完sqlite3的npm包，最主要的是也裝完了sqlite資料庫，因為sqlite是嵌入式資料庫，嵌入到用戶端中。sqlite3使用node-pre-gyp為各個平台下載下傳指定的預編譯的二進制檔案。如果無法下載下傳到預編譯的二進制檔案，sqlite3将使用node-gyp和源代碼來建構擴充。

這個過程出現兩個的庫——node-pre-gyp和node-gyp。他們究竟是什麼呢？

node-gyp是一個跨平台的指令行工具，用于編譯C++編寫的nodejs擴充，首先gyp是為Chromium項目建立的項目生成工具，可以從平台無關的配置生成平台相關的Visual Studio、Xcode、Makefile的項目檔案，node-gyp就是将其內建到nodejs中。因為linux的二進制分發快平台做的并不好，所有npm為了友善幹脆就直接源碼分發，使用者裝的時候再現場編譯。不過對有些項目二進制分發就比源碼分發簡單多了，是以還有個node-pre-gyp來直接二進制擴充的分發。

兩者差別在于node-gyp是釋出擴充的源碼，然後安裝時候編譯；node-pre-gyp是直接釋出編譯後的二級制形式的擴充。

和sqlite3一樣的需要基于node-gyp安裝的npm子產品也有很多，比如node-sass等，都是釋出源代碼，然後編譯安裝。

基礎api

sqlite3的api都是基于函數回調的，因為nodejs中沒有像java的jdbc那種官方的資料庫用戶端接口，是以每個資料庫的api都不一樣，這裡簡單介紹幾個sqlite3重要的api。

建立并打開資料庫

new sqlite3.Database(filename, [mode], [callback])
      

該方法傳回一個自動打開的資料庫對象，參數：

filename：有效值是一個檔案名，如：“mydatebase.db”，資料庫打開之後會建立一個“mydatebase.db”的檔案用于儲存資料。如果檔案名是“:memory:”，表示是一個記憶體資料庫（類似h2那種），資料不會持久化儲存，當關閉資料庫時，内容将丢失。

mode（可選）：資料庫的模式，共3種值：sqlite3.OPEN_READONLY（隻讀），sqlite3.OPEN_READWRITE（可讀寫）和sqlite3.OPEN_CREATE（可以建立）。 預設值為OPEN_READWRITE |OPEN_CREATE。

callback（可選）:則當資料庫成功打開或發生錯誤時，将調用此函數。 第一個參數是一個錯誤對象，當它為空時，表示打開成功。

打開一個資料庫，如：

//資料庫的名字是"mydatebase.db"
var database;
database = new sqlite3.Database("mydatebase.db", function(e){
 if (err) throw err;
});
//也可以使用記憶體型，資料不會永久儲存
database = new sqlite3.Database(":memory:", function(e){
 if (err) throw err;
});
      

執行後會在項目的根目錄生成一個“mydatebase.db”檔案，這就是sqlite儲存資料的檔案了。

關閉資料庫

Database#close([callback])
      

該方法可以關閉一個資料庫連接配接對象，參數：

callback（可選）:關閉成功的回調。 第一個參數是一個錯誤對象，當它為“null”時，表示關閉成功。

執行DDL和DML語句

Database#run(sql, [param, ...], [callback])
      

該方法可以執行DDL和DML語句，如建表、删除表、删除行資料、插入行資料等，參數：

sql：要運作的SQL字元串。sql的類型是DDL和DML，DQL不能使用這個指令。執行後傳回值不包含任何結果，必須通過callback回調函數擷取執行結果。

param，...（可選）：當SQL語句包含占位符（?）時，這裡可以傳對應的參數。 這裡有三種傳值方法，如：

// 直接通過參數傳值.
db.run("UPDATE tbl SET name = ? WHERE id = ?", "bar", 2);


      

// 将值封裝為一個數組傳值.
db.run("UPDATE tbl SET name = ? WHERE id = ?", [ "bar", 2 ]);


      

// 使用一個json傳值.參數的字首可以是“:name”，“@name”和“$name”。推薦用“$name”形式
db.run("UPDATE tbl SET name = $name WHERE id = $id", {
 $id: 2,
 $name: "bar"
});
      

關于占位符的命名，sqlite3還支援更複雜的形式，這裡不再擴充，有興趣了解的話請檢視官方文檔。

callback（可選）：如果執行成功，則第一個參數為null，否則就是出錯。

如果執行成功，上下文this包含兩個屬性：lastID和changes。lastID表示在執行INSERT指令語句時，最後一條資料的id；changes表示UPADTE指令和DELETE指令時候，影響的資料行數。

db.run("UPDATE foo SET id = 1 WHERE id <= 500", function(err) {
if (err) throw err;
 //使用this.changes擷取改變的行數
 assert.equal(500, this.changes);
 done();
});
      

執行多條語句

Database#exec(sql, [callback])
      

Database#exec與Database＃run函數一樣，都是DDL和DML語句，但是Database#exec可以執行多條語句，并且不支援占位符參數。

database.run("CREATE TABLE foo (id INT)", function(e){
 if(e !== null){
 throw e;
}
 //循環生成sql語句，批次插入多條資料
 var sql = "";
 for(var i = 0 ; i < 500; i ++){
 sql += 'INSERT INTO foo VALUES(' + i + ');'
}
 database.exec(sql, done)
});
      

查詢一條資料

Database#get(sql, [param, ...], [callback])
      

sql：要運作的SQL字元串。sql的類型是DQL。這裡僅傳回第一條查詢到的資料。

param，...（可選）：同Database#run的param參數

callback（可選）：同樣是傳回null代表執行成功。回調的簽名是function（err，row）。如果查詢結果集為空，則第二個參數為undefined；否則第二個參數值是查詢到的第一個對象，他是個json對象，屬性名稱對應于結果集的列名稱，是以查詢的每一列都應該給出一個清單名。

查詢所有資料

Database#all(sql, [param, ...], [callback])
      

sql：要運作的SQL字元串。sql的類型是DQL。和Database#get不同，Database#all會傳回所有查詢到的語句。

callback（可選）：同樣是傳回null代表執行成功。回調的簽名是function(err, rows) 。rows是一個數組，如果查詢結果集為空數組。

注意，Database#all首先檢索所有結果行并将其存儲在記憶體中。 對于資料量可能很大的查詢指令時候，請使用Database＃each函數或Database＃prepare代替這個方法。

周遊資料

Database#each(sql, [param, ...], [callback], [complete])
      

與Database＃run函數相同，都是查詢多條資料，但是具有以下差別：

回調的簽名是function（err，row）。如果結果內建功但為空，則不會調用回調。對于每個檢索到的行，該方法都會調用一次回調。執行順序與結果集中的行順序完全對應。

調用所有行回調後，如果存在complete回調函數，将調用這個回調。第一個參數是一個錯誤對象，第二個參數是檢索行數。

語句執行順序

sqlite3的API都是異步的，這就會出現可能有若幹個指令同時進行的情況，是以sqlite3提供了兩個函數來幫助控制語句的執行流程。預設是并行模式。

序列化執行

Database#serialize([callback])
      

如果提供回調，它将立即被調用，即此方法的回調不是異步回調。在該回調中排程的所有資料庫語句将被序列化運作，即一個接一個地執行。 函數傳回後，資料庫将再次設定為其原始模式。

// 這裡執行的指令是并行的
db.serialize(function() {
 // 這裡執行的指令是串行的
 db.serialize(function() {
 // 這裡執行的指令是串行的
});
 // 這裡執行的指令是串行的
});
// 這裡執行的指令是并行的
      

并行執行模式

Database#parallelize([callback])
      

如果提供回調，它将立即被調用，即此方法的回調不是異步回調。在該回調中排程的所有資料庫語句将并行運作。函數傳回後，資料庫将再次設定為其原始模式。

db.serialize(function() {
 // 這裡執行的指令是串行的
 db.parallelize(function() {
 // 這裡執行的指令是并行的
});
 // 這裡執行的指令是串行的
});
      

預編譯SQL相關api

在java的jdbc中，有個PreparedStatement相關的api，可以預編譯sql語句，執行的時候再連結具體參數。這樣的好處是可以減少sql語句被編譯的次數。在sqlite3中，也存在實作這樣功能的api。

Database#prepare(sql, [param, ...], [callback])
      

Database#prepare執行後，會傳回一個指令對象，這個指令對象可以反複執行。下面看看這個指令對象（statement ）的api：

Statement#run([param, ...], [callback])
Statement#get([param, ...], [callback])
Statement#all([param, ...], [callback])
Statement#each([param, ...], [callback])
      

以上api方法與Database的同名方法調用方式相同。不同點是這裡的Statement對象是可以複用的，避免了重複編譯sql語句，是以項目中更推薦使用上述方法。

注意，這些方法的param參數都會對Statement對象綁定參數，在下一次執行的時候，如果沒有重新綁定參數，是會使用上一次參數的。

綁定參數

Statement#bind([param, ...], [callback])
      

Database#prepare執行的時候，是可以綁定參數的。不過使用此方法可以全重置語句對象和行遊标，并删除所有先前綁定的參數，實作重新綁定的功能。

重置語句的行遊标

Statement#reset([callback])
      

重置語句的行遊标，并保留參數綁定。使用此功能可以使用相同的綁定重新執行相同的查詢。

資料庫事務

事務是關系型資料庫中的一個重要部分，sqlite自然也是支援事務的，但是sqlite3并沒有提供特殊API去實作的事務相關的操作，隻能靠SQL語句去控制事務。這裡舉一個事務相關的例子。

var db = new sqlite3.Database(db_path);
db.run("CREATE TABLE foo (id INT, txt TEXT)");
db.run("BEGIN TRANSACTION");
var stmt = db.prepare("INSERT INTO foo VALUES(?, ?)");
for (var i = 0; i < count; i++) {
 stmt.run(i, randomString());
}
db.run("COMMIT TRANSACTION");
      

對SQLCipher的支援

SQLCipher是一個在SQLite基礎之上進行擴充的開源資料庫，他和SQLite不同就是提供了對資料的加密，可提供資料庫檔案的透明256位AES加密。

sqlite3的官網特意提及他對SQLCipher的內建，如果要內建sqlcipher需要在編譯時候通過建構選項告訴sqlite3要內建的是SQLCipher：

npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=/usr/
node -e 'require("sqlite3")'
      

不過筆者并沒嘗試對SQLCipher的內建，具體內建方法請自行查閱官網對這部分的詳細介紹。

基于promise對sqlite3API的封裝

sqlite3的API是node早期的API風格，對異步的書寫風格并不友好，很容易出現“金字塔回調”式的代碼。為了讓對API的調用更加優雅，我們往往會把回調封裝成Promise。事實上這個工作并不需要我們自己做，sqlite3生态下已經有其他庫可以實作這樣的功能。sqlite就是一個這樣的庫。他基于sqlite3，隻手用Promise重新封裝了一下sqlite3的API，使其代碼風格更加優雅，也更容易使用。