sqlite
- Version 5.1.1
- Published
- 98.5 kB
- No dependencies
- MIT license
Install
npm i sqlite
yarn add sqlite
pnpm add sqlite
Overview
SQLite client for Node.js applications with SQL-based migrations API written in Typescript
Index
Functions
Classes
Namespaces
Functions
function open
open: < Driver extends sqlite3.Database = sqlite3.Database, Stmt extends sqlite3.Statement = sqlite3.Statement>( config: ISqlite.Config) => Promise<Database>;
Opens a database for manipulation. Most users will call this to get started.
Classes
class Database
class Database< Driver extends sqlite3.Database = sqlite3.Database, Stmt extends sqlite3.Statement = sqlite3.Statement> {}
Promisified wrapper for the sqlite3#Database interface.
constructor
constructor(config: ISqlite.Config);
property config
config: ISqlite.Config;
property db
db: sqlite3.Database;
method all
all: <T = any[]>(sql: ISqlite.SqlType, ...params: any[]) => Promise<T>;
Runs the SQL query with the specified parameters. The parameters are the same as the Database#run function, with the following differences:
If the result set is empty, it will be an empty array, otherwise it will have an object for each result row which in turn contains the values of that row, like the Database#get function.
Note that it first retrieves all result rows and stores them in memory. For queries that have potentially large result sets, use the Database#each function to retrieve all rows or Database#prepare followed by multiple Statement#get calls to retrieve a previously unknown amount of rows.
Parameter sql
The SQL query to run.
Parameter params
When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters. This automatically sanitizes inputs.
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#databaseallsql-param--callback
method close
close: () => Promise<void>;
Closes the database.
method configure
configure: (option: ISqlite.ConfigureOption, value: any) => any;
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#databaseconfigureoption-value
method each
each: { <T = any>( sql: ISqlite.SqlType, callback: (err: any, row: T) => void ): Promise<number>; <T = any>( sql: ISqlite.SqlType, param1: any, callback: (err: any, row: T) => void ): Promise<number>; <T = any>( sql: ISqlite.SqlType, param1: any, param2: any, callback: (err: any, row: T) => void ): Promise<number>; <T = any>( sql: ISqlite.SqlType, param1: any, param2: any, param3: any, callback: (err: any, row: T) => void ): Promise<number>; <T = any>(sql: ISqlite.SqlType, ...params: any[]): Promise<number>;};
Runs the SQL query with the specified parameters and calls the callback once for each result row. The parameters are the same as the Database#run function, with the following differences:
If the result set succeeds but is empty, the callback is never called. In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set.
There is currently no way to abort execution!
The last parameter to each() *must* be a callback function.
Returns
Promise Number of rows returned
Example 1
await db.each('SELECT * FROM x WHERE y = ?', 'z', (err, row) => { // row contains the row data // each() resolves when there are no more rows to fetch })
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#databaseeachsql-param--callback-complete
method exec
exec: (sql: ISqlite.SqlType) => Promise<void>;
Runs all SQL queries in the supplied string. No result rows are retrieved. If a query fails, no subsequent statements will be executed (wrap it in a transaction if you want all or none to be executed).
Note: This function will only execute statements up to the first NULL byte. Comments are not allowed and will lead to runtime errors.
Parameter sql
The SQL query to run.
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#databaseexecsql-callback
method get
get: <T = any>(sql: ISqlite.SqlType, ...params: any[]) => Promise<T | undefined>;
Runs the SQL query with the specified parameters and resolves with with the first result row afterwards. If the result set is empty, returns undefined.
The property names correspond to the column names of the result set. It is impossible to access them by column index; the only supported way is by column name.
Parameter sql
The SQL query to run.
Parameter params
When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters. This automatically sanitizes inputs.
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#databasegetsql-param--callback
method getDatabaseInstance
getDatabaseInstance: () => Driver;
Returns the underlying sqlite3 Database instance
method loadExtension
loadExtension: (path: string) => Promise<void>;
Loads a compiled SQLite extension into the database connection object.
Parameter path
Filename of the extension to load
method migrate
migrate: (config?: MigrationParams) => Promise<void>;
Performs a database migration.
method on
on: (event: string, listener: any) => void;
Event handler when verbose mode is enabled.
See Also
https://github.com/mapbox/node-sqlite3/wiki/Debugging
method open
open: () => Promise<void>;
Opens the database
method parallelize
parallelize: () => void;
method prepare
prepare: (sql: ISqlite.SqlType, ...params: any[]) => Promise<Statement<Stmt>>;
Prepares the SQL statement and optionally binds the specified parameters. When bind parameters are supplied, they are bound to the prepared statement.
Parameter sql
The SQL query to run.
Parameter params
When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters. This automatically sanitizes inputs.
Returns
Promise Statement object
method run
run: ( sql: ISqlite.SqlType, ...params: any[]) => Promise<ISqlite.RunResult<Stmt>>;
Runs the SQL query with the specified parameters. It does not retrieve any result data. The function returns the Database object for which it was called to allow for function chaining.
Parameter sql
The SQL query to run.
Parameter params
When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters. This automatically sanitizes inputs.
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#databaserunsql-param--callback
method serialize
serialize: () => void;
The methods underneath requires creative work to implement. PRs / proposals accepted!
class Statement
class Statement<S extends sqlite.Statement = sqlite.Statement> {}
Promisified wrapper for the sqlite3#Statement interface.
constructor
constructor(stmt: sqlite.Statement);
property stmt
stmt: sqlite.Statement;
method all
all: <T = any[]>(...params: any[]) => Promise<T>;
Binds parameters, executes the statement and calls the callback with all result rows. The parameters are the same as the Statement#run function, with the following differences:
If the result set is empty, it will resolve to an empty array, otherwise it contains an object for each result row which in turn contains the values of that row. Like with Statement#run, the statement will not be finalized after executing this function.
Parameter params
When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters. This automatically sanitizes inputs.
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#databaseallsql-param--callback
method bind
bind: (...params: any[]) => Promise<void>;
Binds parameters to the prepared statement.
Binding parameters with this function completely resets the statement object and row cursor and removes all previously bound parameters, if any.
method each
each: { <T = any>(callback: (err: any, row: T) => void): Promise<number>; <T = any>( param1: any, callback: (err: any, row: T) => void ): Promise<number>; <T = any>( param1: any, param2: any, callback: (err: any, row: T) => void ): Promise<number>; <T = any>( param1: any, param2: any, param3: any, callback: (err: any, row: T) => void ): Promise<number>; <T = any>(...params: any[]): Promise<number>;};
Binds parameters, executes the statement and calls the callback for each result row.
If the result set succeeds but is empty, the callback is never called. In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set.
Like with Statement#run, the statement will not be finalized after executing this function.
There is currently no way to abort execution!
The last parameter to each() *must* be a callback function, where the first parameter will be the returned row.
Returns
Promise Number of rows returned
Example 1
await stmt.each('someParamValue', (err, row) => { // row contains the row data // each() resolves when there are no more rows to fetch })
See Also
https://github.com/mapbox/node-sqlite3/wiki/API#statementeachparam--callback-complete
method finalize
finalize: () => Promise<void>;
Finalizes the statement. This is typically optional, but if you experience long delays before the next query is executed, explicitly finalizing your statement might be necessary. This might be the case when you run an exclusive query (see section Control Flow). After the statement is finalized, all further function calls on that statement object will throw errors.
method get
get: <T = any>(...params: any[]) => Promise<T | undefined>;
Binds parameters, executes the statement and retrieves the first result row. The parameters are the same as the Statement#run function, with the following differences:
Using this method can leave the database locked, as the database awaits further calls to Statement#get to retrieve subsequent rows. To inform the database that you are finished retrieving rows, you should either finalize (with Statement#finalize) or reset (with Statement#reset) the statement.
Parameter params
When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters. This automatically sanitizes inputs.
method getStatementInstance
getStatementInstance: () => S;
Returns the underlying sqlite3 Statement instance
method reset
reset: () => Promise<void>;
Resets the row cursor of the statement and preserves the parameter bindings. Use this function to re-execute the same query with the same bindings.
method run
run: (...params: any[]) => Promise<ISqlite.RunResult>;
Binds parameters and executes the statement.
If you specify bind parameters, they will be bound to the statement before it is executed. Note that the bindings and the row cursor are reset when you specify even a single bind parameter.
The execution behavior is identical to the Database#run method with the difference that the statement will not be finalized after it is run. This means you can run it multiple times.
Parameter params
When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters. This automatically sanitizes inputs.
Namespaces
namespace IMigrate
namespace IMigrate {}
interface MigrationData
interface MigrationData {}
interface MigrationFile
interface MigrationFile {}
interface MigrationParams
interface MigrationParams {}
property force
force?: boolean;
If true, will force the migration API to rollback and re-apply the latest migration over again each time when Node.js app launches.
property migrations
migrations?: readonly MigrationData[];
Migration data read from migrations folder.
migrationsPath
will be ignored if this is provided.
property migrationsPath
migrationsPath?: string;
Path to the migrations folder. Default is
path.join(process.cwd(), 'migrations')
property table
table?: string;
Migrations table name. Default is 'migrations'
namespace ISqlite
namespace ISqlite {}
interface Config
interface Config {}
property driver
driver: any;
The database driver. Most will install
sqlite3
and use theDatabase
class from it. As long as the library you are using conforms to thesqlite3
API, you can use it as the driver.Example 1
import sqlite from 'sqlite3'const driver = sqlite.Database
property filename
filename: string;
Valid values are filenames, ":memory:" for an anonymous in-memory database and an empty string for an anonymous disk-based database. Anonymous databases are not persisted and when closing the database handle, their contents are lost.
property mode
mode?: number;
One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE and sqlite3.OPEN_CREATE. The default value is OPEN_READWRITE | OPEN_CREATE.
interface RunResult
interface RunResult<Stmt extends sqlite3.Statement = sqlite3.Statement> {}
property changes
changes?: number;
Number of rows changed.
Only contains valid information when the query was a successfully completed UPDATE or DELETE statement.
property lastID
lastID?: number;
Row id of the inserted row.
Only contains valid information when the query was a successfully completed INSERT statement.
property stmt
stmt: Statement<Stmt>;
Statement object.
It is not possible to run the statement again because it is automatically finalized after running for the first time. Any subsequent attempts to run the statement again will fail.
interface SQLStatement
interface SQLStatement {}
type ConfigureOption
type ConfigureOption = 'trace' | 'profile' | 'busyTimeout';
type SqlType
type SqlType = SQLStatement | string;
Allows for input of a normal SQL string or
sql-template-strings
object
Package Files (4)
Dependencies (0)
No dependencies.
Dev Dependencies (27)
- @theo.gravity/changelog-version
- @theo.gravity/version-bump
- @types/jest
- @types/node
- @types/sqlite3
- @typescript-eslint/eslint-plugin
- @typescript-eslint/parser
- eslint
- git-commit-stamper
- jest
- jest-cli
- jest-junit-reporter
- lint-staged
- pre-commit
- prettier-standard
- sql-template-strings
- sqlite3
- sqlite3-offline-next
- standardx
- toc-md-alt
- ts-jest
- ts-node
- ts-node-dev
- typedoc
- typedoc-plugin-markdown
- typescript
- version-bump-plugin-git
Peer Dependencies (0)
No peer dependencies.
Badge
To add a badge like this oneto your package's README, use the codes available below.
You may also use Shields.io to create a custom badge linking to https://www.jsdocs.io/package/sqlite
.
- Markdown[![jsDocs.io](https://img.shields.io/badge/jsDocs.io-reference-blue)](https://www.jsdocs.io/package/sqlite)
- HTML<a href="https://www.jsdocs.io/package/sqlite"><img src="https://img.shields.io/badge/jsDocs.io-reference-blue" alt="jsDocs.io"></a>
- Updated .
Package analyzed in 3251 ms. - Missing or incorrect documentation? Open an issue for this package.