sqlite

SQLite is a lightweight disk/memory based database that does not require a server or third party applications. It allows users to work with the database using its sigtly variant version of the Structured Query Lanaguage. For this reason, it is a perfect fit for embeded and highly portable database applications. It can be used as a prototyping database from which an application can be migrated to a more robust database system such as Oracle.

This module provides an interface to working with SQLite databases and is compartible with SQLite3.

The open() function is the entry point to this module and must be called to create a valid SQLite3 connection to a valid database. The following example shows how to create a connection to a database.

import sqlite
var con = sqlite.open('test.db')

The above code opens a connection to the database file test.db in the current directory. Any valid file path is acceptable here. You can also open a kind of connection a database that is stored completely in virtual memory, allowing you to use SQLite like an in-memory database (albeit relational). The following example shows how to open a that make SQLite behave like an in-memory database.

import sqlite
var con = sqlite.open()

Once a connection has been established, you can use the connection to run all sorts of queries. For example, you can run queries that do not return a result set (for example, a CREATE TABLE query) using the exec() function as shown in the example below.

# Create a new table
con.exec('CREATE TABLE users (id integer primary key, name text, gender text)')

# Insert data into the table
# This isn't exactly the most optimal way to do it but you get the idea. Right?
con.exec('INSERT INTO users (id, name, gender) VALUES (0, "James", "Male")')
con.exec('INSERT INTO users (id, name, gender) VALUES (1, "Lilith", "Female")')
con.exec('INSERT INTO users (id, name, gender) VALUES (2, "Candy", "Non-Binary")')

This function will return true if the query was successful or false if it failed.

You can retrieve the ID of the last insert query in the above command for example using the last_insert_id() function. For example,

con.last_insert_id()
# 2

On the other hand, there are two ways to run queries that return a dataset.

Using the `query()` method.

This function returns a SQLite3Cursor that allows you iterate through the dataset and do as you wish with them. For example,

var result = con.query('SELECT * FROM users')

There are two ways to loop through this result set. The first way is to use the has_next() function. This function automatically moves the cursor to the next datarow in the result set and return true or false when there are no more rows in the result set.

while result.has_next() {
  var name = result.get(1)
  var gender = result.get(2)
  echo 'Name = ${name}, Gender = ${gender}'
}

# ---- result ----------
Name = James, Gender = Male
Name = Lilith, Gender = Female
Name = Candy, Gender = Non-Binary

Once has_next() returns true, you’ll be able to get the value of the different columns in the result run using the get() method of the SQLite3Cursor by passing their ordinal position as an argument to the function.

Another way to get the result entries in a SQLite3Cursor is obviously using the for loop as the class implements the iterable decorators (as indicated in the class documentation below). For example,

for row in result {
  echo 'Name = ${row.name}, Gender = ${row.gender}'
}
# ---- result ----------
Name = James, Gender = Male
Name = Lilith, Gender = Female
Name = Candy, Gender = Non-Binary

Much shorter right? Care should be taken though as a few of our tests have shown that for result sets with a large number of columns, the first option (using while) might be slightly faster for performace critical applications. However, no realworld dataset has been testd.

Using the `fetch()` method.

The second way to run queries that return a result set is to use the fetch() function. Unlike the query() function that allows you to lazily access the resultset of a SQL query, the fetch() function retrieves all results into a dictionary as a flat object. This function is useful for returning all the data in the resultset.

For example,

con.fetch('SELECT * FROM users')

# ---- result ---------
[
  {
    id: 0, 
    name: James, 
    gender: Male
  }, 
  {
    id: 1, 
    name: Lilith, 
    gender: Female
  }, 
  {
    id: 2, 
    name: Candy, 
    gender: Non-Binary
  }
]

Parameterized Queries

This module provides support for parameterized queries and as such offer protection against SQL injection. An example of a parameterized query is show below.

%> con.fetch('SELECT * FROM users WHERE name = ?', [ 'James' ])

# ---- result ---------
[
  {
    id: 0, 
    name: James, 
    gender: Male
  }
]

You can also used a dictionary as an argument instead of a list for named parameterized queries. When you do this, the order or count of the parameters will not matter. Instead, parameters will be matched based on their value in the dictionary. For example,

