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 = <Your db name 'without .db'>;

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

like
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.

[michalpleszczynski]

I couldn't get it working for Android with any of the solutions pasted above, so in conjunction with code from this PR I arrived at something like this:

const getDatabase = async () => {
  if (database) return database;

  const dbAssetPath = 'myDbName.db';
  const realPathDest = isAndroid()
    ? `${rnfs.DocumentDirectoryPath}/databases/${dbAssetPath}`
    : `${rnfs.DocumentDirectoryPath}/${dbAssetPath}`;
  const exists = await rnfs.exists(realPathDest);
  if (!exists) {
    if (isAndroid()) {
      await rnfs.mkdir(`${rnfs.DocumentDirectoryPath}/databases`);
      await rnfs.copyFileAssets(dbAssetPath, realPathDest);
    } else {
      await rnfs.copyFile(
        `${rnfs.MainBundlePath}/frequencies.db`,
        realPathDest,
      );
    }
  }

  const adapter = new SQLiteAdapter({
    schema,
    dbName: realPathDest.replace('/databases', ''),
  });

  database = new Database({
    adapter,
    modelClasses: [Frequency],
    actionsEnabled: true,
  });
  return database;
};

Maybe it'll prove useful if the PR for Android path is ever merged.

[sidferreira]

Follows a gist with my current use:

https://gist.github.com/sidferreira/2b6b640e25544d601f311e35e430bce0

@radex should we add this to the docs?

[radex]

@sidferreira What do you propose? In docs, this whole discussion is linked: https://github.com/Nozbe/WatermelonDB/blob/master/docs-master/Advanced/ProTips.md

[sidferreira]

@radex My suggestion was just about having clear documentation about it, instead of a link to a thread that will force the developer to search for a solution. IMHO clearly state one can embed a 🍉 DB is a major selling point.

[radex]

@sidferreira OK, PRs are welcome.

[ultra-mine]

What is the work around for the schema overriding the pre-populated database that I am adding.

[KrisLau]

@radex Is there a way of adding a event handler to the DB setup kind of like the onSetupError but something like onInitialSetup for when the local database is initially created on the device?

[radex]

@KrisLau Not currently, but you can easily implement it in app code by setting a one-time flag at launch. if the flag isn't there, it means the db is new

[Stophface]

@ultra-mine Did you find a solution for this?

[ultra-mine]

@Stophface I took a detour and use the turbo login solution

[Stophface]

@michalpleszczynski @sidferreira @cursivedgreat Could you share the absolute path to your databases von iOS and Android? I stored my databases in .../ios/App/www (for iOS) and .../android/app/src/main/assets/www (for Android).

However, when I try

import RNFS from 'react-native-fs';

const targetDbName = `${RNFS.MainBundlePath}/foo.db`;
RNFS.exists(targetDbName).then(exists => {...});

It always tells me that it does not exist. Or do I have to set something in XCode/Android Studio that it bundles the database?

[sidferreira]

@michalpleszczynski @sidferreira @cursivedgreat Could you share the absolute path to your databases von iOS and Android? I stored my databases in .../ios/MapStar/www (for iOS) and .../android/app/src/main/assets/www (for Android).

However, when I try

import RNFS from 'react-native-fs';

const targetDbName = `${RNFS.MainBundlePath}/foo.db`;
RNFS.exists(targetDbName).then(exists => {...});

It always tells me that it does not exist. Or do I have to set something in XCode/Android Studio that it bundles the database?

You are running in a simulator right?

[Stophface]

@sidferreira Yes I am. I contact you on twitter.

[Stophface]

How to copy and move the bundled database to somewhere, where watermelonDB can work with it, follow along @sidferreira posts and gist. However, you need to do more to get it working.
The SQLite database needs two columns watermelonDB relies on, _status and _changed. Add them to all the tables you want watermelonDB to use! Additionally, make sure your prepopulated SQLite database has a column id of type text, not (!) integer.
However, don't describe that idcolumn (or _status, _changed) in your schema nor model.

// this is the create statement for the SQLite database. Note how id is of type TEXT and that there are the two columns watermelon relies on, _changed and _status
CREATE TABLE foo (
	id TEXT, -- <- Yep, TEXT, not INTEGER!
	a TEXT,
	b TEXT,
	_changed TEXT,
	_status TEXT
);

export const whatever = appSchema({
    version: 1, // <- this number is important! Why, keep on reading
    tables: [
        tableSchema({
            name: 'foo',
            columns: [
                // do not add the columns id, _changed, _status here
                { name: 'a', type: 'text' },
                { name: 'b', type: 'text' },
            ],
        }),
    ]
});

