Core Concepts

Model Basics

In this tutorial you will learn what models are in DB Manager and how to use them.

Concept

The core of DB Manager lies in its models, which are abstractions that represent tables in a database. In the context of the database manager, a model is essentially a class.

Through a model, the DB Manager gains insights into the entity it represents, including the name of the corresponding table in the database and its respective columns (along with their data types).

Each model in the DB Manager is assigned a name, which doesn't necessarily have to match the name of the corresponding table in the database. Typically, models have singular names (e.g., User), while tables have plural names (e.g., Users). However, this configuration can be customized as needed.

Model Definition

Models can be defined in two equivalent ways in DB Manager:

• Calling DBManager:define(modelName, modelDefinition)

Let's consider an example to better understand this concept. Suppose we need to create a model that represents a list of bank accounts, which have attributes such as id, name, and balance. In the database, the corresponding table is named Banks.

Using define method:

-- it's important save the data in a variable
local conn = DBManager:new({ dialect = "sqlite" })

-- it's obrigatory define a primary key
local Bank = conn:define('Bank', {
    id = {
        type = DBManager.INTEGER(), -- we will use INTEGER because we are using sqlite
        primaryKey = true,
        autoIncrement = true
    },
    name = {
        type = DBManager.STRING(), -- we will use STRING because we are using sqlite
        allowNull = false
    },
    balance = {
        type = DBManager.FLOAT(), -- we will use FLOAT because we are using sqlite
        -- allowNull defaults to true
    }
})

-- `conn:define` returns the model, so we can use it directly
print(Bank == conn.models.Bank) -- true

Model synchronization

To synchronize a model with the database, we can use the model:sync() function. By making this call, the DB Manager automatically executes an SQL query to ensure that the model and the corresponding database table are aligned.

NOTE: Model synchronization is a critical process as it allows for the preservation of all database data to a local cache table. This feature enables us to optimize our queries by looking at the cache table instead of directly accessing the database.

• Bank:sync() - This creates the table if it doesn't exist (and does nothing if it already exists)

Example:

Bank:sync()
print("The table for the Bank model was just (re)created!")

Synchronizing all models at once

You can use DBManager:sync() to automatically synchronize all models. Example:

local conn = DBManager({ dialect = "sqlite" })

conn:sync()
print("All models were synchronized successfully.")

Dropping tables

To drop a table related to a model:

Bank:drop()
print("Bank table dropped!")

To drop all tables:

conn:drop()
print("All tables of database dropped!")

Data Types

Every column you define in your model must have a data type.

Strings

DBManager.STRING()            -- VARCHAR(255)
DBManager.STRING(1234)        -- VARCHAR(1234)
DBManager.TEXT()              -- TEXT
DBManager.TEXT('tiny')        -- TINYTEXT

Boolean

DBManager.BOOLEAN()           -- BOOLEAN

Numbers

DBManager.INT()               -- INT
DBManager.INT(11)             -- INT(11)

DBManager.INTEGER()           -- INTEGER

DBManager.DOUBLE()            -- DOUBLE
DBManager.DOUBLE(10)          -- DOUBLE(10)
DBManager.DOUBLE(10,2)        -- DOUBLE(10,2)

DBManager.BIGINT()            -- BIGINT
DBManager.BIGINT(20)          -- BIGINT(20)

DBManager.FLOAT()             -- FLOAT
DBManager.FLOAT(10)           -- FLOAT(10)
DBManager.FLOAT(10,2)         -- FLOAT(10,2)

DBManager.DECIMAL()           -- DECIMAL
DBManager.DECIMAL(10,2)       -- DECIMAL(10,2)

Dates

DBManager.DATE()              -- DATE
DBManager.DATETIME()          -- DATETIME
DBManager.TIME()              -- TIME
DBManager.DATEONLY()          -- DATEONLY

Others

DBManager.BLOB()              -- BLOB
DBManager.BLOB('tiny')        -- TINYBLOB

DBManager.BINARY()            -- BINARY
DBManager.BINARY(1)           -- BINARY(1)

UUIDs

UUID, which stands for Universally Unique Identifier, is a type of identifier that is used to uniquely identify objects or entities. The UUID standard was designed to ensure that these identifiers can be generated in a way that is both globally unique and highly unlikely to be duplicated by chance. UUIDs are commonly used in software development, particularly in distributed systems where data is stored across multiple servers, to ensure that each piece of data is uniquely identifiable.

