LuaORM

LuaORM is an Object-Relational Mapping (ORM) for Lua.
It is based on 4DaysORM.
Some of the APIs are inspired by Propel

Usage

Include LuaORM

You can include LuaORM by using require "<path to LuaORM directory>/src/API".
If you installed LuaORM with luarocks you can include it with require "LuaORM.API".
The returned object has the following fields:

Field Name	Description
FieldType	The FieldType class to define custom FieldType's
Model	The Model class to create Table's and Query's
ORM	The ORM instance to configure the ORM
fieldTypes	The list of default FieldType's

Configure the ORM

Call API.ORM:initialize(options) to initialize the ORM.

API.ORM:initialize({
  connection = "LuaSQL/MySQL",
  database = {
    databaseName = "orm_test",
    host = "127.0.0.1",
    portNumber = 3306,
    userName = "root",
    password = "root"
  },
  logger = { isEnabled = true, isDebugEnabled = false }
})

You can then close the connection with API.ORM:close()

API.ORM:close()

connection

Option	Description	Default Value	Example
connection	Defines the DatabaseConnection class	-	"LuaSQL/MySQL"

Available connection types are:

• LuaSQL/MySQL
• LuaSQL/SQLite (not supported yet)
• LuaSQL/PostgreSQL (not supported yet)

database

Option	Description	Default Value	Example
databaseName	The path to the database file (SQLite) or the name of the database	-	"db_1"
host	The address (IP, URL, hostname) of the database server	-	"127.0.0.1"
portNumber	The port number of the database on the server	-	3306
userName	The name of the database user	-	"user"
password	The password of the database user	-	"pwd"

logger

Option	Description	Default Value
isEnabled	Defines whether log messages will be shown	false
isDebugEnabled	Defines whether debug log messages will be shown (including SQL queries)	false

Create Models

After initializing the ORM you can start creating the models for your tables.
A table contains one or more table columns and must contain exactly one primary key column.
If no primary key column is defined, a "id" column will be automatically added to the table.
The first parameter of Model() are the Table Configuration settings. The second parameter are the Model Options settings.
The second parameter is optional.

local Model = API.Model
local fieldTypes = API.fieldTypes

local User = Model({
  name = "users",
  columns = {
    { name = "user_name", fieldType = fieldTypes.charField, maxLength = 100, unique = true },
    { name = "password", fieldType = fieldTypes.charField, maxLength = 50, unique = true },
    { name = "age", fieldType = fieldTypes.unsignedIntegerField, maxLength = 2, mustBeSet = false },
    { name = "job", fieldType = fieldTypes.charField, maxLength = 50, mustBeSet = false },
    { name = "time_create", fieldType = fieldTypes.dateTimeField, mustBeSet = false }
  }
},
    { createTable =  true }
)

Table Configuration

Option	Description	Default Value
name	The name of the table	-
columns	The columns of the table	-

Model Options

Option	Description	Default Value
createTable	Creates the table in the database when calling Model()	true

Table columns

Each table column has at least a name and a field type. The available options per table column are:

Option	Description	Default Value
name	The name of the table column	-
fieldType	The field type of the column	-
isPrimaryKey	Defines whether the column is a primary key	false
autoIncrement	Defines whether the values of this column are automatically incremented	false
isForeignKeyTo	Defines the table that is referenced by this column	-
mustBeSet	Defines if the column must always have a value	true
unique	Defines if the column values must be unique	false
maxLength	The number of available bytes per value	-
escapeValue	If true text values will be escaped in order to prevent SQL injection	false
defaultValue	Default value if the value is not set (May be a function)

Field types

Field types define which kind of data can be stored in the column. They also control how data from the database will be converted.
The default field types are:

Field type name	Description
integerField	Stores integer values
unsignedIntegerField	Stores unsigned integer values
charField	Stores a variable number of characters
textField	Stores text
booleanField	Stores a boolean value (true or false)
dateTimeField	Stores a date (Internally uses the os.date function)

You can also create custom field types and overwrite default field types by editing the API.fieldTypes table.
To create a new FieldType use API.FieldType(options). The available options are:

Option	Description	Default Value
luaDataType	The lua data type for the field type values	-
SQLDataType	The SQL data type (either the id of a predefined one or a custom string)	string
convert	Function that converts input values before they are further processed	-
validator	Function that validates that a value matches this FieldType	-
as	Function that adds additional parameters to a escaped literal value	-
to	Function that converts a query result value to a lua data type	-

The available lua data types are:

• string
• number
• integer
• boolean

The available SQL data types are:

• string
• blob
• text
• number
• integer
• float
• boolean
• unsignedInteger

Create queries

After creating the models you can use them to insert or fetch data from the database.

Select

You can use Select queries to select specific rows from a table.
Call get() to get a new query, then call find() to get all rows of that table.

local rows = User:get()
                 :find()
print(rows[1].user_name)

Use findOne() to return only the first row.

local row = User:get()
                :findOne()
print(row.user_name)

You can access the values of each data row by accessing the data row index with the corresponding column name.

Select rules

By default all columns of the query tables are selected by the queries.
You can use Select rules to additionally select table columns with SQL functions.

local row = User:get()
                :select():max("age")
                :findOne()
print(row.MAX_age)

Available select rule methods are:

