Skip to main content
Version: latest - a1.19.x βš–οΈ

πŸ’Ύ Database

The Database entity provides programmers a way to access SQL databases easily through scripting.

πŸ’‚Authority
This class can only be spawned on 🟦 Server.
tip

Currently nanos world supports SQLite, MySQL and PostgreSQL out of the box.

πŸŽ’ Examples​

-- Creates a SQLite connection, using a local file called 'database_filename.db'
local sqlite_db = Database(DatabaseEngine.SQLite, "db=database_filename.db timeout=2")

-- Creates a table
sqlite_db:Execute([[
CREATE TABLE IF NOT EXISTS test (
id INTEGER,
name VARCHAR(100)
)
]])

-- Executes a Query
sqlite_db:Execute("INSERT INTO test VALUES (1, 'amazing')", function(affected_rows)
Package.Log("Affected Rows: " .. tostring(affected_rows))
-- Will output: 1
end)

-- Selects the data
sqlite_db:Select("SELECT * FROM test", function(rows)
Package.Log(NanosUtils.Dump(rows))
-- Will output a table with all data from 'test'
end)

-- Selects the data with filter
sqlite_db:Select("SELECT * FROM test WHERE name = :0", function(rows)
Package.Log(NanosUtils.Dump(rows))
-- Will output a table with all data from 'test' where name matches 'amazing'
end, 'amazing')
tip

All requests are thread safe! πŸ₯³

πŸ—οΈ Constructor​

local my_database = Database(database_engine, connection_string, pool_size?)
TypeNameDefaultDescription
DatabaseEnginedatabase_engineDatabase Engine
string connection_stringConnection String used to create and connect to the database
number pool_size10Size of the connection pool when calling several queries simultaneously

🦠 Functions​

ReturnsNameDescription
CloseCloses the Database
ExecuteExecute a query asyncronously
number string ExecuteSyncExecute a query syncronously
SelectExecute a select query asyncronously
table of table string SelectSyncSelects a query syncronously

Close​

Closes the Database
my_database:Close()

Execute​

Execute a query asyncronously
my_database:Execute(query, callback?, parameters...?)
TypeParameterDefaultDescription
string queryQuery to execute
function callback?nilCallback in the format (rows_affected, error)
any parameters...?nilSequence of parameters to escape into the Query

ExecuteSync​

Execute a query syncronously

Returns number string (affected rows, error (if any))
local ret_01, ret_02 = my_database:ExecuteSync(query, parameters...?)
TypeParameterDefaultDescription
string queryQuery to execute
any parameters...?nilSequence of parameters to escape into the Query

Select​

Execute a select query asyncronously
my_database:Select(query, callback?, parameters...?)
TypeParameterDefaultDescription
string queryQuery to execute
function callback?nilCallback in the format (rows: table[], error: string?)
any parameters...?nilSequence of parameters to escape into the Query

SelectSync​

Selects a query syncronously

Returns table of table string (rows fetched, error (if any))
local ret_01, ret_02 = my_database:SelectSync(query, parameters...?)
TypeParameterDefaultDescription
string queryQuery to execute
any parameters...?nilSequence of parameters to escape into the Query

🧡 Connection String​

Each Database Engine has it's own parameters which can be used on the connection_string constructor. Those parameters are defined and backend-dependent by the Engine, being passed directly to the Backend when creating the connection.

They should be set in the following format: "param1=value1 param2=value2 param3=value3".

tip

Usually you don't need to explicitly define all (or most) of the parameters described here, just use the ones you make sure are useful for your needs. Some of them are described by the libraries but aren't 100% tested in nanos world.

▢️ SQLite​

tip

There is a special connection_string for SQLite: :memory:. This will create a database in the memory which is destroyed when the server closes.

ParameterDefault ValueDescription
db/dbnameThe database name
timeout0set sqlite busy timeout (in seconds) (link)
readonlyfalseopen database in read-only mode instead of the default read-write (note that the database file must already exist in this case, see the documentation)
synchronousset the pragma synchronous flag (link)
shared_cacheshould be true (link)
vfsset the SQLite VFS used to as OS interface. The VFS should be registered before opening the connection, see the documentation

▢️ MySQL​

ParameterDefault ValueDescription
db/dbnameThe database name
userUser name to connect as
password/passPassword to be used if the server demands password authentication
hostName of host to connect to
portPort number to connect to at the server host
unix_socket
sslca
sslcert
local_infileshould be 0 or 1, 1 means MYSQL_OPT_LOCAL_INFILE will be set
charset
reconnect0if set to 1, will attempt to reconnect on connection loss
connect_timeoutshould be positive integer value that means seconds corresponding to MYSQL_OPT_CONNECT_TIMEOUT
read_timeoutshould be positive integer value that means seconds corresponding to MYSQL_OPT_READ_TIMEOUT
write_timeoutshould be positive integer value that means seconds corresponding to MYSQL_OPT_WRITE_TIMEOUT

▢️ PostgreSQL​

More parameters and complete information can be found at the PostgreSQL Official Documentation.

ParameterDefault ValueDescription
hostName of host to connect to
hostaddrNumeric IP address of host to connect to
portPort number to connect to at the server host
usersame as OS user nameUser name to connect as
dbnamesame as user nameThe database name
passwordPassword to be used if the server demands password authentication
connect_timeout0Maximum wait for connection, in seconds
optionsCommand-line options to be sent to the server