norm

A database ORM for the nelua programming language

Requirements

• nelua
• postgres
• mysql
• sqlite

NB: The db requirements are only specific to whatever databases you make use of in your program

Quick Start

## NORM_DB = "SQLITE"
local norm = require ".norm"

-- Instantiate a new Db object
local db, err = norm.Db.new({
  name = "test.db"
})

-- Create and run your Db migrations
local schema = norm.Schema
local type = schema.ColumnType
local migrations: hashmap(string, norm.Schema.MigrationFn)
migrations["1"] = function(db: norm.Db): string
  local err = schema.create_table(db, "users", {
    {"id", type.integer, { primary_key = true }},
    {"name", type.text, { unique = true }},
    {"age", type.integer}
  })
  return err
end
local err = schema.migrate(db, migrations)

-- Insert a value into the users table
local users_insert: hashmap(string, string)
users_insert["name"] = "James"
users_insert["age"] = "21"
local rows, err = db:insert("users", users_insert)
assert(err == "", err)

-- Select matching rows from the users table
local users_select: hashmap(string, string)
users_select["name"] = "James"
users_select["age"] = "21"
local rows, err = db:select("* FROM users", users_select)
assert(err == "", err)
print(rows[1]["name"]) -- Should print "James"

-- Delete matching rows from the user table
local users_delete: hashmap(string, string)
users_delete["name"] = "James"
users_delete["age"] = "21"
local err = db:delete("users", users_delete)
assert(err == "", err)

local model = norm.Model

-- Create a new User model
local Users, err = model.new(db, "users", "Users")
assert(err == "", err)

-- Create a new model instance
local users_create: hashmap(string, string)
users_create["name"] = "James"
users_create["age"] = "21"
local user, err = Users:create(users_create)
assert(err == "", err)

-- Get a column from the model instance
local name, err = user:get_col("name")
assert(err == "", err)
print(name) -- Should print "James"

-- Delete the User model instance
local err = user:delete()
assert(err == "", err)

norm.nelua

norm

The norm module

local norm = @record{}

norm.destroy_rows

See base.nelua

local norm.destroy_rows = base.destroy_rows

norm.escape_identifier

See base.nelua

local norm.escape_identifier = base.escape_identifier

norm.escape_literal

See base.nelua

local norm.escape_literal = base.escape_literal

norm.Config

This record is used to configure the ORM for the specified Database

## if NORM_DB == "SQLITE" then
  local norm.Config = @record{
    name: string,
    log: logger.NotSetOrBool
  }
## elseif NORM_DB == "PG" then
  local norm.Config = @record{
    name: string,
    user: string,
    password: string,
    host: string,
    port: uinteger,
    log: logger.NotSetOrBool
  }
## elseif NORM_DB == "MYSQL" then
  local norm.Config = @record{
    name: string,
    user: string,
    password: string,
    host: string,
    port: uinteger,
    log: logger.NotSetOrBool
  }
## end

norm.Db

This module is a collection of functions to make queries to the database

## if NORM_DB == "SQLITE" then
local norm.Db = @record{
  conn: *sqlite.type,
  log: logger.NotSetOrBool,
  models: hashmap(string, pointer) -- norm.Model
}
## elseif NORM_DB == "PG" then
local norm.Db = @record{
  conn: *pg.type,
  log: logger.NotSetOrBool,
  models: hashmap(string, pointer) -- norm.Model
}
## elseif NORM_DB == "MYSQL" then
local norm.Db = @record{
  conn: *mysql.type,
  log: logger.NotSetOrBool,
  models: hashmap(string, pointer) -- norm.Model
}
## end

norm.Db.new

This function returns a new norm.Db object and an error string If no error occurs, an empty string is returned

function norm.Db.new(conf: norm.Config): (norm.Db, string)

norm.Db:destroy

This function cleans up the memory used by the Db object

function norm.Db:destroy()

norm.Db:query

Sends a query to the database returning a sequence(rows) of hashmap(columns) and a string In the case of an error, a non empty string is returned describing the error All further Db functions are constructed around this function

function norm.Db:query(sql: string): (sequence(hashmap(string, string)), string)

norm.Db:table_exists

This function checks if a table exists in the database returning a true if it does, false otherwise

function norm.Db:table_exists(name: string): boolean

norm.Db:select

This function at it's simplest just appends "SELECT" to a query Optionally, you can pass where to the query which will append a WHERE clause at the end of the sql with your conditions

