Watermelondb: Prepopulated database

[olhapi]

Is there any info/docs how to use prepopulated sqlite db with watermelon db?

[cristian-milea]

I too am looking for information about this and did not find anything / maybe my search skills are bad.
The best option I could think about is having an external API or a local JSON to load initial data but it seems an odd approach.

Did you find any solution?

[olhapi]

Hello @sidferreira @radex. Can you share some info on how to use prepopulated SQLite DB with WatermelonDB? We're struggling here with a lack of documentation on this one.

[radex]

I'm sure someone asked this before and I answered, but can't find it either ¯_(ツ)_/¯

There's no built-in support for this. One way is to generate a SQLite DB (you can use the the Node SQLite support in 0.19.0-2 pre-release or extract it from an ios/android app), bundle it with the app, and then use a bit of native code, to check if the DB you're expecting it available, and if not, making a copy of the default DB — before you attempt loading DB from JS side. Yes, some native code is required for that (or you could use an RN plugin to manipulate the FS)

Or you could prepare a JSON file compatible with the Watermelon Sync format, and use synchronize() to import it. Inefficient, and odd, but would work.

If you figure out exactly how to do this, please do share in this thread to help others.

[sidferreira]

We actually have a work around for it. Will post it later today.

[sidferreira]

@olhapi ( cc @radex )

let watermelonSingleton;

export const getWatermelon = async () => {
  if (!watermelonSingleton) {
    const dbName = `${RNFS.DocumentDirectoryPath}/watermelon.db`;
    const exists = await RNFS.exists(dbName);
    if (!exists) {
      await RNFS.copyFile(`${RNFS.MainBundlePath}/watermelon.db`, dbName);
    }

    const adapter = new SQLiteAdapter({
      schema,
      migrations,
      dbName,
    });

    watermelonSingleton = new Database({
      dbName,
      adapter,
      modelClasses,
      actionsEnabled: true,
    });
    watermelonSingleton.dbName = dbName;
  }
  return watermelonSingleton;
};

This is a very simplified version of it.
The caveat is that you need to use the getWatermelon async everywhere.

But that's not a big deal IMHO...

BTW, you need to add a .db file to the project properly. I added it to the assets folder, but under the hood, it is just in the main bundle root folder...

[olhapi]

@sidferreira thanks for sharing. Much appreciated!

[sidferreira]

I'll try to get a tutorial into the whole nodejs + prepopulate thing, but can't give any timeline on that

[cursivedgreat]

@sidferreira
RNFS.MainBundlePath fails on android.

[sidferreira]

@cursivedgreat I'll check the android version in the coming weeks and update accordingly... I guess RNFS.DocumentDirectoryPath would do the trick

[cursivedgreat]

Here is my version..
Kept the pre-populated db in asset folder.
I call this async method only first time.
for subsequent call, call normal method
Works on Android.

`var RNFS = require('react-native-fs');
const dbFileName = ;

let database;

const adapter = new SQLiteAdapter({
dbName: dbFileName,
schema: allSchemas
});

export async function getDatabaseAsync() {
//since watermelon db points to same folder as files
const dbName = ${RNFS.DocumentDirectoryPath}/../${dbFileName}.db;
try {
await RNFS.copyFileAssets(${dbFileName}.db, dbName);
} catch (error) {
console.log("ErrorCheck:: DB copy error", error);
}

database = new Database({
  dbName,
  adapter,
  modelClasses,
  actionsEnabled: true,
});

return database;
}`

[genesy]

Trying out your solutions. What do i have to do with the schema? seems it's overriding the tables for me

[sidferreira]

@cursivedgreat interesting approach... I have specific reasons to use the async every time :)

[genesy]

@sidferreira can you tell me how you setup your schemas? Do you just copy the one on the existing db?

[cursivedgreat]

Trying out your solutions. What do i have to do with the schema? seems it's overriding the tables for me

Although that solution worked for me. I rewrote my approach because of async reason.

Here is my updated steps
For Android:

Put the pre-populated copy code on native side.
First put your pre-populated db in asset folder. Then copy that asset to designated path just on first launch.
Here is copy code I've put in MainApplication.java

private boolean copyDBAsset(AssetManager assetManager, String toPath) { InputStream in = null; OutputStream out = null;; String fromAssetPath = "databaseName.db"; try { in = assetManager.open(fromAssetPath); new File(toPath).createNewFile(); out = new FileOutputStream(toPath); copyFile(in, out); in.close(); in = null; out.flush(); out.close(); out = null; return true; } catch(Exception e) { e.printStackTrace(); return false; } }

call the above method from onCreate method in MainApplication.java
`
AssetManager assetManager = getAssets();
String toPath = "/data/user/0//files/../databaseName.db";
try {
String[] list = assetManager.list(toPath);
if (list != null && list.length > 0) {

  } else {
    copyDBAsset(assetManager, toPath);
  }
} catch (Exception ignored) {
}

`

In javascript side do normal setup and that should work

[genesy]

i'm actually trying this out on ios simulator atm. on ios sim i am successfully copying the asset to the directory but it seems its making a new blank database copy because of the schema.

btw it's three backticks to make multiline code

this

[sidferreira]

@genesy I created a tool to create and update databases on command line. And it generates the DB file for me. Another option is to run the simulator and then use Finder (if iOS) to locate the .db file and copy it.

[genesy]

@sidferreira thanks for the quick reply. i actually have made a .db file successfully, my issue might be weird but when i do the copyDb file to the db it makes a new blank db using the schema from my AppSchema

my question is how did you do your schemas to match your database? manually? seems mine is creating a new.db file because of my schema definition

The db path here actually contains the correct .db file with all the tables but i can't query it

not sure if these dbName values are doing anything for me

    database = new Database({
      dbName: dbPath,
      adapter,
      modelClasses,
      actionsEnabled: true,
    });
    database.dbName = dbPath;

[fabianmedina09]

I'm sure someone asked this before and I answered, but can't find it either ¯_(ツ)_/¯

There's no built-in support for this. One way is to generate a SQLite DB (you can use the the Node SQLite support in 0.19.0-2 pre-release or extract it from an ios/android app), bundle it with the app, and then use a bit of native code, to check if the DB you're expecting it available, and if not, making a copy of the default DB — before you attempt loading DB from JS side. Yes, some native code is required for that (or you could use an RN plugin to manipulate the FS)

Or you could prepare a JSON file compatible with the Watermelon Sync format, and use synchronize() to import it. Inefficient, and odd, but would work.

If you figure out exactly how to do this, _please_ do share in this thread to help others.

Can We see a little example with this sync and JSON file implementation ? I need that for initializate the DB, or maybe you know a simpler way?

[genesy]

Ok I just solved my issue. I copied the new db that watermelondb generates and used that as my db file and insert all my items there. that was my issue.