Working with SQLite and Databases

1. Setting Up SQLite

Example: Installing SQLite Dependency

npm install react-native-sqlite-storage

Step	Description	Command
1	Install the SQLite library for React Native	`npm install react-native-sqlite-storage`
2	Link the library (if using React Native < 0.60)	`react-native link react-native-sqlite-storage`
3	Import the library in your component	`import SQLite from 'react-native-sqlite-storage'`

Note: Ensure that the SQLite library is properly linked for your React Native version to avoid runtime errors.

2. Creating Database Tables

Example: Creating a Users Table

import { open } from 'react-native-sqlite-storage';

const db = await open({
  name: 'MyDatabase.db',
  location: 'default'
});

await db.executeSql(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );
`);

Field	Type	Constraints	Description
id	`INTEGER`	PRIMARY KEY, AUTOINCREMENT	Unique identifier for each user record
name	`TEXT`	NOT NULL	User's full name
email	`TEXT`	UNIQUE, NOT NULL	User's email address (must be unique)
created_at	`DATETIME`	DEFAULT CURRENT_TIMESTAMP	Timestamp when the record was created

Note: The AUTOINCREMENT keyword ensures that IDs are never reused even if records are deleted.

Warning: Always define NOT NULL constraints on fields that must have values to avoid data integrity issues.

3. Executing SQL Queries

Example: Basic SELECT Query

import { open } from 'react-native-sqlite-storage';

async function fetchUsers() {
  const db = await open({
    name: 'MyDatabase.db',
    location: 'default'
  });

  const results = await db.executeSql('SELECT * FROM users');
  
  console.log(results[0].rows.raw());
  await db.close();
}

Method	Description	Parameters
executeSql	Executes a SQL statement	`sql`, `params`
transaction	Executes multiple SQL statements in a transaction	`callback`

Example: INSERT Query

async function insertUser(name, email) {
  const db = await open({
    name: 'MyDatabase.db',
    location: 'default'
  });

  const result = await db.executeSql(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, email]
  );

  console.log('Inserted row ID:', result[0].insertId);
  await db.close();
}

Note: Always use parameterized queries to prevent SQL injection.

Example: UPDATE Query

async function updateUser(id, newName) {
  const db = await open({
    name: 'MyDatabase.db',
    location: 'default'
  });

  const result = await db.executeSql(
    'UPDATE users SET name = ? WHERE id = ?',
    [newName, id]
  );

  console.log('Rows affected:', result[0].rowsAffected);
  await db.close();
}

Example: DELETE Query

async function deleteUser(id) {
  const db = await open({
    name: 'MyDatabase.db',
    location: 'default'
  });

  const result = await db.executeSql(
    'DELETE FROM users WHERE id = ?',
    [id]
  );

  console.log('Rows deleted:', result[0].rowsAffected);
  await db.close();
}

4. Implementing CRUD Operations

Example: Setting up SQLite Database

import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('mydb.db');

db.transaction(tx => {
  tx.executeSql(
    'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT);'
  );
});

Operation	Description	SQL Query	JavaScript Method
Create	Insert a new record into the database	`INSERT INTO users (name, email) VALUES (?, ?)`	`db.transaction`
Read	Retrieve records from the database	`SELECT * FROM users WHERE id = ?`	`db.transaction`
Update	Modify existing records in the database	`UPDATE users SET name = ?, email = ? WHERE id = ?`	`db.transaction`
Delete	Remove records from the database	`DELETE FROM users WHERE id = ?`	`db.transaction`

Example: Create Operation

const createUser = (name, email) => {
  db.transaction(tx => {
    tx.executeSql(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email],
      (_, result) => console.log('User created with ID:', result.insertId)
    );
  });
};

Example: Read Operation

const getUserById = (id) => {
  db.transaction(tx => {
    tx.executeSql(
      'SELECT * FROM users WHERE id = ?',
      [id],
      (_, { rows }) => console.log('User:', rows._array[0])
    );
  });
};

Example: Update Operation

const updateUser = (id, name, email) => {
  db.transaction(tx => {
    tx.executeSql(
      'UPDATE users SET name = ?, email = ? WHERE id = ?',
      [name, email, id],
      (_, result) => console.log('Rows affected:', result.rowsAffected)
    );
  });
};

Example: Delete Operation

const deleteUser = (id) => {
  db.transaction(tx => {
    tx.executeSql(
      'DELETE FROM users WHERE id = ?',
      [id],
      (_, result) => console.log('Rows deleted:', result.rowsAffected)
    );
  });
};

5. Using Transactions

Transactions in SQLite ensure data consistency by grouping multiple database operations into a single unit of work. If any operation within the transaction fails, all changes are rolled back.

Method	Description	Notes
transaction()	Starts a new transaction block	Wraps multiple operations in a single atomic unit
executeSql()	Executes a SQL statement within the transaction	Must be called inside a transaction context

Example: Using Transactions

import { openDatabase } from 'react-native-sqlite-storage';

const db = openDatabase({ name: 'MyApp.db' });

db.transaction((tx) => {
  tx.executeSql('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'john@example.com']);
  tx.executeSql('UPDATE users SET email = ? WHERE name = ?', ['john.doe@example.com', 'John Doe']);
});

Note: Transactions in SQLite are ACID-compliant. All operations within a transaction are either committed or rolled back together.

6. Handling Database Migrations

Example: Using SQLite with React Native and migrations

import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('mydb.db');

// Migration function
const migrate = async () => {
  const version = await getDatabaseVersion();
  
  if (version < 1) {
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
      );
    `);
  }

  if (version < 2) {
    await db.execAsync(`
      ALTER TABLE users
      ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;
    `);
  }

  await setDatabaseVersion(2);
};