function norm.Db:select(sql: string, where: hashmap(string, string)): (sequence(hashmap(string, string)), string)

norm.Db:insert

This function inserts values into the table tbl_name returning the specified row of columns If returning is not set, and empty sequence is returned

function norm.Db:insert(
    tbl_name: string,
    values: hashmap(string, string),
    returning: facultative(string)
  ): (sequence(hashmap(string, string)), string)

norm.Db:update

This function attempts deletes rows from a table tbl_name based on conditions, returning an error string If condition is a string, it is appended in the WHEREclause directly If condition is a hashmap, it is formatterd into aWHERE` clause If no error occurs, the string is empty

function norm.Db:update(
    tbl_name: string,
    values: hashmap(string, string),
    conditions: overload(string, hashmap(string, string)),
    returning: facultative(string)
  ): (sequence(hashmap(string, string)), string)

norm.Db:delete

This function attempts deletes rows from a table tbl_name based on conditions, returning an error string If condition is a string, it is appended in the WHEREclause directly If condition is a hashmap, it is formatterd into aWHERE` clause If no error occurs, the string is empty

function norm.Db:delete(tbl_name: string, conditions: overload(string, hashmap(string, string))): string

norm.Schema

This is a collection of types and function for creating and managing your database schema

local norm.Schema = @record{}

norm.Schema.MigrationFn

Type alias for what a migration function is in norm.Schema.migrate

local norm.Schema.MigrationFn = @function(db: norm.Db): string

norm.Schema.ColumnOpts

These are options used to modify how a column is generated in norm.Schema.Column

local norm.Schema.ColumnOpts = @record{
  default_val: string,
  is_null: boolean,
  unique: boolean,
  primary_key: boolean
}

norm.Schema.ColumnType

This defines the type of a column in norm.Schema.Column

local norm.Schema.ColumnType: type = @enum{
  not_set = 0,
  -- generic types
  integer,
  blob,
  text,
  numeric,
  real,
  varchar,
  any,
  ----------
  -- pg/mysql specific types
  timestamp,
  date,
  ----------
  -- pg specific types
  serial,
  ----------
  -- mysql specific types
  id,
  ---------
}

norm.Schema.Column

This defines the type of a column in norm.Schema.Column

local norm.Schema.Column = @record{
  name: string,
  type: norm.Schema.ColumnType,
  opts: norm.Schema.ColumnOpts
}

norm.Schema.CreateTableOpts

These are options used to customise the creation of a table, see norm.Schema.create_table

• if_not_exists: Changes the table creation prefix to "CREATE TABLE IF NOT EXISTS"
• strict: For sqlite, appends "STRICT" immediately after the closing ) to make the table a strict table
• extra_sql is appended before the final ) of the create query

local norm.Schema.CreateTableOpts = @record{
  if_not_exists: boolean,
  extra_sql: string,
  strict: boolean
}

norm.Schema.create_table

This function creates a table, name, with the specified columns returning an error string If no error occurs, the string is empty

function norm.Schema.create_table(
  db: norm.Db,
  name: string,
  columns: sequence(norm.Schema.Column),
  opts: norm.Schema.CreateTableOpts
): string

norm.Schema.drop_table

This function drops a table, name, returning an error string If no error occurs, the string is empty

function norm.Schema.drop_table(db: norm.Db, name: string): string

norm.Schema.IndexOpts

These are options used to customise the creation of an index in a table, see norm.Schema.create_index

local norm.Schema.IndexOpts = @record{
  where: string,
  unique: boolean,
  if_not_exists: boolean
}

norm.Schema.create_index

This function adds new indexes to a table, name, returning an error string It takes a list of cols and optional opts to create a new index If no error occurs, the string is empty

function norm.Schema.create_index(db: norm.Db, name: string, cols: sequence(string), opts: norm.Schema.IndexOpts): string

norm.Schema.drop_index

This function drops an index from a table, name, returning an error string It takes a list of cols to determine the index to drop If no error occurs, the string is empty

function norm.Schema.drop_index(db: norm.Db, name: string, cols: sequence(string)): string

norm.Schema.add_column

This function adds a new col to a table, tbl_name, returning an error string If no error occurs, the string is empty

function norm.Schema.add_column(db: norm.Db, tbl_name: string, col: norm.Schema.Column): string

norm.Schema.drop_column

