Count with SQFLite(Flutter)

Last month I wrote a short post about how to use the database library Hive. I have also used the SQFlite package in another project and today I thought I'd write about how to use 'count' with SQFlite - because it's something that I needed to implement myself and it wasn't immediately obvious to me. It ended up being pretty easy and straightforward though.

SQFlite has a method called rawQuery and this can be used for any raw SQL statement/query that is too complex for the query method. Naturally you can use this to perform a COUNT. Here's a quick example of what a COUNT statement looks like in SQL:

Copycopy code to clipboard
1SELECT COUNT(*) FROM table_name

In this case you would replace table_name with whatever the name of the table you want to query is called. Now it's just a matter of combining the above with SQFLite's rawQuery method. Below is some example pseudo-code:

Copycopy code to clipboard
1final result = await db.rawQuery('SELECT COUNT(*) FROM table_name');
2final count = Sqflite.firstIntValue(result);

The result variable will contain the raw result text from the SQL query. You can then use SQFLite's firstIntValue utility method to get the int value for the result - which will be a count of rows in the target table.

I should also probably add that I highly recommend SQFLite, I've been using it in a production app for awhile now without any issues. Maybe one day I will write a short tutorial on how to use it like I did with Hive.