Method name	Alias	Description
min	MIN_<column name>	Selects the minimum value of a column
max	MAX_<column name>	Selects the maximum value of a column
count	COUNT_<column name>	Selects the number of rows where a column is set
sum	SUM_<column name>	Selects the sum of values in a column

Join

Use join rules if you want to select the data from multiple tables at once.
In order to use joins one of the tables in the query must have a relation to the target join table. Note: The foreign key field type must match the field type of the referenced tables primary key (auto primary keys have the field type "unsignedIntegerField").

So let's create a "news" table that references the "users" table

local News = Model({
  name = "news",
  columns = {
    { name = "title", fieldType = fieldTypes.charField, maxLength = 100, unique = false, mustBeSet = true, unique = true },
    { name = "text", fieldType = fieldTypes.textField, mustBeSet = false },
    { name = "create_user_id", fieldType = fieldTypes.unsignedIntegerField, isForeignKeyTo = User }
  }
})

Now you can join the "news" table to a "User" query or vice versa.

-- Notation type one
News:get():leftJoinUsers():find()
User:get():leftJoinNews():find()

-- Notation type two
News:get():leftJoin():table("users")
User:get():leftJoin():table("news")

-- Notation type three
News:get():join("users", "left"):find()
User:get():join("news", "left"):find()


-- Specifiy the columns that will be used to join the tables
News:get():leftJoinUsers():on("user_id", "users.id")

The data rows of the joined table can be found by accessing the index with the corresponding table name, e.g. rows.news returns all related "news" entries of one "user" row.

Available join types are:

• leftJoin(<table name>) / "left"
• rightJoin(<table name>) / "right"
• innerJoin(<table name>) / "inner"
• fullJoin(<table name>) / "full" (not supported yet)

Note that the column and table names for the methods are converted as follows:

1. The first letter is converted to uppercase (e.g. "user_name" => "User_name")
2. Underscores are removed and the letters following a underscore are converted to uppercase (e.g. "User_name" => "UserName")

Where

You can use Where rules to filter the result rows.

-- Notation type one
User:get()
    :where():column("user_name"):equals("root")
    :find()

-- Notation type two (only for "equals" rules)
User:get()
    :where({ user_name = "root" })
    :find()

-- Notation type three (only for "equals" rules)
User:get()
    :filterByUserName("root")
    :find()

The following methods are available to configure Where rules:

Method name	Description
NOT()	Find all rows that do not match the rule
column(string)	Set which column shall be compared to a value
isLessThan(number)	column value < number
isLessThanOrEqual(number)	column value <= number
isGreaterThan(number)	column value > number
isGreaterThanOrEqual(number)	column value >= number
equals(any)	column value = any
equalsColumn(string)	column value = <other column's value>
isLike(string)	column value = pattern
isInList(table)	table contains column's value
isNotSet()	Checks whether the column is not set
AND()	Appends the next rule with the "AND" operator
OR()	Appends the next rule with the "OR" operator

Group by

You can use "Group by" clauses to group multiple rows into one based on specific columns.
This can be useful when using aggregate functions in the select rules.

-- Notation type one
User:get()
    :groupById()
    :find()

-- Notation type two
User:get()
    :groupBy("users.id")
    :find()

You can also use having() to filter the grouped rows.

-- Notation type one
User:get()
    :groupById()
    :having():column("age"):equals(20)
    :find()

-- Notation type two
User:get()
    :groupBy("users.id")
    :having({ age = 20 })
    :find()

Having can only be used after calling groupBy(). It provides the same methods like where().

Order by

You can sort the result rows by their column values.

-- Notation type one
User:get()
    :orderByAge():desc()
    :findOne()

-- Notation type two
User:get()
    :orderBy("age"):desc()
    :findOne()

Available methods are:

Method name	Description
asc	Order the result rows from lowest to highest ("ascending") by the specifeid column
desc	Order the result rows from highest to lowest ("descending") by the specified column

Limit

You can use a Limit clause to limit the number of returned rows.

User:get()
    :limit(2)
    :find()

You can also specifiy a offset to skip the first <x> result rows.

User:get()
    :limit(2)
    :offset(2)
    :find()

Insert

local user = User:new({
  user_name = "root",
  password = "pwd"
})

user:save()

print(user.id)

Use the new() method to create a new data row for one of your models.
The values are in the format { [column name] = value}
When you finished editing the row call save() to add the row to the database. The new row now has a id that you can get by fetching the id index.

Update

You can also update multiple data rows at once by creating a query.

User:get()
    :where():column("time_create"):isNotSet()
    :update({ time_create = os.time() })

Delete

You can use a Delete query to delete rows from the tables.

User:get()
    :where():column("time_create"):isNotSet()
    :delete()

Using Select query results

Select query results provide methods to execute queries.
The following queries are supported:

Update

Update a single row

You can update data rows that you created yourself or that you fetched from the database. Simply change the values of the row by editing the indexes with the names of the rows columns. Then save the row to update the row in the database.

local user = User:get():findOne()

user.user_name = "a_new_user"
user:save()

Update multiple rows

You can also update multiple rows at once by using the update method.

local users = User:get():limit(2):find()

users:update({ age = 50 })

Delete

You can delete data rows from the database by calling the delete() method.

local user = User:get():findOne()

user:delete()

Count

You can count the number of rows with the count() method.

local users = User:get():find()

print(users:count())