SQLite

import sqlite from 'node:sqlite';

const sqlite = require('node:sqlite');

import { Database } from 'node:sqlite';
const database = new Database(':memory:');

// 从字符串执行 SQL 语句。
database.exec(`
  CREATE TABLE data(
    key INTEGER PRIMARY KEY,
    value TEXT
  ) STRICT
`);
// 创建一个预编译语句，用于向数据库插入数据。
const insert = database.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
// 使用绑定值执行预编译语句。
insert.run(1, 'hello');
insert.run(2, 'world');
// 不再需要预编译语句时将其终结。
insert.close();
// 创建一个预编译语句，用于从数据库读取数据。
const query = database.prepare('SELECT * FROM data ORDER BY key');
// 执行预编译语句并输出结果集。
console.log(query.all());
// 输出：[ { key: 1, value: 'hello' }, { key: 2, value: 'world' } ]
query.close();

const { Database } = require('node:sqlite');
const database = new Database(':memory:');

// 从字符串执行 SQL 语句。
database.exec(`
  CREATE TABLE data(
    key INTEGER PRIMARY KEY,
    value TEXT
  ) STRICT
`);
// 创建一个预编译语句，用于向数据库插入数据。
const insert = database.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
// 使用绑定值执行预编译语句。
insert.run(1, 'hello');
insert.run(2, 'world');
// 不再需要预编译语句时将其终结。
insert.close();
// 创建一个预编译语句，用于从数据库读取数据。
const query = database.prepare('SELECT * FROM data ORDER BY key');
// 执行预编译语句并输出结果集。
console.log(query.all());
// 输出：[ { key: 1, value: 'hello' }, { key: 2, value: 'world' } ]
query.close();

const { Database } = require('node:sqlite');

const db = new Database(':memory:');
db.exec(`
  CREATE TABLE t3(x, y);
  INSERT INTO t3 VALUES ('a', 4),
                        ('b', 5),
                        ('c', 3),
                        ('d', 8),
                        ('e', 1);
`);

db.aggregate('sumint', {
  start: 0,
  step: (acc, value) => acc + value,
});

using query = db.prepare('SELECT sumint(y) as total FROM t3');
query.get(); // { total: 21 }

import { Database } from 'node:sqlite';

const db = new Database(':memory:');
db.exec(`
  CREATE TABLE t3(x, y);
  INSERT INTO t3 VALUES ('a', 4),
                        ('b', 5),
                        ('c', 3),
                        ('d', 8),
                        ('e', 1);
`);

db.aggregate('sumint', {
  start: 0,
  step: (acc, value) => acc + value,
});

using query = db.prepare('SELECT sumint(y) as total FROM t3');
query.get(); // { total: 21 }

import { Database } from 'node:sqlite';
const database = new Database(':memory:', { allowExtension: true });

// 使用从文件名推导出的入口点进行加载。
database.loadExtension('./decimal.dylib');

// 当推导出的名称不匹配时覆盖入口点。
database.loadExtension('./base64.dylib', 'sqlite3_base64_init');

const { Database } = require('node:sqlite');
const database = new Database(':memory:', { allowExtension: true });

// 使用从文件名推导出的入口点进行加载。
database.loadExtension('./decimal.dylib');

// 当推导出的名称不匹配时覆盖入口点。
database.loadExtension('./base64.dylib', 'sqlite3_base64_init');

const { Database, constants } = require('node:sqlite');
const db = new Database(':memory:');

// 设置一个拒绝所有建表操作的授权器
db.setAuthorizer((actionCode) => {
  if (actionCode === constants.SQLITE_CREATE_TABLE) {
    return constants.SQLITE_DENY;
  }
  return constants.SQLITE_OK;
});

// 这将正常运行
using query = db.prepare('SELECT 1');
query.get();

// 由于授权被拒绝，这里会抛出错误
try {
  db.exec('CREATE TABLE blocked (id INTEGER)');
} catch (err) {
  console.log('操作被阻止：', err.message);
}

import { Database, constants } from 'node:sqlite';
const db = new Database(':memory:');

// 设置一个拒绝所有建表操作的授权器
db.setAuthorizer((actionCode) => {
  if (actionCode === constants.SQLITE_CREATE_TABLE) {
    return constants.SQLITE_DENY;
  }
  return constants.SQLITE_OK;
});

// 这将正常运行
using query = db.prepare('SELECT 1');
query.get();

// 由于授权被拒绝，这里会抛出错误
try {
  db.exec('CREATE TABLE blocked (id INTEGER)');
} catch (err) {
  console.log('操作被阻止：', err.message);
}

import { Database } from 'node:sqlite';

const db = new Database(':memory:');
db.exec('CREATE TABLE t(key INTEGER PRIMARY KEY, value TEXT)');
db.exec("INSERT INTO t VALUES (1, 'hello')");
const buffer = db.serialize();
console.log(buffer.length); // 打印数据库的字节长度

const { Database } = require('node:sqlite');

const db = new Database(':memory:');
db.exec('CREATE TABLE t(key INTEGER PRIMARY KEY, value TEXT)');
db.exec("INSERT INTO t VALUES (1, 'hello')");
const buffer = db.serialize();
console.log(buffer.length); // 打印数据库的字节长度

import { Database } from 'node:sqlite';

const original = new Database(':memory:');
original.exec('CREATE TABLE t(key INTEGER PRIMARY KEY, value TEXT)');
original.exec("INSERT INTO t VALUES (1, 'hello')");
const buffer = original.serialize();
original.close();

const clone = new Database(':memory:');
clone.deserialize(buffer);
using query = clone.prepare('SELECT value FROM t');
console.log(query.get());
// 输出：{ value: 'hello' }

