Ionic2 - Prepopulated SQLite DB

Hello

I have hit a brick wall trying to include a pre-populated DB in my ionic app. I have scoured the interweb, but haven’t found a solution that works for Ionic2. No matter where I include the prepopulated db, the SQLite plugin can’t find the file and creates a new one.

Steps to reproduce

  1. I created a new blank starter project
  2. I then added the Cordova-SQlite-Ext plugin by following the instructions from Ionic Docs.
  3. Created a prepopulated DB in the www folder.
  4. Added code to open the db and run a select query (shown below)
  5. FAIL
$ ionic start PrePopDB
$ ionic cordova plugin add cordova-sqlite-storage
$ npm install --save @ionic-native/sqlite

Files modified

app.module.ts

...
import { HomePage } from '../pages/home/home';
import { SQLite } from '@ionic-native/sqlite'

@NgModule({
  declarations: [
...
home.ts

import { Component } from '@angular/core';
import { NavController, Platform } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite'

@Component({
  selector: 'page-home',
  templateUrl: 'home.html'
})
export class HomePage {

  constructor(public navCtrl: NavController, sqlite: SQLite, platform: Platform) {
    platform.ready().then(() => {
      sqlite.create({
        name: 'colors.db',
        location: 'default',
        createFromLocation: 1
      }).then((db: SQLiteObject) => {
        db.executeSql('select * from colors', {})
          .then(() => {
            console.log('Success')
          })
          .catch(e => console.error(JSON.stringify(e)));
      }).catch(e => console.error(JSON.stringify(e)));
    });
  }
}

Console Output

Ran it on an Android device
$ ionic cordova run android --device -l -cr

[21:58:24]  console.log: Angular is running in the development mode. Call enableProdMode() to enable the production mode.
[21:58:24]  console.log: Ionic Native: deviceready event fired after 1696 ms
[21:58:24]  console.warn: Native: tried calling StatusBar.styleDefault, but the StatusBar plugin is not installed.
[21:58:24]  console.warn: Install the StatusBar plugin: 'ionic cordova plugin add cordova-plugin-statusbar'
[21:58:24]  console.log: OPEN database: colors.db
[21:58:24]  console.log: new transaction is waiting for open operation
[21:58:24]  console.log: OPEN database: colors.db - OK
[21:58:24]  console.error: {"message":"sqlite3_prepare_v2 failure: no such table: colors","code":5}

You can browse the code on my github repo.

Can someone tell me what I’m doing wrong please? Thank you!

1 Like

Would be the import from a SQL dump or JSON file an option? After the first start of the app, you could populate your DB from that file using SQLite Porter. See also this blog post.

I’d prefer not having to do that as the DB is rather chunky. Thanks for the links though! I didn’t know about SQLite Porter. Will try it out.

Hi.
Did you solve the problem?
Actually I am having the same problem.
My application is not load existing database by “createFromLocation: 1” and create new database.
So “no such table”.


Object {message: “sqlite3_prepare_v2 failure: no such table: users”, code: 5}

Nope. Haven’t solved it yet. What’s happening is that the plugin isn’t able to find the db (perhaps the prepopulated db should be in another location and not the www location?) and creates a new one.

Dont put stuff in www as it is a generated folder

Use assets under src

Tom

Thanks for the suggestion Tom. I’ve placed the pre-populated db in the src/assets folder and also src/assets/database folder but no joy so far. Any idea what I am doing wrong?

Cordova SQLite Storage doesn’t use your app’s www folder to store data.

Mobile devices don’t allow for app executables’ data to be modified. This is to prevent installing an app that overwrites code in another app (imagine installing WhatsApp and it corrupts all other messaging apps so you can’t use them). It also makes it impossible for code to be injected into the app and take over the OS if your app security isn’t great.

To avoid this, the data is stored separately in storage somewhere that is OS-specific. This location can be set to a few options, such as external storage on Android or iCloud on iOS rather than default locations.

What can you do?

You can still access the /www contents of your cordova app through the file:// protocol. You cannot access storage without a plugin.

https://ionicframework.com/docs/native/file/ lets you access your app’s data directory directly and read, modify and write them.

So what you could do is read “file://android_asset/www/db.sqlite” using AJAX (Angular HTTP), then store that same data using the File plugin.

There are some specifics you will have to work out, such as where exactly SQLite keeps the database per platform.

What might be slightly easier is to do the same reading of file://android_asset/www/db.sqlite, but then use a provider to read the file and simply replicate it using Cordova SQLite rather than trying to directly create the database.

The process would work as so:

  1. Fetch the file from file://…/www/… using Angular Http OR using XMLHttpRequest, but set the responsetype to arraybuffer (so we get a Blob)
  2. Read the blob using something like sql.js (https://github.com/lovasoa/sql.js)
  3. As you select everything from the database, insert it into a new database using Ionic Native sqlite (cordova-sqlite-storage)

A disadvantage of this is it will take a lot of memory if your database is too large, and will be quite process intensive on your first app launch.