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:

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

Available connection types are:

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

database

logger

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

Model Options

Table columns

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

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:

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:

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:

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:

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:

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