• expo-sqlite-reactive
  • Comparación con Realm
  • Features
  • Installation
  • Basic Usage
    • 1. Database Initialization
    • 2. Create Tables
    • 3. Insert Data
    • 4. Perform Queries with useQuery
      • useQuery Parameters
    • 5. Delete and Update Data
    • Complex Query Examples
      • Query with Conditions
      • Insert and Query Data
  • Comparison with Realm
  • Complete Example
• expo-sqlite-reactive Methods
  • Main Methods
    • initialize(databaseName: string): SQLiteManager
    • createTable(tableName: string, data: ColumnDefinition): Promise<boolean>
    • dropTable(tableName: string): Promise<boolean | null>
    • insert(tableName: string, data: KeyValueData): Promise<TypeReturnQuery | null>
    • update(tableName: string, whereClause: object, data: KeyValueData): Promise<number | null>
    • delete(tableName: string, whereClause?: object): Promise<number | null>
    • select<T>(tableName: string, columns?: string[], whereClause?: object, sort?: { [key: string]: number }, limit?: number, offset?: number): Promise<T[] | null>
  • Advanced Methods
    • createIndex(tableName: string, columnName: string): Promise<void>
    • addTableColumns(tableName: string, changes: ColumnDefinition): Promise<boolean>
    • getAllTables(): Promise<tableInternalSchema[]>
    • getTableSchema(tableName: string): Promise<tableRowSchema[]>
    • validateTableSchema(tableName: string, expectedSchema: ColumnDefinition): Promise<boolean>
  • Reactivity Methods
    • useWatchTable(tableName: string, listener: () => void): void
    • useQuery<T>(tableName: string, columns?: string[], whereClause?: object, sort?: { [key: string]: number }): T[]
    • translateMongoJsonToSql(query: object): { whereStatement: string; values: any[] }
• Examples
  • Create Table and Indexes
  • Using useQuery
  • Listening for Changes in Tables
• Roadmap
• About the Author
• License

expo-sqlite-reactive

expo-sqlite-reactive is a solution that extends the functionality of expo-sqlite to provide a reactive way of working with SQLite databases in Expo and React Native applications. This package allows creating tables, performing CRUD operations, and keeping your UI updated using the useQuery hook. It enables the creation of offline applications quickly, simply, and reliably. Stock42 uses this library for all its applications. We recommend S42 for faster mobile application development.

Comparación con Realm

Feature	expo-sqlite-reactive	Realm
Library Size	Lightweight	Heavy
Reactivity	Yes, with useQuery	Yes, integrated
Compatibility	Native SQLite	Limited to Realm database
Ease of Use	Simple	Complex for beginners

Features

• Reactivity: Use useQuery to listen for and automatically update the UI when changes occur in the database.
• CRUD Support: Simple methods to create, read, update, and delete data.
• SQLite-Based: Leverages the robustness of expo-sqlite for local storage.
• Simplicity: Provides an intuitive and straightforward interface.

Installation

1npm install expo-sqlite-reactive

Make sure expo-sqlite is installed as a dependency:

1expo install expo-sqlite

Basic Usage

1. Database Initialization

First, initialize the database with the desired name:

1import { SQLiteManager } from 'expo-sqlite-reactive';
2
3async function initializeDB() {
4  try {
5    SQLiteManager.initialize('mydatabase.db');
6
7    await SQLiteManager.createTable('users', {
8      usersUUID: 'text',
9      firstName: 'text',
10      lastName: 'text',
11      email: 'text',
12      password: 'text',
13    });
14  } catch (error) {
15    console.error('Error initializing database:', error);
16  }
17}

2. Create Tables

1await SQLiteManager.createTable('products', {
2  productUUID: 'text',
3  productName: 'text',
4  productPrice: 'integer',
5});

3. Insert Data

1const product = {
2  productUUID: '1234',
3  productName: 'Laptop',
4  productPrice: 999,
5};
6
7await SQLiteManager.insert('products', product);

4. Perform Queries with useQuery

useQuery provides a reactive way to query data and update the UI when changes occur.

1import { useQuery } from 'expo-sqlite-reactive';
2
3export default function ProductList() {
4  // Reactive query on the "products" table
5  const [products, error] = useQuery('products', ['*'], undefined, { productName: 1 });
6
7  if (error) return <Text>Error loading products</Text>;
8
9  return (
10    <View>
11      {products.map((product) => (
12        <Text key={product.productUUID}>{product.productName} - ${product.productPrice}</Text>
13      ))}
14    </View>
15  );
16}