This function drops a col from a table, tbl_name, returning an error string If no error occurs, the string is empty

function norm.Schema.drop_column(db: norm.Db, tbl_name: string, col_name: string): string

norm.Schema.rename_column

This function renames a col from a table, tbl_name, replacing the old_col_name with the new_col_name, returning an error string If no error occurs, the string is empty

function norm.Schema.rename_column(db: norm.Db, tbl_name: string, old_col_name: string, new_col_name: string): string

norm.Schema.rename_table

This function renames a table replacing the old_tbl_name with the new_tbl_name, returning an error string If no error occurs, the string is empty

function norm.Schema.rename_table(db: norm.Db, old_tbl_name: string, new_tbl_name: string): string

norm.Schema.migrate

This function runs all migrations, returning an error string If no error occurs, the string is empty

function norm.Schema.migrate(db: norm.Db, migrations: hashmap(string, norm.Schema.MigrationFn)): string

norm.RelationKind

This enum is used to determine the type of relation in norm.Relation

local norm.RelationKind = @enum{
  not_set = 0,
  belongs_to,
  has_one,
  has_many
}

norm.Relation

This record is used to define new relations for a model

local norm.Relation = @record{
  kind: norm.RelationKind,
  rel: record{
    name: string,
    model_name: string,
    key: string
  }
}

norm.ModelOpts

This is used to configure extra options for a model instance

• primary_keys: By default all models expect the table to have a primary key called "id" at index 1. This can be changed by setting the primary_keys value here
• rels: A sequence of defined relations used to connect models

local norm.ModelOpts = @record{
  primary_keys: sequence(string),
  rels: sequence(norm.Relation)
}

norm.Model

This is used to interact with a particular table directly with more specific functions

local norm.Model = @record{
  db: *norm.Db,
  model_name: string,
  tbl_name: string,
  primary_keys: sequence(string),
  rels: sequence(norm.Relation)
}

norm.Model.OffsetPaginator

Used to get model instances split by pages

local norm.Model.OffsetPaginator = @record{
  parent: *norm.Model,
  per_page: uinteger,
  fields: sequence(string),
  order_by: string,
  order: string,
  where: string
}

norm.Model.Inst

Model Instance This is meant to represent a single row in a model's table

local norm.Model.Inst = @record{
  parent: *norm.Model,
  row: hashmap(string, string)
}

norm.Model.new

This function returns a new Model object and an error string If no error occurs, the string is empty

This function allocates memory for the model as it needs to be stored as a pointer in the db instance to be used with relations Maked sure to call Model:destroy to clear it out from the db before trying to create a new one with similar relations

function norm.Model.new(db: *norm.Db, tbl_name: string, model_name: string, opts: norm.ModelOpts): (*norm.Model, string)

norm.Model:destroy

Used to clean up memory created by the model and clear it from the model cache in the db instance

function norm.Model:destroy()

norm.Model:find

This function attempts to find a row based on the conditions, returning a norm.model.Inst object and an error string If condition is a string, it is appended in a WHERE clause directly If condition is a hashmap, it is formatterd into a WHERE clause If no error occurs, the string is empty

function norm.Model:find(conditions: overload(string, hashmap(string, string))): (norm.Model.Inst, string)

norm.Model:count

This function returns the total number of records in a model based on the provided conditions and an error string If condition is a string, it is appended in a WHERE clause directly If condition is a hashmap, it is formatterd into a WHERE clause If no condition is provided then every row in the table will be counted If no error occurs, the string is empty

function norm.Model:count(conditions: overload(niltype, string, hashmap(string, string))): (integer, string)

norm.Model.SelectOpts

fields: Set the returned fields of the query, default is "*" extra_sql: Appened at the end of the generated WHERE clause

local norm.Model.SelectOpts = @record{
  fields: sequence(string),
  extra_sql: string
}

norm.Model:select

This function attempts to find rows based on the conditions, returning a sequence of norm.model.Inst objects and an error string If condition is a string, it is appended in a WHERE clause directly If condition is a hashmap, it is formatterd into a WHERE clause If no condition is provided then every row in the table will be returned If no error occurs, the string is empty

function norm.Model:select(conditions: overload(niltype, string, hashmap(string, string)), opts: norm.Model.SelectOpts): (sequence(norm.Model.Inst), string)

norm.Model:OffsetPaginatorOpts

