Working with SQLite (expo-sqlite)

1. Opening Database

Method	Description	Parameters	Return Value
openDatabase	Opens or creates a SQLite database file	`name: string`, `version?: string`	`Database` object

Example: Opening a Database

import { openDatabase } from 'expo-sqlite';

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

2. Executing Raw Queries

Method	Description	Parameters	Return Value
executeRaw	Executes a raw SQL query without returning results	`query: string`	`Promise<SQLiteResult>`
executeRawAsync	Asynchronous version of executeRaw	`query: string`	`Promise<SQLiteResult>`

Example: Executing a Raw Query

import { openDatabase } from 'expo-sqlite';

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

await db.executeRawAsync('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)');

Example: Inserting Data with Raw Query

await db.executeRawAsync(
  'INSERT INTO users (name) VALUES (?), (?)',
  ['Alice', 'Bob']
);

3. Running Prepared Statements

Method	Description	Parameters	Return Value
prepare	Prepares a SQL statement for execution	`sql: string`	Prepared statement object
run	Executes a prepared statement with provided parameters	`params?: any[] \| Record<string, any>`	Result object with `changes`, `lastInsertRowId`
all	Executes a prepared statement and returns all rows	`params?: any[] \| Record<string, any>`	Array of rows
get	Executes a prepared statement and returns the first row	`params?: any[] \| Record<string, any>`	First row or `null`

Example: Running Prepared Statements

import { openDatabaseSync } from 'expo-sqlite';

const db = openDatabaseSync('mydb.db');

// Prepare a statement
const insertStatement = db.prepare("INSERT INTO users (name, age) VALUES (?, ?)");

// Run the statement with parameters
const result = insertStatement.run(["Alice", 30]);
console.log("Changes:", result.changes);
console.log("Last Insert Row ID:", result.lastInsertRowId);

// Prepare a select statement
const selectStatement = db.prepare("SELECT * FROM users WHERE age > ?");

// Get all matching rows
const rows = selectStatement.all([25]);
console.log("Users over 25:", rows);

// Get a single row
const firstRow = selectStatement.get([25]);
console.log("First user over 25:", firstRow);

Note: Prepared statements are more efficient for repeated execution and help prevent SQL injection.

4. Getting All Results

Example: Fetching All Rows from a Query

import * as SQLite from 'expo-sqlite';

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

const results = await db.executeAsync(
  'SELECT * FROM users'
);

console.log(results.rows._array);
// Output:
// [
//   { id: 1, name: 'Alice', email: 'alice@example.com' },
//   { id: 2, name: 'Bob', email: 'bob@example.com' }
// ]

Property	Description	Notes
rows	Object containing all returned rows	Access via `rows._array`
rows._array	Array of all returned rows	Each row is an object with column names as keys
rows.length	Number of rows returned	Zero if no results

Note: The _array property is the standard way to access all rows in a query result. It's an array of objects, where each object represents one row.

5. Getting Single Row

Method	Description	Parameters	Return Value
getSingleRow()	Executes a query and returns the first row of the result set	`query: string`, `params?: any[]`	`Record<string, unknown>` or `null`

Example: Fetching a single user by ID

import { openDatabaseSync } from 'expo-sqlite';

const db = openDatabaseSync('mydb.db');

const user = db.getSingleRow(
  'SELECT * FROM users WHERE id = ?',
  [1]
);

console.log(user); // { id: 1, name: 'John Doe', email: 'john@example.com' }

Note: If no rows match the query, null is returned.

6. Using Transactions

Transactions in Expo SQLite ensure data consistency and atomicity for a set of database operations. They allow you to group multiple SQL statements into a single unit of work that either commits entirely or rolls back on failure.

Method	Description	Notes
db.transaction()	Executes a series of SQL statements within a transaction	Accepts a callback function with a transaction object
transaction.executeSql()	Executes a single SQL statement within the transaction	Can be called multiple times in one transaction

Example: Using Transactions

import * as SQLite from 'expo-sqlite';

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

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

Note: Transactions are not supported in read-only mode. Ensure your database is opened with write permissions.

7. Creating Tables (CREATE TABLE)

