sqlite3

What is sqlite3?

The sqlite3 npm package is a library that provides a straightforward interface for interacting with SQLite databases in Node.js applications. It allows you to create, read, update, and delete records in SQLite databases, execute SQL queries, and manage database connections.

What are sqlite3's main functionalities?

Create a Database

This code demonstrates how to create an in-memory SQLite database, create a table, and insert some records into it.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
  db.run('CREATE TABLE lorem (info TEXT)');
  const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
  for (let i = 0; i < 10; i++) {
    stmt.run('Ipsum ' + i);
  }
  stmt.finalize();
});
db.close();

Query a Database

This code shows how to create a table, insert records, and query the database to retrieve and print the records.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
  db.run('CREATE TABLE lorem (info TEXT)');
  const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
  for (let i = 0; i < 10; i++) {
    stmt.run('Ipsum ' + i);
  }
  stmt.finalize();
  db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
    console.log(row.id + ': ' + row.info);
  });
});
db.close();

Update Records

This code demonstrates how to update records in an SQLite database.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
  db.run('CREATE TABLE lorem (info TEXT)');
  const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
  for (let i = 0; i < 10; i++) {
    stmt.run('Ipsum ' + i);
  }
  stmt.finalize();
  db.run('UPDATE lorem SET info = ? WHERE rowid = ?', ['Updated Ipsum', 1]);
  db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
    console.log(row.id + ': ' + row.info);
  });
});
db.close();

Delete Records

This code shows how to delete records from an SQLite database.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
  db.run('CREATE TABLE lorem (info TEXT)');
  const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
  for (let i = 0; i < 10; i++) {
    stmt.run('Ipsum ' + i);
  }
  stmt.finalize();
  db.run('DELETE FROM lorem WHERE rowid = ?', 1);
  db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
    console.log(row.id + ': ' + row.info);
  });
});
db.close();

Other packages similar to sqlite3

better-sqlite3

better-sqlite3 is a faster and simpler alternative to sqlite3. It provides a more synchronous API, which can be easier to work with in some cases. Unlike sqlite3, better-sqlite3 does not use callbacks and instead returns results directly.

sequelize

Sequelize is a promise-based Node.js ORM for various SQL databases, including SQLite. It provides a higher-level abstraction over SQL queries and supports features like model definition, associations, and migrations. It is more feature-rich compared to sqlite3 but also more complex.

knex

Knex.js is a SQL query builder for Node.js that supports multiple databases, including SQLite. It provides a flexible and powerful API for building and executing SQL queries. Knex.js can be used with or without an ORM and offers more flexibility compared to sqlite3.

README

bl (BufferList)

A Node.js Buffer list collector, reader and streamer thingy.

bl is a storage object for collections of Node Buffers, exposing them with the main Buffer readable API. Also works as a duplex stream so you can collect buffers from a stream that emits them and emit buffers to a stream that consumes them!

The original buffers are kept intact and copies are only done as necessary. Any reads that require the use of a single original buffer will return a slice of that buffer only (which references the same memory as the original buffer). Reads that span buffers perform concatenation as required and return the results transparently.

const BufferList = require('bl')

var bl = new BufferList()
bl.append(new Buffer('abcd'))
bl.append(new Buffer('efg'))
bl.append('hi')                     // bl will also accept & convert Strings
bl.append(new Buffer('j'))
bl.append(new Buffer([ 0x3, 0x4 ]))

console.log(bl.length) // 12

console.log(bl.slice(0, 10).toString('ascii')) // 'abcdefghij'
console.log(bl.slice(3, 10).toString('ascii')) // 'defghij'
console.log(bl.slice(3, 6).toString('ascii'))  // 'def'
console.log(bl.slice(3, 8).toString('ascii'))  // 'defgh'
console.log(bl.slice(5, 10).toString('ascii')) // 'fghij'

// or just use toString!
console.log(bl.toString())               // 'abcdefghij\u0003\u0004'
console.log(bl.toString('ascii', 3, 8))  // 'defgh'
console.log(bl.toString('ascii', 5, 10)) // 'fghij'

// other standard Buffer readables
console.log(bl.readUInt16BE(10)) // 0x0304
console.log(bl.readUInt16LE(10)) // 0x0403

Give it a callback in the constructor and use it just like concat-stream:

const bl = require('bl')
    , fs = require('fs')

fs.createReadStream('README.md')
  .pipe(bl(function (err, data) { // note 'new' isn't strictly required
    // `data` is a complete Buffer object containing the full data
    console.log(data.toString())
  }))

Note that when you use the callback method like this, the resulting data parameter is a concatenation of all Buffer objects in the list. If you want to avoid the overhead of this concatenation (in cases of extreme performance consciousness), then avoid the callback method and just listen to 'end' instead, like a standard Stream.

Or to fetch a URL using hyperquest (should work with request and even plain Node http too!):

const hyperquest = require('hyperquest')
    , bl         = require('bl')
    , url        = 'https://raw.github.com/rvagg/bl/master/README.md'