per_page - Maximum number of instances returned per get_page request order_by - Field to order the request by, default is "id" order - How to order the request, default is "ASC" fields - Fields to be returned by the request, default is "*"

local norm.Model.OffsetPaginatorOpts = @record{
  per_page: uinteger,
  order_by: string,
  order: string,
  fields: sequence(string)
}

norm.Model:offset_paginated

This function returns a new Model OffsetPaginator object and an error string If condition is a string, it is appended in a WHERE clause directly If condition is a hashmap, it is formatterd into a WHERE clause If no error occurs, the string is empty

function norm.Model:offset_paginated(conditions: overload(hashmap(string, string), string), opts: norm.Model.OffsetPaginatorOpts): (norm.Model.OffsetPaginator, string)

norm.Model.OffsetPaginator:get_page

This function gets page, where pages are 1 indexed returning the relevant sequence of model instances and an error string If no error occurs, the string is empty

function norm.Model.OffsetPaginator:get_page(page: uinteger): (sequence(norm.Model.Inst), string)

norm.Model.OffsetPaginator:total_items

This function returns the total number of items based of the paginators where clause and an error string If no error occurs, the string is empty

function norm.Model.OffsetPaginator:total_items(): (integer, string)

norm.Model.OffsetPaginator:total_pages

This function returns the total number of pages based of the paginators where clause and an error string If no error occurs, the string is empty

function norm.Model.OffsetPaginator:total_pages(): (integer, string)

norm.Model:create

This function inserts values into the model's table, returning a norm.model.Inst object and an error string If returning is not set, and empty object is returned If no error occurs, the string is empty

function norm.Model:create(values: hashmap(string, string), returning: facultative(string)): (norm.Model.Inst, string)

norm.Model.Inst:get_col

This function get's the value of the col by name if it exists, returning the value and an error string If no error occurs, the string is empty

function norm.Model.Inst:get_col(name: string): (string, string)

norm.Model.Inst:update

This function updates the row instance with values into the model's table, returning a norm.model.Inst object and an error string If returning is not set, and empty object is returned If no error occurs, the string is empty

function norm.Model.Inst:update(values: hashmap(string, string), returning: facultative(string)): (norm.Model.Inst, string)

norm.Model.Inst:delete

This function deletes the row instance from the model's table, returning an error string If no error occurs, the string is empty

function norm.Model.Inst:delete(): string

norm.Model.Inst:get_belongs_to

This function returns a new model instance based on the rel_name and an error string If no error occurs, the string is empty

A relation that fetches a single related model instance. The foreign key column used to fetch the other model is located on the same table as the model. For example, a table named posts with a column named user_id would belong to a table named users.

function norm.Model.Inst:get_belongs_to(rel_name: string): (norm.Model.Inst, string)

norm.Model.Inst:get_has_one

This function returns a new model instance based on the rel_name and an error string If no error occurs, the string is empty

A relation that fetches a single related model. Similar to belongs_to, but the foreign key used to fetch the other model is located on the other table

function norm.Model.Inst:get_has_one(rel_name: string): (norm.Model.Inst, string)

norm.Model.Inst:get_has_one

This function returns a sequence of new model instances based on the rel_name and an error string If no error occurs, the string is empty

A relation that fetches a sequence of the related model. Similar to has_one, but returning multiple

function norm.Model.Inst:get_has_many(rel_name: string): (sequence(norm.Model.Inst), string)

base.nelua

This file provides some utility functions used by the library

base.escape_identifier

Escapes a string s so it can be used as an identifier in sql queries

function base.escape_identifier(s: string)

base.escape_literal

Escapes a string s so it can be used as a literal in sql queries If the whole of s matches an integer or float, it is not escaped

function base.escape_literal(s: string)

base.format_date

Returns a date string formatted properly for insertion in the database. The time argument is optional, will default to the current UTC time.

function base.format_date(time: facultative(integer))

base.destroy_rows

Cleans up memory for rows returned from running sql queries

function base.destroy_rows(rows: sequence(hashmap(string, string)))

logger.nelua

logger record

The logger module

local logger = @record{}

logger.NotSetOrBool

Used to determine if the logger should print to console or not

local logger.NotSetOrBool = @enum{
  NOT_SET = 0,
  TRUE,
  FALSE
}

logger.log

If the log is not set or set to true, will print s

function logger.log(log: logger.NotSetOrBool, s: string)

---

Acknowledgements

This library is heavliy inspired by the lapis and GORM