SQLite plugin for Flutter

SQLite plugin for Flutter. Supports iOS, Android and MacOS.

  • Support transactions and batches
  • Automatic version managment during open
  • Helpers for insert/query/update/delete queries
  • DB operation executed in a background thread on iOS and Android

Other platforms support:

Usage example:

  • notepad_sqflite: Simple flutter notepad working on iOS/Android/Windows/linux/Mac

Getting Started

In your flutter project add the dependency:

dependencies:
  ...
  sqflite:

For help getting started with Flutter, view the online documentation.

Usage example

Import sqflite.dart

import 'package:sqflite/sqflite.dart';

Opening a database

A SQLite database is a file in the file system identified by a path. If relative, this path is relative to the path obtained by getDatabasesPath(), which is the default database directory on Android and the documents directory on iOS/MacOS.

var db = await openDatabase('my_db.db');

There is a basic migration mechanism to handle schema changes during opening.

Many applications use one database and would never need to close it (it will be closed when the application is terminated). If you want to release resources, you can close the database.

await db.close();

Raw SQL queries

Demo code to perform Raw SQL queries

// 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)');
});

// 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');
});

// Update some record
int count = await database.rawUpdate(
    'UPDATE Test SET name = ?, value = ? WHERE name = ?',
    ['updated name', '9876', 'some name']);
print('updated: $count');

// Get the records
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
  {'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
  {'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));

// Count the records
count = Sqflite
    .firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);

// Delete a record
count = await database
    .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);

// Close the database
await database.close();

Basic information on SQL here.

SQL helpers

Example using the helpers

final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';
final String columnDone = 'done';

class Todo {
  int id;
  String title;
  bool done;

  Map<String, Object?> toMap() {
    var map = <String, Object?>{
      columnTitle: title,
      columnDone: done == true ? 1 : 0
    };
    if (id != null) {
      map[columnId] = id;
    }
    return map;
  }

  Todo();

  Todo.fromMap(Map<String, Object?> map) {
    id = map[columnId];
    title = map[columnTitle];
    done = map[columnDone] == 1;
  }
}

class TodoProvider {
  Database db;

  Future open(String path) async {
    db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute('''
create table $tableTodo ( 
  $columnId integer primary key autoincrement, 
  $columnTitle text not null,
  $columnDone integer not null)
''');
    });
  }

  Future<Todo> insert(Todo todo) async {
    todo.id = await db.insert(tableTodo, todo.toMap());
    return todo;
  }

  Future<Todo> getTodo(int id) async {
    List<Map> maps = await db.query(tableTodo,
        columns: [columnId, columnDone, columnTitle],
        where: '$columnId = ?',
        whereArgs: [id]);
    if (maps.length > 0) {
      return Todo.fromMap(maps.first);
    }
    return null;
  }

  Future<int> delete(int id) async {
    return await db.delete(tableTodo, where: '$columnId = ?', whereArgs: [id]);
  }

  Future<int> update(Todo todo) async {
    return await db.update(tableTodo, todo.toMap(),
        where: '$columnId = ?', whereArgs: [todo.id]);
  }

  Future close() async => db.close();
}

Read results

Assuming the following read results:

List<Map<String, Object?>> records = await db.query('my_table');

Resulting map items are read-only

// get the first record
Map<String, Object?> mapRead = records.first;
// Update it in memory...this will throw an exception
mapRead['my_column'] = 1;
// Crash... `mapRead` is read-only

You need to create a new map if you want to modify it in memory:

// get the first record
Map<String, Object?> map = Map<String, Object?>.from(mapRead);
// Update it in memory now
map['my_column'] = 1;

Transaction

Don't use the database but only use the Transaction object in a transaction to access the database

await database.transaction((txn) async {
  // Ok
  await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');
  
  // DON'T  use the database object in a transaction
  // this will deadlock!
  await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');
});

A transaction is committed if the callback does not throw an error. If an error is thrown, the transaction is cancelled. So to rollback a transaction one way is to throw an exception.

Batch support

To avoid ping-pong between dart and native code, you can use Batch:

batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
results = await batch.commit();

Getting the result for each operation has a cost (id for insertion and number of changes for update and delete), especially on Android where an extra SQL request is executed. If you don't care about the result and worry about performance in big batches, you can use

await batch.commit(noResult: true);

Warning, during a transaction, the batch won't be committed until the transaction is committed

