sqlcool 4.3.1

  • Readme
  • Changelog
  • Example
  • Installing
  • 91

Sqlcool #

pub package Build Status Coverage Status

A database helper library for Sqflite. Forget about implementation details and focus on the business logic.

  • Simple: easy api for crud operations
  • Reactive: stream of changes, select bloc
  • Adaptative: plug custom models into the database

Check the documentation or the api doc for usage instructions

Simple crud #

Define the database schema #

   import 'package:sqlcool/sqlcool.dart';

   Db db = Db();
   // define the database schema
   DbTable category = DbTable("category")..varchar("name", unique: true);
   DbTable product = DbTable("product")
      ..varchar("name", unique: true)
      ..text("descripton", nullable: true)
      ..foreignKey("category", onDelete: OnDelete.cascade)
   List<DbTable> schema = [category, product];

Initialize the database #

   String dbpath = "db.sqlite"; // relative to the documents directory
   try {
     await db.init(path: dbpath, schema: schema);
   } catch(e) {

Insert #

   final Map<String, String> row = {name: "My item"};
   try {
     int id = await db.insert(table: "category", row: row)
   } catch(e) {

Select #

   try {
     List<Map<String, dynamic>> rows = await db.select(
       table: "product",
       limit: 20,
       columns: "id,name",
       where: "name LIKE '%something%'",
       orderBy: "name ASC",
   } catch (e) {

Update #

   try {
     int numRowsUpdated = await db.update(table: "category", 
      row: row, where: "id=1");
   } catch(e) {

Delete #

   try {
     await db.delete(table: "category", where: "id=3");
   } catch(e) {

Reactivity #

Changefeed #

A stream of database change events is available. Inspired by Rethinkdb

   import 'dart:async';
   import 'package:sqlcool/sqlcool.dart';

   StreamSubscription changefeed;

   changefeed = db.changefeed.listen((change) {
      print("Change in the database:");
      print("Query: ${change.query}");
      if (change.type == DatabaseChange.update) {
        print("${change.value} items updated");
   // Dispose the changefeed when finished using it

Reactive select bloc #

The bloc will rebuild itself on any database change because of the reactive parameter set to true:

   import 'package:flutter/material.dart';
   import 'package:sqlcool/sqlcool.dart';

   class _PageSelectBlocState extends State<PageSelectBloc> {
     SelectBloc bloc;

     void initState() {
       this.bloc = SelectBloc(
           table: "items", orderBy: "name", reactive: true);

     void dispose() {

     Widget build(BuildContext context) {
       return Scaffold(
         appBar: AppBar(title: Text("My app")),
         body: StreamBuilder<List<Map>>(
             stream: bloc.items,
             builder: (BuildContext context, AsyncSnapshot snapshot) {
               if (snapshot.hasData) {
                 // the select query has not found anything
                 if (snapshot.data.length == 0) {
                   return Center(child: const Text("No data"));
                 // the select query has results
                 return ListView.builder(
                     itemCount: snapshot.data.length,
                     itemBuilder: (BuildContext context, int index) {
                       var item = snapshot.data[index];
                       return ListTile(
                         title: GestureDetector(
                           child: Text(item["name"]),
                           onTap: () => someFunction()),
               } else {
                 // the select query is still running
                 return CircularProgressIndicator();

   class PageSelectBloc extends StatefulWidget {
     _PageSelectBlocState createState() => _PageSelectBlocState();

Database models #

New in 4.0.0: define models that have database methods. The main advantage of this is to use only typed model data and avoid the type conversions from maps for every query. It directly plugs custom models into the database. Example:

In schema.dart:

   final carTable = DbTable("car")
     ..boolean("is_4wd", defaultValue: false)
     ..foreignKey("manufacturer", onDelete: OnDelete.cascade);

   final manufacturerTable = DbTable("manufacturer")..varchar("name");

In car_model.dart:

   import 'package:sqlcool/sqlcool.dart';
   // the database schema
   import 'schema.dart';
   // another model
   import 'manufacturer_model.dart';

   class Car with DbModel {

     /// define some class properties

     final String name;
     final int maxSpeed;
     final double price;
     final DateTime year;
     final bool is4wd;
     // this is a foreign key to another model
     Manufacturer manufacturer;

     /// [DbModel] required overrides

     int id;

     /// the [Db] used
     /// pass it your main db
     Db get db => db;

     /// the table schema representation
     /// check example/pages/dbmodels/schema.dart
     DbTable get table => carTable;

     /// serialize a row to the database
     Map<String, dynamic> toDb() {
       // we want the foreign key to be recorded
       assert(manufacturer?.id != null);
       final row = <String, dynamic>{
         "name": name,
         "max_speed": maxSpeed,
         "price": price,
         "year": year.millisecondsSinceEpoch,
         "is_4wd": is4wd,
         "manufacturer": manufacturer.id
       return row;

     /// deserialize a row from database
     Car fromDb(Map<String, dynamic> map) {
       final car = Car(
         id: map["id"] as int,
         name: map["name"].toString(),
         maxSpeed: map["max_speed"] as int,
         price: map["price"] as double,
         year: DateTime.fromMillisecondsSinceEpoch(map["year"] as int),
         is4wd: (map["is_4wd"].toString() == "true"),
       // the key will be present only with join queries
       // in a simple select this data is not present
       if (map.containsKey("manufacturer")) {
         car.manufacturer =
             Manufacturer().fromDb(map["manufacturer"] as Map<String, dynamic>);
       return car;

     /// Create a static join method for convenience

     static Future<List<Car>> selectRelated({String where, int limit}) async {
       final cars = List<Car>.from(
           await Car().sqlJoin(where: where, limit: limit, verbose: true));
       return cars;

Then use the models:

  /// car is an instance of [Car]
  await car.sqlInsert();
  await car.sqlUpdate();
  await car.sqlUpsert();
  await car.sqlDelete();
  final cars = Car.selectRelated(where: "speed>200");
  // foreign keys are retrieved as model instances

Using this #

  • Sqlview: admin view and infinite list view
  • Kvsql: a type safe key/value store
  • Geopoint sql: sql operations for geospatial data

Changelog #

4.3.1 #

  • Refactor and fix DbModel.sqlJoin
  • Add a toString method to DbColumn

4.3.0 #

  • Update dependencies
  • Fix boolean column in schema
  • Fix unique in schema
  • Fix uniqueTogether in schema
  • Fix edge case in DbModel.sqlJoin
  • Deprecate insertIfNotExists
  • Deprecate DbModels.insertIfNotExists

4.2.0 #

  • Update dependencies
  • Add insertManageConflict method
  • Fix typo in confligAlgoritm parameter for batchInsert

4.1.1 #

Fix update query constructor bug #16

4.1.0 #

  • Use extra_pedantic for stronger analysis_options
  • Add more custom exceptions
  • Add a DbModel.sqlInsertIfNotExists method
  • Add a preserveColumn parameter to DbModel.sqlUpsert

4.0.0 #

  • Add informative getters to the schema
  • Join on multiple foreign keys
  • Database models
  • Query support in SelectBloc
  • Update dependencies
  • Use more strict analysis options

3.2.1 #

  • Run create queries and schema for asset database
  • Use create if not exists in create table query

3.2.0 #

  • Update to Dart sdk 2.2.2
  • Update dependencies

3.1.1 #

  • Use pedantic for static analysis
  • Add more tests
  • Improve the docs
  • Linting

3.1.0 #

  • Add a timestamp column type to schema
  • Add a data property to DatabaseChangeType
  • Fix the upsert method to be testable
  • Add more tests

3.0.0 #

Breaking change: the SynchronizedMap feature was removed due to broken dependencies after the Dart Sdk 2.4.0 upgrade

2.9.0 #

  • Fix index in DbTable in case of same row name for different tables
  • Fix the initialization when the fromAsset parameter is used
  • Fix schema constructor in case of multiple foreign keys
  • Add the timestamp method to DbTable
  • Add a uniqueTogether method to DbTable
  • Add a blob method to schema constructor
  • Improve the docs for schema definition

2.8.2 #

  • Add the columns getter for DbSchema
  • Fix defaultValue for thereal method of DbSchema
  • Fix the example

2.8.1 #

  • Update dependencies
  • Improve schema management
  • Minor fix in SynchronizedMap
  • Add the hasSchema getter

2.8.0 #

  • Add the batchInsert method
  • Add the schema parameter to init
  • Improve the count method
  • Update the changefeed from batchInsert
  • Fix nullables in schema constructor
  • Improve foreignKey in schema constructor

2.7.0 #

  • Add the database schema constructor

2.6.1 #

  • Add the columns parameter to SychronizedMap

2.6.0 #

  • Add the synchronized map feature

2.5.0 #

  • Add the group by sql clause to select and join methods
  • Add the upsert method
  • Use transactions for all queries
  • Remove the default values for offset and limit in join query

2.4.0 #

  • Add the ability to use an existing Sqflite database
  • Make all the DatabaseChangeEvent parameters final
  • Add a table parameter to DatabaseChangeEvent
  • Update SelectBloc to use the table parameter of DatabaseChangeEvent
  • Use travis-ci builds
  • Start adding tests

2.3.0 #

  • Update dependencies
  • Add the update method to SelectBloc

2.2.0 #

  • Add the absolutePath parameter to the init method
  • Use more strict linting rules
  • Improve docstrings

2.1.1 #

  • Fix race condition in SelectBloc
  • Fix in the fromAsset option of init: create the directories path if needed instead of throwing an error

2.1.0 #

  • Add the onReady callback
  • Upgrade dependencies

2.0.0 #

Breaking changes:

  • The default Db instance has been removed
  • The database parameter is now required for SelectBloc
  • The changeType parameter in changefeed has been renamed type and now uses the DatabaseChange data type

New features:

  • Add support for the Sqflite debug mode
  • Add a query timer
  • Add the query method

Changes and fixes:

  • Add a check to make sure the database is ready before running any query
  • Better examples
  • Various minor fixes

1.2.0 #

  • Downgrade to path_provider 0.4.1

  • Add mutexes for write operations

  • Add the query to the changefeed info

  • Fix return values for update and delete

  • Fix bloc select verbose param

  • Fix verbosity for update and insert queries

  • Improve the example

  • Improve the doc and readme

1.1.2 #

Fix: close _changeFeedController sink

1.1.1 #

Minor fixes

1.1.0 #

Add changefeed and reactive select bloc

1.0.0 #

Initial release


import 'package:flutter/material.dart';
import 'pages/select_bloc.dart';
import 'pages/index.dart';
import 'pages/join_query.dart';
import 'pages/upsert.dart';
import 'pages/dbmodels/dbmodels.dart';
import 'dbviewer/dbviewer.dart';
import 'init_db.dart';
import 'conf.dart';

void main() {

  /// initialize the database async. We will use the [onReady]
  /// callback later to react to the initialization completed event
  initDb(db: db);

final routes = {
  '/': (BuildContext context) => PageIndex(),
  '/select_bloc': (BuildContext context) => PageSelectBloc(),
  '/join': (BuildContext context) => PageJoinQuery(),
  '/upsert': (BuildContext context) => UpsertPage(),
  '/dbmodel': (BuildContext context) => DbModelPage(),
  '/dbmanager': (BuildContext context) => DbViewer(db: db),

class MyApp extends StatelessWidget {
  Widget build(BuildContext context) {
    return MaterialApp(
      debugShowCheckedModeBanner: false,
      title: 'Sqlcool example',
      routes: routes,

Use this package as a library

1. Depend on it

Add this to your package's pubspec.yaml file:

  sqlcool: ^4.3.1

2. Install it

You can install packages from the command line:

with Flutter:

$ flutter pub get

Alternatively, your editor might support flutter pub get. Check the docs for your editor to learn more.

3. Import it

Now in your Dart code, you can use:

import 'package:sqlcool/sqlcool.dart';
Describes how popular the package is relative to other packages. [more]
Code health derived from static analysis. [more]
Reflects how tidy and up-to-date the package is. [more]
Weighted score of the above. [more]
Learn more about scoring.

We analyzed this package on Apr 7, 2020, and provided a score, details, and suggestions below. Analysis was completed with status completed using:

  • Dart: 2.7.1
  • pana: 0.13.6
  • Flutter: 1.12.13+hotfix.8


Package Constraint Resolved Available
Direct dependencies
Dart SDK >=2.2.2 <3.0.0
cupertino_icons ^0.1.2 0.1.3
extra_pedantic ^1.1.1+3 1.2.0
flutter 0.0.0
path_provider ^1.6.0 1.6.5
pedantic ^1.8.0 1.9.0
sqflite ^1.2.0 1.3.0
synchronized ^2.2.0 2.2.0
Transitive dependencies
collection 1.14.11 1.14.12
meta 1.1.8
path 1.6.4
path_provider_macos 0.0.4
path_provider_platform_interface 1.0.1
platform 2.2.1
plugin_platform_interface 1.0.2
sky_engine 0.0.99
sqflite_common 1.0.0+1
typed_data 1.1.6
vector_math 2.0.8
Dev dependencies