Pragma are not set when write inside SQL DB file.

[EArminjon]

I created a DB and would like to put inside its scheme some pragma rules. On open, I should be able to use the database using theses pragma.

In the following example, i would like to set the following pragma inside my database sql file.

PRAGMA recursive_triggers = true;

After opening the database, the pragma is not set but when I manually set the pragma inside onConfigure it works... That mean, pragma are well skipped in this scenario.

Code example bellow :

import 'dart:io';

import 'package:flutter/material.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

void main() => runApp(
      const MaterialApp(
        home: Home(),
      ),
    );

class Home extends StatefulWidget {
  const Home({super.key});

  @override
  State<Home> createState() => _HomeState();
}

class _HomeState extends State<Home> {
  bool? pragmaEnabled;

  Future<void> fromDBFile() async {
    final Directory directory = await getApplicationDocumentsDirectory();
    const String dbName = "test.db";
    final String dbPath = "${directory.path}/$dbName";
    final File dbFile = File(dbPath);
    await dbFile.writeAsString(
      """
        PRAGMA recursive_triggers = true;
      """,
      mode: FileMode.writeOnly,
    );

    final Database db = await openDatabase(
      dbPath,
      version: 1,
      onCreate: (Database db, _) async {
        final List<Map<String, Object?>> result =
            await db.rawQuery("PRAGMA recursive_triggers;");
        print("TEST-fromDBFile: $result");
        pragmaEnabled = result.first['recursive_triggers'] == 1;
        setState(() {});
      },
    );
    await db.close();
  }

  Future<void> fromDBOnConfigure() async {
    final Directory directory = await getApplicationDocumentsDirectory();
    const String dbName = "test.db";
    final String dbPath = "${directory.path}/$dbName";
    final File dbFile = File(dbPath);
    await dbFile.writeAsString(
      "",
      mode: FileMode.writeOnly,
    );

    final Database db = await openDatabase(
      dbPath,
      version: 1,
      onConfigure: (Database db) async {
        await db.rawQuery("PRAGMA recursive_triggers = true;");
      },
      onCreate: (Database db, _) async {
        final List<Map<String, Object?>> result =
            await db.rawQuery("PRAGMA recursive_triggers;");
        print("TEST-fromDBOnConfigure: $result");
        pragmaEnabled = result.first['recursive_triggers'] == 1;
        setState(() {});
      },
    );

    await db.close();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("Pragma issue"),
        centerTitle: true,
      ),
      body: Center(
        child: Column(
          children: <Widget>[
            const SizedBox(height: 16),
            OutlinedButton(
              onPressed: fromDBFile,
              child: const Text("From DB file"),
            ),
            const SizedBox(height: 16),
            OutlinedButton(
              onPressed: fromDBOnConfigure,
              child: const Text("From onConfigure method"),
            ),
            const SizedBox(height: 16),
            Text("Pragma is set ? $pragmaEnabled"),
          ],
        ),
      ),
    );
  }
}

Screenshot :

Using the code example above you can run a demo app using the two methods to set the pragma :

• from the sql db file
• from the onConfigute callback