What is the recommended way to set up a Flutter mobile device to perform CRUD operations in a remote SQL Server instance?

I read somewhere that trying to connect Flutter directly to SQL Server is unwise because the point of Flutter is to be able to work with multiple backends. If this is true, then what is the recommended way to do this?

One thing I've read about is to build a backend that Flutter can communicate with and then have the backend point to SQL Server, but I have no clue how to get started on this.

What I've done:

I've already tried this flutter package called sql_conn and it works, but apparently the JDBC driver it uses hasn't been updated in around 10 years. That worries me because I'm going to be deploying this in a production env.

import 'dart:async';
import 'package:flutter/material.dart';
import 'package:sql_conn/sql_conn.dart';
void main() {
WidgetsFlutterBinding.ensureInitialized();
runApp(MyApp());
}
class MyApp extends StatelessWidget {
@override
Widget build(BuildContext context) {
return const MaterialApp(
title: "Test",
home: TestPage(),
);
}
}
class TestPage extends StatefulWidget {
const TestPage({Key? key}) : super(key: key);
@override
_TestPageState createState() => _TestPageState();
}
class _TestPageState extends State<TestPage> {
Future<void> connect(BuildContext ctx) async {
debugPrint("Connecting...");
try {
showDialog(
context: context,
builder: (context) {
return const AlertDialog(
title: Text("LOADING"),
content: CircularProgressIndicator(),
);
},
);
await SqlConn.connect(
ip: "192.168.128.176",
port: "1433",
databaseName: "MDCData",
username: "AS",
password: "112233");
debugPrint("Connected!");
} catch (e) {
debugPrint(e.toString());
} finally {
Navigator.pop(context);
}
}
Future<void> read(String query) async {
var res = await SqlConn.readData(query);
debugPrint(res.toString());
}
Future<void> write(String query) async {
var res = await SqlConn.writeData(query);
debugPrint(res.toString());
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text('Plugin example app'),
),
body: Center(
child: Column(
mainAxisAlignment: MainAxisAlignment.center,
crossAxisAlignment: CrossAxisAlignment.center,
children: [
ElevatedButton(
onPressed: () => connect(context),
child: const Text("Connect")),
ElevatedButton(
onPressed: () => read("SELECT * FROM IP_List"),
child: const Text("Read")),
ElevatedButton(
onPressed: () => write("DELETE FROM IP_List WHERE LOC='vv1'"),
child: const Text("Write")),
ElevatedButton(
onPressed: () => write(
"CREATE TABLE Persons (PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255),City varchar(255))"),
child: const Text("Create Table")),
ElevatedButton(
onPressed: () => write(
"DROP TABLE Persons"),
child: const Text("Delete Table")),
ElevatedButton(
onPressed: () => SqlConn.disconnect(),
child: const Text("Disconnect"))
],
),
));
}
}

Typically it is bad practice to have any type of application connect directly to a database. And the reasoning for flutter to be database agnostic, is not why you shouldn't connect directly to SQL Server, it is just saying you don't have to only use SQL Server.

But the preferred practice is on project scope level you have flutter -> API -> SQL Server. The API layer is what would handle the flutter requests and manage getting/sending data to SQL Server. As for the best option that is completely up to you, since you will have to host the API somewhere, if you have a website you can have it "live" on the same host, with a subdomain or port number difference. But as for what to build the api in there are plenty of options, NodeJS Express, Deno, Flask, ASP .NET, etc.