Expo SQLite: SQLite in Expo Mobile Apps

Local data storage is a critical component of most mobile applications. Whether you need to store user data, cache content for offline use, or sync data between devices, having a persistent local database unlocks key mobile app capabilities.

That’s where SQLite comes in. SQLite is an embedded relational database that is self-contained, serverless, zero-configuration, and transactional. It has become the standard local database for mobile apps, used by millions of apps across iOS, Android, and other platforms.

In this comprehensive guide, you’ll learn how to use SQLite in Expo apps to store, query and sync app data:

• Installing and configuring expo-sqlite
• Creating databases and tables
• Performing CRUD operations
• Querying data with SQL
• Handling async operations
• Importing existing databases
• Syncing data between devices

We’ll look at real code examples from a sample Todo app, demonstrating practical patterns you can apply in your own Expo projects.

Getting Started with expo-sqlite

To use SQLite in an Expo managed workflow, you’ll need to install the expo-sqlite module:

npm install expo-sqlite

This wraps native SQLite implementations for iOS, Android and web into a unified JavaScript interface.

Once installed, import and open a database connection:

import * as SQLite from 'expo-sqlite';

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

This will return a SQLite.SQLiteDatabase instance representing the connection.

If the database file does not already exist, it will be created automatically under FileSystem.documentDirectory.

Creating Tables

Before we can insert and query rows of data, we need to define tables to store that data.

Here is an example table schema for storing Todos:

db.transaction(tx => {
  tx.executeSql(
    'CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY NOT NULL, text TEXT NOT NULL, completed INT NOT NULL);'
  );
});

This will create a todos table with id, text and completed columns, if it does not already exist.

The transaction method wraps this in a transaction, helping handle errors and async execution.

Inserting Data

With our table created, we can start inserting rows of data:

const insertSql = `INSERT INTO todos (text, completed) VALUES (?, ?)`;

db.transaction(tx => {
  tx.executeSql(insertSql, ['Buy groceries', 0]); 
  tx.executeSql(insertSql, ['Walk the dog', 0]);
}); 

We parameterize our statement using ? placeholders to avoid SQL injection risks.

The executeSql method accepts the SQL string, parameters array, success callback, and error callback.

Our table now contains two rows of sample todo data!

Querying Data with SQL

To query inserted rows, we can execute a SELECT statement:

db.transaction(tx => {
  tx.executeSql('SELECT * FROM todos', [], (_, { rows }) => 
    console.log(JSON.stringify(rows))
  );
});

This will print all rows in our todos table to the console as JSON.

We can add WHERE clauses to filter specific rows:

SELECT * FROM todos WHERE completed = 0

Or use parameters to build dynamic queries:

db.transaction(tx => {
  tx.executeSql('SELECT * FROM todos WHERE text LIKE ?', ['%groceries%'], (_, { rows }) => {
    console.log(rows); 
  });
});

SQLite supports the full range of SQL commands like JOINs, GROUP BY, ORDER BY and much more.

Handling Async Execution

One complication when working with SQLite is that operations are asynchronous by default.

This means we need to handle callbacks, promises and async/await instead of expecting immediate return values from queries.

Here is an example using async/await:

async function getOpenTodos() {
  const todos = [];
  
  await db.transaction(async tx => {
    const result = await tx.executeSql('SELECT * FROM todos WHERE completed = 0');    
    todos.push(...result.rows._array);
  });

  return todos; 
}

const openTodos = await getOpenTodos();

We await asynchronous steps, build up our result set, and return it once all queries have completed.

Syncing Data Between Devices

A key benefit of SQLite is it enables fully offline experiences and sync across devices.

The recently released expo-sqlite-crsql module (currently alpha) makes this simple.

It integrates SQLite with CRDT-based sync powered by CR-SQLite and TinyBase.

Enable sync on a table:

SELECT crsql_as_crr('todos'); 

Then exchange changelogs between devices:

// Request changes
const changes = await db.executeSql('SELECT * FROM crsql_changes');

// Send to server

// Insert received changes 
await db.executeSql(INSERT INTO crsql_changes...', changes); 

That’s it! The devices will now automatically sync.

See the todo-sync-example for a full working demo.

Importing Existing Databases

You can pre-populate your app’s database from an existing SQLite file:

1. Install expo-file-system and expo-asset
2. Add your .db file as an asset
3. Copy the file from assets into FileSystem.documentDirectory/SQLite on first run:

async function loadDB() {
  if (!(await FileSystem.getInfoAsync(dir)).exists) {
    await FileSystem.makeDirectoryAsync(dir);
  }

  await FileSystem.downloadAsync(assetUri, dbPath);  
  return SQLite.openDatabase(dbName);
}

This keeps the initial app binary small while allowing a pre-populated database.

Conclusion

SQLite unlocks powerful local data storage, offline functionality and cross-device sync for mobile apps. With expo-sqlite, it’s easy to leverage these capabilities in your Expo projects.

We’ve only scratched the surface of what’s possible. SQLite supports an extensive SQL feature set for modeling complex data. The ecosystem also provides modules like full-text search, spatial extensions, backup/restore, and cloud sync adapters.

As you scale your app and user base, remember SQLite as a battle-tested, embedded database. It can handle apps with millions of users once tuned properly. And if needed, there are commercial options like Couchbase Lite that build on SQLite for added robustness.

Local storage is often an app capability that gets deprioritized early on. But investing in it pays dividends as your app grows. Users increasingly expect mobile apps to work just as well offline as online.

So take advantage of SQLite, build offline-first into your Expo apps, and delight your users no matter their network conditions!