Flutter, Sqflite – DatabaseException(no such table: Project…)

So I have a class DBProvider, which is creating a new instance whenever it hasn’t been initialized yet. The database currently contains 8 tables that are created in 1 query (the same thing happens when there is only 1 table). Then I’m trying to retrieve one of the tables, but it fails on “no such table” every time.

This is shorter version of my DBProvider

class DBProvider {
  DBProvider._();

  static final DBProvider db = DBProvider._();
  static Database _database;

  Future<Database> get database async {
    if (_database != null)
      return _database;

    // if _database is null we instantiate it
    _database = await initDB();
    return _database;
  }

  static const tableProject = """
  CREATE TABLE IF NOT EXISTS Project (
        id TEXT PRIMARY KEY,
        managerId TEXT,
        consultantID TEXT
        name TEXT,
        description TEXT,
        created TEXT,
        deadline TEXT
      );""";
  static const tableAudit = """
  CREATE TABLE IF NOT EXISTS Audit (
        id TEXT PRIMARY key,
        projectId TEXT,
        timeTrackId TEXT,
        jsonChanges TEXT,
        date TEXT,
        employeeId TEXT
      );""";
  static const tableEmployee = """
  CREATE TABLE IF NOT EXISTS Employee (
        id TEXT PRIMARY key,
        fullName TEXT,
        managementLogonAccess INTEGER
      );""";
  static const tableJobPosition = """
  CREATE TABLE IF NOT EXISTS JobPosition (
        id TEXT PRIMARY KEY,
        name TEXT
      );""";
  static const tableWorkType = """
  CREATE TABLE IF NOT EXISTS WorkType (
        id TEXT PRIMARY key,
        name TEXT
      );""";
  static const tableAssignedJobPosition = """
  CREATE TABLE IF NOT EXISTS AssignedJobPosition (
        employeeId TEXT,
        positionId TEXT
      );""";
  static const tableTimeTrack = """
  CREATE TABLE IF NOT EXISTS TimeTrack (
        id TEXT PRIMARY key,
        timeSpan INTEGER,
        employeeId TEXT,
        projectId TEXT,
        workType TEXT,
        note TEXT,
        date TEXT
      );""";
  static const tableAllowedWorkType = """
  CREATE TABLE IF NOT EXISTS AllowedWorkType (
        projectId TEXT,
        workTypeId TEXT
      );""";

  Future<Database> initDB() async {
    print("initDB executed");
    //Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(await getDatabasesPath(), "core.db");
    await deleteDatabase(path);
    return await openDatabase(path, version: 2,
        onCreate: (Database db, int version) async {
      await db.execute(
          tableEmployee +
              tableAudit +
              tableProject +
              tableJobPosition +
              tableWorkType +
              tableAssignedJobPosition +
              tableTimeTrack +
              tableAllowedWorkType
      );
    });
  }

 
  ///get all Projects
  Future/*<List<Project>>*/ getAllProjects() async{
    final db = await database;
    return await db.query("Project");
    /*var res =
    return res.isNotEmpty ? res.map((c) => Project.fromMap(c, false)).toList() : [];*/
  }

Afterwards I call it in main like following:

var res = await DBProvider.db.getAllProjects();
    print(res);

Answer

You can copy paste run full code below
Step 1: You can change onCreate and use await db.execute for each table

onCreate: (Database db, int version) async {
          await db.execute(tableEmployee);
          await db.execute(tableAudit);
          await db.execute(tableProject);
          await db.execute(tableJobPosition);
          await db.execute(tableWorkType);
          await db.execute(tableAssignedJobPosition);
          await db.execute(tableTimeTrack);
          await db.execute(tableAllowedWorkType);

Step 2: onCreate will only execute in the first time when running your APP
Any code revise after that will not create db or modify table
you can directly change db name from core.db to core1.db
Or delete core.db with Device File Explorer

String path = join(await getDatabasesPath(), "core1.db");

full test code

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

class DBProvider {
  DBProvider._();

  static final DBProvider db = DBProvider._();
  static Database _database;

  Future<Database> get database async {
    if (_database != null) return _database;

    // if _database is null we instantiate it
    _database = await initDB();
    return _database;
  }

  static const tableProject = """
  CREATE TABLE IF NOT EXISTS Project (
        id TEXT PRIMARY KEY,
        managerId TEXT,
        consultantID TEXT
        name TEXT,
        description TEXT,
        created TEXT,
        deadline TEXT
      );""";
  static const tableAudit = """
  CREATE TABLE IF NOT EXISTS Audit (
        id TEXT PRIMARY key,
        projectId TEXT,
        timeTrackId TEXT,
        jsonChanges TEXT,
        date TEXT,
        employeeId TEXT
      );""";
  static const tableEmployee = """
  CREATE TABLE IF NOT EXISTS Employee (
        id TEXT PRIMARY key,
        fullName TEXT,
        managementLogonAccess INTEGER
      );""";
  static const tableJobPosition = """
  CREATE TABLE IF NOT EXISTS JobPosition (
        id TEXT PRIMARY KEY,
        name TEXT
      );""";
  static const tableWorkType = """
  CREATE TABLE IF NOT EXISTS WorkType (
        id TEXT PRIMARY key,
        name TEXT
      );""";
  static const tableAssignedJobPosition = """
  CREATE TABLE IF NOT EXISTS AssignedJobPosition (
        employeeId TEXT,
        positionId TEXT
      );""";
  static const tableTimeTrack = """
  CREATE TABLE IF NOT EXISTS TimeTrack (
        id TEXT PRIMARY key,
        timeSpan INTEGER,
        employeeId TEXT,
        projectId TEXT,
        workType TEXT,
        note TEXT,
        date TEXT
      );""";
  static const tableAllowedWorkType = """
  CREATE TABLE IF NOT EXISTS AllowedWorkType (
        projectId TEXT,
        workTypeId TEXT
      );""";

  Future<Database> initDB() async {
    print("initDB executed");
    //Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(await getDatabasesPath(), "core1.db");
    await deleteDatabase(path);
    return await openDatabase(path, version: 2,
        onCreate: (Database db, int version) async {
          await db.execute(tableEmployee);
          await db.execute(tableAudit);
          await db.execute(tableProject);
          await db.execute(tableJobPosition);
          await db.execute(tableWorkType);
          await db.execute(tableAssignedJobPosition);
          await db.execute(tableTimeTrack);
          await db.execute(tableAllowedWorkType);
      /*await db.execute(tableEmployee +
          tableAudit +
          tableProject +
          tableJobPosition +
          tableWorkType +
          tableAssignedJobPosition +
          tableTimeTrack +
          tableAllowedWorkType);*/
    });
  }

  ///get all Projects
  Future/*<List<Project>>*/ getAllProjects() async {
    final db = await database;
    return await db.query("Project");
    /*var res =
    return res.isNotEmpty ? res.map((c) => Project.fromMap(c, false)).toList() : [];*/
  }
}

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

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
        visualDensity: VisualDensity.adaptivePlatformDensity,
      ),
      home: MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  final String title;

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

class _MyHomePageState extends State<MyHomePage> {
  int _counter = 0;

  void _incrementCounter() async {
    var res = await DBProvider.db.getAllProjects();
    print(res);
    setState(() {
      _counter++;
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            Text(
              'You have pushed the button this many times:',
            ),
            Text(
              '$_counter',
              style: Theme.of(context).textTheme.headline4,
            ),
          ],
        ),
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _incrementCounter,
        tooltip: 'Increment',
        child: Icon(Icons.add),
      ),
    );
  }
}

Leave a Reply

Your email address will not be published. Required fields are marked *