KVP is fantastic, but there are drawbacks that make utilising it less than ideal for most people; relational data and GUI tools. So far the best results have come from utilising node to connect to the database while .NET connectors seem to have some issues with performance (not speaking about the general overhead, rather slower queries).
I have two different thoughts on this implementation, as I’m uncertain about what would be best.
1. Lua Library
Both JS and C# are able to utilise their own connectors or include packages which handle it for them. Including a library in the Lua SCRT, loaded with require (like glm and lmprof) wouldn’t be the strangest thing. I know there were considerations towards allowing the use of libraries on the server, but that may have been more Gottfried’s preference.
2. Natives
(This is well outside my “expertise” and I have no idea what I’m talking about)
Connector utilised internally on the server, with a native to execute a query; obviously requiring the current coroutine to await a result.
I obviously don’t expect this to be a simple solution, and perhaps it’s been considered and there is a sufficient reason to not do this. If so, I know that dblyr exists and I’d like to know if there’s a reason to prefer C# over JS; in which case we can rethink the current state of oxmysql.
There was some thought of binding to a few Go SQL libraries using a common API and a fake ScRT to implement some export-likes (as natives were seen as problematic here i.e. requiring too much glue code to be written).
Go was chosen as it’s the only language that has a) an ecosystem of non-blocking IO libraries for various RDBMSes using a common API and b) has somewhat safe/stable interop with C.
Using any of the extremely low-quality Lua libraries that exist now would be counterproductive and not solve anything here.
.NET performance with data connectivity really depends on how you’re connecting. If you’re using Entity Framework for an ORM approach, then if you aren’t careful then your expresions can result in table scans and other poorly performing queries. There may be general lag with EF but when you’re dealign with .NET 4.5 as a constraint to write resources for FiveM, then arguably that could be a push for CFX to update everything to .NET Core, if it’s an option… anyone?
You actually don’t have to use Entity Framework, you could go with vanilla ADO.NET or Dapper or others, there’s so many options, so please don’t just say that .NET is slow, it depends on how you go about it.
Just on EF, one of the key issues with it that may affect performance isn’t so much it’s overall speed, but you can’t have multiple threads accessing a single DbContext, you have to create and destroy per query, so there’s an overhead on the creation per query execution that isn’t about the running of the query itself. Again like I’ve said in the past architecture is a key thing. You could run a dedicated query processor thread for holding an open connection and throw non response queries like update, delete and insert at a queue which is processed by that thread. Queries couldn’t run like that as you’d need a response, not a fire and forget approach. I’m a fan of the diea of hydrating an entire database into an in memory domain model though and all actions act against that entirely meaning you’d never get any query lag. You’d have .NET events firing when that in memory model changes to persist changes to the database which would be happening in different threads. Database access shouldn’t be part of core functionality, rather seen as a cross cutting concern that should be outside general operation.
… server code isn’t ‘.NET 4.5’, neither is client ‘.NET 4.5’ either. Client is not even matching any ‘.NET’ version, server supports netstandard2.0 and will be moved to newer Mono versions that should support anything up to net6.0 once a memory corruption bug is fixed on their side.
Also, this user isn’t even talking about EF or any other ORM, but rather about some perceived design fluke with plain ADO.NET wrappers like mysql-async originally was, or the dblyr example resource.
No other parts of your reply are relevant at all in the context of the original post.