💾 Database
The Database entity provides programmers a way to access SQL databases easily through scripting.
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)
Console.Log("Affected Rows: " .. tostring(affected_rows))
-- Will output: 1
end)
-- Selects the data
sqlite_db:Select("SELECT * FROM test", function(rows)
Console.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)
Console.Log(NanosUtils.Dump(rows))
-- Will output a table with all data from 'test' where name matches 'amazing'
end, 'amazing')
All requests are thread safe! 🥳
🛠 Constructors
Default Constructor
local my_database = Database(database_engine, connection_string, pool_size?)
Type | Name | Default | Description |
---|---|---|---|
DatabaseEngine | database_engine | Database Engine | |
string | connection_string | Connection String used to create and connect to the database | |
integer | pool_size | 10 | Size of the connection pool when calling several queries simultaneously |
The initial connection to the Database (when it's being constructed) is made on the main thread, so expect the server hanging for a few seconds during that.
If the Database fails to connect, it will spit an error on console and will return nil
.
🗿 Static Functions
This entity doesn't have own static functions.🦠 Functions
Returns | Name | Description | |
---|---|---|---|
Close | Closes the Database | ||
ExecuteAsync | Execute a query asyncronously | ||
integerstring | Execute | Execute a query syncronously | |
SelectAsync | Execute a select query asyncronously | ||
table of tablestring | Select | Selects a query syncronously |

Close
Closes the Database
my_database:Close()

ExecuteAsync
Execute a query asyncronously
my_database:ExecuteAsync(query, callback?, parameters...?)
Type | Parameter | Default | Description |
---|---|---|---|
string | query | Query to execute | |
function | callback? | nil | Callback in the format (rows_affected, error) |
any | parameters...? | nil | Sequence of parameters to escape into the Query |

Execute
Execute a query syncronously
— Returns integerstring (affected rows, error (if any)).
local ret_01, ret_02 = my_database:Execute(query, parameters...?)
Type | Parameter | Default | Description |
---|---|---|---|
string | query | Query to execute | |
any | parameters...? | nil | Sequence of parameters to escape into the Query |

SelectAsync
Execute a select query asyncronously
my_database:SelectAsync(query, callback?, parameters...?)
Type | Parameter | Default | Description |
---|---|---|---|
string | query | Query to execute | |
function | callback? | nil | Callback in the format (rows: table[], error: string?) |
any | parameters...? | nil | Sequence of parameters to escape into the Query |

Select
Selects a query syncronously
— Returns table of tablestring (rows fetched, error (if any)).
local ret_01, ret_02 = my_database:Select(query, parameters...?)
Type | Parameter | Default | Description |
---|---|---|---|
string | query | Query to execute | |
any | parameters...? | nil | Sequence of parameters to escape into the Query |
When passing arguments to a query, use the following syntax: :?
where ? is the placeholder argument (i.e. :0) passed into the function.
For a more in-depth example see Examples
🧵 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"
.
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
There is a special connection_string for SQLite: :memory:
. This will create a database in the memory which is destroyed when the server closes.
Parameter | Default Value | Description |
---|---|---|
db/dbname | The database name | |
timeout | 0 | set sqlite busy timeout (in seconds) (link) |
readonly | false | open 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) |
synchronous | set the pragma synchronous flag (link) | |
shared_cache | should be true (link) | |
vfs | set the SQLite VFS used to as OS interface. The VFS should be registered before opening the connection, see the documentation |
▶ MySQL
Parameter | Default Value | Description |
---|---|---|
db/dbname | The database name | |
user | User name to connect as | |
password/pass | Password to be used if the server demands password authentication | |
host | Name of host to connect to | |
port | Port number to connect to at the server host | |
unix_socket | ||
sslca | ||
sslcert | ||
local_infile | should be 0 or 1 , 1 means MYSQL_OPT_LOCAL_INFILE will be set | |
charset | ||
reconnect | 0 | if set to 1 , will attempt to reconnect on connection loss |
connect_timeout | should be positive integer value that means seconds corresponding to MYSQL_OPT_CONNECT_TIMEOUT | |
read_timeout | should be positive integer value that means seconds corresponding to MYSQL_OPT_READ_TIMEOUT | |
write_timeout | should 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.
Parameter | Default Value | Description |
---|---|---|
host | Name of host to connect to | |
hostaddr | Numeric IP address of host to connect to | |
port | Port number to connect to at the server host | |
user | same as OS user name | User name to connect as |
dbname | same as user name | The database name |
password | Password to be used if the server demands password authentication | |
connect_timeout | 0 | Maximum wait for connection, in seconds |
options | Command-line options to be sent to the server |