Database functions #

Warning: making changes to the database tables directly is not recommended. It can lead to various system parts failing to work properly if supplied with inconsistent data.

For most use cases consider using storage.

Core functions #

db:execute(query) #

Execute the given query.

db:escape(value)

Escape given value so it can be used in a query.

db:getlastautoid()

Get the automatically generated ID of the most recent successful INSERT query.

db:query(query, ...)

Execute the given query, question marks in the query are replaced by additional parameters.

INSERT/UPDATE/DELETE helpers #

Note: Lua tables passed in values and where must not have fields that are not present in the given database table. Otherwise the query will fail.

db:insert(tablename, values) #

Perform INSERT query based on given values.

db:update(tablename, values, where) #

Perform UPDATE query based on given values and where parameters.

db:delete(tablename, where) #

Perform DELETE query based on where parameter.

SELECT helpers #

Note: parameters must be passed in the same way as for db:query() function.

db:getone(query, ...) #

Get the first field of the first matching row from the given query.

db:getrow(query, ...) #

Get the first matching row from the given query.

db:getlist(query, ...) #

Get the complete query result as a Lua table, where each table item is the first field from each row.

db:getall(query, ...) #

Get the complete query result as a Lua table, where each table item is a Lua table with field -> value mapping.

Examples #

-- Query parameter replacement
db:query('UPDATE table SET field=? WHERE id=?', 'test', 42)

-- INSERT INTO table (id, value) VALUES (42, 'test')
db:insert('table', {
  id = 42,
  value = 'test',
})

-- UPDATE table SET value='test' WHERE id=42
db:update('table', { value = 'test' }, { id = 42 })

-- DELETE FROM table WHERE id=42
db:delete('table', { id = 42 })