const getDatabaseVersion = async () => {
  const result = await db.execAsync(
    "SELECT value FROM metadata WHERE key = 'version'"
  );
  return result[0] && result[0].value ? parseInt(result[0].value) : 0;
};

const setDatabaseVersion = async (version) => {
  await db.execAsync(`
    INSERT OR REPLACE INTO metadata (key, value)
    VALUES ('version', '${version}')
  `);
};

migrate();

Migration Step	Description	Action
Version Check	Check current database version	Query metadata table for version
Migration Logic	Apply changes if version is less than target	Run `execAsync` with DDL statements
Version Update	Update version after successful migration	Insert or update metadata table

Note: Always backup the database before running migrations in production.

Warning: Migrations must be backward compatible to avoid breaking existing app versions.

Best Practices for Migrations

Use versioned migrations to track database changes
Apply migrations in sequence, checking current version before each step
Test migrations on a copy of the production database
Use ALTER TABLE instead of recreating tables when possible
Implement rollback logic for critical migrations

7. Using WatermelonDB for Performance

Example: Setting up WatermelonDB

import { Database, Q } from '@nozbe/watermelondb';
import SQLiteAdapter from '@nozbe/watermelondb/adapters/sqlite';

const adapter = new SQLiteAdapter({
  schema: mySchema,
  dbName: 'myapp.db',
});

const database = new Database({
  adapter,
  modelClasses: [Post, Comment],
});

Feature	Description	Notes
Schema Definition	`const mySchema = new Schema({ version: 1, tables: [...] });`	Defines database structure
Adapter	`SQLiteAdapter`	Uses SQLite for storage
Database Initialization	`new Database({ adapter, modelClasses })`	Connects models to database

Note: WatermelonDB is optimized for performance in mobile apps and supports real-time sync.

Key Benefits of WatermelonDB

High performance with minimal overhead
Real-time sync capabilities
Supports offline-first architecture
Easy integration with React Native

Setup Workflow

Create schema definition
Initialize SQLite adapter
Instantiate database with models
Use database in components

