A typesafe sqlite persistence library for flutter

Overview

Warning: This library has been deprecated. Please use moor instead. It requires less work to set up, needs less boilerplate code and has more features (like natively supported datetimes, a fluent query DSL, and reactive streams).

tinano

Tinano is a local persistence library for flutter apps based on sqflite. While sqflite is an awesome library to persist data, having to write all the parsing code yourself is tedious and can become error-prone quickly. With Tinano, you specify the queries and the data structures they return, and it will automatically take care of all that manual and boring stuff, giving you a clean and type-safe way to manage your app's data.

Table of Contents

Getting Started

Setting up your project

First, let's prepare your pubspec.yaml to add this library and the tooling needed to automatically generate code based on your database definition:

dependencies:
  tinano:
  # ...
dev_dependencies:
  tinano_generator:
  build_runner:
  # test, ...

The tinano library will provide some annotations for you to write your database classes, whereas the tinano_generator plugs into the build_runner to generate the implementation. As we'll only do code-generation during development (and not at runtime), these two can be a dev-dependency.

Creating a database

With Tinano, creating a database is simple:

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

part 'database.g.dart'; // this is important!

@TinanoDb(name: "my_database.sqlite", schemaVersion: 1)
abstract class MyDatabase {

  static DatabaseBuilder<MyDatabase> createBuilder() => _$createMyDatabase();

}

It is important that your database class is abstract and has a static method called createBuilder that uses the => notation. The _$createMyDatabase() method will be generated automatically later on. Of course, you're free to choose whatever name you want, but the method to create the database has to start with _$. Right now, this code will give us a bunch of errors because the implementation has not been generated yet. A swift flutter packages pub run build_runner build in the terminal will fix that. If you want to automatically rebuild your database implementation every time you change the specification (might be useful during development), you can use flutter packages pub run build_runner watch.

Opening the database

To get an instance of your MyDatabase, you can just use the builder function like this:

Future<MyDatabase> openMyDatabase() async {
  return await (MyDatabase
    .createBuilder()
    .doOnCreate((db, version) async {
      // This await is important, otherwise the database might be opened before
      // you're done with initializing it!
      await db.execute("""CREATE TABLE `users` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `name` TEXT NOT NULL )""");
    })
    .build());
}

The doOnCreate block will be executed for the first time your database is opened. The db parameter will give you access to the raw sqflite database, the version parameter is the schema version specified in your @TinanoDb annotation. You can use the addMigration methods to do schema migrations - more info on that below.

Database queries

Of course, just opening the database is pretty boring. In order to actually execute some queries to the database, just create methods annotated with either @Query, @Update, @Delete or @Insert. Here is an example that fits to the doOnCreate method defined above:

@TinanoDb(name: "my_database.sqlite", schemaVersion: 1)
abstract class MyDatabase {
  static DatabaseBuilder<MyDatabase> createBuilder() => _$createMyDatabase();

  @Query("SELECT * FROM users")
  Future<List<UserRow>> getAllUsers();

  // If we know we'll only get one user, we can skip the List<>. Note that this
  // really expects there to be one row -> if there are 0, it will throw an
  // exception.
  @Query("SELECT * FROM users WHERE id = :id")
  Future<UserRow> getUserById(int id);

  // For queries with only one column that is either a String, a num or a
  // Uint8List, we don't have to define a new class.
  @Query("SELECT COUNT(id) FROM users")
  Future<int> getAmountOfUsers();

  // Inserts defined to return an int will return the insert id. Could also 
  // return nothing (Future<Null> or Future<void>) if we wanted.
  @Insert("INSERT INTO users (name) VALUES (:name)")
  Future<int> createUserWithName(String name);

  // Inserts return values based on their return type:
  // For Future<Null> or Future<void>, it won't return any value
  // For Future<int>, returns the amount of changed rows
  // For Future<bool>, checks if the amount of changed rows is greater than zero
  @Update("UPDATE users SET name = :updatedName WHERE id = :id")
  Future<bool> changeName(int id, String updatedName);

  // The behavior of deletes is identical to those of updates.
  @Delete("DELETE FROM users WHERE id = :id")
  Future<bool> deleteUser(int id);
}

// We have to annotate composited classes as @row. They should be immutable.
@row
class UserRow {

  final int id;
  final String name;

  UserRow(this.id, this.name);

}

Variables

As you can see, you can easily map the parameters of your method to sql variables by using the :myVariable notation directly in your sql. If you want to use a : character in your SQL, that's fine, just escape them with a backslash \. Note that you will have to use two of them ("\\:") in your dart strings.

The variables will not be inserted into the query directly (which could easily result in an sql injection vulnerability), but instead use prepared statements to first send the sql without data, and then the variables. This means that you won't be able to use variables for everything, see this for some examples where you can't.

Schema updates

