To make use of the Sqlite module an import is required.
Note: Unlike SQLite and most other libraries, foreign keys are enabled by default when a connection is opened.
This opens a connection to a SQLite database. Returns a Result type and on success wraps an abstract SQLite type.
Note: You can pass “:memory:” to open the SQLite database in memory rather than a file.
execute method is ran on the abstract that is returned from
.connect rather than the
Sqlite module, hence the lower case
execute method executes an SQL query and can return one of 3 values.
A Result type is returned, the value being wrapped depends on the query: If the query is a type that does not return data (such as UPDATE / DELETE), then
nil is wrapped on successful queries. If the query is a SELECT statement then a list is wrapped on successful queries (empty list for a select query that returns no rows).
sqlite.execute("UPDATE mytable SET mycolumn = 10"); // <Result Suc> var query = sqlite.execute("UPDATE unknown_table SET mycolumn = 10"); // <Result Err> print(query.unwrapError()); // no such table: unknown_table
Passing user input to a query should always be done through the method of binding parameters and never through string interpolation, otherwise your application will be at risk to SQL injection. Binding values to placeholders is incredibly simple.
sqlite.execute("SELECT * FROM mytable WHERE mycolumn = ?", [ "test" ]);
? will take the value of the item in the list. If there are multiple placeholders then it is done by index, for example.
sqlite.execute("SELECT * FROM mytable WHERE mycolumn = ? AND mycolumn1 = ?", [ "test", "next value" ]);
? matches with the first value in the list, and the second
? with the second value in the list, and so on.
Closes the database.