useQuery Parameters

1const [data, error] = useQuery(
2  tableName: string,          // Name of the table to query
3  columns: string[],          // Columns to select (e.g., ['*'] to select all)
4  whereClause?: object,       // Optional filter conditions (e.g., { price: { $gt: 50 } })
5  sort?: { [key: string]: 1 | -1 } // Sorting (e.g., { price: 1 } for ascending order by price)
6);

• tableName: The name of the table to query.
• columns: An array of strings specifying which columns to select. ['*'] selects all columns.
• whereClause: An optional object to define filter conditions, similar to a MongoDB query.
• sort: An optional object to define the sorting order (1 for ascending, -1 for descending).

5. Delete and Update Data

Drop a table:

1await SQLiteManager.dropTable('products');

Update a record:

1await SQLiteManager.update('products', { productUUID: '1234' }, { productPrice: 899 });

Delete a record:

1await SQLiteManager.delete('products', { productUUID: '1234' });

Complex Query Examples

Query with Conditions

1const [products, error] = useQuery(
2  'products',
3  ['productName', 'productPrice'],
4  { productPrice: { $gt: 100 } }, // Condición: precios mayores a 100
5  { productName: 1 }              // Orden ascendente por nombre de producto
6);

Insert and Query Data

1// Insertar new row
2await SQLiteManager.insert('users', {
3  usersUUID: '1234',
4  firstName: 'John',
5  lastName: 'Doe',
6  email: 'john.doe@example.com',
7  password: 'password123',
8});
9
10// get all users
11const [users, error] = useQuery('users', ['*']);

Comparison with Realm

expo-sqlite-reactive an be considered a lightweight and efficient alternative to Realm in certain situations:

• Reactive Sync: useQuery provides a reactive way to update the UI, similar to Realm's reactive collections.
• Simplicity: While Realm offers many advanced features, expo-sqlite-reactive is simpler and designed for scenarios where complex synchronization or heavy databases are not required.
• Native Support: Being based on SQLite, it leverages a backend that is common in many systems and devices, offering advantages in performance and compatibility.

Complete Example

1import React from 'react';
2import { View, Text, Button } from 'react-native';
3import { SQLiteManager, useQuery } from 'expo-sqlite-reactive';
4import * as uuid from 'uuid';
5
6export default function App() {
7  const [stores, error] = useQuery('stores', ['*'], undefined, { added: -1 });
8
9  async function createStores() {
10    for (let i = 0; i < 5; i++) {
11      const store = {
12        storesUUID: uuid.v4(),
13        storeName: `Store ${i}`,
14        storeAddress: `Address ${i}`,
15      };
16      await SQLiteManager.insert('stores', store);
17    }
18  }
19
20  return (
21    <View>
22      <Button title="Create Stores" onPress={createStores} />
23      {error && <Text>Error loading stores: {error.message}</Text>}
24      {stores.map((store) => (
25        <Text key={store.storesUUID}>
26          {store.storeName} - {store.storeAddress}
27        </Text>
28      ))}
29    </View>
30  );
31}

expo-sqlite-reactive Methods

This section includes all the available methods in the expo-sqlite-reactive library, with detailed descriptions and usage examples.

Main Methods

initialize(databaseName: string): SQLiteManager

Initializes the SQLite database and sets the WAL mode to improve performance.

Parameters:

• databaseName: The name of the database.

Example:

1SQLiteManager.initialize('mydatabase.db');

createTable(tableName: string, data: ColumnDefinition): Promise<boolean>

Creates a table in the database if it does not already exist.

Parameters:

• tableName: The name of the table.
• data: An object defining the columns and their data types.

Example:

1await SQLiteManager.createTable('users', {
2  userId: 'TEXT PRIMARY KEY',
3  userName: 'TEXT',
4  createdAt: 'INTEGER',
5});

dropTable(tableName: string): Promise<boolean | null>

Deletes a table from the database if it exists.

Parameters:

• tableName: The table name.

Example:

1await SQLiteManager.dropTable('users');

insert(tableName: string, data: KeyValueData): Promise<TypeReturnQuery | null>

Inserta new row into table

Parameters:

• tableName: The table name
• data: Objec with data to insert

Example:

1await SQLiteManager.insert('users', {
2  userId: '1',
3  userName: 'John Doe',
4  createdAt: Date.now(),
5});

update(tableName: string, whereClause: object, data: KeyValueData): Promise<number | null>