After bumping your version in @TinanoDb, you will have to perform some migrations manually. You can do this directly with your DatabaseBuilder by using addMigration:

MyDatabase
  .createBuilder()
  .doOnCreate((db, version) {...})
  .addMigration(1, 2, (db) async {
	  await db.execute("ALTER TABLE ....")
  });

For bigger migrations (e.g. from 1 to 5), just specify all the migrations for each step. Tinano will then apply them sequentially to ensure that the database is ready before it's opened.

Supported types

As the database access is asynchronous, all methods must return a Future.

For modifying statements (update / delete)

A Future<int> will resolve to the amount of updated rows, whereas a Future<bool> as return type will resolve to true if there were any changes and to false if not.

For insert statements

A Future<int> will resolve to the last inserted id. A Future<bool> will always resolve to true, so using it is not recommended here.

For select statements

You'll have to use a List<T> if you want to receive all results, or just T right away if you're fine with just receiving the first one. Notice that, in either case, the entire response will be loaded into memory at some point, so please set LIMITs in your sql.
Now, if your result is just going to have one column, you can use that type directly:

@Query("SELECT COUNT(*) FROM users")
Future<int> getAmountOfUsers();

This will work for int, num, Uint8List and String. Please see the documentation from sqflite to check which dart types are compatible with which sqlite types.
If your queries will return more than one column, you'll have to define it in a new immutable class that must only have the unnamed constructor to set the fields:

@row
class UserResult {
  final int id;
  final String name;

  UserResult(this.id, this.name);
}

// this should be a method in your @TinanoDb class....
@Query("SELECT id, name FROM users")
Future<List<UserResult>> getBirthmonthDistribution();

Each @row class may only consist of the primitive fields int, num, Uint8List and String.

Accessing the raw database

If you want to use Tinano, but also have some use cases where you have to use the Database from sqflite directly to send queries, you can just define a field Database database; in your @TinanoDb class. It will be generated and available after your database has been opened.

TO-DO list

  • It would be cool if we could get rid of doOnCreate and instead define these methods right in our database class with some more annotations. This can also apply to migration functions.
  • Batches and transactions for improved performance and reliability.
  • Auto-updating queries that return a Stream emitting new values as the underlying data changes. Could be similar to the Room library on Android.
  • Supporting a DateTime right from the library, auto-generating code to store it as a timestamp in the database.
  • Support @row classes that have other @row types as fields.
  • Support for custom classes as variable parameters, specifying something like WHERE id = :user.id in your sql and then having a User user as a parameter.
  • Being able to use different variable / column names for sql and dart types. Adding some annotations like @FromColumn("my_column").

Questions and feedback

This library is still in quite an early stage and will likely see some changes on the way, so please feel free to open an issue if you have any feedback or ideas for improvement. Also, even though there are some awesome dart tools doing most of the work, automatic code generation based on your database classes is pretty hard and there are a lot of edge-cases. So please, if you run into any weird issues or unhelpful error messages during the build step, please do let me know so that I can take a look at them. Thanks! Of course, I greatly appreciate any PRs made to this library, but if you wankt to implement some new features, please let me know first by creating an issue first. That way, we can talk about how to approach that.

