Source   Edit  

Exa provides ergonomic database connectors for Nim, currently it supports only the SQLite database engine.

The aim of Exa is not to provide every single feature possible, nor for it to be an ORM of some sorts, instead the aim of Exa is to provide the means to work comfortably with modern database engines in Nim.

This module can be imported as exa/sqlite.

import exa/sqlite

Opening connections

This module provides the openSqlite procedure for opening connections to sqlite databases. The only required parameter is the path to the database.

import exa/sqlite

let db = openSqlite("path/to/your/sqlite.db")

Note: Don't make connections immutable with let, or else you'll have a bad time!

SQLite supports in-memory database where the data is stored in program memory, rather than a real file, this can be useful for testing.

let db = openSqlite(":memory:") # Opening an in-memory database.

Just to reiterate, you should not be using in-memory databases for any actual data storage, since the database will be wiped out as soon as the connection is closed.

Connection profiles

When opening a database connection using the openSqlite procedure, you can supply a "connection profile" which lets Exa enable a bunch of default settings and run optimizations suited for the app you're developing. You can think of this as "editions" for SQLite.

let db = openSqlite(
  "path/to/database/file/",
  profile = DbConnProfile.Server # Using the "Server" profile
)

The connection profile is supplied with the DbConnProfile enum, there currently exists 3 different profiles you can choose from.

1. DbConnProfile.Default
2. DbConnProfile.Minimal (This is the default profile)
3. DbConnProfile.Server

The Default profile represents plain SQLite defaults, it does nothing at all.

The Minimal profile enforces foreign keys and enables the Write-Ahead Log, this has the effect of not only enhancing database performance but also enabling better concurrency. This profile is what most people will want, since it makes SQLite behave like a modern database engine.

The Server profile is meant for long-running backend applications, this profile enables foreign keys and the Write-Ahead Log, but it also sets SQLite's synchronization mode to "normal", which helps improve performance slightly. The third profile also runs some optimizations that are useful for long-running process, as per SQLite's documentation.

The profile you ought to select will depend on your use-case, you can always read the SQLite documentation behind the profile options to figure out if this is a good fit for you or not, or you can just choose to use SQLite's defaults with the Default profile.

Opening read-only connections

the openSqlite procedure takes in an optional boolean argument named readOnly, if this is set to true, then Exa will instruct SQLite that this connection is meant to be a read-only database connection.

var db = openSqlite("test.db", readOnly = true)

You can read more here.

Setting a busy timeout

Whenever Exa encounters a SQLITE_BUSY error, it will raise a DbBusyError exception, which you can catch and handle in your own application.

By default, Exa will set a busy timeout amount of 3 seconds. This means that if a query fails to run due to another connection or process using it beforehand, then SQLite will wait for 3 seconds and try again. If the query still fails to run after that, a DbBusyError exception is raised.

You can customize the amount of time that SQLite will wait using the busyTimeout argument on the openSqlite procedure, this has to be given in milliseconds.

var db = openSqlite("test.db", busyTimeout = -1) # Don't set busy timeout

var db = openSqlite("test.db", busyTimeout = 5000) Set it to 5 seconds

You can also set the busy timeout amount by calling setBusyTimeout whenever you want.

var db = openSqlite(":memory:")

db.setBusyTimeout(5000) # Set to 5 seconds

db.setBusyTimeout(-1) # Or disable completely.

You can read more here.

Running SQL operations

This section covers how to run SQL operations that don't return any data.

Running a single operation

Exa's SQLite connector provides the exec procedure for when you want to run a single query without actually fetching any rows.

db.exec("INSERT INTO users VALUES (?, ?);", 10, "John Doe")

You might use this to update already-existing data, delete rows or insert new rows into a table.

You'll notice right away that we can substitute values directly into the query by using question marks and adding in our values as a parameter to the exec procedure. More about this will be explained later on in the "parameter substitution" section.

Running multiple SQL operations

It's quite common to store a "schema file", which contains commands for initializing tables and data, the problem though is that exec does not let you run multiple queries for security's sake. Instead, you might opt for the execScript procedure which can run multiple SQL queries, at the cost of not letting you substitute parameters directly.

db.execScript(readFile("schema.sql"))

# Or, alternatively

db.execScript(
 """
CREATE TABLE IF NOT EXISTS users (
   id INTEGER PRIMARY KEY,
   name TEXT
);
   
   ...
 """
)

execScript should be used for running bulk SQL commands without the need for parameter substition, there currently exists no mechanism to run multiple SQL commands with parameter substitution.

Database transactions

Exa provides the transaction template, this is a bit of sugar syntax which lets you create database transactions, and easily commit if they're successful. (Or rollback in the case of an exception)

db.transaction:
  let row = db.one("SELECT name FROM users;")
  assert row[0].strVal == "John"
  
  # This operation will cause the transaction to rollback
  # because it is illegal. (Not valid)
  db.exec("RANDOM STRING HERE")

Parameter substitution

Parameter substitution lets you embed parameters (data) directly into a query, without exposing your application to SQL injection attacks or other security flaws. The way it works is that you write out question marks (?) in the places you want your data to be embedded in the query, and you provide your data as a parameter to the function being called.

db.exec("SELECT id FROM users WHERE name = ?;", "John")

Pay attention to the question mark after the condition, and also to the input (the name) being added as an extra argument to exec after the query portion.

Exa will use SQLite's bind functions to embed the data into the query without exposing you to SQL injection attacks. As a bonus, this is also usually faster than escaping and concatenating the input into the query.

Creating your own auto-converters