await database.transaction((txn) async {
  var batch = txn.batch();
  
  // ...
  
  // commit but the actual commit will happen when the transaction is committed
  // however the data is available in this transaction
  await batch.commit();
  
  //  ...
});

By default a batch stops as soon as it encounters an error (which typically reverts the uncommitted changes). You can ignore errors so that every successfull operation is ran and committed even if one operation fails:

await batch.commit(continueOnError: true);

Table and column names

In general it is better to avoid using SQLite keywords for entity names. If any of the following name is used:

"add","all","alter","and","as","autoincrement","between","case","check","collate","commit","constraint","create","default","deferrable","delete","distinct","drop","else","escape","except","exists","foreign","from","group","having","if","in","index","insert","intersect","into","is","isnull","join","limit","not","notnull","null","on","or","order","primary","references","select","set","table","then","to","transaction","union","unique","update","using","values","when","where"

the helper will escape the name i.e.

db.query('table')

will be equivalent to manually adding double-quote around the table name (confusingly here named table)

db.rawQuery('SELECT * FROM "table"');

However in any other raw statement (including orderBy, where, groupBy), make sure to escape the name properly using double quote. For example see below where the column name group is not escaped in the columns argument, but is escaped in the where argument.

db.query('table', columns: ['group'], where: '"group" = ?', whereArgs: ['my_group']);

Supported SQLite types

No validity check is done on values yet so please avoid non supported types https://www.sqlite.org/datatype3.html

DateTime is not a supported SQLite type. Personally I store them as int (millisSinceEpoch) or string (iso8601)

bool is not a supported SQLite type. Use INTEGER and 0 and 1 values.

More information on supported types here.

INTEGER

  • Dart type: int
  • Supported values: from -2^63 to 2^63 - 1

REAL

  • Dart type: num

TEXT

  • Dart type: String

BLOB

  • Dart type: Uint8List

Current issues

  • Due to the way transaction works in SQLite (threads), concurrent read and write transaction are not supported. All calls are currently synchronized and transactions block are exclusive. I thought that a basic way to support concurrent access is to open a database multiple times but it only works on iOS as Android reuses the same database object. I also thought a native thread could be a potential future solution however on android accessing the database in another thread is blocked while in a transaction...

More

Use this package as a library

Depend on it

Run this command:

With Flutter:

 $ flutter pub add sqflite

This will add a line like this to your package's pubspec.yaml (and run an implicit flutter pub get):

dependencies:
  sqflite: ^2.0.0+4

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

Import it

Now in your Dart code, you can use:

import 'package:sqflite/sqflite.dart'; 

example/lib/main.dart

import 'dart:async';

import 'package:flutter/material.dart';
import 'package:sqflite_example/batch_test_page.dart';
import 'package:sqflite_example/deprecated_test_page.dart';
import 'package:sqflite_example/exception_test_page.dart';
import 'package:sqflite_example/exp_test_page.dart';
import 'package:sqflite_example/manual_test_page.dart';
import 'package:sqflite_example/src/dev_utils.dart';

import 'model/main_item.dart';
import 'open_test_page.dart';
import 'raw_test_page.dart';
import 'slow_test_page.dart';
import 'src/main_item_widget.dart';
import 'todo_test_page.dart';
import 'type_test_page.dart';

void main() {
  runApp(const MyApp());
}

/// Sqflite test app
class MyApp extends StatefulWidget {
  /// test app.
  const MyApp({Key? key}) : super(key: key);
  // This widget is the root of your application.

  @override
  _MyAppState createState() => _MyAppState();
}

/// Simple test page.
const String testRawRoute = '/test/simple';

/// Open test page.
const String testOpenRoute = '/test/open';

/// Slow test page.
const String testSlowRoute = '/test/slow';

/// Type test page.
const String testTypeRoute = '/test/type';

/// Batch test page.
const String testBatchRoute = '/test/batch';

/// `todo` example test page.
const String testTodoRoute = '/test/todo';

/// Exception test page.
const String testExceptionRoute = '/test/exception';

/// Manual test page.
const String testManualRoute = '/test/manual';

/// Experiment test page.
const String testExpRoute = '/test/exp';

/// Deprecated test page.
const String testDeprecatedRoute = '/test/deprecated';

