anysql 0.2.1

A unified Dart database abstraction, setup generator, HTTP backend client, and driver layer for SQL and NoSQL databases.

anysql

anysql is a small Dart database abstraction for projects that want one application-facing API for SQL, NoSQL, direct database drivers, and backend proxy connections.

Use it when you want your app code to work with AnySqlConfig, AnySqlConnection, AnySqlResult, and AnySqlOptions instead of being tightly coupled to PostgreSQL, MySQL, SQLite, MongoDB, or a custom data service.

Important: direct database connections are for trusted Dart environments such as CLIs, backend services, workers, and tests. Flutter mobile apps should normally call a secure backend API instead of shipping production database credentials inside the app.

What This Package Includes

• A shared connection contract: AnySqlConnection.
• A shared driver contract: AnySqlDriver.
• Normalized query results: AnySqlResult.
• Connection settings for PostgreSQL, MySQL, SQLite, MongoDB, and custom backends: AnySqlConfig.
• App-level options files: AnySqlOptions.
• A ready-to-use JSON HTTP backend client: AnySqlHttpBackendClient.
• Built-in direct drivers:
  • PostgresAnySqlDriver
  • MysqlAnySqlDriver
  • SqliteAnySqlDriver
  • MongodbAnySqlDriver
• CLI setup commands:
  • dart run anysql init
  • dart run anysql configure

Install

dart pub add anysql

For Flutter:

flutter pub add anysql

Import the core API:

import 'package:anysql/anysql.dart';

Create your first options file interactively:

dart run anysql setup

The core API and HTTP backend client are suitable for Dart and Flutter native apps. This package currently includes native direct-driver dependencies, so it does not advertise browser/web support. A future package split can move direct drivers into a separate package and keep the core API web-safe.

Import the built-in direct database drivers only when you need them:

import 'package:anysql/anysql_drivers.dart';

Fastest Working Example

SQLite can run in memory, so this example works without any external database server:

import 'package:anysql/anysql.dart';
import 'package:anysql/anysql_drivers.dart';

Future<void> main() async {
  final connection = await AnySql.connect(
    config: AnySqlConfig.sqlite(database: ':memory:'),
    driver: const SqliteAnySqlDriver(),
  );

  try {
    await connection.query(
      'create table users (id integer primary key, email text not null)',
    );

    await connection.query(
      'insert into users (email) values (?)',
      parameters: AnySqlParameters.positional(['[e-mail]']),
    );

    final result = await connection.query('select id, email from users');
    print(result.rows);
  } finally {
    await connection.close();
  }
}

SQLite positional parameters use AnySqlParameters.positional.

Generate an Options File

For the easiest setup after installing the package, run:

dart run anysql setup

The command asks for your dialect, database, optional backend URL, and writes lib/anysql_options.dart.

Create an editable options file with sample configs for PostgreSQL, MySQL, SQLite, and MongoDB:

dart run anysql init

This creates:

lib/anysql_options.dart

Use one generated config like this:

import 'package:anysql/anysql.dart';
import 'package:anysql/anysql_drivers.dart';

import 'anysql_options.dart';

Future<void> main() async {
  final connection = await AnySqlOptionsFile.postgres.connect(
    driver: const PostgresAnySqlDriver(),
  );

  try {
    final result = await connection.query(
  'select id, email from users where id = @id',
      parameters: AnySqlParameters.named({'id': 1}),
    );

    print(result.firstOrNull);
  } finally {
    await connection.close();
  }
}

Configure One Database

Generate a smaller options file for one database:

dart run anysql configure \
  --dialect postgres \
  --host localhost \
  --database app \
  --username postgres \
  --password-env ANYSQL_PASSWORD

Run your app with the password as a Dart define:

dart -DANYSQL_PASSWORD=your_password run

Flutter uses:

flutter run --dart-define=ANYSQL_PASSWORD=your_password

Then connect from trusted Dart code:

import 'package:anysql/anysql.dart';
import 'package:anysql/anysql_drivers.dart';

import 'anysql_options.dart';

Future<void> main() async {
  final connection = await DefaultAnySqlOptions.connect(
    driver: const PostgresAnySqlDriver(),
  );

  try {
    final result = await connection.query('select now() as server_time');
    print(result.firstOrNull);
  } finally {
    await connection.close();
  }
}

Direct Connections

Direct connections are a good fit for Dart servers, command-line tools, background jobs, local scripts, and tests.

import 'package:anysql/anysql.dart';
import 'package:anysql/anysql_drivers.dart';

Future<void> main() async {
  final config = AnySqlConfig.postgres(
    host: 'localhost',
    database: 'app',
    username: 'postgres',
    password: const String.fromEnvironment('ANYSQL_PASSWORD'),
  );

  final connection = await AnySql.connect(
    config: config,
    driver: const PostgresAnySqlDriver(),
  );

  try {
    final users = await connection.query(
  'select id, email from users where active = @active',
      parameters: AnySqlParameters.named({'active': true}),
    );

    for (final row in users.rows) {
      print(row);
    }
  } finally {
    await connection.close();
  }
}

Flutter Mobile and Backend Access

Do not put production database usernames or passwords directly in Flutter mobile apps. Instead, keep the real database connection on your server and let the app call that server. Browser/web support is not advertised while the package includes native direct-driver dependencies.

anysql supports this with AnySqlBackendClient. For a JSON HTTP backend, use the built-in AnySqlHttpBackendClient:

final connection = await DefaultAnySqlOptions.connectBackend(
  client: AnySqlHttpBackendClient(),
);