Example: Creating a Users Table

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 NOT NULL,
      email TEXT UNIQUE NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );`
  );
});

Clause	Description	Notes
CREATE TABLE	Creates a new table in the database	Use `IF NOT EXISTS` to avoid errors if table already exists
table_name	Name of the table to create	Must be unique within the database
column_definition	Defines columns with name, type, and constraints	Each column must have a data type and optional constraints
PRIMARY KEY	Uniquely identifies each row in the table	Can be a single column or composite key
AUTOINCREMENT	Automatically increments the value of a column	Only valid for integer primary key columns
UNIQUE	Ensures all values in a column are distinct	Can be applied to one or more columns
NOT NULL	Prevents null values in a column	Enforces data integrity for required fields
DEFAULT	Assigns a default value if none is provided	Can be used with `CURRENT_TIMESTAMP` for date/time fields

Note: The expo-sqlite library uses SQLite's syntax, which supports a wide range of data types and constraints. Ensure that column names and table names do not conflict with reserved keywords.

8. Inserting Data (INSERT INTO)

Property	Description	Example
INSERT INTO	Used to insert new rows into a table	`INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');`
INSERT OR REPLACE	Inserts a new row or replaces an existing one if a conflict occurs	`INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Jane Smith');`
INSERT OR IGNORE	Inserts a new row or ignores the operation if a conflict occurs	`INSERT OR IGNORE INTO users (id, name) VALUES (1, 'Jane Smith');`

Example: Inserting a Single Row

import * as SQLite from 'expo-sqlite';

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

db.transaction(tx => {
  tx.executeSql(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['John Doe', 'john@example.com'],
    (_, result) => {
      console.log('Inserted row with ID:', result.insertId);
    },
    (_, error) => {
      console.error('Error inserting data:', error);
    }
  );
});

Example: Inserting Multiple Rows

import * as SQLite from 'expo-sqlite';

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

const users = [
  { name: 'Alice Johnson', email: 'alice@example.com' },
  { name: 'Bob Smith', email: 'bob@example.com' }
];

db.transaction(tx => {
  users.forEach(user => {
    tx.executeSql(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [user.name, user.email]
    );
  });
});

Note: The executeSql method returns a result object with an insertId property when a new row is inserted.

9. Updating Data (UPDATE)

Property	Description	Example
UPDATE	SQL command to modify existing records in a database table	`UPDATE users SET name = 'John' WHERE id = 1;`

Example: Updating a User's Name

import * as SQLite from 'expo-sqlite';

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

db.transaction(tx => {
  tx.executeSql(
    'UPDATE users SET name = ? WHERE id = ?',
    ['John Doe', 1],
    (tx, results) => {
      console.log('Rows affected:', results.rowsAffected);
    }
  );
});

Example: Updating Multiple Fields

import * as SQLite from 'expo-sqlite';

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

db.transaction(tx => {
  tx.executeSql(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    ['Jane Smith', 'jane@example.com', 2],
    (tx, results) => {
      console.log('Rows affected:', results.rowsAffected);
    }
  );
});

Note: Always use parameterized queries (with ?) to prevent SQL injection attacks.

Warning: If no WHERE clause is provided, all records in the table will be updated.

10. Deleting Data (DELETE FROM)

Operation	Description	Example
DELETE FROM	Removes rows from a table based on a condition	`DELETE FROM users WHERE id = 1;`

Example: Delete a User by ID

import * as SQLite from 'expo-sqlite';

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

db.transaction(tx => {
  tx.executeSql(
    'DELETE FROM users WHERE id = ?',
    [1],
    (tx, results) => {
      console.log('Rows affected:', results.rowsAffected);
    },
    (tx, error) => {
      console.error('Delete failed:', error);
    }
  );
});

Note: The DELETE FROM statement removes rows but does not drop the table structure. Use carefully to avoid unintended data loss.

11. Closing Database

When you're done with your SQLite database in an Expo project, it's important to close the database connection to free up resources and ensure data integrity.

Method	Description	Notes
db.close()	Closes the database connection.	Must be called to properly close the database. Once closed, the database object cannot be used again.

Example: Closing Database

import * as SQLite from 'expo-sqlite';

const db = await SQLite.openDatabaseAsync('mydb.db');

// Perform database operations here...

await db.close(); // Close the database connection

Note: If you're using openDatabaseAsync, make sure to call close() when you're done with the database to prevent memory leaks.

12. Using Foreign Keys

Foreign keys in SQLite are used to enforce referential integrity between tables. In Expo projects using expo-sqlite, you can define and use foreign keys to maintain relationships between data.

Property	Description	Example
FOREIGN KEY	Defines a column that references the primary key of another table.	`FOREIGN KEY (user_id) REFERENCES users(id)`

Example: Creating Tables with Foreign Keys

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  user_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Note: Ensure that the referenced column in the parent table is a primary key or has a UNIQUE constraint for the foreign key to work correctly.