Sqflite Gen
Open source code generator for unified sqlite database access for Flutter & Dart.
Overview
The goal of this package is to support the developer by taking over the repetitive tasks of rewriting database access
layers when using the package sqflite. It parses the sql create script and generates models
provider
and consts for accessing all tables. It also creates a database access layer and handles the automatic creating of all
tables on first run of the app.
It handles automatic type mapping for boolean, Uint8List and DateTime values.
The generated source follows the best principles as provided by sqflite (see section "SQL helpers").
Installation 💻
❗ In order to start using Sqflite Gen you must have the Dart SDK installed on your machine.
Install via dart pub add:
dart pub add sqflite_gen
Then run:
dart pub get
or
flutter pub get
Now SqfliteGen will generate the database access layer files for you by running:
dart run build_runner build
Configuration
In order to make the package generate the required source code please place a file with the ending .sql in the
assets directory of your app. The file must include all create table statements for the database.
All generated files will be placed in/under the directory lib/db.
Overview
Autoincrement primary keys are supported.
For each table a subdirectory will be created in lib/db/tables. The package creates for each table a data model
table access provider and constants for unified access of table and fields.
The model file is named like the database table. It represents a table contains a property for each column of
that table. It also contains the methods for converting to and from map and a copyWith method to create a new clone.
The provider class is named like the database table with the suffix Provider. It allows basic access to a table by
providing methods for crud operations: insert
get
update and delete. For convenience
it also provides a
getAll method which returns all record of the table.
Further constants are created for encapsulate the table name and the names of all columns.
Usage examples
Import files for getting database access (replace the example_app with references to your app)
import 'package:example_app/db/database.dart';
import 'package:example_app/db/db.dart';
Opening a database
Opening the database also contains a basic migration mechanism. This is currently only used to create all tables on the fly when opening the database for the first time. Afterward the database structure is not changed anymore.
const dbName = 'test.db';
late Database database;
.
.
.
database = await openDatabaseWithMigration(dbName);
Many applications use one database and would never need to close it (it will be closed when the application is terminated). If you want to release resources, you can close the database.
await database.close();
Accessing database tables
Each table is represented by a model and a provider class. They encapsulate access in a typesafe way.
For the following examples we assume that the database table will look like this:
CREATE TABLE Test(
id INTEGER PRIMARY KEY AUTOINCREMENT,
text VARCHAR NOT NULL
);
Insert
final record = Test(text: 'This is a test');
final table = TestProvider(database);
final insertedRecord = await table.insert(record);
log(insertedRecord.id.toString());
Please note that the insert method automatically handles the autoincrement column id (when value is not provided
explicitly). insert also returns a clone of the original record containing the value of the autoincrement column
id (as given by the database).
Select
The get method expected a value for the primary key.
final table = TestProvider(database);
final record = await table.get(1);
log(insertedRecord.text);
Update
final table = TestProvider(database);
final record = await table.get(1);
final changedRecord = record.copyWith(text: 'Changed text');
await table.update(changedRecord);
Delete
The delete method expected a value for the primary key.
final table = TestProvider(database);
final success = await table.delete(1);
log(success ? 'Successfully deleted' : 'Failing deleting record');
Handling null values
Columns marked as nullable are represented as nullable fields in the model class.
CREATE TABLE Test(
id INTEGER PRIMARY KEY AUTOINCREMENT,
text VARCHAR
);
This is an issue for the copyWith method as the given value can now basically represent 3 different actions:
- Set value to new value
- Set value to null
- Keep value
To support this nullable fields are wrapped in the copyWith method:
// Initialize model
final record = Test(text: 'Original text');
log(record.text); // prints Original text
// Change nullable column text to a different string
final changedRecord = record.copyWith(text: Wrapped.value('New text'));
log(record.text); // prints New text
// Keep value of column text
final otherRecord = record.copyWith(); // same as: record.copyWith(text: null)
log(record.text); // prints Original text
// Change nullable column text to null value
final nulledRecord = record.copyWith(text: Wrapped.value(null));
log(record.text); // prints <null>
Supported SQLite types
No validity check is done on values yet so please avoid non-supported types https://www.sqlite.org/datatype3.html
| Example Typenames From The CREATE TABLE Statement or CAST Expression | Generated Dart Field Type |
|---|---|
| INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 |
int |
| CHARACTER VARCHAR VARYING CHARACTER NCHAR NATIVE CHARACTER NVARCHAR TEXT CLOB STRING |
String |
| BLOB | Uint8List |
| BOOL | bool |
| DATE DATETIME |
double |
| REAL DOUBLE DOUBLE PRECISION FLOAT NUMERIC DECIMAL |
DateTime |
More information on supported types here.
Continuous Integration 🤖
Sqflite Gen uses a built-in GitHub Actions workflow powered by Very Good Workflows.
Out of the box
on each pull request and push
the CI formats
lints
and tests the code. This ensures the code
remains consistent and behaves correctly as you add functionality or make changes. The project uses
Very Good Analysis for a strict set of analysis options.
Code coverage is enforced using the Very Good Workflows.
Running Tests 🧪
To run all unit tests:
dart pub global activate coverage 1.2.0
dart test --coverage=coverage
dart pub global run coverage:format_coverage --lcov --in=coverage --out=coverage/lcov.info
To view the generated coverage report you can use lcov.
# Generate Coverage Report
genhtml coverage/lcov.info -o coverage/
# Open Coverage Report
open coverage/index.html
Libraries
- builder
- sqflite_gen
- Declaration of library