Hi, could someone guide me how to copy a pre-filled ( prepopulated ) sqlite database in Ionic 2?
1 Like
Not ionic 2 specific, but it’s the same idea. You would take the file you want use as the DB data, and using the file plugin, pass this along
https://pouchdb.com/2016/04/28/prebuilt-databases-with-pouchdb.html
Hi, a couple of days ago I had the same issue - no clear solution found on this forum, however mine is:
npm install -g sqlite3
sqlite3 -csv -header database_name.db "SELECT * FROM table_name" > data.csv
npm install -g csvtojson
csvtojson data.csv > data.json
then you import freshly created json data through data service:
this.http.get('data/data.json')...
Hope this helps…
There must be a way to access resources without going through all the needless overhead of Http. It’s trivial using require with webpack.
I do agree with you rapropos, but the thing is - everybody talks about it, but nobody found it (the solution how to load prepopulated db I mean…) It is sad, because the topic is super clear and forum seems to help us with the knowledge and solutions 
Actually, it works just like it does in webpack. I just tested it.
let data = require('data/data.json');
Ok for JSON file source, but I have talked about the famous code:
this._db = win.sqlitePlugin.openDatabase(util_1.assign({
name: dbOptions.name,
location: location_1,
createFromLocation: dbOptions.existingDatabase ? 1 : 0
}, dbOptions));`
Worked as a charm in Ionic 1 and then in Ionic 2 does nothing - prefilled database is simply not imported…
Thanks anyway for the require solution that I is of course a valid one.
You can open an issue against ionic-native if you want, I suppose. Looks like that feature isn’t included in the stock cordova sqlite plugin that ionic-native uses, just the ext version. Personally, I find the entire concept of magic prepopulation rather fragile and prefer to just use SQL to initialize databases.
How do you then load initial data?
SQL INSERT statements.
I’ve tried to use File to copy the prepopulated sqlite file from www/ but I cannot figure out how to use File also.
In Ionic 1, all things are fine, but in Ionic 2, cordova.file.dataDirectory does not work. If you have experience about this case, pleas share with us 
SQL INSERT statements.
Would it be possible to have an exapmle of your load service/function?
The insert statement suppose to be run once - only if DB doesn’t exists and traight after table creation. How to then detect it - I mean avoid:
SELECT * FROM XXX and if(data.res.rows.length === 0)
statements for each table load.
I use a special version table to decide whether the database has been initialized; this also allows for upgrades to the app to modify databases created by older versions.
private _initializeDb(): void {
if (win.sqlitePlugin) {
let openparms = {
name: _SQLITE_DB_NAME,
location: 2, // local backup
createFromLocation: 0
};
this._db = win.sqlitePlugin.openDatabase(openparms);
} else {
console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
this._db = win.openDatabase(_SQLITE_DB_NAME, '1.0', 'database', 5 * 1024 * 1024);
}
this._query("SELECT version FROM version")
.then((dbrv) => {
// dbrv.rows.item(0).version is the version
// database has been initialized
},
(dbrv) => {
// database needs initialization
this._batch([
"CREATE TABLE version (version INT NOT NULL PRIMARY KEY)",
["INSERT INTO version (version) VALUES ($1)", [1]],
// more CREATE TABLEs and INSERTs
])
.then(() => {
console.log("completed database initialization");
}, (dbrv) => {
console.log(JSON.stringify(dbrv.err));
});
});
}
If you’re confident that you’ll only ever be running on device, you don’t need the special case initialization logic for WebSQL, and you can replace _batch() with the plugin’s executeSqlBatch(). I prefer to maintain WebSQL compatibility by rolling a version of batch that works with transactions:
private _batch(stmts: any[]): Promise<any> {
let rv: Promise<any> = new Promise((resolve, reject) => {
let wincb = (dbrv: any) => {
resolve(dbrv);
};
let losecb = (tx: any, err: Error) => {
reject(err);
return false;
};
this._db.transaction((tx: any) => {
let nstmts = stmts.length;
for (let ix = 0; ix < nstmts; ix++) {
let sql:string;
let phs:any[];
if (Array.isArray(stmts[ix])) {
sql = stmts[ix][0];
phs = stmts[ix][1];
} else {
sql = stmts[ix];
phs = [];
}
tx.executeSql(sql, phs, undefined, losecb);
}
}, losecb, wincb);
});
return rv;
}
Great, many thanks…
There is a section (using Angular) that may be helpful. He seems to indicate the pre-populated database can be created and shipped with the app, and if that is so (unless it needs to be
pre-populated at app run-time) Im not sure I see why you even need to use ionic/angular. HTH
Hi rapropos, could you please give me a clue how do you prepopulate the DB table with about 500 entries using your logic… It gonna be a huge array:
> this._batch([...............]
Did you solve this? I’m trying to solve a similar issue for a week with no luck. My DB table has 20K entries 
Hi, don’t know if you still need help but if you still need help i figured it out.
Basically you need to use the plugin cordova-sqlite-ext.
-
To install use ionic plugin add cordova-sqlite-ext
-
Copy your Database file to the /www folder
-
In one of your classes (i tried it on MyApp class contructor after the platfor.ready(…) do this:
this.storage = new Storage(SqlStorage,
{
name: ‘WUT.db’, <- Name of your database .db
location: 1,<- Don’t know if this makes any difference
backupFlag: SqlStorage.BACKUP_LOCAL,
existingDatabase: true <-This way you’re looking for an existing DB
}); -
Now do something like
storage.query(‘SELECT * from table’).then
(
(data) => { console.log(data): },
(error) => { console.log(error); }
});
Hi ruialves_programming, sorry for the late reply. Well in fact I have sopped to develop the Ionic part of the project fo a while. To reply: No no solution found till now, but I will check yours asap and give you a feedback. Thanks.
1 Like
Hi,
Please report back when you do.