sqlite

Enable the extension to query the local SQLite database.

Functions

sqlite.sql

sqlite.sql(sql: string): Statement

Execute sql string.

sqlite.exec

sqlite.exec(sql: string): Promise<void>

Execute sql string. Unlike the sqlite.sql this can execute strings that contain multiple SQL statements. This method performs worse and less safe.

Example

import { _extension } from '@aldot/extension';
import migration from './migrate-schema.sql?raw';

await _extension.sqlite.exec(migration);

sqlite.open

sqlite.exec(options: OpenOptions): Database

Open existing SQlite database.

Example

import { _extension } from '@aldot/extension';

const db = _extension.sqlite.open({ path: '/path/to/db-file' });
console.log(await db.sql('SELECT * from table').all());

Classes

sqlite.Database

An object representing a single SQlite database.

class Database {
  close(): Promise<void>;
  execute(sql: string): void;
  sql<T = unknown>(sql: string): Statement<T>;
}

Property	Type	Description
execute	(sql: string) => void	Same as the sqlite.exec method
sql	<T = unknown>(sql: string): Statement<T>	Same as the sqlite.sql method
close	() => Promise<void>	Close the database connection

sqlite.Statement

An object representing a single SQL statement.

interface Statement<P = unknown> {
  run(...params: unknown[]): Promise<DBRunResult>;
  get<T = P>(...params: unknown[]): Promise<T>;
  all<T = P>(...params: unknown[]): Promise<T[]>;
}

sqlite.Statement.run

sqlite.Statement.run(…params: unknown[]): Promise<DBRunResult>

Run the sql string and return the changes info it made.

Example

import { _extension } from '@aldot/extension';

_extension.sqlite.sql('INSERT INTO users (name, age) VALUES (?, ?)').run(
  ['John Doe', 20]
);

sqlite.Statement.get

sqlite.Statement.get<T = unknown>(…params: unknown[]): Promise<T>

Return the first row retrieved by the query.

Example

import { _extension } from '@aldot/extension';

interface User {
  age: number;
  name: string;
}

export default async function Command() {
  const user = await _extension.sqlite
    .sql('SELECT name, age FROM users WHERE name = ?')
    .get<User>('John Doe');

  console.log(user.name);
}

sqlite.Statement.all

sqlite.Statement.all<T = unknown>(…params: unknown[]): Promise<T[]>

Return all the match rows.

Example

import { _extension } from '@aldot/extension';

export default async function Command() {
  const users = await _extension.sqlite
    .sql('SELECT name, age FROM users WHERE name = ?')
    .all('John Doe');

  console.log(users, users.length);
}

Types

sqlite.DBRunResult

Result when running the run statement.

interface DBRunResult {
  changes: number;
  lastInsertRowid: number;
}

Property	Type	Description
changes	number	The total number of rows that were inserted, updated, or deleted by this operation
lastInsertRowid	number	The rowid of the last row inserted into the database

sqlite.OpenOptions

Options for the sqlite.open method;

interface OpenOptions {
  path: string;
}

Property	Type	Description
path	string	The SQlite DB file path