con.fetch(
  'select * from users where name = :name and id = :id', 
  {
    ':id': 0, 
    ':name': 'James',
  }
)

# ---- result ---------
[
  {
    id: 0, 
    name: James, 
    gender: Male
  }
]

It is also a very good practice to always close your connection once done with it. This is really simple.

con.close()

See below for more info

Functions

sqlite.open([path: string]): Returns an handle to a sqlite3 database. If path is not given, it will create an in-memory sqlite database.
return SQLite3

Classes

class SQLite3

SQLite3 management class

class SQLite3 properties

SQLite3.path: The path to the SQLite3 file
default = :memory:

class SQLite3 methods

SQLite3(path: string)

the database doesn’t need to exist.

constructor

open()

Opens the handle to a database file

close()

Closes the handle to the database and return true if successfully closed or false otherwise.

return boolean

exec(query: string [, params: list | dict])

Executes a query string as is and returns true if the query was executed or false otherwise.

this method does not return a query result

this method takes optional params like query() (see below).

return boolean

throws SQLiteException if an error occured

last_insert_id()

The id of the last insert operation.

Returns:

-1 if the last insert failed,
0 if no insert statement has been executed or
A number greater than 0 if it succeeded

returns number

throws SQLiteException if database is not opened

query(sql: string [, params: list | dict])

Executes and sql query and returns the result of the execution.

return SQLite3Cursor

throws SQLiteException if an error occured.

Pass a list as params if you have unnamed parameterized queries.

For example,

sqlite.query('SELECT * FROM users WHERE id = ? AND name = ?', [3, 'James'])

Or pass a dictionary as params if you use named paramters

For Example,

sqlite.query(
  'SELECT * FROM user WHERE id = :id AND name = :name', 
  {':id': 1, ':name': 'James'}
)

fetch(sql: string [, params: list | dict])

Runs an SQL query and returns the result as a list of dictionaries.

if the result is empty or the query is not a SELECT, it returns an empty list

class SQLiteException < Exception

General Exception for SQLite

class SQLiteException methods

SQLiteException(message: string): constructor

class SQLite3Cursor

A cursor for navigation through sql results @iterable

class SQLite3Cursor properties

SQLite3Cursor.connection

The SQLite3 connection that owns this cursor

readonly

SQLite3Cursor.row_count

The number of rows in the cursor

readonly

SQLite3Cursor.modified_count

This value hold the number of rows modified, inserted or deleted by the the query that owns this cursor provided the query is one of INSERT, UPDATE or DELETE statement. Executing any other type of SQL statement does not change this value from 0.

Only changes made directly by the INSERT, UPDATE or DELETE statement are considered

auxiliary changes caused by triggers, foreign key actions or REPLACE constraint resolution are not counted.

Changes to a view that are intercepted by INSTEAD OF triggers are not counted. The value returned by modified_count immediately after an INSERT, UPDATE or DELETE statement run on a view is always zero. Only changes made to real tables are counted.

readonly

If a separate thread makes changes on the same database connection at the exact time the original query was also making a change, the result of this value will become undependable.

SQLite3Cursor.columns

A list of the columns available in the result set.

readonly

class SQLite3Cursor methods

SQLite3Cursor(db: SQLite3, cursor: pointer): constructor

SQLite3Cursor should NEVER be maually instantiated.

close()

Closes the cursor and prevents further reading

return bool

has_next()

Returns true if there are more rows in the result set not yet retrieved, otherwise it returns false.

return boolean

get(index: number | string)

Returns the value of the column matching the index in the current result set.

if index is a number, it returns the value in the column at the given index.

that index must be lower than columns.length() in this case.

if index is a string, it returns the value in the column with the given name.

throws SQLiteException if no matching column can be found.

sqlite

Using the query() method.

Using the fetch() method.

Parameterized Queries

Functions

Classes

class SQLite3

class SQLite3 properties

class SQLite3 methods

class SQLiteException < Exception

class SQLiteException methods

class SQLite3Cursor

class SQLite3Cursor properties

class SQLite3Cursor methods

Using the `query()` method.

Using the `fetch()` method.