v1.9.0 - lib/MySQL and export deprecation

[thelindat]

Though this was going to be part of v2.0.0 I want to get some changes to the resource out ASAP to improve functionality, performance, etc.

This update serves as a good transition period for people to begin swapping calls to exports.oxmysql and use the syntax provided by @oxmysql/lib/MySQL.lua. I'll be working on documentation changes soon, but you can reference this issue for converting your queries. If you absolutely don't want to use the lib (it's recommended at this point) you can use the new _async exports, but you'll get better performance otherwise.

You can utilise either mysql-async syntax with complete backwards compatibility, or use the more accurate oxmysql syntax, i.e.

MySQL.Async.fetchAll() --[[same as]] MySQL.query()
MySQL.Sync.fetchAll()  --[[same as]] MySQL.query.await()

Summary

• fetch and fetchSync have been removed
  • They were deprecated months ago, and were synonymous with execute
• Exports with the sync suffix were renamed to _async
  • JS didn't have async retvals and the Lua runtime would await promises; the "sync" terminology comes from mysql-async
• execute was replaced with query

Javascript

Obviously the Lua library isn't an option for you; I've considered making a node_module so you can do MySQL.fetchScalar, MySQL.execute etc. for the slightly easier syntax and benefit of annotations, but don't expect anything.

For now you can continue using exports, though you might want to replace all and change executeSync to execute_async or whatever else.

Functions and counterparts

The exports used for ghmattimysql and previous versions of oxmysql. The header is the correct syntax to use with lib/MySQL.

MySQL.update / MySQL.Async.execute

Returns the number of affected rows

exports.oxmysql:update(query, parameters, cb)
exports.ghmattimysql:update(query, parameters, cb)

MySQL.update.await / MySQL.Sync.execute

Returns the number of affected rows

exports.oxmysql:updateSync(query, parameters)
exports.ghmattimysql:updateSync(query, parameters)

MySQL.query / MySQL.Async.fetchAll

Returns an array of matching rows or result data

exports.oxmysql:execute(query, parameters, cb)
exports.ghmattimysql:execute(query, parameters, cb)

MySQL.query.await / MySQL.Sync.fetchAll

Returns an array of matching rows or result data

exports.oxmysql:executeSync(query, parameters)
exports.ghmattimysql:executeSync(query, parameters)

MySQL.scalar / MySQL.Async.fetchScalar

Returns the value of the first column in a single row

exports.oxmysql:scalar(query, parameters, cb)
exports.ghmattimysql:scalar(query, parameters, cb)

MySQL.scalar.await / MySQL.Sync.fetchScalar

Returns the value of the first column in a single row

exports.oxmysql:scalarSync(query, parameters)
exports.ghmattimysql:scalarSync(query, parameters)

MySQL.insert / MySQL.Async.insert

Returns the row id of the inserted value

exports.oxmysql:insert(query, parameters, cb)
exports.ghmattimysql:insert(query, parameters, cb)

MySQL.insert.await / MySQL.Sync.insert

Returns the row id of the inserted value

exports.oxmysql:insertSync(query, parameters)
exports.ghmattimysql:insertSync(query, parameters)

MySQL.transaction / MySQL.Async.transaction

Returns true if the transaction succeeds

exports.oxmysql:transaction(query, parameters, cb)
exports.ghmattimysql:transaction(query, parameters, cb)

MySQL.transaction.await / MySQL.Sync.transaction

Returns true if the transaction succeeds

exports.oxmysql:transactionSync(query, parameters)
exports.ghmattimysql:transactionSync(query, parameters)

OxMySQL only

These functions have no counterparts in mysql-async and ghmattimysql.

MySQL.single / MySQL.Async.fetchSingle

Returns the value of a single row

exports.oxmysql:single(query, parameters, cb)

MySQL.single.await / MySQL.Sync.fetchSingle

Returns the value of a single row

exports.oxmysql:singleSync(query, parameters)

MySQL.prepare / MySQL.Async.prepare

The return type will differ based on the query submitted.
Parameters can be a single table containing placeholders (perform one query) or contain multiple tables with a set of placeholders.

MySQL.prepare('SELECT * FROM users WHERE firstname = ?', {{'Dunak'}, {'Linden'}, {'Luke'}}, function(result) end)
MySQL.prepare('SELECT * FROM users WHERE firstname = ?', {'Linden'}, function(result) end)

exports.oxmysql:prepare(query, parameters, cb)

MySQL.prepare.await / MySQL.Sync.prepare

The return type will differ based on the query submitted.
Parameters can be a single table containing placeholders (perform one query) or contain multiple tables with a set of placeholders.

result = MySQL.prepare.await('SELECT * FROM users WHERE firstname = ?', {{'Dunak'}, {'Linden'}, {'Luke'}})
result = MySQL.prepare.await('SELECT * FROM users WHERE firstname = ?', {'Linden'})

exports.oxmysql:prepareSync(query, parameters)

Replace all

Use VSCode, Nodepad++ or whatever you use to edit and utilise Replace All within your resource directory.

See comment below for a list of function aliases.

[thelindat]

Upon request I've added aliases for functions in lib/MySQL. You can use either, and some exports are no longer being deprecated to support this syntax (if you want to continue utilising exports directly, though it's not recommended).

