How to create multiple tables in a database in sqflite?

Question

Im building and app with flutter that uses SQLite database. I have created first table using this piece of code:

 void _createDb(Database db, int newVersion) async {
    await db.execute('''CREATE TABLE cards (id_card INTEGER PRIMARY KEY, 
         color TEXT, type TEXT, rarity TEXT, name TEXT UNIQUE, goldCost INTEGER,
         manaCost INTEGER, armor INTEGER, attack INTEGER, health INTEGER, description TEXT)''');
}

Table gets created and I can access it without problems.

Unfortunately I cannot include more than 1 table that i just created. I tried adding another SQL CREATE TABLE clause in the same method, and repeating method db.execute with a different SQL clause just in the next line.

I'm mimicing code from this tutorial: https://www.youtube.com/watch?v=xke5_yGL0uk

How to add another table within the same database?

If you can afford to lose data, then uninstall the app and run with the extra table included as you tried. This deletes the database and so **createDB** will run (it only runs once when the database is created). — MikeT, Jan 22 '19 at 20:55
What error message do you get? Do you pass a different table name for each table? — Günter Zöchbauer, Jan 22 '19 at 20:57
I tried uninstallig the app and clearing both local storage and cache. I cannot acces AS now, but IIRC i tried to execute ```CREATE TABLE IF EXISTS decks (id_deck INTEGER PRIMARY KEY, nameDeck TEXT)``` and when i INSERT data into it it says "table decks doesnt have column 'nameDeck'" — filiard, Jan 22 '19 at 21:08
You should be using `CREATE TABLE IF NOT EXISTS decks (id_deck INTEGER PRIMARY KEY, nameDeck TEXT)` (note **NOT** added). Again, delete the App's data/uninstall the App and try again. — MikeT, Jan 22 '19 at 22:27

score 33 · Answer 1 · answered Feb 28 '19 at 22:08

You can just combine multiple db.execute calls for exampple

await db.execute('''
      create table $reminderTable (
        $columnReminderId integer primary key autoincrement,
        $columnReminderCarId integer not null,
        $columnReminderName text not null,
        $columnReminderNotifyMileage integer not null,
        $columnReminderEndMileage integer not null
       )''');
await db.execute('''
       create table $carTable (
        $columnCarId integer primary key autoincrement,
        $columnCarTitle text not null
       )''');

score 25 · Answer 2 · answered Jul 17 '19 at 15:47

25

yes you can do it

 void _createDb(Database db, int newVersion) async {
 await db.execute('''
   create table $carTable (
    $columnCarId integer primary key autoincrement,
    $columnCarTitle text not null
   )''');
 await db.execute('''
   create table $userTable(
    $userId integer primary key autoincrement,
    $name text not null
   )''');
  }

but to speed up the process, let's assume we have 10 tables, you could use the batch this way

void _createDb(Database db, int newVersion) async {
Batch batch = db.batch();
batch.execute("Your query-> Create table if not exists");
batch.execute("Your query->Create table if not exists");
List<dynamic> res = await batch.commit();
//Insert your controls
}

answered Jul 17 '19 at 15:47

AlexPad

10,364
3
38
48

this piece of code is good if we wish to create multiple tables at SAME TIME but, if I need to create a table in an existing DB at a later point of time than how do I do it, or its not possible? – Speedy11 Dec 05 '19 at 15:41
I don't know if it's possible in runtime I've never tried. But you can try and let me know this way. In your DB Helper make sure to return the Database instance, take my code above to create the table and try this way. – AlexPad Apr 28 '20 at 14:13

score 7 · Answer 3 · edited Jul 31 '19 at 13:10

You can use a .sql file that contains your DB script.

First,add the script file to assets.

Then, import the following packages:

import 'package:path/path.dart';

import 'package:sqflite/sqflite.dart';

import 'package:flutter/services.dart' show rootBundle;

finally, use the following code

void _createDb() async 
{
      final database = openDatabase( join( await getDatabasesPath(), 'mydb.db'),
      onCreate: (db, version) async  
      {
          // call database script that is saved in a file in assets
          String script =  await rootBundle.loadString("assets\\db\\script.sql");
          List<String> scripts = script.split(";");
          scripts.forEach((v) 
          {
              if(v.isNotEmpty ) 
              {
                   print(v.trim());
                   db.execute(v.trim());
              }
          });
       },
       version: 1,
       );
}

score 4 · Answer 4 · edited May 13 '19 at 04:22

4

Change the name of the DB file. This will 'reset' your DB and creation will work.

e.g:

final dabasesPath = await getDatabasesPath(); 
final path = join(dabasesPath, "newName2.db");

edited May 13 '19 at 04:22

juancito

866
2
9
22

answered May 12 '19 at 21:35

Moa

41
3

score 4 · Answer 5 · edited May 04 '21 at 05:45

In openDatabase(path, onCreate, version) use one more optional parameter "onUpgrade" and define the dropping and again creating table scripts. and also upgrade (increase) the parameter version by one.

----- Code snippet ------

openDatabase(path, onCreate:_createDb, onUpgrade: onUpgrade,version:_DB_VERSION);
...
...

    _onUpgrade( Database db, int oldVersion, int newVersion ) async {

    Batch batch = db.batch();

    // drop first

    batch.execute("DROP TABLE IF EXISTS $_TABLE_3 ;");

    batch.execute("DROP TABLE IF EXISTS $_TABLE_2 ;");
    batch.execute("DROP TABLE IF EXISTS $_TABLE_1 ;");
    // then create again
    batch.execute("CREATE TABLE $TABLE_1 ...... ");
    batch.execute("CREATE TABLE $TABLE_2 ...... ");
    batch.execute("CREATE TABLE $TABLE_3 ...... ");
    List<dynamic> result = await batch.commit();

}

Note: Each and every time when you will create or change the structure of some table(s), you will have to increase database version in openDatabase() method. so that the upgradation will be called, otherwise it will not be called.

score 2 · Answer 6 · answered Feb 04 '19 at 08:15

Hard to say without seeing your openDatabase call and whether the database exists before or not. One of my guess is that you are still using the same database version. Once onCreate has been called it will never be called again. You should try to bump your database version and add the new table in on onUpgrade

score 0 · Answer 7 · answered Dec 01 '22 at 10:39

you can use db.execute for creation of multiple tables under single database.

Future _createDB(Database db, int version) async {
    await db.execute('CREATE TABLE users(userId INTEGER PRIMARY KEY, userName TEXT NOT NULL)');
    await db.execute('CREATE TABLE tasks(taskId INTEGER PRIMARY KEY, userId INTEGER, task TEXT NOT NULL, status BOOL NOT NULL)');
  }

How to create multiple tables in a database in sqflite?

7 Answers7

Linked