MS SQL Server Connector [Flutter integration]

Question

I'm looking to implement a function to connect to SQL Server in a Flutter Desktop app and looking for some best practices on how to do this. The requirement is that this runs locally (mentioning this because I've seen the advice to build a web server and make an HTTP call from the Flutter app). Given the lack of Flutter/Dart libraries to connect to MS SQL Server, I would write that part of the code in another language and then integrate that into Flutter.

Different patterns I've considered:

• Write it in C++ as a flutter Plugin. Example
• Write it in Python, build it to an exe and call that exe from Flutter, call that exe with command line arguments, and pass the result back to Flutter/Dart. Using something like PyInstaller to package the Python
• Write it in Python but as part of a local Flask app. Launch that when Flutter starts up, and call the API Here is the SO post with the recommendation to launch a web server. DO this, but locally

Options 2 and 3 seem more hackish than the first. Is there some guidance around design patterns or for flutter apps generally, or does anyone have experience implementing something like this?

Answer 1

I faced the same problem few weeks ago: in my case, luckily, I had to run on the SQL Server some specific queries with very limited options regarding the WHERE clause.
Since there is little to no support at the moment and I had no time to write a plugin for that, I ended up writing a small Java app with no GUI that takes as arguments the necessary parameters to run the different queries, outputs any relevant information on the standard error and the results of the query on the standard output (formatted as JSON), and returns a different integer values in case of errors. Having control on both sides of the code makes things really easier than what I initially thought.

This is an excerpt of the flutter code that invokes the Java app:

var process = await Process.run(javaExe, [
  "-jar",
  "jConnector/app.jar",
  cfg.ip,
  cfg.port,
  cfg.query,
  cfg.whereClause
]);

List<String> stdoutLines = process.stdout.split("\r\n"); //saves the JSON output, parsed later to retrieve the query results

_logger.e(process.stderr.toString()); //logs any info coming from the Java into the main logger of my flutter app

var exitCode = process.exitCode; //uses this return value to detect any errors happened on the Java side

I chose Java out of personal preference, not any real convenience in doing so. I hope this can give you some tips.