Updates records in a table based on a condition.

Parameters:

• tableName: Table name.
• whereClause: An object that defines the update conditions.
• data: An object containing the values to update.

Example:

1await SQLiteManager.update('users', { userId: '1' }, { userName: 'Jane Doe' });

delete(tableName: string, whereClause?: object): Promise<number | null>

Deletes records in a table based on a condition.

Parameters:

• tableName: Table Name
• whereClause: An object that defines the deletion conditions.

Example:

1await SQLiteManager.delete('users', { userId: '1' });

select<T>(tableName: string, columns?: string[], whereClause?: object, sort?: { [key: string]: number }, limit?: number, offset?: number): Promise<T[] | null>

Performs a query on a table.

Parameters:

• tableName: Table name.
• columns: An array of column names to select.
• whereClause: An object that defines the query conditions.
• sort: An object that defines the sorting order of the results.
• limit: An integer that defines the maximum number of results to return.
• offset: An integer that defines the number of results to skip.

Example:

1const users = await SQLiteManager.select('users', ['userId', 'userName'], { userName: { $like: '%John%' } }, undefined, 10, 0);

Advanced Methods

createIndex(tableName: string, columnName: string): Promise<void>

Creates an index on a column to improve query performance.

Example:

1await SQLiteManager.createIndex('users', 'userName');

addTableColumns(tableName: string, changes: ColumnDefinition): Promise<boolean>

Adds new columns to an existing table.

Example:

1await SQLiteManager.addTableColumns('users', {
2  middleName: 'TEXT',
3  isActive: 'INTEGER',
4});

getAllTables(): Promise<tableInternalSchema[]>

Returns a list of all tables in the database.

Example:

1const tables = await SQLiteManager.getAllTables();
2console.log(tables);

getTableSchema(tableName: string): Promise<tableRowSchema[]>

Retrieves the schema of a specific table.

Example:

1const schema = await SQLiteManager.getTableSchema('users');
2console.log(schema);

validateTableSchema(tableName: string, expectedSchema: ColumnDefinition): Promise<boolean>

Validates whether the schema of a table matches an expected schema.

Example:

1const isValid = await SQLiteManager.validateTableSchema('users', {
2  userId: 'TEXT',
3  userName: 'TEXT',
4});
5console.log(isValid);

Reactivity Methods

useWatchTable(tableName: string, listener: () => void): void

Listens for changes in a table and executes a function when changes occur.

Example:

1useWatchTable('users', () => {
2  console.log('La tabla "users" ha cambiado.');
3});

useQuery<T>(tableName: string, columns?: string[], whereClause?: object, sort?: { [key: string]: number }): T[]

Consulta datos de una tabla y actualiza la UI automáticamente cuando hay cambios.

Example:

1const [users, error] = useQuery('users', ['userId', 'userName'], { userName: { $like: '%John%' } });

translateMongoJsonToSql(query: object): { whereStatement: string; values: any[] }

Queries data from a table and automatically updates the UI when changes occur.

Example:

1const { whereStatement, values } = translateMongoJsonToSql({
2  name: { $like: '%John%' },
3  age: { $gte: 18 },
4});

Examples

Create Table and Indexes

1await SQLiteManager.createTable('users', {
2  userId: 'TEXT PRIMARY KEY',
3  userName: 'TEXT',
4  createdAt: 'INTEGER',
5});
6
7await SQLiteManager.createIndex('users', 'userName');
8

Using useQuery

1function UserList() {
2  const [users, error] = useQuery('users', ['userId', 'userName'], { userName: { $like: 'John%' } });
3
4  if (error) return <Text>Error loading users: {error.message}</Text>;
5
6  return (
7    <View>
8      {users.map(user => (
9        <Text key={user.userId}>{user.userName}</Text>
10      ))}
11    </View>
12  );
13}

Listening for Changes in Tables

1useWatchTable('users', () => {
2  console.log('Table "users" has changed');
3});

Roadmap

• Support for transactions.
• Add support for remote server synchronization.
• Add support to export the entire database to CSV.
• Add support to export the entire database to JSON.
• Add support to export the entire database to Excel.
• Add support to import data from CSV.
• Add support to import data from JSON.
• Add support to import data from Excel.
• Add support for background database updates.
• Add support for background database updates with transactions.
• Add support to send the database to a remote server.

About the Author

Library developed by César Casas / Stock42.

License

MIT License. See the LICENSE file for more details.