Tinano – Typesafe SQLite Persistence

flutter 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.

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 IF NOT EXISTS `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 IGNORE 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").

121

download the full project for this post from the following button

this source is fully free for all time

download as zip