Node.js 的 SQLite 教程

在本教程中，我將演示如何借助 sqlite3 Node.js 驅動程序在 Node.js 環境中結合使用 SQLite 和 JavaScript。對於不熟悉 SQLite 的人來說，它是一個簡單的單文件關係型數據庫，在智能設備、嵌入式系統甚至小型 Web 應用程序中非常流行。

設置和安裝

我將首先使用 npm init 創建一個新的 npm 包 在一個名為 node-sqlite-tutorial 的空目錄中。

$ npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sane defaults.

See `npm help json` for definitive documentation on these fields
and exactly what they do.

Use `npm install <pkg> --save` afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
name: (app) node-sqlite
version: (0.0.0) 0.1.0
description: Code for tutorial blog on node and sqlite
entry point: (index.js) main.js
test command: 
git repository: 
keywords: 
author: Adam McQuistan
license: (BSD) MIT
About to write to /node-sqlite/app/package.json:

{
  "name": "node-sqlite",
  "version": "0.1.0",
  "description": "Code for tutorial blog on node and sqlite",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "repository": "",
  "author": "Adam McQuistan",
  "license": "MIT"
}


Is this ok? (yes) 

接下來我需要通過 npm 安裝 sqlite3 包，如下所示：

$ npm install --save sqlite3

除了 sqlite3，我還將安裝 Bluebird，以便在我的數據庫編程中使用熟悉的 Promise 功能。

$ npm install --save bluebird

我現在將在 package.json 文件旁邊創建一個名為 database.sqlite3 的空文件，SQLite 將在其中存儲數據。

設計數據庫

與我的幾乎所有其他文章一樣，我將使用一個組成的應用程序來幫助描述使用 Node.js 和 SQLite 進行數據庫編程的一些重要方面。對於本文，我假設我正在為項目和任務跟踪應用程序構建數據訪問層。本應用數據訪問層的基本業務規則如下：

• 應用有項目
• 每個項目都可以完成一項或多項任務

說明業務規則後，我可以獲取該信息並開始設計必要的表及其字段。很明顯，我需要一個 projects 表以及 tasks 桌子。其餘的我將只使用一點直覺，一些組成測試數據，然後順其自然（大多數開發人員的常見工作特徵）。

項目表

任務表

好的，現在我知道我需要創建什麼，我現在可以將其轉換為代碼。

創建數據庫

首先，我需要在 package.json 文件所在的同一目錄中創建一個 main.js 文件以及一個 dao.js（或數據訪問對象）文件。

在 dao.js 中，我將為 sqlite3 和 Bluebird 的 Promise 添加導入 對象。之後，我將搭建一個名為 AppDAO 的數據訪問類 這將在構造函數中建立與數據庫的連接並將其分配給名為 db 的成員字段 .

// dao.js

const sqlite3 = require('sqlite3')
const Promise = require('bluebird')

class AppDAO {
  constructor(dbFilePath) {
    this.db = new sqlite3.Database(dbFilePath, (err) => {
      if (err) {
        console.log('Could not connect to database', err)
      } else {
        console.log('Connected to database')
      }
    })
  }
}

module.exports = AppDAO

連接非常簡單。您只需實例化 sqlite3 Database 類構造函數，通過將路徑傳遞給您要連接的 SQLite 數據庫文件，並可選擇檢查可能發生的錯誤。如上所述，我將此連接對象存儲在名為 db 的字段中 在 AppDAO 類。

我將解釋如何使用連接對象向數據庫提交查詢。 sqlite3 Node.js 包提供了一些執行查詢的不同方法，但我將在本教程中重點介紹的是：

• run :用於創建或更改表以及插入或更新表數據
• get :從一個或多個表中選擇一行數據
• all :從一個或多個表中選擇多行數據

首先，我想探索 run 方法。它的一般語法如下所示：

db.run('SOME SQL QUERY', [param1, param2], (err) => {
  if (err) {
    console.log('ERROR!', err)
  }
})

傳遞給run(...)的第一個參數 是要執行的 SQL 字符串，是唯一必需的參數。第二個是可選的參數數組，sqlite3 庫將為任何“？”交換。查詢中的佔位符（我將稍作演示）。 final是一個錯誤回調函數。

您可能會懷疑我將使用 run(...) 創建和更新我的項目和任務的功能。但是，我實際上會將它包裝在我自己的 run 版本中 AppDAO 上的方法 類，因為我想將它封裝在 bluebird Promise 讓事情像這樣顯式異步和基於承諾：

// dao.js

const sqlite3 = require('sqlite3')
const Promise = require('bluebird')

class AppDAO {
  // omitting constructor code
  
  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function (err) {
        if (err) {
          console.log('Error running sql ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve({ id: this.lastID })
        }
      })
    })
  }
}

使用我的自定義 AppDAO.run(...) 方法我現在可以用它來創建產品和任務表。

