UMUTech

SQLite Node.js

选型

mapbox / sqlite3: Asynchronous, non-blocking SQLite3 bindings for Node.js

安装：

1
yarn add sqlite3

常见操作

1. 创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
const path = require('path')
const sqlite = require('sqlite3')

const dbPath = path.join(__dirname, 'test.db')
const db = new sqlite.Database(dbPath)

const sqls = [`CREATE TABLE test(
  id CHAR(64) NOT NULL PRIMARY KEY CHECK(LENGTH(id) == 64),
  timeStamp INTEGER NOT NULL,
  state INTEGER NOT NULL DEFAULT 0)`
  , 'CREATE INDEX index_id ON txs(id)'
  , 'CREATE INDEX index_timeStamp ON txs(timeStamp)'
  , 'CREATE INDEX index_state ON txs(state)'
]

db.serialize(() => {
  for (let sql of sqls) {
    db.run(sql, (err) => {
      if (err) {
        console.error(err)
      } else {
        console.log('SQL executed.')
      }
    })
  }
})

db.close()

2. 插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
const items = [
  { hash: 'D141925E39814FB5256615A1A94EC82B7043D983F68423D8C149A2AE360B623C'
    , ts: 1563273661316, state: 0 }
  , { hash: '2E9F26F5D0A73AE5DAFC8A1C22264725972AA997A22522A906D8CD7E225096ED'
    , ts: 1563273661317, state: 1 }
  , { hash: '0FF5F5F5E96664939D07D94975342D71F824747EFECE1D24FDDBB3B29DD91DCB'
    , ts: 1563273661318, state: 0 }
]

db.serialize(() => {
  const stmt = db.prepare("INSERT INTO test VALUES (?, ?, ?)")
  for (const item of items) {
    stmt.run(item.hash, item.ts, item.state, (err) => {
        if (err) {
          console.error(err)
        } else {
          console.log('INSERT', item.hash)
        }
      })
  }
  stmt.finalize()
})

3. 查询

1
2
3
4
5
6
7
8
9
10
11
db.serialize(() => {
  db.each("SELECT * FROM test WHERE state=0", (err, row) => {
    if (err) {
      console.error('SELECT state=0 error:', err)
    } else {
      // do something here
    }
  }, (err, count) => {
    // do something here
  })
})

4. 更新

1
2
3
4
5
6
7
db.run("UPDATE test SET state=1 WHERE state=0", (err) => {
    if (err) {
      console.error('UPDATE txs error:', err)
    } else {
      console.log('UPDATE state to 1')
    }
  })

参考

http://www.sqlitetutorial.net/sqlite-nodejs/