class _MyAppState extends State<MyApp> {
  var routes = <String, WidgetBuilder>{
    '/test': (BuildContext context) => MyHomePage(),
    testRawRoute: (BuildContext context) => RawTestPage(),
    testOpenRoute: (BuildContext context) => OpenTestPage(),
    testSlowRoute: (BuildContext context) => SlowTestPage(),
    testTodoRoute: (BuildContext context) => TodoTestPage(),
    testTypeRoute: (BuildContext context) => TypeTestPage(),
    testManualRoute: (BuildContext context) => const ManualTestPage(),
    testBatchRoute: (BuildContext context) => BatchTestPage(),
    testExceptionRoute: (BuildContext context) => ExceptionTestPage(),
    testExpRoute: (BuildContext context) => ExpTestPage(),
    testDeprecatedRoute: (BuildContext context) => DeprecatedTestPage(),
  };

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
        title: 'Sqflite Demo',
        theme: ThemeData(
          // This is the theme of your application.
          //
          // Try running your application with 'flutter run'. You'll see
          // the application has a blue toolbar. Then, without quitting
          // the app, try changing the primarySwatch below to Colors.green
          // and then invoke 'hot reload' (press 'r' in the console where
          // you ran 'flutter run', or press Run > Hot Reload App in IntelliJ).
          // Notice that the counter didn't reset back to zero -- the application
          // is not restarted.
          primarySwatch: Colors.blue,
        ),
        home: MyHomePage(title: 'Sqflite Demo Home Page'),
        routes: routes);
  }
}

/// App home menu page.
class MyHomePage extends StatefulWidget {
  /// App home menu page.
  MyHomePage({Key? key, this.title}) : super(key: key) {
    _items.add(
        MainItem('Raw tests', 'Raw SQLite operations', route: testRawRoute));
    _items.add(MainItem('Open tests', 'Open onCreate/onUpgrade/onDowngrade',
        route: testOpenRoute));
    _items
        .add(MainItem('Type tests', 'Test value types', route: testTypeRoute));
    _items.add(MainItem('Batch tests', 'Test batch operations',
        route: testBatchRoute));
    _items.add(
        MainItem('Slow tests', 'Lengthy operations', route: testSlowRoute));
    _items.add(MainItem(
        'Todo database example', 'Simple Todo-like database usage example',
        route: testTodoRoute));
    _items.add(MainItem('Exp tests', 'Experimental and various tests',
        route: testExpRoute));
    _items.add(MainItem('Exception tests', 'Tests that trigger exceptions',
        route: testExceptionRoute));
    _items.add(MainItem('Manual tests', 'Tests that requires manual execution',
        route: testManualRoute));
    _items.add(MainItem('Deprecated test',
        'Keeping some old tests for deprecated functionalities',
        route: testDeprecatedRoute));

    // Uncomment to view all logs
    //Sqflite.devSetDebugModeOn(true);
  }

  final List<MainItem> _items = [];

  /// Page title.
  final String? title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

String? _debugAutoStartRouteName;

/// (debug) set the route to start with.
String? get debugAutoStartRouteName => _debugAutoStartRouteName;

/// Deprecated to avoid calls
@Deprecated('Deb only')
set debugAutoStartRouteName(String? routeName) =>
    _debugAutoStartRouteName = routeName;

class _MyHomePageState extends State<MyHomePage> {
  int get _itemCount => widget._items.length;