首先，我將另外兩個文件添加到我的項目中，名為 project_repository.js 和 task_repository.js。在 project_repository.js 中，我定義了一個名為 ProjectRepository 的類 它有一個接受 AppDAO 實例的構造函數 對象和一個 createTable 像這樣執行一些 DDL（數據定義語言）SQL 的方法：

// project_repository.js

class ProjectRepository {
  constructor(dao) {
    this.dao = dao
  }

  createTable() {
    const sql = `
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT)`
    return this.dao.run(sql)
  }
}

module.exports = ProjectRepository;

然後我再次做同樣的事情，但這次是在 task_repository.js 文件中。

// task_repository.js

class TaskRepository {
  constructor(dao) {
    this.dao = dao
  }

  createTable() {
    const sql = `
      CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        description TEXT,
        isComplete INTEGER DEFAULT 0,
        projectId INTEGER,
        CONSTRAINT tasks_fk_projectId FOREIGN KEY (projectId)
          REFERENCES projects(id) ON UPDATE CASCADE ON DELETE CASCADE)`
    return this.dao.run(sql)
  }
}

module.exports = TaskRepository;

創建表的 DDL SQL 已經完成，接下來我將繼續介紹向表中插入數據的方法。

插入數據

在 ProjectRepository 類我需要添加一個 create 接收項目名稱以創建並使用 AppDAO.run(...) 執行適當的 INSERT 語句的方法 方法。請注意我是如何使用“？”的代表項目名稱的值，然後把 name run(...) 的可選 params 數組參數中的參數 方法。這被稱為參數化查詢語句，它將對輸入進行過濾，以最大限度地降低 SQL 注入風險。

// project_repository.js

class ProjectRepository {
  // omitting other methods

  create(name) {
    return this.dao.run(
      'INSERT INTO projects (name) VALUES (?)',
      [name])
  }
}

module.exports = ProjectRepository;

TaskRepository 需要類似的創建方法 類。

免費電子書：Git Essentials

查看我們的 Git 學習實踐指南，其中包含最佳實踐、行業認可的標準以及隨附的備忘單。停止谷歌搜索 Git 命令並真正學習 它！

// task_repository.js

class TaskRepository {
  // omitting other methods

  create(name, description, isComplete, projectId) {
    return this.dao.run(
      `INSERT INTO tasks (name, description, isComplete, projectId)
        VALUES (?, ?, ?, ?)`,
      [name, description, isComplete, projectId])
  }
}

module.exports = TaskRepository;

現在我已經能夠將數據插入到數據庫中，我想添加更新它的功能。

更新數據

在 ProjectRepository 類我將添加一個 update 採用 project 的方法 對象並再次使用 AppDAO.run(...) 更新該項目的數據庫記錄的所有字段 方法，像這樣：

// project_repository.js

class ProjectRepository {
  // omitting other methods

  update(project) {
    const { id, name } = project
    return this.dao.run(
      `UPDATE projects SET name = ? WHERE id = ?`,
      [name, id]
    )
  }
}

module.exports = ProjectRepository;

接下來就是在TaskRepository中添加對應的更新方法 類。

// task_repository.js

class TaskRepository {
  // omitting other methods

  update(task) {
    const { id, name, description, isComplete, projectId } = task
    return this.dao.run(
      `UPDATE tasks
      SET name = ?,
        description = ?,
        isComplete = ?,
        projectId = ?
      WHERE id = ?`,
      [name, description, isComplete, projectId, id]
    )
  }
}

module.exports = TaskRepository;

刪除數據

要實現的最後一個突變功能是提供從數據庫中刪除記錄的能力。為此，我將再次使用 AppDAO.run(...) 方法結合新的 delete ProjectRepository 的方法 和 TaskRepository 類。

對於 ProjectRepository 這看起來像這樣：

// project_repository.js

class ProjectRepository {
  // omitting other methods

  delete(id) {
    return this.dao.run(
      `DELETE FROM projects WHERE id = ?`,
      [id]
    )
  }
}

module.exports = ProjectRepository;

對於 TaskRepository 它看起來像這樣：

// task_repository.js

class TaskRepository {
  // omitting other methods

  delete(id) {
    return this.dao.run(
      `DELETE FROM tasks WHERE id = ?`,
      [id]
    )
  }
}

module.exports = TaskRepository;

好的，這總結了我將使用 run 的所有方式 方法。接下來介紹另外兩個相關的get 和 all sqlite3 Node.js 封裝方法。

讀取數據

在本節中，我將介紹如何使用 get 和 all sqlite3 Node.js 庫的方法。如前所述，get 用於檢索單行數據，而 all 用於查詢多行數據。

使用 get 的基本語法 看起來像這樣：

db.get('SELECT ...', [param1, param2], (err, result) => {
  if (err) {
    console.log(err)
  } else {
    // do something with result
  }
})

db 是一個 sqlite3 連接對象。您會注意到語法與 run 基本相同 方法，只是回調有一個額外的參數，它保存查詢的結果對象，假設沒有拋出錯誤。

all 的基本語法 本質上還是一樣的，只是回調的第二個參數是查詢返回的結果數組，如下所示：