const { Database } = require('node:sqlite');

const original = new Database(':memory:');
original.exec('CREATE TABLE t(key INTEGER PRIMARY KEY, value TEXT)');
original.exec("INSERT INTO t VALUES (1, 'hello')");
const buffer = original.serialize();
original.close();

const clone = new Database(':memory:');
clone.deserialize(buffer);
using query = clone.prepare('SELECT value FROM t');
console.log(query.get());
// 输出：{ value: 'hello' }

import { Database } from 'node:sqlite';

const db = new Database(':memory:');
const sql = db.createTagStore();

db.exec('CREATE TABLE users (id INT, name TEXT)');

// 使用 'run' 方法插入数据。
// 带标签字面量用于标识预编译语句。
sql.run`INSERT INTO users VALUES (1, 'Alice')`;
sql.run`INSERT INTO users VALUES (2, 'Bob')`;

// 使用 'get' 方法检索单行。
const name = 'Alice';
const user = sql.get`SELECT * FROM users WHERE name = ${name}`;
console.log(user); // { id: 1, name: 'Alice' }

// 使用 'all' 方法检索所有行。
const allUsers = sql.all`SELECT * FROM users ORDER BY id`;
console.log(allUsers);
// [
//   { id: 1, name: 'Alice' },
//   { id: 2, name: 'Bob' }
// ]

const { Database } = require('node:sqlite');

const db = new Database(':memory:');
const sql = db.createTagStore();

db.exec('CREATE TABLE users (id INT, name TEXT)');

// 使用 'run' 方法插入数据。
// 带标签字面量用于标识预编译语句。
sql.run`INSERT INTO users VALUES (1, 'Alice')`;
sql.run`INSERT INTO users VALUES (2, 'Bob')`;

// 使用 'get' 方法检索单行。
const name = 'Alice';
const user = sql.get`SELECT * FROM users WHERE name = ${name}`;
console.log(user); // { id: 1, name: 'Alice' }

// 使用 'all' 方法检索所有行。
const allUsers = sql.all`SELECT * FROM users ORDER BY id`;
console.log(allUsers);
// [
//   { id: 1, name: 'Alice' },
//   { id: 2, name: 'Bob' }
// ]

db.createModule('counter', {
  columns: [{ name: 'value', type: 'INTEGER' }],
  *rows() {
    yield [1];   // typeof(value) is 'real'
    yield [2n];  // typeof(value) is 'integer'
  },
});

const { Database } = require('node:sqlite');

const db = new Database(':memory:');

db.createModule('generate_series', {
  columns: [
    { name: 'value', type: 'INTEGER' },
    { name: 'start', type: 'INTEGER', hidden: true },
    { name: 'stop', type: 'INTEGER', hidden: true },
    { name: 'step', type: 'INTEGER', hidden: true },
  ],
  *rows(start, stop, step) {
    start ??= 0;
    stop ??= 10;
    step ??= 1;
    for (let i = start; i <= stop; i += step) {
      yield [i];
    }
  },
});

console.log(db.prepare('SELECT * FROM generate_series(1, 5, 1)').all());
// 打印： [ { value: 1 }, { value: 2 }, { value: 3 }, { value: 4 }, { value: 5 } ]

import { Database } from 'node:sqlite';

const db = new Database(':memory:');

db.createModule('generate_series', {
  columns: [
    { name: 'value', type: 'INTEGER' },
    { name: 'start', type: 'INTEGER', hidden: true },
    { name: 'stop', type: 'INTEGER', hidden: true },
    { name: 'step', type: 'INTEGER', hidden: true },
  ],
  *rows(start, stop, step) {
    start ??= 0;
    stop ??= 10;
    step ??= 1;
    for (let i = start; i <= stop; i += step) {
      yield [i];
    }
  },
});

console.log(db.prepare('SELECT * FROM generate_series(1, 5, 1)').all());
// 打印： [ { value: 1 }, { value: 2 }, { value: 3 }, { value: 4 }, { value: 5 } ]

import { Database } from 'node:sqlite';

const sourceDb = new Database(':memory:');
const targetDb = new Database(':memory:');

sourceDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');
targetDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');

const session = sourceDb.createSession();

using insert = sourceDb.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
insert.run(1, 'hello');
insert.run(2, 'world');

const changeset = session.changeset();
targetDb.applyChangeset(changeset);
// changeset 现在已被应用，targetDb 中包含与 sourceDb 相同的数据。

const { Database } = require('node:sqlite');

const sourceDb = new Database(':memory:');
const targetDb = new Database(':memory:');

sourceDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');
targetDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');

const session = sourceDb.createSession();

using insert = sourceDb.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
insert.run(1, 'hello');
insert.run(2, 'world');

const changeset = session.changeset();
targetDb.applyChangeset(changeset);
// changeset 现在已被应用，targetDb 中包含与 sourceDb 相同的数据。

const { backup, Database } = require('node:sqlite');

(async () => {
  const sourceDb = new Database('source.db');
  const totalPagesTransferred = await backup(sourceDb, 'backup.db', {
    rate: 1, // 一次复制一页。
    progress: ({ totalPages, remainingPages }) => {
      console.log('Backup in progress', { totalPages, remainingPages });
    },
  });

  console.log('Backup completed', totalPagesTransferred);
})();

import { backup, Database } from 'node:sqlite';

const sourceDb = new Database('source.db');
const totalPagesTransferred = await backup(sourceDb, 'backup.db', {
  rate: 1, // 一次复制一页。
  progress: ({ totalPages, remainingPages }) => {
    console.log('Backup in progress', { totalPages, remainingPages });
  },
});

console.log('Backup completed', totalPagesTransferred);