Provides a resource for this. To use, call the function DBManager.UUID(), example:

local UUID = DBManager.UUID() -- UUID is a string, example: "12345678-1234-1234-1234-123456789012"

Column Options

Apart from specifying the column's data type, allowNull, and defaultValue options as mentioned earlier, several other options can be used when defining a column. Here are some examples:

-- When define a id field, the field will be auto increment and primary key
id = {
    type = DBManager.INTEGER(),
    primaryKey = true,
    autoIncrement = true
}

-- Define a text field with not null
name = {
    type = DBManager.TEXT(),
    allowNull = false,
}

-- Define a cpf field with not null and unique
cpf = {
    type = DBManager.TEXT(),
    allowNull = false,
    unique = true
}

-- Define a country field with not null and unique and default value
country = {
    type = DBManager.TEXT(),
    allowNull = false,
    defaultValue = "Brazil"
}

-- Define a country field with not null and unique and default value and onUpdate value with a function
position = {
    type = DBManager.TEXT(),
    defaultValue = { x = 0, y = 0, z = 0 },
    onUpdate = { getElementPosition(player) },
}

Model Querying

DB Manager provides various methods to assist querying your database for data.

This guide will show how to make the standard CRUD queries.

INSERT queries

A simple example:

-- Creating a new user with name LODS
local lods = Users:create({ firstName = "LODS", lastName = "Gray" })
print("LODS auto-generated ID: lods.id)

INSERT INTO ... (`id`, `firstName`, `lastName`) VALUES (1, "LODS", "Gray")

SELECT queries

You can read the whole table from the database with the findAll method:

local users = Users:findAll()
iprint("All users:", toJSON(users))

SELECT * FROM ...

Specifying attributes for SELECT queries

To select only some attributes, you can use the attributes option:

local users = Users:findAll({
    attributes = {'id', 'name'}
})

SELECT id, firstName FROM ...

NOTE: You can combine all options too.

UPDATE queries

Update queries also accept the where option, just like the read queries shown above.

-- Change everyone without a name to "LODS"
Users:update({ name = "LODS" }, {
    where = {
        id = 1
    }
})

UPDATE ... SET name = "LODS" WHERE id = 1

DELETE queries

Delete queries also accept the where option, just like the read queries shown above.

-- Delete everyone named "LODS"
Users:destroy({
    where = { name = "LODS" }
})

DELETE * FROM ... WHERE name = "LODS"

To destroy everything the TRUNCATE SQL can be used:

-- Truncate the table
Users:destroy({
    truncate = true
})

Ordering

DB Manager provides the order option to work with ORDER BY

Model:findAll({
    orderBy = 'id'
    order = 'DESC' -- [ASC|DESC] default: ASC
})

SELECT * FROM ... ORDER BY id DESC

Limites and Pagination

The limit and offset options allow you to work with limiting / pagination:

-- Fetch 4 instances/rows
Model:findAll({ limit = 4 })

-- Skip 2 instances/rows
Model:findAll({ offset = 2 })

-- Skip 5 instances and fetch the 5 after that
Model:findAll({ offset = 5, limit = 5 })

Model Querying - Finders

Finder methods are the ones that generate SELECT queries.

By default, the results of all finder methods are instances of the model class. This means that all data is searched in a local cache.

findAll

As you may recall from the previous tutorial, the findAll method is used to generate a standard SELECT query that retrieves all entries from a table, unless constrained by a WHERE clause or other similar restrictions.

findByPk

The findByPk method retrieves a single entry from a table, using the primary key provided as an argument.

local user = Users:findByPk(123)
-- It's primary key is 123

findOne

The findOne method retrieves the first entry that satisfies the optional query options, if provided.

local user = Users:findOne({ where = { name = "LODS" } })
if (not user) then
    error("User not found!")
else
    print("The name of user is: " user.name) -- LODS
end

Raw Queries

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the query method.

Example:

local conn = DBManager:new({ dialect = "sqlite" })

local result, numAffectedRows, lastInsertId = conn:query("UPDATE users SET age = 20 WHERE name = 'LODS'")
-- its will execute a dbQuery default