db.all('SELECT ...', [param1, param2], (err, results) => {
  if (err) {
    console.log(err)
  } else {
    // do something with results
  }
})

就像我對 sqlite3 run 所做的那樣 方法我要實現 get 和 all 利用 bluebird 的方法 Promise AppDAO 內 類如下圖：

// dao.js

const sqlite3 = require('sqlite3').verbose()
const Promise = require('bluebird')

class AppDAO {
  // omitting other methods
  
  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, result) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(result)
        }
      })
    })
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(rows)
        }
      })
    })
  }
}

我現在可以在 ProjectRepository 中使用這些方法 和 TaskRepository 類從 SQLite 數據庫中檢索數據。

首先，我將添加 getById 每個類的方法來通過 id 選擇他們的記錄。

在 ProjectRepository 我補充一下：

// project_repository.js

class ProjectRepository {
  // omitting other methods

  getById(id) {
    return this.dao.get(
      `SELECT * FROM projects WHERE id = ?`,
      [id])
  }
}

module.exports = ProjectRepository;

而在 TaskRepository 類似的：

// task_repository.js

class TaskRepository {
  // omitting other methods

  getById(id) {
    return this.dao.get(
      `SELECT * FROM tasks WHERE id = ?`,
      [id])
  }
}

module.exports = TaskRepository;

演示AppDAO.all(...) 方法我將添加選擇所有項目以及給定項目的所有任務的功能。

選擇所有項目的代碼如下所示：

// project_repository.js

class ProjectRepository {
  // omitting other methods

  getAll() {
    return this.dao.all(`SELECT * FROM projects`)
  }
}

module.exports = ProjectRepository;

然後為一個項目選擇所有任務，我將使用一個名為 getTasks(projectId) 的方法 它需要你想要任務的項目的 id。

// project_repository.js
class ProjectRepository {
  // omitting other methods

  getTasks(projectId) {
    return this.dao.all(
      `SELECT * FROM tasks WHERE projectId = ?`,
      [projectId])
  }
}

module.exports = ProjectRepository;

使用數據訪問代碼

到目前為止，我基本上已經為這個虛構的項目和任務跟踪應用程序創建了一個數據訪問庫。我現在想做的是用它來加載我在設計數據庫的表格中顯示的測試數據 部分。

在 main.js 文件中，我將要拉入 AppDAO , ProjectRepository , 和 TaskRepository 通過 require 的類 .然後我將使用它們來創建表，然後用數據填充它們，從數據庫中檢索數據並顯示到控制台。

// main.js

const Promise = require('bluebird')
const AppDAO = require('./dao')
const ProjectRepository = require('./project_repository')
const TaskRepository = require('./task_repository')

function main() {
  const dao = new AppDAO('./database.sqlite3')
  const blogProjectData = { name: 'Write Node.js - SQLite Tutorial' }
  const projectRepo = new ProjectRepository(dao)
  const taskRepo = new TaskRepository(dao)
  let projectId

  projectRepo.createTable()
    .then(() => taskRepo.createTable())
    .then(() => projectRepo.create(blogProjectData.name))
    .then((data) => {
      projectId = data.id
      const tasks = [
        {
          name: 'Outline',
          description: 'High level overview of sections',
          isComplete: 1,
          projectId
        },
        {
          name: 'Write',
          description: 'Write article contents and code examples',
          isComplete: 0,
          projectId
        }
      ]
      return Promise.all(tasks.map((task) => {
        const { name, description, isComplete, projectId } = task
        return taskRepo.create(name, description, isComplete, projectId)
      }))
    })
    .then(() => projectRepo.getById(projectId))
    .then((project) => {
      console.log(`\nRetreived project from database`)
      console.log(`project id = ${project.id}`)
      console.log(`project name = ${project.name}`)
      return taskRepo.getTasks(project.id)
    })
    .then((tasks) => {
      console.log('\nRetrieved project tasks from database')
      return new Promise((resolve, reject) => {
        tasks.forEach((task) => {
          console.log(`task id = ${task.id}`)
          console.log(`task name = ${task.name}`)
          console.log(`task description = ${task.description}`)
          console.log(`task isComplete = ${task.isComplete}`)
          console.log(`task projectId = ${task.projectId}`)
        })
      })
      resolve('success')
    })
    .catch((err) => {
      console.log('Error: ')
      console.log(JSON.stringify(err))
    })
}

main()

使用 node 運行 像這樣：

$ node main.js

您將看到如下所示的輸出。

Connected to database
Retreived project from database
project id = 1
project name = 1
Retrieved project tasks from database
task id = 1
task name = Outline
task description = High level overview of sections
task isComplete = 1
task projectId = 1
task id = 2
task name = Write
task description = Write article contents and code examples
task isComplete = 0
task projectId = 1

結論

在本教程中，我回顧了 Node.js sqlite3 包 API 的基礎知識，並演示瞭如何將該功能封裝在面向對象的 JavaScript 中，重點是基於 Promise 的異步實現。

一如既往，感謝您的閱讀，歡迎在下方發表評論和批評。