export class Foo extends Model {
    // do not add the columns id, _changed, _status here

    static table = 'foo';

    @field('a')
    a;

    @field('b')
    b;
};

Further, make sure the pragma user_version is set to the same as the version used in the schema. Here it is 1.

export const whatever = appSchema({
    version: 1, // <- this number must match the user_version of your SQLite table
    tables: [...
...

You can set that number for the SQLite database the following (https://www.sqlite.org/pragma.html#pragma_user_version):

PRAGMA <SCHEMA>.user_version = 1;

If that does not work, download a SQLite browser like DB Browser for SQLite, select your database, and look for Edit Pragma. In the bottom part you can set the user version too.

Then, when you create the SQLite Adapter, make sure you pass it the path to the database

const adapter = new SQLiteAdapter({
    schema: databaseSchema,
    dbName: PATH_TO_DATABASE,
    jsi: false,
});

new Database({
    adapter,
    modelClasses: [A, B, C],
});

Once you initialized the database, only pass the database name to the SQLite Adapter, not the complete path. Otherwise watermelonDB will overwrite your database.

const adapter = new SQLiteAdapter({
    schema: databaseSchema,
    dbName: NAME_OF_DATABASE,
    jsi: false,
});

new Database({
    adapter,
    modelClasses: [A, B, C],
});

[JerryHuang2015]

me too.
I am unable to backup the .db file to another location.

if (!watermelonSingleton) {
  const localDBFilePath = `${Platform.OS === 'ios' ? RNFS.MainBundlePath : RNFS.DocumentDirectoryPath}`/abc.db;
  console.log('localDBFilePath', localDBFilePath)
  const exists = await RNFS.exists(localDBFilePath);
  console.log('exists', exists)
  // this return false

I open the location on a mobile phone or emulator, but both don't have any files and don't know where they are located.
working fine without copying

any solutions?

[heyalexchoi]

I have an open PR to add sqlite backup and restore on iOS. May or may not be relevant here. I found that copying out the underlying database file (watermelon.db in this example) in a running app was not properly reflecting the data in the db.

#1391

[Stophface]

@heyalexchoi I am curious, could you share how you did it before creating a PR. With JavaScript I assume?

[Hostname47]

Anyone here could give us a link to a tutorial or something helps me as a beginner to have a prepopulated database with some default data !?

[Stophface]

@Hostname47 there are all the information you need in this thread. Read through it and follow along :)

[codaisa]

Hey everyone, this alredy have a native function? if not, have a lot of solutions in this issue, what is the best?

[sidferreira]

@Poowerllz I suggest following #774 (comment) as I helped @Stophface, and it was the last time I made it work. Sadly I'm not using WatermelonDB anymore.

[linhttbk97]

How to copy and move the bundled database to somewhere, where watermelonDB can work with it, follow along @sidferreira posts and gist. However, you need to do more to get it working. The SQLite database needs two columns watermelonDB relies on, _status and _changed. Add them to all the tables you want watermelonDB to use! Additionally, make sure your prepopulated SQLite database has a column id of type text, not (!) integer. However, don't describe that idcolumn (or _status, _changed) in your schema nor model.

// this is the create statement for the SQLite database. Note how id is of type TEXT and that there are the two columns watermelon relies on, _changed and _status
CREATE TABLE foo (
	id TEXT, -- <- Yep, TEXT, not INTEGER!
	a TEXT,
	b TEXT,
	_changed TEXT,
	_status TEXT
);

export const whatever = appSchema({
    version: 1, // <- this number is important! Why, keep on reading
    tables: [
        tableSchema({
            name: 'foo',
            columns: [
                // do not add the columns id, _changed, _status here
                { name: 'a', type: 'text' },
                { name: 'b', type: 'text' },
            ],
        }),
    ]
});

export class Foo extends Model {
    // do not add the columns id, _changed, _status here

    static table = 'foo';

    @field('a')
    a;

    @field('b')
    b;
};

Further, make sure the pragma user_version is set to the same as the version used in the schema. Here it is 1.

export const whatever = appSchema({
    version: 1, // <- this number must match the user_version of your SQLite table
    tables: [...
...

You can set that number for the SQLite database the following (https://www.sqlite.org/pragma.html#pragma_user_version):

PRAGMA <SCHEMA>.user_version = 1;

If that does not work, download a SQLite browser like DB Browser for SQLite, select your database, and look for Edit Pragma. In the bottom part you can set the user version too.

Then, when you create the SQLite Adapter, make sure you pass it the path to the database

const adapter = new SQLiteAdapter({
    schema: databaseSchema,
    dbName: PATH_TO_DATABASE,
    jsi: false,
});

new Database({
    adapter,
    modelClasses: [A, B, C],
});

Once you initialized the database, only pass the database name to the SQLite Adapter, not the complete path. Otherwise watermelonDB will overwrite your database.

const adapter = new SQLiteAdapter({
    schema: databaseSchema,
    dbName: NAME_OF_DATABASE,
    jsi: false,
});

new Database({
    adapter,
    modelClasses: [A, B, C],
});

So we cannot enable jsi for prepopulated database

[sidferreira]

@linhttbk97 Great guide! Indeed, adding many steps I did not mention. About the path, I used a relative path to still work with JSI, but it was back in the day. Sadly, I don't use Watermelon anymore.

[linhttbk97]

@linhttbk97 Great guide! Indeed, adding many steps I did not mention. About the path, I used a relative path to still work with JSI, but it was back in the day. Sadly, I don't use Watermelon anymore.

Yeah, I use database path on Android. I still can inspect database but it's a new database with addition columns like id, _status, _changed. But i can't find the that database file inside device explorer (support in android studio)

[sidferreira]

@linhttbk97 I remember there was a small trick to find it... I used the DOCUMENT folder to host the DB and make it easier

[linhttbk97]

@sidferreira
watermelondb.jsi com.recyclerviewstarionexample I Opened database at /data/user/0/com.recyclerviewstarionexample/files/linhtt_linhthan_test.db
I got a log as database was opened but the query result still empty

[rvibit]

How to copy and move the bundled database to somewhere, where watermelonDB can work with it, follow along @sidferreira posts and gist. However, you need to do more to get it working. The SQLite database needs two columns watermelonDB relies on, _status and _changed. Add them to all the tables you want watermelonDB to use! Additionally, make sure your prepopulated SQLite database has a column id of type text, not (!) integer. However, don't describe that idcolumn (or _status, _changed) in your schema nor model.

// this is the create statement for the SQLite database. Note how id is of type TEXT and that there are the two columns watermelon relies on, _changed and _status
CREATE TABLE foo (
	id TEXT, -- <- Yep, TEXT, not INTEGER!
	a TEXT,
	b TEXT,
	_changed TEXT,
	_status TEXT
);

export const whatever = appSchema({
    version: 1, // <- this number is important! Why, keep on reading
    tables: [
        tableSchema({
            name: 'foo',
            columns: [
                // do not add the columns id, _changed, _status here
                { name: 'a', type: 'text' },
                { name: 'b', type: 'text' },
            ],
        }),
    ]
});

export class Foo extends Model {
    // do not add the columns id, _changed, _status here

    static table = 'foo';

    @field('a')
    a;

    @field('b')
    b;
};

Further, make sure the pragma user_version is set to the same as the version used in the schema. Here it is 1.

export const whatever = appSchema({
    version: 1, // <- this number must match the user_version of your SQLite table
    tables: [...
...

You can set that number for the SQLite database the following (https://www.sqlite.org/pragma.html#pragma_user_version):

PRAGMA <SCHEMA>.user_version = 1;

If that does not work, download a SQLite browser like DB Browser for SQLite, select your database, and look for Edit Pragma. In the bottom part you can set the user version too.
Then, when you create the SQLite Adapter, make sure you pass it the path to the database

const adapter = new SQLiteAdapter({
    schema: databaseSchema,
    dbName: PATH_TO_DATABASE,
    jsi: false,
});

new Database({
    adapter,
    modelClasses: [A, B, C],
});

Once you initialized the database, only pass the database name to the SQLite Adapter, not the complete path. Otherwise watermelonDB will overwrite your database.

const adapter = new SQLiteAdapter({
    schema: databaseSchema,
    dbName: NAME_OF_DATABASE,
    jsi: false,
});

new Database({
    adapter,
    modelClasses: [A, B, C],
});

So we cannot enable jsi for prepopulated database

Why we can't use JSI with this?

[GitMurf]

Anyone know if following this guide of prepopulated database is the only way I can use watermelondb Sync engine without using watermelon for anything else? We have our own rendering and complex needs (and a desktop app) so all the watermelon rendering frontend stuff we don’t need. But we love the Sync engine.

Does anyone know how we can do that? Thanks!