Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Prepopulated database #774

Open
olhapi opened this issue Jul 28, 2020 · 47 comments
Open

Prepopulated database #774

olhapi opened this issue Jul 28, 2020 · 47 comments

Comments

@olhapi
Copy link

olhapi commented Jul 28, 2020

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

@cristian-milea
Copy link

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
Copy link
Author

olhapi commented Aug 3, 2020

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
Copy link
Collaborator

radex commented Aug 3, 2020

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
Copy link
Contributor

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

@sidferreira
Copy link
Contributor

@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
Copy link
Author

olhapi commented Aug 3, 2020

@sidferreira thanks for sharing. Much appreciated!

@sidferreira
Copy link
Contributor

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

@cursivedgreat
Copy link

cursivedgreat commented Aug 24, 2020

@sidferreira
RNFS.MainBundlePath fails on android.

@sidferreira
Copy link
Contributor

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

@cursivedgreat
Copy link

cursivedgreat commented Aug 25, 2020

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
Copy link

genesy commented Sep 13, 2020

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

@sidferreira
Copy link
Contributor

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

@genesy
Copy link

genesy commented Sep 14, 2020

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

@cursivedgreat
Copy link

cursivedgreat commented Sep 14, 2020

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
Copy link

genesy commented Sep 14, 2020

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
Copy link
Contributor

@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
Copy link

genesy commented Sep 14, 2020

@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
image

not sure if these dbName values are doing anything for me

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

@fabianmedina09
Copy link

fabianmedina09 commented Sep 14, 2020

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
Copy link

genesy commented Sep 14, 2020

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
Copy link

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
Copy link
Contributor

sidferreira commented Apr 2, 2021

Follows a gist with my current use:

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

@radex should we add this to the docs?

@radex
Copy link
Collaborator

radex commented May 28, 2021

@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
Copy link
Contributor

@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
Copy link
Collaborator

radex commented May 28, 2021

@sidferreira OK, PRs are welcome.

@ultra-mine
Copy link

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

@KrisLau
Copy link
Contributor

KrisLau commented Sep 8, 2021

@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
Copy link
Collaborator

radex commented Sep 13, 2021

@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
Copy link

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

@ultra-mine
Copy link

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

@Stophface
Copy link

Stophface commented Apr 24, 2022

@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
Copy link
Contributor

@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
Copy link

Stophface commented Apr 24, 2022

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

@Stophface
Copy link

Stophface commented May 3, 2022

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
Copy link

JerryHuang2015 commented May 23, 2022

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
Copy link

heyalexchoi commented Sep 14, 2022

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
Copy link

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

@Hostname47
Copy link

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
Copy link

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

@codaisa
Copy link

codaisa commented Mar 27, 2023

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

@sidferreira
Copy link
Contributor

@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
Copy link

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
Copy link
Contributor

@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
Copy link

@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
Copy link
Contributor

@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
Copy link

@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
Copy link

rvibit commented May 2, 2024

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
Copy link

GitMurf commented Sep 13, 2024

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests