Expo Docs MCP Server

--- title: SQLite description: A library that provides access to a database that can be queried through a SQLite API. sourceCodeUrl: https://github.com/expo/expo/tree/sdk-53/packages/expo-sqlite packageName: expo-sqlite iconUrl: /static/images/packages/expo-sqlite.png platforms: ["android", "ios", "macos", "web"] --- `expo-sqlite` gives your app access to a database that can be queried through a SQLite API. The database is persisted across restarts of your app. ## Installation ## Configuration in app config You can configure `expo-sqlite` for advanced configurations using its built-in [config plugin](/config-plugins/introduction/) if you use config plugins in your project ([EAS Build](/build/introduction) or `npx expo run:[android|ios]`). The plugin allows you to configure various properties that cannot be set at runtime and require building a new app binary to take effect. ```json app.json { "expo": { "plugins": [ [ "expo-sqlite", { "enableFTS": true, "useSQLCipher": true, "android": { // Override the shared configuration for Android "enableFTS": false, "useSQLCipher": false }, "ios": { // You can also override the shared configurations for iOS "customBuildFlags": ["-DSQLITE_ENABLE_DBSTAT_VTAB=1 -DSQLITE_ENABLE_SNAPSHOT=1"] } } ] ] } } ``` ## Web setup > **warning** Web support is still experimental and may be unstable. [Create an issue on GitHub](https://github.com/expo/expo/issues) if you encounter any issues. To use `expo-sqlite` on web, you need to configure Metro bundler to support **wasm** files and add HTTP headers to allow [`SharedArrayBuffer`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/SharedArrayBuffer) usage. Add the following configuration to your **metro.config.js**. If you don't have the **metro.config.js** yet, you can run `npx expo customize metro.config.js`. [Learn more](/guides/customizing-metro/). If you deploy your app to web hosting services, you will also need to add the `Cross-Origin-Embedder-Policy` and `Cross-Origin-Opener-Policy` headers to your web server. [Learn more about the `COEP`, `COOP` headers, and `SharedArrayBuffer`](https://developer.chrome.com/blog/enabling-shared-array-buffer/). ## Usage Import the module from `expo-sqlite`. ```js Import the module from expo-sqlite ``` ### Basic CRUD operations ```js Basic CRUD operations const db = await SQLite.openDatabaseAsync('databaseName'); // `execAsync()` is useful for bulk queries when you want to execute altogether. // Note that `execAsync()` does not escape parameters and may lead to SQL injection. await db.execAsync(` PRAGMA journal_mode = WAL; CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY NOT NULL, value TEXT NOT NULL, intValue INTEGER); INSERT INTO test (value, intValue) VALUES ('test1', 123); INSERT INTO test (value, intValue) VALUES ('test2', 456); INSERT INTO test (value, intValue) VALUES ('test3', 789); `); // `runAsync()` is useful when you want to execute some write operations. const result = await db.runAsync('INSERT INTO test (value, intValue) VALUES (?, ?)', 'aaa', 100); console.log(result.lastInsertRowId, result.changes); await db.runAsync('UPDATE test SET intValue = ? WHERE value = ?', 999, 'aaa'); // Binding unnamed parameters from variadic arguments await db.runAsync('UPDATE test SET intValue = ? WHERE value = ?', [999, 'aaa']); // Binding unnamed parameters from array await db.runAsync('DELETE FROM test WHERE value = $value', { $value: 'aaa' }); // Binding named parameters from object // `getFirstAsync()` is useful when you want to get a single row from the database. const firstRow = await db.getFirstAsync('SELECT * FROM test'); console.log(firstRow.id, firstRow.value, firstRow.intValue); // `getAllAsync()` is useful when you want to get all results as an array of objects. const allRows = await db.getAllAsync('SELECT * FROM test'); for (const row of allRows) { console.log(row.id, row.value, row.intValue); } // `getEachAsync()` is useful when you want to iterate SQLite query cursor. for await (const row of db.getEachAsync('SELECT * FROM test')) { console.log(row.id, row.value, row.intValue); } ``` ### Prepared statements Prepared statements allow you to compile your SQL query once and execute it multiple times with different parameters. You can get a prepared statement by calling [`prepareAsync()`](#prepareasyncsource) or [`prepareSync()`](#preparesyncsource) method on a database instance. The prepared statement can fulfill CRUD operations by calling [`executeAsync()`](#executeasyncparams) or [`executeSync()`](#executesyncparams) method. > **Note:** Remember to call [`finalizeAsync()`](#finalizeasync) or [`finalizeSync()`](#finalizesync) method to release the prepared statement after you finish using the statement. `try-finally` block is recommended to ensure the prepared statement is finalized. ```ts Prepared statements const statement = await db.prepareAsync( 'INSERT INTO test (value, intValue) VALUES ($value, $intValue)' ); try { let result = await statement.executeAsync({ $value: 'bbb', $intValue: 101 }); console.log('bbb and 101:', result.lastInsertRowId, result.changes); result = await statement.executeAsync({ $value: 'ccc', $intValue: 102 }); console.log('ccc and 102:', result.lastInsertRowId, result.changes); result = await statement.executeAsync({ $value: 'ddd', $intValue: 103 }); console.log('ddd and 103:', result.lastInsertRowId, result.changes); } finally { await statement.finalizeAsync(); } const statement2 = await db.prepareAsync('SELECT * FROM test WHERE intValue >= $intValue'); try { const result = await statement2.executeAsync<{ value: string; intValue: number }>({ $intValue: 100, }); // `getFirstAsync()` is useful when you want to get a single row from the database. const firstRow = await result.getFirstAsync(); console.log(firstRow.id, firstRow.value, firstRow.intValue); // Reset the SQLite query cursor to the beginning for the next `getAllAsync()` call. await result.resetAsync(); // `getAllAsync()` is useful when you want to get all results as an array of objects. const allRows = await result.getAllAsync(); for (const row of allRows) { console.log(row.value, row.intValue); } // Reset the SQLite query cursor to the beginning for the next `for-await-of` loop. await result.resetAsync(); // The result object is also an async iterable. You can use it in `for-await-of` loop to iterate SQLite query cursor. for await (const row of result) { console.log(row.value, row.intValue); } } finally { await statement2.finalizeAsync(); } ``` ### `useSQLiteContext()` hook ```tsx useSQLiteContext() hook return ( <SQLiteProvider databaseName="test.db" onInit={migrateDbIfNeeded}> </SQLiteProvider> ); } const db = useSQLiteContext(); const [version, setVersion] = useState(''); useEffect(() => { async function setup() { const result = await db.getFirstAsync<{ 'sqlite_version()': string }>( 'SELECT sqlite_version()' ); setVersion(result['sqlite_version()']); } setup(); }, []); return ( <Text style={styles.headerText}>SQLite version: {version}</Text> ); } interface Todo { value: string; intValue: number; } const db = useSQLiteContext(); const [todos, setTodos] = useState<Todo[]>([]); useEffect(() => { async function setup() { const result = await db.getAllAsync<Todo>('SELECT * FROM todos'); setTodos(result); } setup(); }, []); return ( {todos.map((todo, index) => ( <View style={styles.todoItemContainer} key={index}> <Text>{`${todo.intValue} - ${todo.value}`}</Text> ))} </View> ); } async function migrateDbIfNeeded(db: SQLiteDatabase) { const DATABASE_VERSION = 1; let { user_version: currentDbVersion } = await db.getFirstAsync<{ user_version: number }>( 'PRAGMA user_version' ); if (currentDbVersion >= DATABASE_VERSION) { return; } if (currentDbVersion === 0) { await db.execAsync(` PRAGMA journal_mode = 'wal'; CREATE TABLE todos (id INTEGER PRIMARY KEY NOT NULL, value TEXT NOT NULL, intValue INTEGER); `); await db.runAsync('INSERT INTO todos (value, intValue) VALUES (?, ?)', 'hello', 1); await db.runAsync('INSERT INTO todos (value, intValue) VALUES (?, ?)', 'world', 2); currentDbVersion = 1; } // if (currentDbVersion === 1) { // Add more migrations // } await db.execAsync(`PRAGMA user_version = ${DATABASE_VERSION}`); } const styles = StyleSheet.create({ // Your styles... }); ``` ### `useSQLiteContext()` hook with `React.Suspense` As with the [`useSQLiteContext()`](#usesqlitecontext-hook) hook, you can also integrate the [`SQLiteProvider`](#sqliteprovider) with [`React.Suspense`](https://react.dev/reference/react/Suspense) to show a fallback component until the database is ready. To enable the integration, pass the `useSuspense` prop to the `SQLiteProvider` component. ```tsx useSQLiteContext() hook with React.Suspense return ( }> <SQLiteProvider databaseName="test.db" onInit={migrateDbIfNeeded} useSuspense> </SQLiteProvider> </Suspense> ); } ``` ### Executing queries within an async transaction ```js Executing queries within an async transaction const db = await SQLite.openDatabaseAsync('databaseName'); await db.withTransactionAsync(async () => { const result = await db.getFirstAsync('SELECT COUNT(*) FROM USERS'); console.log('Count:', result.rows[0]['COUNT(*)']); }); ``` Due to the nature of async/await, any query that runs while the transaction is active will be included in the transaction. This includes query statements that are outside of the scope function passed to `withTransactionAsync()` and may be surprising behavior. For example, the following test case runs queries inside and outside of a scope function passed to `withTransactionAsync()`. However, all of the queries will run within the actual SQL transaction because the second `UPDATE` query runs before the transaction finishes. ```ts Promise.all([ // 1. A new transaction begins db.withTransactionAsync(async () => { // 2. The value "first" is inserted into the test table and we wait 2 // seconds await db.execAsync('INSERT INTO test (data) VALUES ("first")'); await sleep(2000); // 4. Two seconds in, we read the latest data from the table const row = await db.getFirstAsync<{ data: string }>('SELECT data FROM test'); // ❌ The data in the table will be "second" and this expectation will fail. // Additionally, this expectation will throw an error and roll back the // transaction, including the `UPDATE` query below since it ran within // the transaction. expect(row.data).toBe('first'); }), // 3. One second in, the data in the test table is updated to be "second". // This `UPDATE` query runs in the transaction even though its code is // outside of it because the transaction happens to be active at the time // this query runs. sleep(1000).then(async () => db.execAsync('UPDATE test SET data = "second"')), ]); ``` The [`withExclusiveTransactionAsync()`](#withexclusivetransactionasynctask) function addresses this. Only queries that run within the scope function passed to `withExclusiveTransactionAsync()` will run within the actual SQL transaction. ### Executing PRAGMA queries ```js Executing PRAGMA queries const db = await SQLite.openDatabaseAsync('databaseName'); await db.execAsync('PRAGMA journal_mode = WAL'); await db.execAsync('PRAGMA foreign_keys = ON'); ``` > **info** **Tip:** Enable [WAL journal mode](https://www.sqlite.org/wal.html) when you create a new database to improve performance in general. ### Import an existing database To open a new SQLite database using an existing **.db** file you already have, you can use the [`SQLiteProvider`](#sqliteprovider) with [`assetSource`](#assetsource). ```tsx useSQLiteContext() with existing database return ( <SQLiteProvider databaseName="test.db" assetSource={{ assetId: require('./assets/test.db') }}> </SQLiteProvider> ); } ``` ### Sharing a database between apps/extensions (iOS) To share a database with other apps/extensions in the same App Group, you can use shared containers by following the steps below: Configure the App Group in app config: ```json app.json { "expo": { "ios": { "bundleIdentifier": "com.myapp", "entitlements": { "com.apple.security.application-groups": ["group.com.myapp"] } } } } ``` Use [`Paths.appleSharedContainers`](filesystem-next/#applesharedcontainers) from the [`expo-file-system`](filesystem-next/) library to retrieve the path to the shared container: ```tsx Using Shared Container for SQLite Database on iOS const dbDirectory = useMemo(() => { if (Platform.OS === 'ios') { return Object.values(Paths.appleSharedContainers)?.[0]?.uri; // or `Paths.appleSharedContainers['group.com.myapp']?.uri` to choose specific container } return defaultDatabaseDirectory; }, []); return ( <View style={styles.container}> <SQLiteProvider databaseName="test.db" directory={dbDirectory}> </SQLiteProvider> </View> ); } ``` ### Passing binary data Use [`Uint8Array`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Uint8Array) to pass binary data to the database: ```ts Passing binary data await db.execAsync(` DROP TABLE IF EXISTS blobs; CREATE TABLE IF NOT EXISTS blobs (id INTEGER PRIMARY KEY NOT NULL, data BLOB); `); const blob = new Uint8Array([0x00, 0x01, 0x02, 0x03, 0x04, 0x05]); await db.runAsync('INSERT INTO blobs (data) VALUES (?)', blob); const row = await db.getFirstAsync<{ data: Uint8Array }>('SELECT * FROM blobs'); expect(row.data).toEqual(blob); ``` ### Browse an on-device database You can inspect a database, execute queries against it, and explore data with the [`drizzle-studio-expo` dev tools plugin](https://github.com/drizzle-team/drizzle-studio-expo). This plugin enables you to launch [Drizzle Studio](https://orm.drizzle.team/drizzle-studio/overview), connected to a database in your app, directly from Expo CLI. This plugin can be used with any `expo-sqlite` configuration. It does not require that you use [Drizzle ORM](#drizzle-orm) in your app. [Learn how to install and use the plugin](https://github.com/drizzle-team/drizzle-studio-expo). ### Key-value storage The `expo-sqlite` library provides [`Storage`](#sqlitestorage) as a drop-in replacement for the [`@react-native-async-storage/async-storage`](https://github.com/react-native-async-storage/async-storage) library. This key-value store is backed by SQLite. If your project already uses `expo-sqlite`, you can leverage `expo-sqlite/kv-store` without needing to add another dependency. [`Storage`](#sqlitestorage) provides the same API as `@react-native-async-storage/async-storage`: ```ts Using the Store // The storage API is the default export, you can call it Storage, AsyncStorage, or whatever you prefer. await Storage.setItem('key', JSON.stringify({ entity: 'value' })); const value = await Storage.getItem('key'); const entity = JSON.parse(value); console.log(entity); // { entity: 'value' } ``` A key benefit of using `expo-sqlite/kv-store` is the addition of synchronous APIs for added convenience: ```ts Using the Store with synchronous APIs // The storage API is the default export, you can call it Storage, AsyncStorage, or whatever you prefer. Storage.setItemSync('key', 'value'); const value = Storage.getItemSync('key'); ``` If you're currently using `@react-native-async-storage/async-storage` in your project, switching to `expo-sqlite/kv-store` is as simple as changing the import statement: ```diff - import AsyncStorage from '@react-native-async-storage/async-storage'; + import AsyncStorage from 'expo-sqlite/kv-store'; ``` ## Third-party library integrations The `expo-sqlite` library is designed to be a solid SQLite foundation. It enables broader integrations with third-party libraries for more advanced higher-level features. Here are some of the libraries that you can use with `expo-sqlite`. ### Drizzle ORM [Drizzle](https://orm.drizzle.team/) is a ["headless TypeScript ORM with a head"](https://orm.drizzle.team/docs/overview). It runs on Node.js, Bun, Deno, and React Native. It also has a CLI companion called [`drizzle-kit`](https://orm.drizzle.team/kit-docs/overview) for generating SQL migrations. Check out the [Drizzle ORM documentation](https://orm.drizzle.team/) and the [`expo-sqlite` integration guide](https://orm.drizzle.team/docs/get-started/expo-new) for more details. ### Knex.js [Knex.js](https://knexjs.org/) is a SQL query builder that is ["flexible, portable, and fun to use!"](https://github.com/knex/knex) Check out the [`expo-sqlite` integration guide](https://github.com/expo/knex-expo-sqlite-dialect) for more details. ## SQLCipher > **Note:** SQLCipher is not supported on [Expo Go](https://expo.dev/go). [SQLCipher](https://www.zetetic.net/sqlcipher/) is a fork of SQLite that adds encryption and authentication to the database. The `expo-sqlite` library supports SQLCipher for Android, iOS, and macOS. To use SQLCipher, you need to add the `useSQLCipher` config to your **app.json** as shown in the [Configuration in app config](#configuration-in-app-config) section and run `npx expo prebuild`. Right after you open a database, you need to set a password for the database using the `PRAGMA key = 'password'` statement. ```ts Add a password to the database const db = await SQLite.openDatabaseAsync('databaseName'); await db.execAsync(`PRAGMA key = 'password'`); ``` ## API ### Cheatsheet for the common API The following table summarizes the common API for [`SQLiteDatabase`](#sqlitedatabase) and [`SQLiteStatement`](#sqlitestatement) classes: | [`SQLiteDatabase`](#sqlitedatabase) methods | [`SQLiteStatement`](#sqlitestatement) methods | Description | Use Case | | ------------------------------------------------ | ----------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | [`runAsync()`](#runasyncsource-params) | [`executeAsync()`](#executeasyncparams) | Executes a SQL query, returning information on the changes made. | Ideal for SQL write operations such as `INSERT`, `UPDATE`, `DELETE`. | | [`getFirstAsync()`](#getfirstasyncsource-params) | [`executeAsync()`](#executeasyncparams) + [`getFirstAsync()`](#getfirstasync) | Retrieves the first row from the query result. | Suitable for fetching a single row from the database. For example: `getFirstAsync('SELECT * FROM Users WHERE id = ?', userId)`. | | [`getAllAsync()`](#getallasyncsource-params) | [`executeAsync()`](#executeasyncparams) + [`getFirstAsync()`](#getallasync) | Fetches all query results at once. | Best suited for scenarios with smaller result sets, such as queries with a LIMIT clause, like `SELECT * FROM Table LIMIT 100`, where you intend to retrieve all results in a single batch. | | [`getEachAsync()`](#geteachasyncsource-params) | [`executeAsync()`](#executeasyncparams) + `for-await-of` async iterator | Provides an iterator for result set traversal. This method fetches one row at a time from the database, potentially reducing memory usage compared to `getAllAsync()`. | Recommended for handling large result sets incrementally, such as with infinite scrolling implementations. |