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 with exa/sqlite

Opening connections

This module provides the openSqlite procedure for opening connections to sqlite databases. It accepts a single parameter, which must be the filename.

import exa/sqlite

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

SQLite supports in-memory database where the data is stored in memory and not an actual location, this is useful for testing but using it for for storing real data is not recommended as it will disappear as soon as the connection is closed.

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

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
3. DbConnProfile.Server

The first profile, Default, does not enable any options, nor does it do anything useful whatsoever. It's meant to represent SQLite defaults, or for when you wish to not have Exa helpfully configure your database connection for you.

The second profile, Minimal, enables foreign keys in SQLite. Yes, SQLite by default does not enforce foreign key constraints and the Minimal profile ensures it does, helping to prevent databases from accumulating junk data, hopefully.

This profile also enables the WAL (Write-Ahead Log) which not only boosts database performance, but also allows concurrency by not letting writers block readers, and readers block writers. In most cases, these two options enable SQLite to act more like a modern database engine by enforcing more strict relations, and by enabling greater concurrency.

The third profile, Server, 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. This is safe to do with the Write-Ahead Log enabled. The third profile also runs some optimizations 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 optionally choose to just use SQLite's defaults with the Default profile.

Running SQL operations

Running a single operation without retrieving data

Exa's SQLite connector provides four ways to actually run commands, the first of these is the simplest, the exec procedure allows you to run a single SQL query without retrieving any rows from the database.

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

It is used for operations that do not involve retrieving data, such as updating already-existing data, creating new tables, deleting old data or inserting new data.

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 proc exec_ procedure. More about this will be explained later on in the "parameter substitution" section.

Running multiple SQL operations without retrieving data

It's quite common to store a "schema file", containing commands for initializing tables, and data, the problem though is that exec does not support running multiple commands for security's sake. There is a dedicated execScript procedure which can run multiple SQL commands, but which does not have parameter substitution.

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

# Or, alternatively

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

execScript should be primarily 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.

Retrieving a single row

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

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

all, unlike iterate, is a procedure which returns sequences 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"

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")

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...