8. Implementing Offline-First Patterns

Example: Setting up SQLite for Offline-First Data

import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('myapp.db');

db.transaction(tx => {
  tx.executeSql(
    'CREATE TABLE IF NOT EXISTS offline_data (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);',
    [],
    () => console.log('Table created'),
    (_, error) => console.error('Error creating table:', error)
  );
});

Feature	Description	Implementation
Local Storage	Stores data locally using SQLite	`expo-sqlite` or `react-native-sqlite-storage`
Sync Strategy	Defines how to sync with remote DB	Manual or automatic sync logic
Conflict Resolution	Handles data conflicts during sync	Last-write-wins or merge strategies

Offline-First Sync Workflow

Store data locally in SQLite
Track changes with a status field (e.g., pending, synced)
When online, send pending changes to server
Update local DB with server response
Mark synced records as synced

Offline-First vs Online-First

Offline-First: Data is stored locally first, synced later
Online-First: Data is stored on server first, fetched locally

Note: Use expo-sqlite for Expo projects or react-native-sqlite-storage for standard React Native apps.

Warning: Ensure proper error handling and retry logic for failed syncs.

9. Syncing Local and Remote Data

Example: Basic Sync Setup with SQLite and REST API

import SQLite from 'react-native-sqlite-storage';
import { syncLocalWithRemote } from './syncUtils';

const db = await SQLite.openDatabase({ name: 'local.db' });

// Sync local data with remote API
const syncResult = await syncLocalWithRemote(db, 'https://api.example.com/data');
console.log('Sync result:', syncResult);

Sync Method	Description	Notes
pushChanges	Uploads local changes to remote server	Handles conflict resolution logic
pullChanges	Downloads remote updates to local DB	Supports incremental sync
fullSync	Complete local-remote data synchronization	May be resource-intensive

Sync Process Workflow

Fetch remote data changes since last sync timestamp
Compare with local database records
Resolve conflicts using conflict resolution strategy
Apply remote changes to local DB
Upload local changes to remote server
Update sync timestamp in local DB

Sync Strategies Comparison

Push-First: Local changes pushed before pulling remote
Pull-First: Remote updates pulled before local push
Conflict-Free: Uses timestamps or version numbers to prevent conflicts

Note: Use lastSyncTimestamp to optimize sync performance by only fetching changes since last sync.

Warning: Large datasets can cause performance issues during full sync. Consider incremental sync for better UX.

Sync Best Practices

Implement conflict resolution logic for concurrent updates
Use incremental sync to reduce bandwidth and processing time
Store lastSyncTimestamp in local DB to track sync state
Handle network errors gracefully with retry logic

10. Using Realm Database

Example: Initializing Realm Database

import Realm from 'realm';

// Define a schema for the User object
const UserSchema = {
  name: 'User',
  primaryKey: 'id',
  properties: {
    id: 'int',
    name: 'string',
    email: 'string',
  },
};

// Open a Realm instance
const realm = new Realm({ schema: [UserSchema] });

Property	Type	Description
name	`string`	Name of the object type
primaryKey	`string`	Name of the property to use as primary key
properties	`object`	Object defining the properties of the Realm object

Note: Realm requires a schema definition to create and manage objects.

Example: Creating a User Object

realm.write(() => {
  const user = realm.create('User', {
    id: 1,
    name: 'John Doe',
    email: 'john@example.com',
  });
});

Example: Querying Data

// Find all users
const allUsers = realm.objects('User');

// Find a user by primary key
const user = realm.objectForPrimaryKey('User', 1);

// Find users with a specific name
const users = realm.objects('User').filtered("name CONTAINS 'John'");

Example: Updating Data

realm.write(() => {
  const user = realm.objectForPrimaryKey('User', 1);
  user.email = 'newemail@example.com';
});

Example: Deleting Data

realm.write(() => {
  const user = realm.objectForPrimaryKey('User', 1);
  realm.delete(user);
});