Node.SQLite

data DB

An opaque handle to an open SQLite database.

data SQLiteError

Errors that can occur when running a query.

• SQLite'Exception: an error thrown by the underlying SQLite engine.
• SQLite'DecodeError: the rows returned by SQLite could not be decoded into the expected PureScript type.

Constructors

• SQLite'Exception Error
• SQLite'DecodeError Error

Instances

• Show SQLiteError

type Options = { allowExtension :: Boolean, allowUnknownNamedParameters :: Boolean, defensive :: Boolean, enableDoubleQuotedStringLiterals :: Boolean, enableForeignKeyConstraints :: Boolean, readOnly :: Boolean, timeout :: Int }

Options for open.

• readOnly: If true, the database is opened in read-only mode. If the database does not exist, opening it will fail.
• enableForeignKeyConstraints: If true, foreign key constraints are enabled. This is recommended but can be disabled for compatibility with legacy database schemas. The enforcement of foreign key constraints can be enabled and disabled after opening the database using PRAGMA foreign_keys.
• enableDoubleQuotedStringLiterals: If true, SQLite will accept double-quoted string literals. This is not recommended but can be enabled for compatibility with legacy database schemas.
• allowExtension: If true, the loadExtension SQL function and the loadExtension() method are enabled.
• timeout The busy timeout in milliseconds. This is the maximum amount of time that SQLite will wait for a database lock to be released before returning an error.
• allowUnknownNamedParameters: If true, unknown named parameters are ignored when binding. If false, an exception is thrown for unknown named parameters.
• defensive: If true, enables the defensive flag. When the defensive flag is enabled, language features that allow ordinary SQL to deliberately corrupt the database file are disabled.

type RunResult = { changes :: Int, lastInsertRowid :: Number }

Result returned by run.

• changes: number of rows affected by the statement.
• lastInsertRowid: rowid of the last INSERT on this connection; unchanged (not reset) for non-INSERT statements.

data Statement :: Row Type -> Row Type -> Typedata Statement t0 t1

An opaque handle to a compiled prepared statement.

all :: forall r rl m inputR. RowToList r rl => Decode (Array (Record r)) => Encode (Record inputR) => MonadEffect m => Statement inputR r -> Record inputR -> ExceptT SQLiteError m (Array (Record r))

all runs a SELECT (or any row-returning statement, e.g. a RETURNING clause) and returns every matching row as an array. Use it when you expect zero or more rows.

close :: forall m. MonadEffect m => DB -> ExceptT SQLiteError m Unit

Closes the database connection.

command :: forall m iR. Encode (Record iR) => MonadEffect m => DB -> String -> Record iR -> ExceptT SQLiteError m RunResult

One-shot run: prepares sql and runs it once, for statements that return no rows (INSERT, UPDATE, DELETE, DDL). The parameter row is taken from the record you pass:

command db "DELETE FROM fruits WHERE quantity < :n;" { n: 160 }

Unlike exec, it binds parameters and reports { changes, lastInsertRowid }, and it runs one statement rather than a script.

defaultOptions :: Options

Default options for open. Foreign keys are enabled and the defensive flag is on; everything else is disabled and the busy timeout is 0.

exec :: forall m. MonadEffect m => DB -> String -> ExceptT SQLiteError m Unit

exec takes a raw SQL string and executes it directly (no preparation, no parameters), and returns nothing.

Good for multi-statement scripts like schema migrations.

get :: forall r rl m iR. RowToList r rl => Decode (Maybe (Record r)) => Encode (Record iR) => MonadEffect m => Statement iR r -> Record iR -> ExceptT SQLiteError m (Maybe (Record r))

get runs a SELECT (or any row-returning statement) and returns the first matching row, or Nothing if no rows matched. Use it for lookups that yield at most one row (e.g. a primary-key or LIMIT 1 query).

open :: forall m. MonadEffect m => String -> Options -> ExceptT SQLiteError m DB

Opens a database file at the given path with the provided options.

openInMemory :: forall m. MonadEffect m => Options -> ExceptT SQLiteError m DB

Opens a temporary in-memory database. The database is gone when closed.

prepare :: forall @inputR @outputR m. MonadEffect m => DB -> String -> ExceptT SQLiteError m (Statement inputR outputR)

Compiles a SQL string into a prepared statement. The two visible type arguments are the input-parameter row and the result-column row, in that order:

stmt <- prepare @(id :: Int) @(email :: String) db "SELECT email FROM users WHERE id = :id;"

Use () for no parameters or no columns, and @_ to leave one to inference where the context fixes it.

Statements are always tied to a specific DB connection.

query :: forall @r rl m iR. RowToList r rl => Decode (Array (Record r)) => Encode (Record iR) => MonadEffect m => DB -> String -> Record iR -> ExceptT SQLiteError m (Array (Record r))

One-shot all: prepares sql and runs it once, returning every row. The parameter row is taken from the record you pass; the result row is the one visible type argument:

query @(name :: String) db "SELECT name FROM fruits WHERE quantity > :n;" { n: 160 }

The statement is compiled on every call. For a statement that runs many times, prepare it once and use all instead.

queryFirst :: forall @r rl m iR. RowToList r rl => Decode (Maybe (Record r)) => Encode (Record iR) => MonadEffect m => DB -> String -> Record iR -> ExceptT SQLiteError m (Maybe (Record r))

One-shot get: like query, but only the first row, or Nothing if there is none.

queryOne :: forall @r rl m iR. RowToList r rl => Decode (Maybe (Record r)) => Encode (Record iR) => MonadEffect m => DB -> String -> Record iR -> ExceptT SQLiteError m (Record r)

queryFirst for statements that always yield a row (SELECT count(*) ..., INSERT ... RETURNING ...), so there is no Maybe to unwrap. A missing row is reported on the same error channel as an engine or decoding failure, as a SQLite'DecodeError: the result did not have the shape the caller declared. The caller writes no branch for a case that cannot happen. Only the first row is taken; a second one is not reported.

run :: forall (iR :: Row Type) m. Encode (Record iR) => MonadEffect m => Statement iR () -> Record iR -> ExceptT SQLiteError m RunResult

runs a prepared statement.

It does not return rows, used for INSERT, UPDATE and DELETE.