exports.oxmysql:query (previously exports.oxmysql:execute)
MySQL.Async.fetchAll = MySQL.query
MySQL.Sync.fetchAll = MySQL.query.await


exports.oxmysql:scalar
MySQL.Async.fetchScalar = MySQL.scalar
MySQL.Sync.fetchScalar = MySQL.scalar.await


exports.oxmysql:single
MySQL.Async.fetchSingle = MySQL.single
MySQL.Sync.fetchSingle = MySQL.single.await


exports.oxmysql:insert
MySQL.Async.insert = MySQL.insert
MySQL.Sync.insert = MySQL.insert.await


exports.oxmysql:update
MySQL.Async.execute = MySQL.update
MySQL.Sync.execute = MySQL.update.await


exports.oxmysql:transaction
MySQL.Async.transaction = MySQL.transaction
MySQL.Sync.transaction = MySQL.transaction.await


exports.oxmysql:prepare
MySQL.Async.prepare = MySQL.prepare
MySQL.Sync.prepare = MySQL.prepare.await

[IdrisDose]

Just double checking, reading that I can call MySQL.Async.fetchAll OR MySQL.query to achieve the same result?

[thelindat]

Yes, they call the same export.

[dunak-debug]

Just double checking, reading that I can call MySQL.Async.fetchAll OR MySQL.query to achieve the same result?

Yes as Linden said.

MySQL.query is syntax provided by us and MySQL.Async.fetchAll is alias for mysql-async compatibility

[zenoualex]

Hello, Store is not supported on this version?

[thelindat]

Hello, Store is not supported on this version?

Looks pretty supported here, unless you're using JS in which case no - we found no benefit to storing some strings in a table; but store was never supported for JS with oxmysql.

[Flynn-Inspired]

exports.oxmysql:fetchSync I can't find what I should replace this with.
Do you help me please

[SmithyDevelopment]

Hello, I have the latest QBCore running, and trying to convert the older exports:oxmysql to MySQL But its just not working, we just get the same error stating attempt to index a nil value (global 'MySQL')

[Drift91]

Hello, I have the latest QBCore running, and trying to convert the older exports:oxmysql to MySQL But its just not working, we just get the same error stating attempt to index a nil value (global 'MySQL')

@SmithyPD Did you add "@oxmysql/lib/MySQL.lua" to your fxmanifest.lua under the "server_scripts" section and set oxmysql as a dependency?

dependency 'oxmysql'
server_script "@oxmysql/lib/MySQL.lua"

[Drift91]

I have several old resources that are built around mysql-async and ghmattimysql. Can I just update the fxmanifest.lua of these resources without altering any of the scripts or will that break functionality? I do understand that changing the code will increase performance of the queries, but I'd rather get around to that at a later time.

[Timo43]

hi, where can i find version 2.0.0 of oxmysql? because ox inventory gives me this error: ox_inventory requires version '2.0.0' of 'oxmysql' (current version: 1.9.2). thank you