react-native-sqlite-storage

This is a fork of https://github.com/andpor/react-native-sqlite-storage in order to:

• Have a well maintained repository to fix bug (Android 11 fix: https://github.com/andpor/react-native-sqlite-storage/issues/462)
• Have same SQLite version on iOS & Android, independent of OS version, and up to date (version: 3.43.1 - 2023-09-11)
• Add extra extensions (UUID)
• Make easy to build & update SQLite version and driver

SQLite3 Native Plugin for React Native for both Android & iOS.

Features:

1. iOS and Android supported via identical JavaScript API.
2. Android in pure Java and Native modes
3. SQL transactions
4. JavaScript interface via plain callbacks or Promises.
5. Pre-populated SQLite database import from application bundle and sandbox
6. Same version of SQLite independent of OS version with JSON, UUID, REGEX & BASE64 extensions (version: 3.43.1 - 2023-09-11)

There are sample apps provided in test directory that can be used in with the AwesomeProject generated by React Native. All you have to do is to copy one of those files into your AwesomeProject replacing index.ios.js.

Please let me know your projects that use these SQLite React Native modules. I will list them in the reference section. If there are any features that you think would benefit this library please post them.

The library has been tested with React 18.2, React Native 0.72.0 and XCode 15.4 - it works fine out of the box without any need for tweaks or code changes.

Installation

# Install this lib as an alias of react-native-sqlite-storage
yarn add react-native-sqlite-storage@npm:@boltcode/react-native-sqlite-storage

# Add typescript if need
yarn add -D @types/react-native-sqlite-storage

As react-native-sqlite-storage original repository is kind of industry standard for React Native and is used in a lot of awesome library (typeorm for example), using an alias make us able to use this package as it was the original one, so package that depend on react-native-sqlite-storage will not break.

Also note that you should continue to import the library like:

# CommonJS
var SQLite = require('react-native-sqlite-storage');

# Typescript
import SQLite from 'react-native-sqlite-storage';

iOS

Don't forget to run cd ios && pod install && cd .. to link the library with iOS.

Android

Since we now only use the SQLite version bundled with this library, there is no extra steps on Android.
Note: if you come from the original repository you may delete what you added in react-native.config.js under dependency -> platforms -> android.

How to use

Promises

Promise are not enable by default, if you want to enable it, run:

SQLite.enablePromise(false);

In the following documentation, we consider enablePromise is enable.

Opening a database

Opening a database is slightly different between iOS and Android. Where as on Android the location of the database file is fixed, there are three choices of where the database file can be located on iOS. The 'location' parameter you provide to openDatabase call indicated where you would like the file to be created. This parameter is neglected on Android.

WARNING: the default location on iOS has changed in version 3.0.0 - it is now a no-sync location as mandated by Apple so the release is backward incompatible.

To open a database in default no-sync location (affects iOS only)::

const db = await SQLite.openDatabase({name: 'my.db', location: 'default'});

To specify a different location (affects iOS only):

const db = await SQLite.openDatabase({name: 'my.db', location: 'Library'});

where the location option may be set to one of the following choices:

• default: Library/LocalDatabase subdirectory - NOT visible to iTunes and NOT backed up by iCloud
• Library: Library subdirectory - backed up by iCloud, NOT visible to iTunes
• Documents: Documents subdirectory - visible to iTunes and backed up by iCloud
• Shared: app group's shared container - see next section

Opening a database in an App Group's Shared Container (iOS)

If you have an iOS app extension which needs to share access to the same DB instance as your main app, you must use the shared container of a registered app group.

Assuming you have already set up an app group and turned on the "App Groups" entitlement of both the main app and app extension, setting them to the same app group name, the following extra steps must be taken:

Step 1 - supply your app group name in all needed Info.plists

In both ios/MY_APP_NAME/Info.plist and ios/MY_APP_EXT_NAME/Info.plist (along with any other app extensions you may have), you simply need to add the AppGroupName key to the main dictionary with your app group name as the string value:

<plist version="1.0">
<dict>
  <!-- ... -->
  <key>AppGroupName</key>
  <string>MY_APP_GROUP_NAME</string>
  <!-- ... -->
</dict>
</plist>

Step 2 - set shared database location

When calling SQLite.openDatabase in your React Native code, you need to set the location param to 'Shared':

await SQLite.openDatabase({name: 'my.db', location: 'Shared'});

Importing a pre-populated database.

You can import an existing - prepopulated database file into your application. Depending on your instructions in openDatabase call, the sqlite-storage will look at different places to locate you pre-populated database file.

Use this flavor of openDatabase call, if your folder is called www and data file is named the same as the dbName - testDB in this example

await SQLite.openDatabase({name : "testDB", createFromLocation : 1});

Use this flavor of openDatabase call if your folder is called data rather than www or your filename does not match the name of the db. In this case db is named testDB but the file is mydbfile.sqlite which is located in a data subdirectory of www

await SQLite.openDatabase({name : "testDB", createFromLocation : "~data/mydbfile.sqlite"});

Use this flavor of openDatabase call if your folder is not in application bundle but in app sandbox i.e. downloaded from some remote location. In this case the source file is located in data subdirectory of Documents location (iOS) or FilesDir (Android).

await SQLite.openDatabase({name : "testDB", createFromLocation : "/data/mydbfile.sqlite"});

Additional options for pre-populated database file

You can provide additional instructions to sqlite-storage to tell it how to handle your pre-populated database file. By default, the source file is copied over to the internal location which works in most cases but sometimes this is not really an option particularly when the source db file is large. In such situations you can tell sqlite-storage you do not want to copy the file but rather use it in read-only fashion via direct access. You accomplish this by providing an additional optional readOnly parameter to openDatabase call

await SQLite.openDatabase({name : "testDB", readOnly: true, createFromLocation : "/data/mydbfile.sqlite"});

Note that in this case, the source db file will be open in read-only mode and no updates will be allowed. You cannot delete a database that was open with readOnly option. For Android, the read only option works with pre-populated db files located in FilesDir directory because all other assets are never physically located on the file system but rather read directly from the app bundle.

Attaching another database

Sqlite3 offers the capability to attach another database to an existing database-instance, i.e. for making cross database JOINs available. This feature allows to SELECT and JOIN tables over multiple databases with only one statement and only one database connection. To archieve this, you need to open both databases and to call the attach()-method of the destination (or master) -database to the other ones.

const dbSecond = await SQLite.openDatabase({name: 'second'});
const dbMaster = await SQLite.openDatabase({name: 'master'});
await dbMaster.attach("second", "second");

The first argument of attach() is the name of the database, which is used in SQLite.openDatabase(). The second argument is the alias, that is used to query on tables of the attached database.

The following statement would select data from the master database and include the "second"-database within a simple SELECT/JOIN-statement:

SELECT * FROM user INNER JOIN second.subscriptions s ON s.user_id = user.id

To detach a database, just use the detach()-method:

await dbMaster.detach('second');

For sure, their is also Callback-support available for attach() and detach().

Acknowledgements

Thank you to all authors & contributors of the following repository, PR, issues, without whom it would not have been possible:

• https://github.com/andpor/react-native-sqlite-storage
• https://github.com/liteglue/Android-sqlite-connector
• https://github.com/brodybits/Android-sqlite-ext-native-driver
• https://github.com/OutSystems/Android-sqlite-connector/pull/1
• https://github.com/OutSystems/Android-sqlite-native-driver/pull/1
• https://github.com/andpor/react-native-sqlite-storage/issues/462
• https://github.com/andpor/react-native-sqlite-storage/pull/494
• https://github.com/sqlg/gluegentools/
• https://github.com/brodybits/libb64-core/
• https://github.com/liteglue/sqlite-amalgamation/
• https://github.com/brodybits/sqlite3-base64
• https://github.com/brodybits/sqlite3-regexp-cached

How to reproduce

If you want to know how this repository is build & managed, you can follow the documentation under docs folder