Sqlite
Use SQLite natively in Gren.
An opened SQLite database. This value is required to complete operations on that database.
Where a database is located when opened.
Memoryopens the database in memory. When your program stops, any data written is lost.Fileopens the database in a givenPathin the file system.
When opening the database, you can define a set of restrictions that apply for all queries and statements executed.
readOnly, whenTrue, opens the database in read-only mode. All attempted modifications will fail.allowExtension, whenTrue, enables the loadExtension SQL function and the loadExtension() method.enableForeignKeyConstraints, whenTrue, 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 usingPRAGMA foreign_keys.timeoutis 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. 0 or negative number means no timeout.
A sensible set of default Options for a SQLite database.
{ readOnly = False
, enableForeignKeyConstraints = True
, allowExtension = False
, timeout = 5000
}
Open a database at the given Location if it already exists. If a
database does not exist at the given location, this function creates a database at
that location.
This is the only way to obtain the Database type that is required for
nearly all functionality in the Sqlite family of modules.
Query
A SQL query to get some data from a database. An example of how this can be used is below:
{ query = "SELECT name, email, age FROM users WHERE id = :user_id
, parameters =
[ Sqlite.Encode.Row.column "user_id" <| Encode.string "123"
]
, rowDecoder =
Sqlite.Decode.Row.column "name" Decode.string <| \name ->
Sqlite.Decode.Row.column "email" Decode.string <| \email ->
Sqlite.Decode.Row.succeed { name = name, email = email }
}
For more information on encoding parameters, visit Sqlite.Encode.Row
module documentation. For more information on decoding the returned rows, visit
Sqlite.Decode.Row module documentation.
Get a single value returned by a Query. This will fail if
either no results or more than one result is returned.
Get either Nothing (no result) or a single value returned by a Query.
Accumulate a new value by iterating over all results from a given
Query, one row at a time.
Statement
SQL that does not return any data from the database. An example of how this can be used is below:
{ statement = "INSERT INTO people (name, role) VALUES (:name, :role)"
, parameters =
[ Sqlite.Encode.Row.column "name" <| Encode.string p.name
, Sqlite.Encode.Row.column "role" <| Encode.string p.role
]
}
For more information on encoding parameters, visit Sqlite.Encode.Row
module documentation.
A summary of how an execution changed the database.
changesis how many rows were inserted, updated, or removed.lastInsertRowIdis the ID of the last row inserted. This could be be negative if no row was inserted.
Execute some given SQL.
Execute many statements sequentially.
Execute a single Statement for many sets of encoded parameters. This is
very useful for inserting lots of rows into the database.
Sqlite.executeForEach db
{ statement = "INSERT INTO people (name) VALUES (:name)"
, parameters = encoder
}
[ { name = "Joey" }
, { name = "Robin" }
, { name = "Justin" }
]
Execute some arbitrary SQL. This is most helpful when you need to run more than one
SQL statement, as execute, executeForAll, and
executeForEach only allow a single SQL statement.
Backup
A backup configuration for a database. This value can be created by running the
backup function.
Produce a backup configuration for the given Database.
Configure the number of pages that are saved in each batch of the backup process. By default, the backup page rate is 100.
Run a database Backup after it's been configured. The database can still
be used safely during the backup process. However, if there are any mutations performed
on the database outside of your Gren program, the backup process will restart.
Errors
All possible errors that can happen when running functions in this module. Most of these come from the SQLite error documentation.
While any of these errors can happen when interacting with a SQLite database, some more commmon types of errors are below:
Transform a given Error into a descriptive String.
Each error string for known SQLite errors contain the error code found at https://sqlite.org/rescode.html.More details on the errors can be found by their code on that page.
Utilities
Run a Task (like statements or queries) in a
transaction. This is equivalant to running some SQL like this:
BEGIN TRANSACTION
-- The given `Task` runs here
COMMIT
If the given Task has an error, COMMIT will instead be ROLLBACK, reverting
any changes within the transaction.