  @override
  void initState() {
    super.initState();

    Future.delayed(Duration.zero).then((_) async {
      if (mounted) {
        // Use it to auto start a test page
        if (debugAutoStartRouteName != null) {
          // only once

          // await Navigator.of(context).pushNamed(testExpRoute);
          // await Navigator.of(context).pushNamed(testRawRoute);
          final future =
              Navigator.of(context).pushNamed(debugAutoStartRouteName!);
          // ignore: deprecated_member_use_from_same_package
          debugAutoStartRouteName = null;
          await future;
          // await Navigator.of(context).pushNamed(testExceptionRoute);
        }
      }
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(
          title: const Center(
              child: Text('Sqflite demo', textAlign: TextAlign.center)),
        ),
        body:
            ListView.builder(itemBuilder: _itemBuilder, itemCount: _itemCount));
  }

  //new Center(child: new Text('Running on: $_platformVersion\n')),

  Widget _itemBuilder(BuildContext context, int index) {
    return MainItemWidget(widget._items[index], (MainItem item) {
      Navigator.of(context).pushNamed(item.route!);
    });
  }
} 

Download Details:

Author: tekartik

Source Code: https://github.com/tekartik/sqflite

#flutter  #sqllite 

What is GEEK

Buddha Community

SQLite plugin for Flutter

Google's Flutter 1.20 stable announced with new features - Navoki

Flutter Google cross-platform UI framework has released a new version 1.20 stable.

Flutter is Google’s UI framework to make apps for Android, iOS, Web, Windows, Mac, Linux, and Fuchsia OS. Since the last 2 years, the flutter Framework has already achieved popularity among mobile developers to develop Android and iOS apps. In the last few releases, Flutter also added the support of making web applications and desktop applications.

Last month they introduced the support of the Linux desktop app that can be distributed through Canonical Snap Store(Snapcraft), this enables the developers to publish there Linux desktop app for their users and publish on Snap Store.  If you want to learn how to Publish Flutter Desktop app in Snap Store that here is the tutorial.

Flutter 1.20 Framework is built on Google’s made Dart programming language that is a cross-platform language providing native performance, new UI widgets, and other more features for the developer usage.

Here are the few key points of this release:

Performance improvements for Flutter and Dart

In this release, they have got multiple performance improvements in the Dart language itself. A new improvement is to reduce the app size in the release versions of the app. Another performance improvement is to reduce junk in the display of app animation by using the warm-up phase.

sksl_warm-up

If your app is junk information during the first run then the Skia Shading Language shader provides for pre-compilation as part of your app’s build. This can speed it up by more than 2x.

Added a better support of mouse cursors for web and desktop flutter app,. Now many widgets will show cursor on top of them or you can specify the type of supported cursor you want.

Autofill for mobile text fields

Autofill was already supported in native applications now its been added to the Flutter SDK. Now prefilled information stored by your OS can be used for autofill in the application. This feature will be available soon on the flutter web.

flutter_autofill

A new widget for interaction

InteractiveViewer is a new widget design for common interactions in your app like pan, zoom drag and drop for resizing the widget. Informations on this you can check more on this API documentation where you can try this widget on the DartPad. In this release, drag-drop has more features added like you can know precisely where the drop happened and get the position.

Updated Material Slider, RangeSlider, TimePicker, and DatePicker

In this new release, there are many pre-existing widgets that were updated to match the latest material guidelines, these updates include better interaction with Slider and RangeSliderDatePicker with support for date range and time picker with the new style.

flutter_DatePicker

New pubspec.yaml format

Other than these widget updates there is some update within the project also like in pubspec.yaml file format. If you are a flutter plugin publisher then your old pubspec.yaml  is no longer supported to publish a plugin as the older format does not specify for which platform plugin you are making. All existing plugin will continue to work with flutter apps but you should make a plugin update as soon as possible.

Preview of embedded Dart DevTools in Visual Studio Code

Visual Studio code flutter extension got an update in this release. You get a preview of new features where you can analyze that Dev tools in your coding workspace. Enable this feature in your vs code by _dart.previewEmbeddedDevTools_setting. Dart DevTools menu you can choose your favorite page embed on your code workspace.

Network tracking

The updated the Dev tools comes with the network page that enables network profiling. You can track the timings and other information like status and content type of your** network calls** within your app. You can also monitor gRPC traffic.

Generate type-safe platform channels for platform interop

Pigeon is a command-line tool that will generate types of safe platform channels without adding additional dependencies. With this instead of manually matching method strings on platform channel and serializing arguments, you can invoke native class and pass nonprimitive data objects by directly calling the Dartmethod.

There is still a long list of updates in the new version of Flutter 1.2 that we cannot cover in this blog. You can get more details you can visit the official site to know more. Also, you can subscribe to the Navoki newsletter to get updates on these features and upcoming new updates and lessons. In upcoming new versions, we might see more new features and improvements.

You can get more free Flutter tutorials you can follow these courses:

#dart #developers #flutter #app developed #dart devtools in visual studio code #firebase local emulator suite in flutter #flutter autofill #flutter date picker #flutter desktop linux app build and publish on snapcraft store #flutter pigeon #flutter range slider #flutter slider #flutter time picker #flutter tutorial #flutter widget #google flutter #linux #navoki #pubspec format #setup flutter desktop on windows

Adobe XD plugin for Flutter with CodePen Tutorial

Recently Adobe XD releases a new version of the plugin that you can use to export designs directly into flutter widgets or screens. Yes, you read it right, now you can make and export your favorite design in Adobe XD and export all the design in the widget form or as a full-screen design, this can save you a lot of time required in designing.

What we will do?
I will make a simple design of a dialogue box with a card design with text over it as shown below. After you complete this exercise you can experiment with the UI. You can make your own components or import UI kits available with the Adobe XD.

#developers #flutter #adobe xd design export to flutter #adobe xd flutter code #adobe xd flutter code generator - plugin #adobe xd flutter plugin #adobe xd flutter plugin tutorial #adobe xd plugins #adobe xd to flutter #adobe xd tutorial #codepen for flutter.

Terry  Tremblay

Terry Tremblay

1598396940

What is Flutter and why you should learn it?

Flutter is an open-source UI toolkit for mobile developers, so they can use it to build native-looking** Android and iOS** applications from the same code base for both platforms. Flutter is also working to make Flutter apps for Web, PWA (progressive Web-App) and Desktop platform (Windows,macOS,Linux).

flutter-mobile-desktop-web-embedded_min

Flutter was officially released in December 2018. Since then, it has gone a much stronger flutter community.

There has been much increase in flutter developers, flutter packages, youtube tutorials, blogs, flutter examples apps, official and private events, and more. Flutter is now on top software repos based and trending on GitHub.

Flutter meaning?

What is Flutter? this question comes to many new developer’s mind.

humming_bird_dart_flutter

Flutter means flying wings quickly, and lightly but obviously, this doesn’t apply in our SDK.

So Flutter was one of the companies that were acquired by **Google **for around $40 million. That company was based on providing gesture detection and recognition from a standard webcam. But later when the Flutter was going to release in alpha version for developer it’s name was Sky, but since Google already owned Flutter name, so they rename it to Flutter.

Where Flutter is used?

Flutter is used in many startup companies nowadays, and even some MNCs are also adopting Flutter as a mobile development framework. Many top famous companies are using their apps in Flutter. Some of them here are

Dream11

Dream11

NuBank

NuBank

Reflectly app

Reflectly app

Abbey Road Studios

Abbey Road Studios

and many more other apps. Mobile development companies also adopted Flutter as a service for their clients. Even I was one of them who developed flutter apps as a freelancer and later as an IT company for mobile apps.

Flutter as a service

#dart #flutter #uncategorized #flutter framework #flutter jobs #flutter language #flutter meaning #flutter meaning in hindi #google flutter #how does flutter work #what is flutter

Juana  O'Keefe

Juana O'Keefe

1599003000

View SQLite DB in Flutter Application

In this small article, I’ll show you how you can see the data in SQLite DB of your flutter application. In the previous two articles, we’ve covered SQLite CRUD Operation in Flutter App.

Credit to Stack Overflow answer here.

Where is Databases are stored ?

When you create an SQLite DB programmically, did you thought about physically location of the DB? If you are coming from web development, you might look into project explorer for a file with the extension ‘db’. But it’s not in the project directory.

It depends on the environment – android or IOS. To find the location of the database we’ve used a package  path_provider in previous article. The db will be stored somewhere in the emulator. To see the path I’ve added the  print statement in  _initDatabase method of  DatabaseHelper class.

Copy to Clipboard

_initDatabase() async {
    ...
    String dbPath = join(dataDirectory.path, _databaseName);
    print('db location : '+dbPath);
    ...
  }

Now to see the printed output, If you are on VS Code, open  Debug Console ( from view menu ). In my case it prints as follows.

View SQLite DB physical location

#flutter #flutter article #how to query sqlite db in flutter #view database in flutter app #view sqlite db in flutter app

Brain  Crist

Brain Crist

1602147600

Flutter App Development Trends 2020

As the new decade dawns upon us, a slew of technologies has been making a lot of noise to grab the developers’ attention. While native app development is going strong, the trade winds are now blowing towards going cross-platform.

Adobe PhoneGap, React Native, Xamarin and Ionic are all leaving no stone unturned to be the undefeated champion of cross-platform development. Still, Google’s Flutter is all set to take them all on at once.

There are a tonne of resources available online to learn about Flutter, and you can start with this step by step flutter guide.

With reduced code development time, increased time-to-market speed, near-native performance, and a bevy of advantages under its hood, Flutter is set to dominate the market this decade.

Before we take a look at trends making the Flutter race ahead in 2020, let us do a quick recap of what Flutter is, for those who have been living under a rock.

#flutter #flutter-for-mobile-app #flutter-app-development #mobile-app-development #flutter-trends #software-development #advantages-of-flutter-mobile #pros-and-cons-of-flutter