final result = await connection.query(
  'users.findById',
  parameters: AnySqlParameters.document({'id': 1}),
);

AnySqlHttpBackendClient sends POST requests to backendUri with the statement, parameters, and non-secret config metadata. It does not send the database password from AnySqlConfig.

Your backend should return JSON in this shape:

{
  "rows": [{"id": 1, "email": "[e-mail]"}],
  "affectedRows": 0,
  "lastInsertId": null,
  "metadata": {"columns": ["id", "email"]}
}

You can also implement AnySqlBackendClient yourself when your backend uses a different protocol, authentication flow, or batching model.

Built-in Driver Notes

Driver Capability Matrix

Driver	Backing package	Best fit	Parameter style	Notes
PostgreSQL	postgres	Dart servers, CLIs, workers	named @id parameters	Supports SSL through sslEnabled
MySQL	mysql_client	Dart servers, CLIs, workers	mysql_client map parameters	options supports collation and timeoutMs
SQLite	sqlite3	local native apps, CLIs, tests	positional values list	:memory: is ideal for tests and examples
MongoDB	mongo_dart	Dart servers, CLIs, workers	document maps	Uses collection.operation statement names

The built-in direct drivers are intentionally thin adapters over established database packages. They normalize results and errors, but they do not hide the database engine or replace database-specific knowledge.

SQLite

final connection = await AnySql.connect(
  config: AnySqlConfig.sqlite(database: ':memory:'),
  driver: const SqliteAnySqlDriver(),
);

Use :memory: for tests or pass a file path such as app.sqlite.

PostgreSQL

final connection = await AnySql.connect(
  config: AnySqlConfig.postgres(
    host: 'localhost',
    database: 'app',
    username: 'postgres',
    password: const String.fromEnvironment('ANYSQL_PASSWORD'),
  ),
  driver: const PostgresAnySqlDriver(),
);

PostgreSQL named parameters use the @name syntax from package:postgres.

MySQL

final connection = await AnySql.connect(
  config: AnySqlConfig.mysql(
    host: 'localhost',
    database: 'app',
    username: 'root',
    password: const String.fromEnvironment('ANYSQL_PASSWORD'),
  ),
  driver: const MysqlAnySqlDriver(),
);

MySQL parameter behavior follows package:mysql_client.

MongoDB

final connection = await AnySql.connect(
  config: AnySqlConfig.mongodb(
    host: 'localhost',
    database: 'app',
  ),
  driver: const MongodbAnySqlDriver(),
);

final users = await connection.query(
  'users.find',
  parameters: AnySqlParameters.document({
    'filter': {'active': true},
  }),
);

MongoDB statements use collection.operation names. Supported operations are find, findOne, insertOne, insertMany, updateOne, updateMany, replaceOne, deleteOne, deleteMany, count, and aggregate.

Errors and Debugging

Built-in drivers throw AnySqlException subclasses:

• AnySqlConfigException for invalid or incomplete configuration.
• AnySqlDriverException when no registered driver supports a config.
• AnySqlConnectionException when a closed connection is used.
• AnySqlQueryException when the underlying database package rejects a query.

Query exceptions include a short statement preview but not parameter values, so logs are useful without accidentally printing secrets.

Transactions

AnySqlConnection.transaction commits when the callback completes and rolls back when it throws. The shared contract does not include nested transactions or savepoints; if you need those, use the underlying database package directly or add a custom driver behavior for your project.

Register Multiple Drivers

Register drivers when your app wants to choose the correct driver from a config:

final anySql = AnySql([
  const PostgresAnySqlDriver(),
  const MysqlAnySqlDriver(),
  const SqliteAnySqlDriver(),
  const MongodbAnySqlDriver(),
]);

final connection = await anySql.open(
  AnySqlConfig.sqlite(database: ':memory:'),
);

CLI Reference

Create one options file interactively:

dart run anysql setup

Create starter options:

dart run anysql init

Overwrite an existing generated file:

dart run anysql init --force

Configure PostgreSQL:

dart run anysql configure --dialect postgres --host localhost --database app

Configure SQLite:

dart run anysql configure --dialect sqlite --database app.sqlite

SQLite uses only --database. Network options such as --host, --port, --username, --password-env, and --ssl are for networked databases.

Show help:

dart run anysql --help

Common Errors Users Hit

• PostgresAnySqlDriver is not found: add import 'package:anysql/anysql_drivers.dart';.
• A Flutter app exposes credentials: move direct database access to a backend service and use AnySqlHttpBackendClient or a custom AnySqlBackendClient in the app.
• SQLite insert parameters do not bind: pass positional values with AnySqlParameters.positional([...]).
• PostgreSQL parameters do not bind: use @name placeholders and pass a map with the same names.
• dart run anysql configure --dialect sqlite --host ... fails: SQLite does not use network options.
• A generated file already exists: re-run the command with --force only when you really want to overwrite it.

More Documentation

See doc/driver_guide.md for a longer driver guide.

See example/main.dart for a runnable example that covers:

• a fake direct PostgreSQL-style driver,
• a fake backend/proxy client,
• a real in-memory SQLite database.

Reliability Notes

• AnySqlConfig validates required host, database, username, and port values.
• AnySqlConfig.toString() masks passwords.
• Generated configs can read passwords from String.fromEnvironment.
• Config maps and result rows are defensively copied.
• Drivers are explicit, so your app can see which database package is doing the real work.

Author

Created and maintained by Azrul Amaline.

• GitHub: Azrul16
• Repository: github.com/Azrul16/anysql

License

anysql is released under the MIT License.