Hello,

First, thanks a lot for this library which is working fine for me on Android and Linux.

I'm trying to make a Windows release and I can do it but only if I add in the root folder a .packages file containing sqflite_common_ffi:lib/ (the lib/ folder being as this: lib/src/windows/sqlite3.dll (<=> the sqlite dll found in C:\Users\{USERNAME}\AppData\Roaming\Pub\Cache\hosted\pub.dartlang.org\sqflite_common_ffi-1.1.1+1

You can find an example of my project below:

• It is working when I add all the elements
• If I try to modify the lib/src/windows/sqlite3.dll path or the .packages file, the application is showing nothing

Note that it also works perfectly fine if my release folder in under {FLUTTER_PROJECT}/build/windows/runner/Release/ and without any .packages nor lib/src/windows/sqlite3.dll (I guess the app is searching in 'prefolders' and finds the .packages (and then the sqlite dll file in AppData) => To reproduce, you need put the windows executable outside your flutter project !

Here is my pubspec.yaml dependencies:

environment:
  sdk: ">=2.1.0 <3.0.0"

dependencies:
  flutter:
    sdk: flutter

  flutter_localizations:
    sdk: flutter

  # Show HTML content
  flutter_html: ^1.1.1
  # Icons
  cupertino_icons: ^1.0.0
  # API requests
  http: ^0.12.2
  # Internal storage
  sqflite: ^1.3.2
  path_provider: ^1.6.24
  # Share content outside the application
  share: ^0.6.5
  # Ads
  admob_flutter: ^1.0.1

dev_dependencies:
  flutter_test:
    sdk: flutter

  # Internal storage
  sqflite_common_ffi: ^1.1.1
  # Searchable dropdown list
  # TODO: Use official version when this issue will be solved: https://github.com/icemanbsi/searchable_dropdown/issues/117
  searchable_dropdown:
    git:
      url: https://github.com/FunnyLabz/searchable_dropdown.git
      ref: master

Here is how I initialized the project:

// Platform
import 'package:flutter/foundation.dart';

// For database handling
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:sqflite/sqflite.dart';

void main() {
  if ((defaultTargetPlatform == TargetPlatform.windows || defaultTargetPlatform == TargetPlatform.linux) && !kIsWeb) {
    // Initialize FFI to be able to use database with Windows/Linux
    // We use 'sqflite_ffi' if Linux or Windows, else we can use default sqlite (Android/IOS/Mac)
    sqfliteFfiInit();
    // Change the default factory
    databaseFactory = databaseFactoryFfi;
  }
  runApp(new MyApp());
}

The error using openDatabase is as follows:MissingPluginException(No implementation found for method openDatabase on channel com.tekartik.sqflite).How should I solve it?

Hi,

thank you for making sqlite available for Flutter - I need to use sqlite with encryption - do you know how the normal way to use encryption with sqlite could be implemented?

https://www.sqlite.org/see/doc/trunk/www/readme.wiki

Thanks!

The method 'execute' and also the method ".query" is throwing an error:

• NoSuchMethodError: The method 'execute' was called on null.
• NoSuchMethodError: The method 'query' was called on null.

import 'dart:async';
import 'dart:io';
import 'package:flutter_app/datenbank/Rezepte.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';

class DatabaseClient{
  Database database;

  //Datenbank wird erstellt
  Future erstellen() async{
    Directory pfad = await getApplicationDocumentsDirectory();
    String datenbankPfad = join(pfad.path, "rezept_buch.db");

    database = await openDatabase(datenbankPfad, version: 1, onCreate: this.erstelleTabellen);
  }

  //Die Tabellen werden erstellt
  Future erstelleTabellen(Database db, int version) async{
    await database.execute("""  //The error is on this line
      create table rezept (
        id integer primary key,
        name text not null,
        personen_anzahl integer not null,
        beschreibung text default null,
        favorit integer default 0
      )
    """);
  }
  
  
  Future setRezept(Rezepte rezepte) async{
    var count = Sqflite.firstIntValue(await database.rawQuery("SELECT * FROM rezept WHERE name =?", [rezepte.name]));
    if(count == 0){
      rezepte.id = await database.insert("rezept", rezepte.toMap());
    } else {
      await database.update("rezept", rezepte.toMap(), where: "id = ?", whereArgs: [rezepte.id]);
    }

    return rezepte;
  }

  //Daten aus Tabellen holen
  Future getAllRezepte(int id) async{
    List ergebnisse = await database.query("rezept", columns: Rezepte.spalten, where: "id=?",whereArgs: [id]);   // error on this line
    Rezepte rezepte = Rezepte.fromMap(ergebnisse[0]);

    return rezepte;
  }
}`

Opening a tracking issue to migrate this repo to use null-safe code. Let's discuss how we can arrange this. The best way is to create a branch that is kept in sync with master until the code can be pushed to master.

@alextekartik @davidmorgan as FYI

I'd like to iterate over a result set with millions of rows, which is not feasible with the current API which loads the entire result set into memory before iteration can begin.

A cursor-based API could potentially be made with BidirectionalIterator.

When attempting to build on iOS, I get the following error message Import of shadowed module 'FMDB'

In my project, I'm using a Swift package that also depends on FMDB.

This is due to new file SqfliteImport.h

#ifndef SqfliteImport_h
#define SqfliteImport_h

#if TARGET_OS_IPHONE
#import <Flutter/Flutter.h>
#else
#import <FlutterMacOS/FlutterMacOS.h>
#endif

#import <fmdb/FMDB.h> // <- shadowed module due to this

#endif // SqfliteImport_h

I tried conditionally including FMDB header unsuccessfully

#ifndef FMDBReturnAutoreleased
#import <fmdb/FMDB.h>
#else
@import FMDB;
#endif

Any idea how to work around this?

During opening of the database I alter the original SQL and add foreign keys to the table and encounter a table sqlite_master may not be modified error. The only happens when I run on iOS 14.

I have checked and pragma writable_schema is 1, so my expectation is that the table is writable.

The only similar issue I could find is: https://github.com/Nozbe/WatermelonDB/issues/772

flutter: error DatabaseException(Error Domain=FMDatabase Code=1 "table sqlite_master may not be modified" UserInfo={NSLocalizedDescription=table sqlite_master may not be modified}) sql 'UPDATE sqlite_master SET sql = replace(sql, 'cloud_file_id INT,', 'cloud_file_id INT REFERENCES cloudfile (id),') WHERE name = 'attachment' AND type = 'table'' args []} during open, closing...

flutter: DatabaseException(Error Domain=FMDatabase Code=1 "table sqlite_master may not be modified" UserInfo={NSLocalizedDescription=table sqlite_master may not be modified}) sql 'UPDATE sqlite_master SET sql = replace(sql, 'cloud_file_id INT,', 'cloud_file_id INT REFERENCES cloudfile (id),') WHERE name = 'attachment' AND type = 'table'' args []}
flutter: #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
<asynchronous suspension>
#1      SqfliteDatabaseFactoryImpl.wrapDatabaseException (package:sqflite/src/factory_impl.dart:78:7)
#2      SqfliteDatabaseMixin.safeInvokeMethod (package:sqflite_common/src/database_mixin.dart:208:15)
#3      SqfliteDatabaseMixin.txnRawUpdate.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:408:14)
#4      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:312:26)
#5      SqfliteDatabaseMixin.txnWriteSynchronized (package:sqflite_common/src/database_mixin.dart:345:7)
#6      SqfliteDatabaseMixin.txnRawUpdate (package:sqflite_common/src/database_mixin.dart:407:12)
#7      SqfliteDatabaseExecutorMixin._rawUpdate (package:sqflite_common/src/database_mixin.dart:143:15)
#8      SqfliteDatabaseExecutorMixin.rawUpdate (package:sqflite_common/src/database_mixin.dart:135:12)
#9      DatabaseMeta.changeIntToForeignKey (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:202:14)
#10     DatabaseMeta.addForeignKey.<anonymous closure> (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:30:66)
#11     DatabaseMeta.alterDatabaseInPlace.<anonymous closure> (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:114:20)
<asynchronous suspension>
#12     DatabaseMeta.alterDatabaseInPlace.<anonymous closure> (package:sa_jaguar_orm_base/config/DatabaseMeta.dart)
#13     SqfliteDatabaseMixin._runTransaction (package:sqflite_common/src/database_mixin.dart:475:28)
<asynchronous suspension>
#14     SqfliteDatabaseMixin.transaction.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:492:14)
#15     SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:312:26)
#16     SqfliteDatabaseMixin.txnWriteSynchronized (package:sqflite_common/src/database_mixin.dart:345:7)
#17     SqfliteDatabaseMixin.transaction (package:sqflite_common/src/database_mixin.dart:491:12)
#18     DatabaseMeta.alterDatabaseInPlace (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:109:23)
#19     DatabaseMeta.addForeignKey (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:30:13)
<asynchronous suspension>
#20     Version0000.addForeignKeysAndIndexes (package:resolution_app/resolution/db/versions/0000.dart:73:24)
<asynchronous suspension>
#21     Version0000.execute (package:resolution_app/resolution/db/versions/0000.dart:27:11)
#22     BaseDatabaseSetup.onCreate (package:sa_jaguar_orm_base/config/BaseDatabaseSetup.dart:40:25)
<asynchronous suspension>
#23     BaseDatabaseSetup.onOpen (package:sa_jaguar_orm_base/config/BaseDatabaseSetup.dart:23:15)
<asynchronous suspension>
#24     SqfliteDatabaseMixin.doOpen (package:sqflite_common/src/database_mixin.dart:727:29)
<asynchronous suspension>
#25     SqfliteDatabaseOpenHelper.openDatabase (package:sqflite_common/src/database.dart:46:22)
#26     SqfliteDatabaseFactoryMixin.openDatabase.<anonymous closure> (package:sqflite_common/src/factory_mixin.dart:104:43)
<asynchronous suspension>
#27     SqfliteDatabaseFactoryMixin.openDatabase.<anonymous closure> (package:sqflite_common/src/factory_mixin.dart)
#28     ReentrantLock.synchronized.<anonymous closure>.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:35:24)
#29     _rootRun (dart:async/zone.dart:1190:13)
#30     _CustomZone.run (dart:async/zone.dart:1093:19)
#31     _runZoned (dart:async/zone.dart:1630:10)
#32     runZoned (dart:async/zone.dart:1550:10)
#33     ReentrantLock.synchronized.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:34:24)
#34     BasicLock.synchronized (package:synchronized/src/basic_lock.dart:32:26)
#35     ReentrantLock.synchronized (package:synchronized/src/reentrant_lock.dart:30:17)
#36     SqfliteDatabaseFactoryMixin.openDatabase (package:sqflite_common/src/factory_mixin.dart:71:17)
#37     openDatabase (package:sqflite/sqflite.dart:145:26)
#38     DatabaseProvider._createDatabase (package:sa_jaguar_orm_base/DatabaseProvider.dart:76:39)
<asynchronous suspension>
#39     ReentrantLock.synchronized.<anonymous closure>.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:35:24)
#40     _rootRun (dart:async/zone.dart:1190:13)
#41     _CustomZone.run (dart:async/zone.dart:1093:19)
#42     _runZoned (dart:async/zone.dart:1630:10)
#43     runZoned (dart:async/zone.dart:1550:10)
#44     ReentrantLock.synchronized.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:34:24)
#45     BasicLock.synchronized (package:synchronized/src/basic_lock.dart:32:26)
#46     ReentrantLock.synchronized (package:synchronized/src/reentrant_lock.dart:30:17)
#47     DatabaseProvider.adapter (package:sa_jaguar_orm_base/DatabaseProvider.dart:38:19)
#48     UserBean.get (package:resolution_app/resolution/user/model/UserBean.dart:27:70)
#49     AuthenticationServiceImpl.onLogin (package:resolution_app/api/AuthenticationServiceImpl.dart:134:46)
<asynchronous suspension>
#50     BaseHttpApiAction.process.<anonymous closure> (package:api_companion/api/http/BaseHttpApiAction.dart:95:21)
...

I've got a flutter app using both sqflite_ffi, with a script that works fine when I run it as pure dart but crashes with this error when I trigger it as part of the flutter app:

I/flutter (18313): [SqfliteFfiException(error, Invalid argument(s): Failed to load dynamic library (dlopen failed: library "/data/data/com.twoplayerchess/lib/libsqlite3.so" not found)} DatabaseException(Invalid argument(s): Failed to load dynamic library (dlopen failed: library "/data/data/com.twoplayerchess/lib/libsqlite3.so" not found)) {}, #0      SqfliteIsolate.handle (package:sqflite_common_ffi/src/isolate.dart:39:9)
I/flutter (18313): <asynchronous suspension>
I/flutter (18313): #1      FfiMethodCallHandler._isolateHandle (package:sqflite_common_ffi/src/database_factory_ffi.dart:54:27)
I/flutter (18313): #2      FfiMethodCallHandler.handleInIsolate (package:sqflite_common_ffi/src/database_factory_ffi.dart:33:26)
I/flutter (18313): #3      databaseFactoryFfiImpl.<anonymous closure> (package:sqflite_common_ffi/src/database_factory_ffi.dart:17:25)
I/flutter (18313): #4      _SqfliteDatabaseFactoryImpl.invokeMethod (package:sqflite_common/src/mixin/factory.dart:20:27)
I/flutter (18313): #5      SqfliteDatabaseMixin.invokeMethod (package:sqflite_common/src/database_mixin.dart:287:15)
I/flutter (18313): #6      SqfliteDatabaseMixin.saf

Flutter doctor -v

[✓] Flutter (Channel stable, 1.20.2, on Mac OS X 10.14.6 18G2022, locale en-GB)
    • Flutter version 1.20.2 at /Users/alex/Developer/flutter
    • Framework revision bbfbf1770c (4 weeks ago), 2020-08-13 08:33:09 -0700
    • Engine revision 9d5b21729f
    • Dart version 2.9.1

 
[✓] Android toolchain - develop for Android devices (Android SDK version 29.0.3)
    • Android SDK at /Users/alex/Library/Android/sdk
    • Platform android-29, build-tools 29.0.3
    • Java binary at: /Applications/Android
      Studio.app/Contents/jre/jdk/Contents/Home/bin/java
    • Java version OpenJDK Runtime Environment (build
      1.8.0_242-release-1644-b3-6222593)
    • All Android licenses accepted.

[✓] Xcode - develop for iOS and macOS (Xcode 11.3.1)
    • Xcode at /Applications/Xcode.app/Contents/Developer
    • Xcode 11.3.1, Build version 11C504
    • CocoaPods version 1.9.1

[✓] Android Studio (version 4.0)
    • Android Studio at /Applications/Android Studio.app/Contents
    • Flutter plugin version 48.0.2
    • Dart plugin version 193.7361
    • Java version OpenJDK Runtime Environment (build
      1.8.0_242-release-1644-b3-6222593)

[!] IntelliJ IDEA Community Edition (version 2020.1)
    • IntelliJ at /Applications/IntelliJ IDEA CE.app
    ✗ Flutter plugin not installed; this adds Flutter specific functionality.
    ✗ Dart plugin not installed; this adds Dart specific functionality.
    • For information about installing plugins, see
      https://flutter.dev/intellij-setup/#installing-the-plugins

[✓] VS Code (version 1.48.2)
    • VS Code at /Applications/Visual Studio Code.app/Contents
    • Flutter extension version 3.14.1

[✓] Connected device (1 available)
    • Android SDK built for x86 (mobile) • emulator-5554 • android-x86 • Android 10
      (API 29) (emulator)

! Doctor found issues in 1 category.

I'm also using the standard sqflite plugin as part of this app for something totally separate. Don't know if that affects anything but that part is running fine. I'm only using sqflite_ffi for this part because it needs to run outside flutter sometimes too and sqflite doesn't support that. I'm not doing anything special when it crashes, just opening a static asset database.

Hi, I'm wondering if there is a way to workaround this problem when running flutter test with sqflite. I know that there is also a similar problem with path_provider when testing because getting a directory for use with tests is different (https://flutter.io/cookbook/persistence/reading-writing-files/).

After following the testing steps on that page I was able to get past the first problem with path_provider, but then get the following:

MissingPluginException(No implementation found for method openDatabase on channel com.tekartik.sqflite)
package:flutter/src/services/platform_channel.dart 153:7   MethodChannel.invokeMethod
  ===== asynchronous gap ===========================
  dart:async                                                 _Completer.completeError
  package:flutter/src/services/platform_channel.dart         MethodChannel.invokeMethod
  ===== asynchronous gap ===========================
  dart:async                                                 _asyncThenWrapperHelper
  package:flutter/src/services/platform_channel.dart 146:74  MethodChannel.invokeMethod
  package:sqflite/src/sqflite_impl.dart 17:28                invokeMethod
  ===== asynchronous gap ===========================
  dart:async                                                 new Future.microtask
  package:sqflite/src/sqflite_impl.dart 16:69                invokeMethod
  package:sqflite/src/database.dart 197:7                    SqfliteDatabase.invokeMethod
  package:sqflite/src/database.dart 459:14                   SqfliteDatabase._openDatabase.<fn>
  package:sqflite/src/exception.dart 78:28                   wrapDatabaseException
  ===== asynchronous gap ===========================
  dart:async                                                 new Future.microtask
  package:sqflite/src/exception.dart 76:62                   wrapDatabaseException
  package:sqflite/src/database.dart 458:12                   SqfliteDatabase._openDatabase
  package:sqflite/src/database.dart 506:28                   SqfliteDatabase.open
  ===== asynchronous gap ===========================
  dart:async                                                 new Future.microtask
  package:sqflite/src/database.dart 487:39                   SqfliteDatabase.open
  package:sqflite/src/database.dart 610:19                   openDatabase
  package:sqflite/sqflite.dart 280:5                         openDatabase
  package:flutter_app/services/data.service.dart 28:28       DataService.initDb

This is what I have tried and it doesn't seem to have worked, the tests run forever:

final Directory directory = await Directory.systemTemp.createTemp();
final String path = join(directory.path, "main.db");

const MethodChannel('com.tekartik.sqflite')
        .setMockMethodCallHandler((MethodCall methodCall) async {
      if (methodCall.method == 'openDatabase') {
        SqfliteDatabase db = new SqfliteDatabase(path);
        return db.open(version: 1, onCreate: _onCreate);
      }
      return null;
});

The latest version of 2.2.1, compilation failed on the ios platform， error message: "Module 'FMDB' not found", my ios project does not contain any swift code.

SqfliteDatabaseException: DatabaseException(open_failed /var/mobile/Containers/Data/Application/494EB596-3835-462C-8164-2B773A19A1C4/Documents/main.db) File "exception_impl.dart", line 11, in wrapDatabaseException File "database_mixin.dart", line 737, in SqfliteDatabaseMixin.openDatabase File "database_mixin.dart", line 839, in SqfliteDatabaseMixin.doOpen File "database.dart", line 46, in SqfliteDatabaseOpenHelper.openDatabase File "factory_mixin.dart", line 110, in SqfliteDatabaseFactoryMixin.openDatabase. ... (20 additional frame(s) were not displayed)

Platform: IOS IOS version: 16.2 Device: iPhone 13 pro max. flutter: 3.3.3

Code: databaseFactory.openDatabase(path, options: options);

On first launch this exception is thrown, but if I close app and relaunch it then it works fine.

My table is shown below:

await db.execute(
          '''
          CREATE TABLE IF NOT EXISTS $maintransaction(
            id TEXT PRIMARY KEY NOT NULL,
            userId TEXT NOT NULL,
            walletId TEXT NOT NULL,
            selectCategoryId TEXT NOT NULL,
            currencyId TEXT NOT NULL,
            transactionCategoryId TEXT NOT NULL,
            eventId TEXT NOT NULL,
            currencyCode TEXT NOT NULL,
            currencySymbol TEXT NOT NULL,
            transactionAmount REAL NOT NULL,
            isCredit INTEGER NOT NULL,
            isDebit INTEGER NOT NULL,
            transactionDate TEXT NOT NULL,
            transactionWith TEXT NOT NULL,
            transactionNote TEXT NOT NULL,
            reminderTime TEXT NOT NULL,
            selectCategoryImage TEXT NOT NULL,
            createdAt TEXT,
            updatedAt TEXT NOT NULL)
          ''',
        );

I want to query using the transactionDate, to get all the transactions that happened last month.

I get the following error:

no such function: regexp, SQL logic error (code 1)

while executing:

select * from Cards where Cards.Rules regexp '(.*?)Add\s\{.*?\}(.*)')

Maybe sqflite was compiled without RegEx support?

If I open an existing SQLITE DB which is not created by SQFLITE and where the user version PRAGMA is 0 then the DB is erased. The onCreate is called during openDatabase instead of onUpgrade and although the query is CREATE TABLE IF NOT EXISTS... all tables are finally empty. Isn't that a wrong assumption that if user version is 0 then the database doesn't exist and must be created? I've got plenty of DBs working perfectly fine on Android/iOS where the user version PRAGMA is 0. However even though the onCreate is called the "IF NOT EXISTS" should protect data, but it doesnt.

The code in the database_mixin.dart responsible for that is:

https://github.com/tekartik/sqflite/blob/1f624edbd48edf751550a39e66da890d25b5493c/sqflite_common/lib/src/database_mixin.dart#L904-L926

sqflite assumes a good level of knowledge of both SQLite and flutter. If you have questions/issues regard SQLite itself, consider asking stack overflow instead. Thanks !