πΎ Database
The Database entity provides programmers a way to access SQL databases easily through scripting.
info
π¦ Authority: This class can only be spawned on Server.
tip
Currently nanos world supports SQLite
, MySQL
and PostgreSQL
out of the box.
Exampleβ
-- 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 Parametersβ
Type | Name | Default | Beschreibung |
---|---|---|---|
DatabaseEngine | database_engine | Database Engine | |
string | connection_string | Connection String used to create and connect to the database | |
number | pool_size | 10 | Size of the connection pool when calling several queries simultaneously |
Functionsβ
Returns | Name | Beschreibung |
---|---|---|
Close | Closes the Database | |
Execute | Execute a query asyncronously | |
ExecuteSync | Execute a query syncronously | |
Select | Execute a select query asyncronously | |
SelectSync | Execute a select query syncronously |
Close
β
Closes the Database
my_database:Close()
Execute
β
Execute a query asyncronously
Callback will be in the format (rows_affected)
my_database:Execute(query, function(rows_affected)
-- Do Something...
end, parameters...)
Type | Parameter | Default Value | Beschreibung |
---|---|---|---|
string | query | Query to execute | |
function | callback | nil | Callback to call when finishes |
any | parameters... | nil | Sequence of parameters to escape into the Query |
ExecuteSync
β
Execute a query syncronously
Returns a number of
affected_rows
local affected_rows = my_database:ExecuteSync(query, parameters...)
Type | Parameter | Default Value | Beschreibung |
---|---|---|---|
string | query | Query to execute | |
any | parameters... | nil | Sequence of parameters to escape into the Query |
Select
β
Execute a select query asyncronously
Callback will be in the format (rows)
my_database:Select(query, function(rows)
-- Do something
end, parameters...)
Type | Parameter | Default Value | Beschreibung |
---|---|---|---|
string | query | Query to select | |
function | callback | nil | Callback to call when finishes |
any | parameters... | nil | Sequence of parameters to escape into the Query |
SelectedSync
β
Selects a query syncronously
local rows = my_database:SelectedSync(query, parameters...)
Type | Parameter | Default Value | Beschreibung |
---|---|---|---|
string | query | Query to select | |
any | parameters... | nil | Sequence 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.
Parameter | Default Value | Beschreibung |
---|---|---|
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 | Beschreibung |
---|---|---|
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 | Beschreibung |
---|---|---|
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 |
passwort | 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 |