You might want to convert one data-type into something much more primitive that SQLite can understand more easily, a common example is creating a procedure to automatically convert DateTime into UNIX epoch timestamps, so that they can be stored as integers. The way you do in this Exa is by defining a procedure for your datatype with the name toDbValue, this should return a DbValue object at the end.

Here's an example of how to make an auto-converters for DateTime objects.

proc toDbValue*[T: DateTime](v: T): DbValue =
  return DbValue(
    kind: DbValueKind.Integer,
    intVal: v.toTime().toUnix()
  )

In the kind portion, we specify what SQLite primitive data type we will be outputting, in our case, we want to convert DateTime into UNIX timestamps, so we want our result to be an integer.

In the intVal portion, we specify the actual value, we first convert the DateTime into Time using toTime() and then we convert it into a UNIX epoch timestamp with toUnix(), this gets used as the actual database value.

Retrieving data

Exa provides three procedures that let you fetch data from your SQLite table:

• one for fetching a single row.
• iterate for looping over multiple rows.
• all for fetching all rows at once.

Retrieving a single row with one

At times, you will need to fetch one and only one row from the database, maybe you're in the middle of a login routine, where it doesn't make sense to fetch more rows than needed. No matter the case, Exa provides a procedure named one for these use-cases.

Like the exec procedure, it supports parameter substition using question marks, so you can substitute values directly into the query without needing to worry about sanitization.

let row = db.one("SELECT name FROM users WHERE id = ?;", 10)

If no rows can be found, then the row returned by one will be just an empty sequence.

let row = db.one("SELECT name FROM users WHERE id = ?;", 9000);

# We will likely not step into this
assert row == @[]

Rows in Exa are represented with the DbRow type, all in all, they are sequences containing DbValue objects. (seq[DbValue])

Exa converts rows into their proper native type, which makes retrieving data easier since you do not need to mess around with parseInt or try to parse the data in any way, it is returned as SQLite sees it.

# Here we fetch a string from the database
let row = db.one("SELECT name FROM users WHERE id = ?;", 10)

# First we check if the row contains anything.
if len(row) == 0:
  quit "Error: No items in row"

# Then we can use it!
echo "Name: ", row[0].strVal

Integers are accessed with .intVal, float values with .floatVal, BLOBs (binary data) with .blobVal and you can check for NULL by using the isNull procedure or manually checking the column kind field. Here's an example of how to do both methods:

let row = db.one("SELECT profession FROM users WHERE name = ?;", "John")

# Checking if row is null with `isNull`
if row[0].isNull():
  quit "Row is null!"

# Checking if row is null manually
if row[0].kind == DbValueKind.Null
  quit "Row is null!"

echo "John's profession is: ", row[0].strVal

Note: A row being empty is not the same as null. one and all will return empty objects if no rows could be found. But if a row is found with NULL columns then the row will have DbValue objects whose kind field is set to DbValueKind.Null (As can be seen in the manual method for null-checks)

Iterating over rows with iterate

To retrieve multiple rows, you can either use all or iterate to iterate over rows, the latter is more efficient if you don't need all the rows. iterate is an iterator, which means you simply use it like so:

for row in db.iterate("SELECT name FROM users;"):
  echo "Found user whose name is:", row[0].strVal

You can break in the middle of this loop whenever you want, it automatically cleans up in the case of interruptions.

Retrieving all rows with all

You might wish to fetch multiple rows all at once, without using an iterator. Thankfully, the all procedure does exactly that, it lets you run a SQL query and it returns a sequence of rows. Here is an example of how to use it:

let allRows = db.all("SELECT * FROM users;")

# Accessing the first row, then the second and third.
# The same field is being accessed in all three cases.
assert allRows[0][0].strVal == "John"
assert allRows[1][0].strVal == "Jane"
assert allRows[2][0].strVal == "Kate"

Note: When fetching multiple rows, iterate will nearly always be faster as it lets you break in the middle of your query, should you ever need it. There's also little overhead, since it's an iterator.

Closing connections

When it's time to close the database connection, you can make use of the closeSqlite procedure. This procedure is idempotent, so it won't close database connections that are already closed, but the other database procedures are not idempotent and they will continue to operate, even on invalid connections.

db.closeSqlite() # That's it!

Using connection pools

Exa provides built-in connection pools for SQLite, these connection pools are most often used in multi-threaded environments such as multithreaded Nim web frameworks of which the most popular one is Mummy.

Connection pools can also be used to structure your read and write operations, SQLite only supports one writer at a time, so you can put all of your read operations into one pool with multiple readers (fx. 4 or 6 connections) and you can put all of your write operations into one pool with a single writer.

Opening a connection pool

You can open a SQLite connection pool with the openSqlitePool procedure, this procedure takes in a filename (path to database, or :memory: for in-memory databases) like openSqlite, but it also takes in an integer (in the size parameter) which tells Exa how many connections to open for the pool.

var pool = openSqlitePool(
  ":memory:",
  4,
  DbConnProfile.Minimal
)

It also takes in a profile parameter which can be used to signal the database connection profile to be used for the database connections.

Note: Don't make connection pools immutable with let, or else you'll have a bad time!

Using connection pools

Using connection pools can be easily accomplished with the withDb template, this is a bit of sugar syntax which automatically calls in low-level procedures (borrow and recycle) to borrow and return database connections to their respective pools. withDb takes in a database pool, a variable name for the database connection (db in the case of the example below) and finally a block of code to execute with the database connection.

pool.withDb db:
  db.exec("INSERT INTO users (?, ?);", "John", 25);

If you'd like to manually borrow connections and return them, then look into the borrow_ and recycle procedures, the above sugar syntax just calls those two procedures for you so you don't forget!

Closing a connection pool

You can close a connection pool with the closeSqlitePool procedure.

closeSqlitePool(pool) # That's it!