Fast and Simple SQLite for React Native with OP-SQLite

React Native OP-SQLite aims to be the fastest and simplest SQLite library for React Native. Per benchmarks, it outperforms popular libraries like react-native-quick-sqlite by avoiding unnecessary data conversions and using optimal data access patterns.

OP-SQLite aims to be an SQLite library that truly feels native to React Native. It provides intuitive APIs that fit React Native’s style, cares deeply about performance, and adds useful hooks into SQLite that simplify everyday tasks.

In this comprehensive guide, you will learn how to:

• Set up OP-SQLite and open databases
• Execute SQL statements and queries
• Use transactions to batch-execute commands
• Attach and query external database files
• Subscribe to commit, rollback, and update events using Hooks
• Tune SQLite performance through PRAGMAs and other mechanisms
• Handle blobs and binary data

We will explore all this functionality through illustrative examples based on a real-world analytics database use case. So let’s get started!

How to Install and Setup OP-SQLite

OP-SQLite is available on npm/yarn. To set it up:

yarn add @op-engineering/op-sqlite

Then, to open or create a database:

import {open} from '@op-engineering/op-sqlite';

const db = open({name: 'mydatabase.sqlite'}); 

The open() method returns a database object through which you can execute SQL and provides various utility methods.

That’s it! The library will handle integrating with React Native and setting up SQLite properly. Now, let’s look at executing some statements.

Executing SQL Statements

The primary method for executing SQL is db.execute(). It allows running simple statements like CREATE TABLE:

db.execute(`
  CREATE TABLE Users (
    id INTEGER PRIMARY KEY, 
    name TEXT,
    email TEXT UNIQUE
  )
`);

To run parameterized SQL statements, pass the parameters as the second argument:

db.execute(
  'INSERT INTO Users (name, email) VALUES (?,?)',
  ['John', '[email protected]'] 
);

The return value contains metadata about affected rows, inserted IDs etc:

let result = db.execute(
  'UPDATE Users SET name = ? WHERE id = ?', 
  ['Johnny', 1]  
);

console.log(result.rowsAffected); // 1

To fetch data, access the rows property of result:

let result = db.execute('SELECT * FROM Users');

result.rows._array.forEach(user => {
  console.log(user); 
})

This uses synchronous execution. For large queries, use the async version db.executeAsync().

Now, let’s look at how we can execute SQL in batches.

Batch Operations using Transactions

Transactions allow execution of a set of SQL statements atomically in a single batch. This is faster as it avoids repeatedly parsing and planning SQL.

Here is an example:

await db.transaction(async tx => {

  await tx.execute(
    'INSERT INTO Users (name, email) VALUES (?,?)',
    ['Mike', '[email protected]']
  );
  
  await tx.execute(
    'UPDATE Users SET name = ? WHERE id = ?',
  	['Robert', 1]
  );
  
});

All the statements above will be executed in a single transaction. If any statement fails, the entire transaction is rolled back automatically.

You can also directly use db.executeBatch() which wraps the commands in a transaction internally.

Now let’s discuss how OP-SQLite allows working with external database files.

Attaching and Querying External Databases

SQLite supports attaching external files to the currently open database connection through a database alias. This allows you to query multiple databases as if querying a single database.

Here is an example:

// Attach external database file
db.attach('main', 'users', 'users.sqlite'); 

// Query across attached database
let result = db.execute(`
  SELECT p.*, u.name 
  FROM main.product p 
  INNER JOIN users.user u
  ON p.created_by = u.id
`);

// Detach when done
db.detach('users');

This keeps your database schema modular and reusable across apps. Note that on iOS, the external database path needs to be within the app’s documents directory.

Now, let’s discuss subscribing to database events using the built-in Hooks.

Using Hooks to Subscribe to Database Events

OP-SQLite provides Hooks that let you directly subscribe to commit, rollback, and data modification events from SQLite.

For example, use updateHook to subscribe to row changes:

db.updateHook(event => {
  console.log(`Row ${event.rowId} updated in ${event.table}`);  
  console.log(JSON.stringify(event.row));
});

db.execute(
  'INSERT INTO Users (name, email) VALUES (?, ?)',
  ['Sam', '[email protected]']  
);

This keeps your business logic modular and reusable. Similar hooks exist for transaction commits, rollbacks, etc.

Now, let’s discuss some performance tuning using SQLite PRAGMAs.

Tuning for Performance using PRAGMA

SQLite lets you tune performance through PRAGMA statements. Some options are:

// Enable memory mapped IO 
db.execute('PRAGMA mmap_size=268435456');  

// Set journal mode to improve write performance
db.execute('PRAGMA journal_mode=MEMORY');

OP-SQLite also supports an exceptional performance compile flag:

// iOS 
export OP_SQLITE_PERF=1

// Android
OPSQLiteFlags="-DPERF_SQLITE=1" 

This significantly speeds up SQLite operations. Now, let’s briefly examine how binary data and blobs can be handled.

Working with Blobs and Binary Data

SQLite has first-class support for Blobs – which map nicely to ArrayBuffers:

// Create Blob table
db.execute(`
  CREATE TABLE Images (
    id INTEGER PRIMARY KEY,
    data BLOB
  )
`);

// Insert blob data  
let binaryData = new Uint8Array(10); 
db.execute(
  'INSERT INTO Images (data) VALUES (?)',
  [binaryData]  
);

// Fetch blobs
let result = db.execute('SELECT * FROM Images');
let retrieved = new Uint8Array(result.rows._array[0].data); 

So, working with binaries is straightforward with OP-SQLite.

And that wraps up this guide on using React Native OP-SQLite! Let’s summarize some key takeaways.

Summary and Takeaways

• OP-SQLite provides a fast, simple, and conversational SQLite interface for React Native.
• Key features like transactions, hooks, and external database attachments make it very powerful.
• Performance tuning with PRAGMA helps build snappy mobile apps
• Blob support and intuitive APIs simplify working with binaries and rich data types

For production use, also consider the encrypted variant OP-SQLCipher or using an in-memory database for extra speed.

Overall, OP-SQLite helps you focus on app functionality rather than database plumbing. Building offline-capable React Native applications is now easy and fun!