Best Practice for creating SQLite DB with several Tables in Ionic 2

Ionic Framework ionic-v3
1

Hello everyone!

I am fairly new to Ionic 2 and am trying to create online/offline synchronization for my app. I am using SQLite on the device and mssql on the server. I followed some tutorials and know how to create an sqlite db with tables and everything in Ionic 2.
Most of these examples however only deal with creating 1 or 2 tables with very few columns.

However the code starts looking really terrible and hard to read and maintain if you have to deal with 8 Tables where some of them have 10 columns.
What would be a good way to handle this? i already moved everything DB related out to a provider and just call my own init/constructor function in the the main.ts file after platform.ready.
Does it even make sense to create the tables etc. with such a “big” DB from within the App?
Or would it be better to just create/design the DB-file with something like “SQLite Browser” and ship it with the App?
That way I would only need to handle the usual CRUD-Queries.
Thanks for the help :slight_smile:

2

Hi! Did you resolve this after? Im interested to know how to create multiple tables, using Ionic2 & sqlite. I cant seem to chain a couple of sql statements and create more than 1 table at the moment! I was hoping it would be as simple as this:

this.platform.ready().then(() => {
SQLite.openDatabase({
name: “myDB.db”,
location: “default”
})
.then((db: SQLite) => {
db.executeSql(‘CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, title TEXT)’, {}).then(() => {}).catch(() => {});
db.executeSql(‘CREATE TABLE IF NOT EXISTS table2 (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, title TEXT)’, {}).then(() => {}).catch(() => {})
})
.catch (error => this.showAlert(‘Error opening database’ + error));

… but apparently this isnt the way to chain a couple of CREATE TABLEs

Interested to hear your method!

thanks
Andy

3

I don’t know how to solve your problem.
But you could make it easier for yourself and use the catch() commands to actually log the error to see where your code actually fails.

Additionally my first guess would have been that you try to do the second db.executeSQL command before the first one has been successfully completed.
The reason for the “.then()” command is to wait for a successful completion of the code and only run the code in the “.then()” after the first part has been successfully completed.
In your case, the app waits for the DB to open, but then simply calls the first SQL-Command, and before that finishes, it starts with the second.
That would be my best guess for the error.

You should look into chaining promises (and how to do this in a flat way).

4

do something like this

this.platform.ready().then(() => {
      if (!this.isOpen) {
        this.storage = new SQLite();
        this.storage.openDatabase({
          name: "school.db",
          location: "default"
        }).then(() => {
          this.isOpen = true;
          this.createTables();
        }, (error) => {
          console.error("Unable to open database", error);
        });
createtables()
{
db.executeSql('CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, title TEXT)', {}).then((
here create next table and so one
) => {}).catch(() => {});}
2 Likes
5

Thanks @vks_gautam1 I didn’t have the time to write down the code for @eazyweb but this is how it should be done.
But I would still suggest him to read up on coding more flat/readable promises etc. because if he has multiple tables he wants to create, it can be very handy to have it in a more readable way.

1 Like
6

Hello, i used the @vks_gautam1 solution and did good for me… but at the end it was pretty messy to add new tables to create by te database.

I came up with this little recursive function:

    createTable(db: SQLiteObject, tables: String[], index: number) {
      if (index < tables.length) {
        db.executeSql(`${tables[index]}`, {})
        .then(() => {
          console.log('Executed: ', tables[index]);
          index++;
          this.createTable(db, tables, index);
        }).catch(e => console.log(e));
      }
    }

Where tables is an array of SQL Scripts.

Hope it works for you guys.

4 Likes