No matter who you are or what you do, sooner or later you’re going to need to store data in your app and then retrieve it later. But what should you use to do that? Lewis Cianci checks it out.

There are a lot of options out there today when it comes to databases in your application. They typically fit into these three categories:

• Relational - these are the databases in the traditional sense. They don’t just store data but also the relationships between the data. SQLite is an example of a relational database.
• NoSQL - these databases store data as documents. A schema is not enforced as is the case with a relational database. They are lightning-quick and handle huge unstructured pieces of data very well. MongoDB is an example of a NoSQL database.
• Individually tailored data storage - while this option is not technically a database, you don’t have to use the above solutions. You can store your data in a JSON file and handle the serialisation and deserialisation yourself. This would be incredibly fast but would open you up to some weird bugs if you weren’t a programming genius. 🤓

By the end of this article, you will know:

• The basics of how each database works. 📰
• How to get set up with each database. ⚙
• What a good use case would be for each database. 📳

In this article, we’ll divide our database types according to the three categories mentioned above.

Relational

Relational databases have been around for a very long time (since 1970, according to a quick Google search). Let’s look at some of the options you have on Flutter for relational databases today.

SQflite is an implementation of SQLite for Flutter. It affords you complete control over your database, queries, relationships, everything you could wish for.

Interacting with a SQLite database in Flutter looks like this (

):

  // Get a location using getDatabasesPath
  var databasesPath = await getDatabasesPath();
  String path = join(databasesPath, 'demo.db');

  // Delete the database
  await deleteDatabase(path);

  // open the database
  Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
   // When creating the db, create the table
   await db.execute(
     'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
  });

Inserting data follows the same age-old SQLite tenants

  // Insert some records in a transaction
  await database.transaction((txn) async {
   int id1 = await txn.rawInsert(
     'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
   print('inserted1: $id1');
   int id2 = await txn.rawInsert(
     'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
     ['another name', 12345678, 3.1416]);
   print('inserted2: $id2');
  });

And querying happens like this:

  // Get the records
  List<Map> list = await database.rawQuery('SELECT * FROM Test');

Pros 👍

• Total control over the database.
• A very efficient SQLite database for your app (given that you have pre-existing SQLite knowledge).
• Easily read by third party apps that can open SQLite databases (so you can open the database on your computer and see what the data looks like).

Cons 👎

• Writing out all your queries by hand can take a lot of time.
• Returned data from the database isn’t strongly typed from the start.
• It is potentially difficult to handle migrations on the device (when the schema changes between version updates for instance).
• It has no support for web.

Use case

SQFlite is good when you need relational data but also fine-grained control over the actual queries. If you’re comfortable with writing your own queries and don’t mind writing both a lot of queries and the code to convert the results to and from your classes, this could be a good fit for you.

#database #flutter #developer