allographer

An asynchronous query builder library inspired by Laravel/PHP and Orator/Python for Nim.
Supported Databases are Sqlite3, PostgreSQL, MySQL, MariaDB and SurrealDB.
Supported Nim for both 1.6.14 and 2.0.0

Easy to access Rdb

Query Builder

import asyncdispatch, json
import allographer/connection
import allographer/query_builder

let maxConnections = 95
let timeout = 30
let rdb = dbOpen(PostgreSql, "database", "user", "password" "localhost", 5432, maxConnections, timeout)
# also available
# let rdb = dbOpen(Sqlite3, "/path/to/db/sqlite3.db", maxConnections=maxConnections, timeout=timeout)
# let rdb = dbOpen(MySQL, "database", "user", "password" "localhost", 3306, maxConnections, timeout)
# let rdb = dbOpen(MariaDB, "database", "user", "password" "localhost", 3306, maxConnections, timeout)
# let surreal = dbOpen(SurrealDb, "test_ns" "test_db", "user", "password" "http://localhost", 8000, maxConnections, timeout)

proc main(){.async.} =
  let result = await rdb
                    .table("users")
                    .select("id", "email", "name")
                    .limit(5)
                    .offset(10)
                    .get()
  echo result

waitFor main()

>> SELECT id, email, name FROM users LIMIT 5 OFFSET 10
>> @[
  {"id":11,"email":"user11@gmail.com","name":"user11"},
  {"id":12,"email":"user12@gmail.com","name":"user12"},
  {"id":13,"email":"user13@gmail.com","name":"user13"},
  {"id":14,"email":"user14@gmail.com","name":"user14"},
  {"id":15,"email":"user15@gmail.com","name":"user15"}
]

Schema Builder

import allographer/schema_builder


rdb.create([
  table("auth", [
    Column.increments("id"),
    Column.string("name").nullable(),
    Column.timestamp("created_at").default()
  ]),
  table("users", [
    Column.increments("id"),
    Column.string("name"),
    Column.foreign("auth_id").reference("id").on("auth").onDelete(SET_NULL)
  ])
])

>> CREATE TABLE auth (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR,
    created_at DATETIME DEFAULT (NOW())
)
>> CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR NOT NULL,
    auth_id INT,
    FOREIGN KEY(auth_id) REFERENCES auth(id) ON DELETE SET NULL
)

rdb.alter(
  table("users", [
    Column.string("email").unique().default("").add(),
    Column.deleteColumn("name")
  ])
)

>> ALTER TABLE "users" ADD COLUMN `email` UNIQUE DEFAULT "" CHECK (length(`email`) <= 255)
>> ALTER TABLE "users" DROP `name`

Index

allographer

Install

nimble install allographer

If you get SIGSEGV: Illegal storage access. (Attempt to read from nil?) when trying to use the database you likely have a problem with the library path. On OS X the default is to check for the brew --prefix of the chosen driver, if that doesn't work it will look in /usr/lib or an environment variable DYLD_xxx_PATH where xxx if your driver: SQLITE, MARIADB, MYSQL or POSTGRES.

Configuation

Allographer loads emvironment variables of DB_SQLITE, DB_POSTGRES, DB_MYSQL DB_MARIADB and DB_SURREAL to define which process should be compiled.
These environment variables have to be set at compile time, so they have to be written in config.nims not in .env.

config.nims

import os

putEnv("DB_SQLITE", $true)
putEnv("DB_POSTGRES", $true)

In this example, even if your runtime environment lacks mysqlclient-dev, execution will success. However if your runtime environment lacks sqlite3, execution will fail.

Createing connection

Database connection should be definded as singleton variable.

database.nim

import allographer/connection

let rdb* = dbOpen(PostgreSql, "database", "user", "password" "localhost", 5432, maxConnections, timeout)

# you can create connection for multiple database at same time.
let sqliteRdb* = dbOpen(Sqlite3, "/path/to/db/sqlite3.db", maxConnections=maxConnections, timeout=timeout)
let mysqlRdb* = dbOpen(MySQL, "database", "user", "password" "localhost", 3306, maxConnections, timeout)
let mariaRdb* = dbOpen(MariaDB, "database", "user", "password" "localhost", 3306, maxConnections, timeout)
let surrealDb* = dbOpen(SurrealDb, "test_ns" "test_db", "user", "password" "http://localhost", 8000, maxConnections, timeout)

Then, call connection when you run query.

run_sql.nim

import asyncdispatch
import allographer/query_builder
from database import rdb

proc main(){.async.}=
  echo await rdb.table("users").get()

waitFor main()

Logging

Please set args in dbOpen()

proc dbOpen*(driver:Driver, database:string="", user:string="", password:string="",
            host: string="", port=0, maxConnections=1, timeout=30,
            shouldDisplayLog=false, shouldOutputLogFile=false, logDir=""):Rdb

shouldDisplayLog: Whether display logging in terminal console or not.
shouldOutputLogFile: Whether output logging in log file or not.
logDir: Define logging dir path.

Documents

Schema Builder for RDB
Query Builder for RDB
Schema Builder for SurrealDB
Query Builder for SurrealDB

Nim API Documents

v2

connection
Schema Builder
Query Builder

v1