libraryDocs / sqlite

sqlite

librarysharedsource

The local SQLite library wrapper.

Classes & types

Functions

`sqlite.cast`	Converts the string values of a result row to the specified types, in-place.
`sqlite.escape`	Converts a string to a safe string for use in an SQL query.
`sqlite.getLastError`	Returns the last error message from the last query.
`sqlite.isExists`	Checks if a schema object ( table, index, view or trigger ) exists in the database.
`sqlite.query`	Executes a SQL query with parameters.
`sqlite.queryFirst`	Executes a SQL query and returns the first row.
`sqlite.queryRow`	Executes a SQL query and returns a specific row.
`sqlite.queryValue`	Executes a SQL query and returns the first value of the first row.
`sqlite.rawQuery`	Executes a raw SQL query.
`sqlite.transaction`	Executes a transaction of SQL queries in one block.

#sqlite.cast

sharedsource

function sqlite.cast(row: sqlite.QueryRow | nil, column_types: table<string, sqlite.ColumnType>): sqlite.QueryRow | nil

Converts the string values of a result row to the specified types, in-place.

null values are kept as is, except for "blob" columns, since hex( NULL ) is an empty string.

Parameters

Name	Type	Description
`row`	`sqlite.QueryRow \| nil`	The row to convert.
`column_types`	`table<string, sqlite.ColumnType>`	The column names mapped to their types.

Returns

Type	Name	Description
`sqlite.QueryRow \| nil`	`row`	The same row.

#sqlite.escape

sharednodiscardsource

function sqlite.escape(str: string | null | nil): string

Converts a string to a safe string for use in an SQL query.

Parameters

Name	Type	Description
`str`	`string \| null \| nil`	The string to convert.

Returns

Type	Name	Description
`string`	`str`	The safe string.

#sqlite.getLastError

sharednodiscardsource

function sqlite.getLastError(): string | nil

Returns the last error message from the last query.

Returns

Type	Name	Description
`string \| nil`	`err_msg`	The last error message.

#sqlite.isExists

sharednodiscardsource

function sqlite.isExists(kind_name: string, kind_type?: sqlite.ObjectType): boolean

Checks if a schema object ( table, index, view or trigger ) exists in the database.

Names are compared case-insensitively, like SQLite identifiers.

Only the main database is checked, objects created with CREATE TEMP are not found.

Indexes that SQLite creates implicitly for UNIQUE / PRIMARY KEY constraints are named sqlite_autoindex_<table>_<n> and count as indexes.

Both arguments are escaped, so any string is safe to pass. A nil or empty kind_name never matches, and an unknown kind_type simply returns false.

lua

sqlite.isExists( "dreamwork.storage" )           -- any type
sqlite.isExists( "dreamwork.storage", "table" )  -- tables only
sqlite.isExists( "my_index", "index" )           -- indexes only

Parameters

Name	Type	Description
`kind_name`	`string`	The name of the schema object.
`kind_type`optional	`sqlite.ObjectType`	The type of the schema object, any type if `nil`.

Returns

Type	Name	Description
`boolean`	`is_exists`	`true` if a schema object with the given name ( and type ) exists, `false` otherwise.

#sqlite.query

sharedsource

function sqlite.query(query_str: sqlite.Query, ...: sqlite.QueryValue): sqlite.QueryRow[] | nil

Executes a SQL query with parameters.

NULL values in the result are replaced with null.

sql.Query returns SQL NULL as the string "NULL", so a text value that is exactly "NULL" is read as null too, store such data as a blob.

Parameters

Name	Type	Description
`query_str`	`sqlite.Query`	The SQL query to execute.
`...`	`sqlite.QueryValue`	The parameters to use in the query.

Returns

Type	Name	Description
`sqlite.QueryRow[] \| nil`	`result`	The result of the query.

#sqlite.queryFirst

sharednodiscardsource

function sqlite.queryFirst(str: sqlite.Query, ...: sqlite.QueryValue): sqlite.QueryRow | nil

Executes a SQL query and returns the first row.

Parameters

Name	Type	Description
`str`	`sqlite.Query`	The SQL query to execute.
`...`	`sqlite.QueryValue`	The parameters to use in the query.

Returns

Type	Name	Description
`sqlite.QueryRow \| nil`	`result`	The first row of the result.

#sqlite.queryRow

sharednodiscardsource

function sqlite.queryRow(str: sqlite.Query, row?: integer, ...: sqlite.QueryValue): sqlite.QueryRow | nil

Executes a SQL query and returns a specific row.

Parameters

Name	Type	Description
`str`	`sqlite.Query`	The SQL query to execute.
`row`optional	`integer`	The row to return.
`...`	`sqlite.QueryValue`	The parameters to use in the query.

Returns

Type	Name	Description
`sqlite.QueryRow \| nil`	`result`	The selected row of the result.

#sqlite.queryValue

sharednodiscardsource

function sqlite.queryValue(str: sqlite.Query, ...: sqlite.QueryValue): sqlite.QueryValue | nil

Executes a SQL query and returns the first value of the first row.

Parameters

Name	Type	Description
`str`	`sqlite.Query`	The SQL query to execute.
`...`	`sqlite.QueryValue`	The parameters to use in the query.

Returns

Type	Name	Description
`sqlite.QueryValue \| nil`	`value`	The first value of the first row of the result.

#sqlite.rawQuery

sharedsource

function sqlite.rawQuery(query_str: sqlite.Query): sqlite.QueryRow[] | nil

Executes a raw SQL query.

Parameters

Name	Type	Description
`query_str`	`sqlite.Query`	The SQL query to execute.

Returns

Type	Name	Description
`sqlite.QueryRow[] \| nil`	`result`	The result of the query.

#sqlite.transaction

sharedsource

function sqlite.transaction(fn: fun( query_fn: ( fun( str: sqlite.Query, ...: sqlite.QueryValue ): ( sqlite.QueryRow[] | nil ) ) ): ( V | nil )): V

Executes a transaction of SQL queries in one block.

Parameters

Name	Type	Description
`fn`	`fun( query_fn: ( fun( str: sqlite.Query, ...: sqlite.QueryValue ): ( sqlite.QueryRow[] \| nil ) ) ): ( V \| nil )`	The function to execute all SQL queries in one transaction.

Returns

Type	Name	Description
`V`	`value`	The result of function execution.