💾 数据库

Database 实体为程序员提供了一种通过脚本轻松访问 SQL 数据库的方法。

---

💂Authority

This class can only be spawned on 🟦 Server side.

🧑‍💻API Source

This page is auto-generated! The Functions, Properties and Events described here are defined in our GitHub's API Repository! Feel free to commit suggestions and changes to the source .json API files!

---

提示

Currently nanos world supports SQLite (3.50), MySQL (8.1) and PostgreSQL (17.5) out of the box.

🎒 示例

Server/Index.lua

-- 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)
	)
]])

-- Insert values in the table
local affected_rows = sqlite_db:Execute("INSERT INTO test VALUES (1, 'amazing')")
Console.Log("Affected Rows: " .. tostring(affected_rows))
-- Will output: 1

-- Selects the data
local rows = sqlite_db:Select("SELECT * FROM test")
Console.Log(NanosTable.Dump(rows))
-- Will output a table with all data from 'test'

-- Selects the data with filter
local rows_filter = sqlite_db:Select("SELECT * FROM test WHERE name = :0", "amazing")
Console.Log(NanosTable.Dump(rows_filter))
-- Will output a table with all data from 'test' where name matches 'amazing'

(Database.Execute) Inserts into database (SQLite)

local affected_rows = my_database:Execute("INSERT INTO MyTable VALUES (:0, ':1')", 123, "MyValue")

(Database.Select) Selects from a table (SQLite)

local rows = my_database:Select("SELECT * FROM MyTable WHERE name = :0 AND title = :1", "Val", "AnotherVal")

(Database.SelectAsync) Selects from a table asynchronously (SQLite)

my_database:SelectAsync("SELECT * FROM MyTable WHERE name = :0 AND title = :1", function(rows)
	Console.log(NanosTable.Dump(rows))
end, "Val", "AnotherVal")

提示

All requests are thread safe! 🥳

🛠 构造函数

Default Constructor

No description provided

local my_database = Database(database_engine, connection_string, pool_size?)

Parameters

Type	Name	Default	Description
DatabaseEngine	database_engine	Required parameter	数据库引擎
string	connection_string	Required parameter	用于创建并连接到数据库的连接字符串
integer	pool_size	10	同时调用多个查询时的连接池大小

备注

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.

🗿 静态函数

This class doesn't have own static functions.

🦠 函数

	Returns	Name	Description
		Close	关闭数据库
	integer, string	Execute	同步执行查询
		ExecuteAsync	异步执行查询
	table of table, string	Select	同步选择查询
		SelectAsync	异步执行选择查询

---

Close

关闭数据库

my_database:Close()

---

Execute

同步执行查询

local ret_1, ret_2 = my_database:Execute(query, parameters...?)

Parameters

Type	Parameter	Default	Description
string	query	Required parameter	要执行的查询
any	parameters...?	nil	要转义到查询中的参数序列

Returns

Type	Description
integer	受影响的行
string	错误（如果有）

Database.Execute Examples

Inserts into database (SQLite)

local affected_rows = my_database:Execute("INSERT INTO MyTable VALUES (:0, ':1')", 123, "MyValue")

---

ExecuteAsync

异步执行查询

my_database:ExecuteAsync(query, callback?, parameters...?)

Parameters

Type	Parameter	Default	Description
string	query	Required parameter	要执行的查询
function	callback?	nil	回调函数 with this format
any	parameters...?	nil	要转义到查询中的参数序列

---

Select

同步选择查询

local ret_1, ret_2 = my_database:Select(query, parameters...?)

Parameters

Type	Parameter	Default	Description
string	query	Required parameter	要执行的查询
any	parameters...?	nil	要转义到查询中的参数序列

Returns

Type	Description
table of table	获取到的行
string	错误（如果有）

Database.Select Examples

Selects from a table (SQLite)

local rows = my_database:Select("SELECT * FROM MyTable WHERE name = :0 AND title = :1", "Val", "AnotherVal")

---

SelectAsync

异步执行选择查询

my_database:SelectAsync(query, callback?, parameters...?)

Parameters

Type	Parameter	Default	Description
string	query	Required parameter	要执行的查询
function	callback?	nil	回调函数 with this format
any	parameters...?	nil	要转义到查询中的参数序列

Database.SelectAsync Examples

Selects from a table asynchronously (SQLite)

my_database:SelectAsync("SELECT * FROM MyTable WHERE name = :0 AND title = :1", function(rows)
	Console.log(NanosTable.Dump(rows))
end, "Val", "AnotherVal")

提示

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.

参数	默认值	描述
db/dbname		The database name (if the *.db file doesn't exist, it will be automatically created)
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

参数	默认值	描述
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.

参数	默认值	描述
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