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

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 {}

                  property down

                  down: string;

                    property id

                    id: number;

                      property name

                      name: string;

                        property up

                        up: string;

                          interface MigrationFile

                          interface MigrationFile {}

                            property filename

                            filename: string;

                              property id

                              id: number;

                                property name

                                name: string;

                                  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 the Database class from it. As long as the library you are using conforms to the sqlite3 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 SqlObj

                                          interface SqlObj {}

                                            property params

                                            params?: any[];

                                              property sql

                                              sql: string;

                                                interface SQLStatement

                                                interface SQLStatement {}

                                                  property sql

                                                  sql: string;

                                                    property values

                                                    values?: any[];

                                                      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)

                                                        Peer Dependencies (0)

                                                        No peer dependencies.

                                                        Badge

                                                        To add a badge like this onejsDocs.io badgeto 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>