Comments
  • Ability to use action annotations in a separate class without database annotation

    Ability to use action annotations in a separate class without database annotation

    Hi,

    As far as I understand in the current implementation all of the action annotated (Update, Select...) methods have to be in a database class annotated as a database. How about generating repository classes that keep a reference to the database class and including the methods in these classes? I haven't tried this but from the code it seems like it wouldn't work. Do you have any suggestions? Is it possible some how?

    opened by mertkokusen 3
  • Ability to open database by name

    Ability to open database by name

    Please can you add the ability to pass database name into "createBuilder" and not into annotation? This can be useful to handle multiple database, regards.

    opened by carmas123 2
  • Fix for adding a migration

    Fix for adding a migration

    Before tried to add a migration to a not initialised list (initialised with null). Fixed that by actually creating a growable list, where future migrations can get added to.

    Resolves #6.

    opened by vitusortner 1
  • Fix example application

    Fix example application

    This adds a fix so that the example application is able to be built again. I also attached the new generated output.

    It would probably be alright to remove the generated class from the example app, so there is less chance of having an outdated class in the repository. The user would have to run the build runner before being able to run the app though.

    Resolves #4.

    opened by vitusortner 1
  • missing type

    missing type

    Please do the following change in builder.dart

    from DatabaseBuilder doOnCreate(OnDatabaseCreateFn fn) { to DatabaseBuilder<T> doOnCreate<T>(OnDatabaseCreateFn fn) {

    opened by the4thfloor 1
  • Adding a migration doesn't work

    Adding a migration doesn't work

    When trying to add a migration, an error is thrown, which is stating that The method 'add' was called on null.

    This is caused by the fact, that the migrations field in the abstract TinanoDatabase class is instantiated with null but the generated code tries to add an element to the migrations list.

    E/flutter ( 3741): [ERROR:flutter/shell/common/shell.cc(181)] Dart Error: Unhandled exception:
    E/flutter ( 3741): NoSuchMethodError: The method 'add' was called on null.
    E/flutter ( 3741): Receiver: null
    E/flutter ( 3741): Tried calling: add(Instance of 'SchemaMigrationWithVersion')
    E/flutter ( 3741): #0      Object.noSuchMethod (dart:core/runtime/libobject_patch.dart:50:5)
    E/flutter ( 3741): #1      _$openMyDatabase (file:///Users/vitusortner/Development/Flutter/tinano/example/lib/database.g.dart:13:8)
    E/flutter ( 3741): <asynchronous suspension>
    E/flutter ( 3741): #2      MyDatabase.open (package:tinano_example/database.dart:10:39)
    E/flutter ( 3741): #3      _MyHomePageState.initState.<anonymous closure> (file:///Users/vitusortner/Development/Flutter/tinano/example/lib/main.dart:56:36)
    E/flutter ( 3741): <asynchronous suspension>
    E/flutter ( 3741): #4      _MyHomePageState.initState (file:///Users/vitusortner/Development/Flutter/tinano/example/lib/main.dart:58:6)
    E/flutter ( 3741): #5      StatefulElement._firstBuild (package:flutter/src/widgets/framework.dart:3787:58)
    E/flutter ( 3741): #6      ComponentElement.mount (package:flutter/src/widgets/framework.dart:3653:5)
    E/flutter ( 3741): #7      Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #8      Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #9      SingleChildRenderObjectElement.mount (package:flutter/src/widgets/framework.dart:4796:14)
    E/flutter ( 3741): #10     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #11     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #12     ComponentElement.performRebuild (package:flutter/src/widgets/framework.dart:3689:16)
    E/flutter ( 3741): #13     Element.rebuild (package:flutter/src/widgets/framework.dart:3531:5)
    E/flutter ( 3741): #14     ComponentElement._firstBuild (package:flutter/src/widgets/framework.dart:3658:5)
    E/flutter ( 3741): #15     ComponentElement.mount (package:flutter/src/widgets/framework.dart:3653:5)
    E/flutter ( 3741): #16     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #17     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #18     SingleChildRenderObjectElement.mount (package:flutter/src/widgets/framework.dart:4796:14)
    E/flutter ( 3741): #19     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #20     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #21     SingleChildRenderObjectElement.mount (package:flutter/src/widgets/framework.dart:4796:14)
    E/flutter ( 3741): #22     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #23     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #24     SingleChildRenderObjectElement.mount (package:flutter/src/widgets/framework.dart:4796:14)
    E/flutter ( 3741): #25     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #26     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #27     SingleChildRenderObjectElement.mount (package:flutter/src/widgets/framework.dart:4796:14)
    E/flutter ( 3741): #28     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #29     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #30     ComponentElement.performRebuild (package:flutter/src/widgets/framework.dart:3689:16)
    E/flutter ( 3741): #31     Element.rebuild (package:flutter/src/widgets/framework.dart:3531:5)
    E/flutter ( 3741): #32     ComponentElement._firstBuild (package:flutter/src/widgets/framework.dart:3658:5)
    E/flutter ( 3741): #33     StatefulElement._firstBuild (package:flutter/src/widgets/framework.dart:3805:11)
    E/flutter ( 3741): #34     ComponentElement.mount (package:flutter/src/widgets/framework.dart:3653:5)
    E/flutter ( 3741): #35     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #36     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #37     ComponentElement.performRebuild (package:flutter/src/widgets/framework.dart:3689:16)
    E/flutter ( 3741): #38     Element.rebuild (package:flutter/src/widgets/framework.dart:3531:5)
    E/flutter ( 3741): #39     ComponentElement._firstBuild (package:flutter/src/widgets/framework.dart:3658:5)
    E/flutter ( 3741): #40     ComponentElement.mount (package:flutter/src/widgets/framework.dart:3653:5)
    E/flutter ( 3741): #41     Element.inflateWidget (package:flutter/src/widgets/framework.dart:2937:14)
    E/flutter ( 3741): #42     Element.updateChild (package:flutter/src/widgets/framework.dart:2740:12)
    E/flutter ( 3741): #43     ComponentElement.performRebuild (pack
    
    opened by vitusortner 0
  • Example application doesn't build

    Example application doesn't build

    The example application doesn't build right now. This is because the type of the id field in the TodoEntry class has been changed to a bool but no changes were made when calling the delete function on a TodoEntry.

    opened by vitusortner 0
  • Broken README in Transactions section

    Broken README in Transactions section

    There is an issue in the README in the Transactions section. It seems like there has been inserted some text by accident, which destroys the markdown.

    screenshot 2018-11-15 at 14 53 16
    opened by vitusortner 0
Owner
Simon Binder
Simon Binder
Password Manager Created Using Flutter And SQLite

Password Manager Created Using Flutter And SQLite

Imira Randeniya 15 Dec 24, 2022
Keeper App made in Flutter with Sqlite database

Keeper App made in Flutter with Sqlite database This is a note taking / keeper app made with flutter. Concepts used: Sqlite database to store custom N

Abdul Muqeet Arshad 12 Oct 21, 2022
SQLite flutter plugin

sqflite SQLite plugin for Flutter. Supports iOS, Android and MacOS. Support transactions and batches Automatic version management during open Helpers

Tekartik 2.5k Jan 1, 2023
Basic banking app - A Banking App that allow transfer money between multiple customers using SQLite database

basic_banking_app A Basic Banking App that allow transfer money between multiple

Esraa Mostfa 0 Feb 10, 2022
QR.Flutter is a Flutter library for simple and fast QR code rendering via a Widget or custom painter.

QR.Flutter is a Flutter library for simple and fast QR code rendering via a Widget or custom painter. Need help? Please do not submit an issue for a "

Yakka 612 Jan 4, 2023
Flutter library to create beautiful surveys (aligned with ResearchKit on iOS)

SurveyKit: Create beautiful surveys with Flutter (inspired by iOS ResearchKit Surveys)

QuickBird Studios 90 Dec 28, 2022
Complete Flutter OpenIdConnect Library

OpenIdConnect for Flutter Standards compliant OpenIdConnect library for flutter that supports: Code flow with PKCE (the evolution of implicit flow). T

null 50 Dec 24, 2022
🎞 Flutter media playback, broadcast & recording library for Windows, Linux & macOS. Written in C++ using libVLC & libVLC++. (Both audio & video)

dart_vlc Flutter media playback, broadcast, recording & chromecast library for Windows, Linux & macOS. Written in C++ using libVLC & libVLC++. Install

Hitesh Kumar Saini 417 Dec 29, 2022
Flutter example project to run Solidity smart contracts using web3Dart library

Fluthereum Description Flutter example project to run Solidity smart contracts using web3Dart library Dependencies web3dart: A dart library that conne

Marcos Carlomagno 72 Dec 27, 2022
Demo library index written in Flutter

demo_books A new Flutter project. Getting Started This project is a starting point for a Flutter application. A few resources to get you started if th

Nemanja Stošić 1 Sep 20, 2022
A playground for me to learn the Riverpod state management library in Flutter.

clothes_shop A new Flutter project. Getting Started This project is a starting point for a Flutter application. A few resources to get you started if

Benhenneda Majid 2 Oct 25, 2021
Playify is a Flutter plugin for play/pause/seek songs, fetching music metadata, and browsing music library.

Playify Playify is a Flutter plugin for play/pause/seek songs, fetching music metadata, and browsing music library. Playify was built using iOS's Medi

Ibrahim Berat Kaya 32 Dec 14, 2022
Flutter Screenshot Library

A simple package to capture widgets as Images. Now you can also capture the widgets that are not rendered on the screen! This package wraps your widge

Sachin 225 Dec 19, 2022
A library of Flutter to add a new style in the navigation bar.

Navigation Dot Bar Una libreria de Flutter, el cual agrega un BottomNavigationBar con un mejor estilo. Inspirada en la aplicacion "Reflectly" Como usa

null 38 Oct 17, 2022
A flutter todo/task listing example app with advance state management using `Momentum` library.

A flutter todo/task listing example app with advanced state management using Momentum library. Simple on the outside but powerful in the inside. This

xamantra 17 Oct 11, 2022
A declarative library with an easy-to-use interface for building Flutter applications on AWS.

Amplify Flutter AWS Amplify provides a declarative and easy-to-use interface across different categories of cloud operations. Our default implementati

AWS Amplify 1.1k Jan 5, 2023
A fast and space efficient library to deal with data in Dart, Flutter and the web.

Dart Data Dart Data is a fast and space efficient library to deal with data in Dart, Flutter and the web. As of today this mostly includes data struct

Lukas Renggli 14 Nov 4, 2022
A powerful Flutter chat UI component library and business logic for Tencent Cloud Chat, creating seamless in-app chat modules for delightful user experiences.

<style> .button-9 { appearance: button; backface-visibility: hidden; background-color: #1d52d9; border-radius: 6px; border-width: 0; box-shadow: rgba(

Tencent Cloud 63 Aug 11, 2023
A type-safe command-line parsing library for Dart

plade Plade is a type-safe CLI parsing library for Dart. Highlights Fully type-safe and null-safe. Support for a variety of different parsing styles (

Ryan Gonzalez 6 Jan 1, 2022