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-51/packages/expo-sqlite packageName: expo-sqlite maxHeadingDepth: 5 iconUrl: /static/images/packages/expo-sqlite.png platforms: ["android", "ios"] --- `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 ## 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 statement allows you 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> ); } ``` ### 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). ## 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-sqlite#expo-sqlite) 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. ## 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. |