Models

In Gin, you define your databases in the directory ./db/.

These databases can then be accessed in the various model files located in ./app/models, where you can implement helper methods to store and query data into and from these databases.

This is an extremely flexible and powerful design, as it allows you to implement multiple Databases support and Message Queues in your application (for example Redis, RabbitMQ, NoSQL databases, but also regular SQL databases such as MySQL or PostgreSQL).

In Gin, a model is therefore not necessarily a SQL database object representation.

However, Gin does come with a more traditional database helper that allows you to quickly define models that have SQL Object-Relational Mapping functionalities.

Currently, only the MySQL and PostgreSQL adapters and ORMs are supported.

MySQL Connection and models

To create a MySQL connection and model with ORM functionalities, we first need to define a new MySQL database in the file ./db/mysql.lua, by requiring Gin's SQL helper and by specifying the mysql adapter like so:

local SqlDatabase = require 'gin.db.sql'
local Gin = require 'gin.core.gin'

-- First, specify the environment settings for this database, for instance:
local DbSettings = {
    development = {
        adapter = 'mysql',
        host = "127.0.0.1",
        port = 3306,
        database = "demo_development",
        user = "root",
        password = "",
        pool = 5
    },

    test = {
        adapter = 'mysql',
        host = "127.0.0.1",
        port = 3306,
        database = "demo_test",
        user = "root",
        password = "",
        pool = 5
    },

    production = {
        adapter = 'mysql',
        host = "127.0.0.1",
        port = 3306,
        database = "demo_production",
        user = "root",
        password = "",
        pool = 5
    }
}

-- Then initialize and return your database:
local MySql = SqlDatabase.new(DbSettings[Gin.env])

return MySql

As you can see, we're defining various database access settings for the three environments development, test and production, and then initializing the database object with the settings that correspond to the enivornment Gin is run in (available in Gin.env).

The newly created object MySql mainly exposes the method execute(sql). It allows you to use the MySql connection in your application to perform database queries, by calling MySql:execute(sql).

Now that we have a database object, we can define a model Users. To do so, create the file ./app/models/users.lua and enter this code:

-- gin
local MySql = require 'db.mysql'
local SqlOrm = require 'gin.db.sql.orm'

-- define
return SqlOrm.define_model(MySql, 'users')

This defines a model Users that corresponds to the database table users, for the database connection MySql.

For the ORM to work properly, the table users must have an AUTO_INCREMENT PRIMARY KEY named id. Please refer to migrations for additional information.

PostgreSQL Connection and models

To create a PostgreSQL connection and model with ORM functionalities, just proceed as instructed in the previous MySQL section. The only differences are:

  • You'll need to specify the postgresql adapter in the database settings, like so:
 development = {
        adapter = 'postgresql',
        host = "127.0.0.1",
        port = 5432,
        database = "demo_development",
        user = "postgres",
        password = "",
        pool = 5
 }
  • You need to pass the PostgreSQL database to the users model. For instance, if you've defined the PostgreSQL database in ./db/postgresql.lua:
 -- gin
 local PostgreSql = require 'db.postgresql'
 local SqlOrm = require 'gin.db.sql.orm'

 -- define
 return SqlOrm.define_model(PostgreSql, 'users')
  • For the ORM to work properly, the table users must have a SERIAL field named id.
ORM Queries

The Users model defined here above can now be used to perform standard SQL queries, after you've required it:

local Users = require 'app.models.users'
  • Users.new(attrs): creates a new user with the passed in attributes. The object is not saved to the database until save is called on it. This returns the newly created user object. For example:
 local user = Users.new({ first_name = 'gin' })
  • Users.create(attrs): creates and saves a new user with the passed in attributes. The attributes must correspond to the ones defined in the table users. This returns the newly created user object, with the sequence id that was returned by MySQL. For example:
 local user = Users.create({ first_name = 'gin' })
 user.id -- => 1
  • Users.all(options): returns all users. If provided, options is a table that can specify the limit, the offset and the order of the resultset. For example:
 local users = Users.all()
  • Users.where(attrs_or_string, options): takes attributes or a SQL string, and returns users that match the query specified in attributes. If provided, options is a table that can specify the limit, the offset and the order of the resultset. For example:
 local users = Users.where({ first_name = 'gin'}, { limit = 5, offset = 10, order = "first_name DESC" })

 local users = Users.where("age > 18")
  • Users.find_by(attrs_or_string, options): same as .where, but will return only the first match. If provided, options is a table that can specify the order of the resultset. For example:
 local user = Users.find_by({ first_name = 'gin'})
  • Users.delete_all(options): deletes all users. If provided, options is a table that can specify the limit of the row count. For example:
 local users = Users.delete_all()
  • Users.delete_where(attrs_or_string, options): deletes all users that match the query specified in the attributes or SQL string. If provided, options is a table that can specify the limit of the row count. For example:
 local users = Users.delete_where({ first_name = 'gin'}, { limit = 5 })
  • user:save(): saves a new model instance or updates an existing one. For example:
 local user = Users.new({ first_name = 'gin' })
 user:save()
  • user:delete(): deletes a model instance. For example:
 local user = Users.find_by({ first_name = 'gin' })
 user:delete()

Docs