Create a new version of paste:

Placeholders
Placeholders allow for query parameters to be safely executed, preventing common SQL injection methods.

Parameters can be passed an an array or map (referred to as named named parameters).

local identifier = 'license:abc123'
local group = 'admin'
 
MySQL.scalar('SELECT `username` FROM `users` WHERE `identifier` = ? AND `group` = ?', { identifier, group })
 
-- Named placeholders (deprecated)
MySQL.scalar('SELECT `username` FROM `users` WHERE `identifier` = @identifier AND `group` = @group', {
    group = group
    identifier = identifier
})
These are distinct from prepared statements which are handled by the MySQL server; you can use MySQL.prepare for more optimised and secure queries.


Insert
Inserts a new entry into the database and returns the insert id for the row, if valid.

Promise
local id = MySQL.insert.await('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
    identifier, firstName, lastName
})
 
print(id)
Aliases

MySQL.Sync.insert
exports.ghmattimysql.executeSync
exports.oxmysql.insert_async
Callback
MySQL.insert('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
    identifier, firstName, lastName
}, function(id)
    print(id)
end)
Aliases

MySQL.Async.insert
exports.ghmattimysql.execute
exports.oxmysql.insert


Prepare
Prepare can be used to execute frequently called queries faster and accepts multiple sets of parameters to be used with a single query.

Date will not return the datestring commonly used in FiveM
TINYINT 1 and BIT will not return a boolean
You can only use ? value placeholders, ?? column placeholders and named placeholders will throw an error
Unlike rawExecute, the SELECT statement will return a column, row, or array of rows depending on the number of columns and rows selected.

Promise
local response = MySQL.prepare.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
 
 
print(json.encode(response, { indent = true, sort_keys = true }))
Aliases

exports.oxmysql.prepare_async
Callback
MySQL.prepare('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    print(json.encode(response, { indent = true, sort_keys = true }))
end)
Aliases

exports.oxmysql.prepare


Query
When selecting data, returns all matching rows and columns; otherwise, returns data like insertId, affectedRows, etc.

Promise
local response = MySQL.query.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
 
if response then
    for i = 1, #response do
        local row = response[i]
        print(row.firstname, row.lastname)
    end
end
Aliases

MySQL.Sync.fetchAll
exports.ghmattimysql.execute
exports.oxmysql.query_async
Callback
MySQL.query('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    if response then
        for i = 1, #response do
            local row = response[i]
            print(row.firstname, row.lastname)
        end
    end
end)
Aliases

MySQL.Async.fetchAll
exports.ghmattimysql.execute
exports.oxmysql.query


RawExecute
rawExecute can be used to execute frequently called queries faster and accepts multiple sets of parameters to be used with a single query.

Date will not return the datestring commonly used in FiveM
TINYINT 1 and BIT will not return a boolean
You can only use ? value placeholders, ?? column placeholders and named placeholders will throw an error
Unlike prepare, the SELECT statement will always return an array of rows. When using SELECT, the return value will match query, single, or scalar depending on the number of columns and rows selected.

Promise
local response = MySQL.rawExecute.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
})
 
 
print(json.encode(response, { indent = true, sort_keys = true }))
Aliases

exports.oxmysql.rawExecute_async
Callback
MySQL.rawExecute('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
    identifier
}, function(response)
    print(json.encode(response, { indent = true, sort_keys = true }))
end)
Aliases

exports.oxmysql.rawExecute


Scalar
Returns the first column for a single row.

Promise
local firstName = MySQL.scalar.await('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
})
 
print(firstName)
Aliases

MySQL.Sync.fetchScalar
exports.ghmattimysql.scalar
exports.oxmysql.scalar_async
Callback
MySQL.scalar('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
}, function(firstName)
    print(firstName)
end)
Aliases

MySQL.Async.fetchScalar
exports.ghmattimysql.scalar
exports.oxmysql.scalar


Single
Returns all selected columns for a single row.

Promise
local row = MySQL.single.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
})
 
if not row then return end
 
print(row.firstname, row.lastname)
Aliases

exports.oxmysql.single_async
Callback
MySQL.single('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
    identifier
}, function(row)
    if not row then return end
 
    print(row.firstname, row.lastname)
end)
Aliases

exports.oxmysql.single


Transaction
A transaction executes multiple queries and commits them only if all succeed.
If one fails, none of the queries are committed.

The return value is a boolean, which is the result of the transaction.

Specific format
When using this format, you must pass an array containing sets of queries and parameters to the transaction method.
In this case, your queries do not necessarily match and the values are unique to each query.

-- You might rename "values" as "parameters" for mysql-async compatibility.
local queries = {
    { query = 'INSERT INTO `test` (id) VALUES (?)', values = { 1 }},
    { query = 'INSERT INTO `test` (id, name) VALUES (?, ?)', values = { 2, 'bob' }},
}
 
-- You can also pass an array of arrays.
local queries = {
  { 'INSERT INTO `test` (id) VALUES (?)', { 1 } },
  { 'INSERT INTO `test` (id, name) VALUES (?, ?)', { 2, 'bob' } },
}
Shared format
When using this format, you must pass an array containing queries and a set containing shared parameters to the transaction method.
In this case, your queries do not necessarily match and the values are unique to each query.

-- You might rename "values" as "parameters" for mysql-async compatibility.
local queries = {
    'INSERT INTO `test` (id, name) VALUES (@someid, @somename)',
    'SET `name` = @newname IN `test` WHERE `id` = @someid'
}
 
local values = {
    someid = 2,
    somename = 'John Doe',
    newname = 'John Notdoe'
}
Promise
local success = MySQL.transaction.await(queries, values --[[leave nil for specific format]])
print(success)
Aliases

MySQL.Sync.transaction
exports.ghmattimysql.transaction
exports.oxmysql.transaction_async
Callback
-- specific
MySQL.transaction(queries, values, function(success)
    print(success)
end)
 
-- shared
MySQL.transaction(queries, function(success)
    print(success)
end)
Aliases

MySQL.Async.transaction
exports.ghmattimysql.transaction
exports.oxmysql.transaction
Transaction Isolation Level
This can be set through the convar mysql_transaction_isolation_level, and is an integer ranging from 1-4.
The default value is 2.

Convar Value	Result
1	Repeatable Read
2	Read Committed
3	Read Uncommitted
4	Serializable

Update
Returns the number of rows affected by the query.

Promise
local affectedRows = MySQL.update.await('UPDATE users SET firstname = ? WHERE identifier = ?', {
    newName, identifier
})
 
print(affectedRows)
Aliases

MySQL.Sync.execute
exports.ghmattimysql.executeSync
exports.oxmysql.update_async
Callback
MySQL.update('UPDATE users SET firstname = ? WHERE identifier = ?', {
    newName, identifier
}, function(affectedRows)
    print(affectedRows)
end)
Aliases

MySQL.Async.execute
exports.ghmattimysql.execute
exports.oxmysql.update