How to Create Multiple Tables in Flutter SQFlite Database?

Are you looking to manage multiple tables within a SQFlite database in your Flutter application but unsure where to start? Setting up and interacting with multiple tables can be a key requirement in many app development scenarios. In this guide, we will walk you through a step-by-step process to help you create and work with multiple tables in your Flutter SQFlite database seamlessly.

Setting Up Your Project

Before diving into creating multiple tables, ensure that you have set up a Flutter project and added the SQFlite dependency to your pubspec.yaml file. To do this, open your project's pubspec.yaml file and add the following dependency:

1dependencies:
2  sqflite: ^2.0.0+2

Once you have added the dependency, run flutter pub get in your terminal to install the SQFlite package.

Creating the Database Helper Class

To effectively manage multiple tables in your SQFlite database, it is essential to create a separate database helper class. This class will handle tasks such as creating the database, initializing tables, and executing SQL queries. Below is an example of how you can structure your database helper class:

1import 'package:sqflite/sqflite.dart';
2import 'package:path_provider/path_provider.dart';
3import 'package:path/path.dart' as p;
4import 'dart:io';
5
6class DBHelper {
7  static final _databaseName = 'myDatabase.db';
8  static final _databaseVersion = 1;
9
10  static final table1 = 'table1';
11  static final table2 = 'table2';
12
13  DBHelper._privateConstructor();
14  static final DBHelper instance = DBHelper._privateConstructor();
15
16  static Database _database;
17
18  Future<Database> get database async {
19    if (_database != null) return _database;
20    _database = await _initDatabase();
21    return _database;
22  }
23
24  _initDatabase() async {
25    Directory documentsDirectory = await getApplicationDocumentsDirectory();
26    String path = p.join(documentsDirectory.path, _databaseName);
27    return await openDatabase(path, version: _databaseVersion, onCreate: _onCreate);
28  }
29
30  Future _onCreate(Database db, int version) async {
31    await db.execute('''
32      CREATE TABLE $table1 (
33        id INTEGER PRIMARY KEY,
34        name TEXT
35      )
36    ''');
37    
38    await db.execute('''
39      CREATE TABLE $table2 (
40        id INTEGER PRIMARY KEY,
41        description TEXT
42      )
43    ''');
44  }
45}

In the above code snippet, we have defined a DBHelper class that manages database operations for tables table1 and table2. The _initDatabase method initializes the database and calls the _onCreate method to create the necessary tables.

Interacting with Multiple Tables

Once you have set up your database helper class and defined your tables, you can start interacting with the tables by executing SQL queries. Below is an example of how you can insert data into table1 and table2:

1class DBHelper {
2  // Existing code
3  
4  Future<int> insertData1(Map<String, dynamic> row) async {
5    Database db = await instance.database;
6    return await db.insert(table1, row);
7  }
8
9  Future<int> insertData2(Map<String, dynamic> row) async {
10    Database db = await instance.database;
11    return await db.insert(table2, row);
12  }
13}

In the above example, we have added methods insertData1 and insertData2 to insert data into table1 and table2, respectively. You can similarly define methods for updating, deleting, and querying data from multiple tables based on your requirements.

Example Usage

To demonstrate how you can interact with multiple tables in your Flutter SQFlite database, consider the following example:

1void main() async {
2  var db = DBHelper.instance;
3
4  // Insert data into table1
5  await db.insertData1({'id': 1, 'name': 'John Doe'});
6
7  // Insert data into table2
8  await db.insertData2({'id': 1, 'description': 'Sample Description'});
9
10  // Fetch data from table1
11  List<Map<String, dynamic>> table1Data = await db.getTable1Data();
12  print(table1Data);
13
14  // Fetch data from table2
15  List<Map<String, dynamic>> table2Data = await db.getTable2Data();
16  print(table2Data);
17}

In the example above, we initiate the database connection using DBHelper.instance and perform operations such as inserting data into table1 and table2, as well as fetching data from both tables.

By following the steps outlined in this guide, you should now have a solid understanding of how to create and work with multiple tables within a SQFlite database in your Flutter application. Managing multiple tables efficiently is crucial for organizing and accessing data effectively in your app. Feel free to explore further SQL operations and database management functionalities to enhance your app development experience.