hyperquest(url).pipe(bl(function (err, data) {
  console.log(data.toString())
}))

Or, use it as a readable stream to recompose a list of Buffers to an output source:

const BufferList = require('bl')
    , fs         = require('fs')

var bl = new BufferList()
bl.append(new Buffer('abcd'))
bl.append(new Buffer('efg'))
bl.append(new Buffer('hi'))
bl.append(new Buffer('j'))

bl.pipe(fs.createWriteStream('gibberish.txt'))

API

• new BufferList([ callback ])
• bl.length
• bl.append(buffer)
• bl.get(index)
• bl.slice([ start[, end ] ])
• bl.copy(dest, [ destStart, [ srcStart [, srcEnd ] ] ])
• bl.duplicate()
• bl.consume(bytes)
• bl.toString([encoding, [ start, [ end ]]])
• bl.readDoubleBE(), bl.readDoubleLE(), bl.readFloatBE(), bl.readFloatLE(), bl.readInt32BE(), bl.readInt32LE(), bl.readUInt32BE(), bl.readUInt32LE(), bl.readInt16BE(), bl.readInt16LE(), bl.readUInt16BE(), bl.readUInt16LE(), bl.readInt8(), bl.readUInt8()
• Streams

---

new BufferList([ callback | Buffer | Buffer array | BufferList | BufferList array | String ])

The constructor takes an optional callback, if supplied, the callback will be called with an error argument followed by a reference to the bl instance, when bl.end() is called (i.e. from a piped stream). This is a convenient method of collecting the entire contents of a stream, particularly when the stream is chunky, such as a network stream.

Normally, no arguments are required for the constructor, but you can initialise the list by passing in a single Buffer object or an array of Buffer object.

new is not strictly required, if you don't instantiate a new object, it will be done automatically for you so you can create a new instance simply with:

var bl = require('bl')
var myinstance = bl()

// equivilant to:

var BufferList = require('bl')
var myinstance = new BufferList()

---

bl.length

Get the length of the list in bytes. This is the sum of the lengths of all of the buffers contained in the list, minus any initial offset for a semi-consumed buffer at the beginning. Should accurately represent the total number of bytes that can be read from the list.

---

bl.append(Buffer | Buffer array | BufferList | BufferList array | String)

append(buffer) adds an additional buffer or BufferList to the internal list. this is returned so it can be chained.

---

bl.get(index)

get() will return the byte at the specified index.

---

bl.slice([ start, [ end ] ])

slice() returns a new Buffer object containing the bytes within the range specified. Both start and end are optional and will default to the beginning and end of the list respectively.

If the requested range spans a single internal buffer then a slice of that buffer will be returned which shares the original memory range of that Buffer. If the range spans multiple buffers then copy operations will likely occur to give you a uniform Buffer.

---

bl.copy(dest, [ destStart, [ srcStart [, srcEnd ] ] ])

copy() copies the content of the list in the dest buffer, starting from destStart and containing the bytes within the range specified with srcStart to srcEnd. destStart, start and end are optional and will default to the beginning of the dest buffer, and the beginning and end of the list respectively.

---

bl.duplicate()

duplicate() performs a shallow-copy of the list. The internal Buffers remains the same, so if you change the underlying Buffers, the change will be reflected in both the original and the duplicate. This method is needed if you want to call consume() or pipe() and still keep the original list.Example:

var bl = new BufferList()

bl.append('hello')
bl.append(' world')
bl.append('\n')

bl.duplicate().pipe(process.stdout, { end: false })

console.log(bl.toString())

---

bl.consume(bytes)

consume() will shift bytes off the start of the list. The number of bytes consumed don't need to line up with the sizes of the internal Buffers—initial offsets will be calculated accordingly in order to give you a consistent view of the data.

---

bl.toString([encoding, [ start, [ end ]]])

toString() will return a string representation of the buffer. The optional start and end arguments are passed on to slice(), while the encoding is passed on to toString() of the resulting Buffer. See the Buffer#toString() documentation for more information.

---

bl.readDoubleBE(), bl.readDoubleLE(), bl.readFloatBE(), bl.readFloatLE(), bl.readInt32BE(), bl.readInt32LE(), bl.readUInt32BE(), bl.readUInt32LE(), bl.readInt16BE(), bl.readInt16LE(), bl.readUInt16BE(), bl.readUInt16LE(), bl.readInt8(), bl.readUInt8()

All of the standard byte-reading methods of the Buffer interface are implemented and will operate across internal Buffer boundaries transparently.

See the Buffer documentation for how these work.

---

Streams

bl is a Node Duplex Stream, so it can be read from and written to like a standard Node stream. You can also pipe() to and from a bl instance.

---

Contributors

bl is brought to you by the following hackers:

• Rod Vagg
• Matteo Collina
• Jarett Cruger

=======

License & copyright

Copyright (c) 2013-2014 bl contributors (listed above).

bl is licensed under the MIT license. All rights not explicitly granted in the MIT license are reserved. See the included LICENSE.md file for more details.