• 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 }): 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

npm install expo-sqlite-reactive

Make sure expo-sqlite is installed as a dependency:

expo install expo-sqlite

Basic Usage

1. Database Initialization

First, initialize the database with the desired name:

import { SQLiteManager } from 'expo-sqlite-reactive';

async function initializeDB() {
  try {
    SQLiteManager.initialize('mydatabase.db');

    await SQLiteManager.createTable('users', {
      usersUUID: 'text',
      firstName: 'text',
      lastName: 'text',
      email: 'text',
      password: 'text',
    });
  } catch (error) {
    console.error('Error initializing database:', error);
  }
}

2. Create Tables

await SQLiteManager.createTable('products', {
  productUUID: 'text',
  productName: 'text',
  productPrice: 'integer',
});

3. Insert Data

const product = {
  productUUID: '1234',
  productName: 'Laptop',
  productPrice: 999,
};

await SQLiteManager.insert('products', product);

4. Perform Queries with useQuery

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

import { useQuery } from 'expo-sqlite-reactive';

export default function ProductList() {
  // Reactive query on the "products" table
  const [products, error] = useQuery('products', ['*'], undefined, { productName: 1 });

  if (error) return <Text>Error loading products</Text>;

  return (
    <View>
      {products.map((product) => (
        <Text key={product.productUUID}>{product.productName} - ${product.productPrice}</Text>
      ))}
    </View>
  );
}

useQuery Parameters

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

• 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:

await SQLiteManager.dropTable('products');

Update a record:

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

Delete a record:

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

Complex Query Examples

Query with Conditions

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

Insert and Query Data

// Insertar new row
await SQLiteManager.insert('users', {
  usersUUID: '1234',
  firstName: 'John',
  lastName: 'Doe',
  email: '[email protected]',
  password: 'password123',
});

// get all users
const [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

import React from 'react';
import { View, Text, Button } from 'react-native';
import { SQLiteManager, useQuery } from 'expo-sqlite-reactive';
import * as uuid from 'uuid';

export default function App() {
  const [stores, error] = useQuery('stores', ['*'], undefined, { added: -1 });

  async function createStores() {
    for (let i = 0; i < 5; i++) {
      const store = {
        storesUUID: uuid.v4(),
        storeName: `Store ${i}`,
        storeAddress: `Address ${i}`,
      };
      await SQLiteManager.insert('stores', store);
    }
  }

  return (
    <View>
      <Button title="Create Stores" onPress={createStores} />
      {error && <Text>Error loading stores: {error.message}</Text>}
      {stores.map((store) => (
        <Text key={store.storesUUID}>
          {store.storeName} - {store.storeAddress}
        </Text>
      ))}
    </View>
  );
}

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:

SQLiteManager.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:

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

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

Deletes a table from the database if it exists.

Parameters:

• tableName: The table name.

Example:

await 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:

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

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:

await 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:

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

select<T>(tableName: string, columns?: string[], whereClause?: object, sort?: { [key: string]: 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.

Example:

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

Advanced Methods

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

Creates an index on a column to improve query performance.

Example:

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

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

Adds new columns to an existing table.

Example:

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

getAllTables(): Promise<tableInternalSchema[]>

Returns a list of all tables in the database.

Example:

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

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

Retrieves the schema of a specific table.

Example:

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

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

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

Example:

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

Reactivity Methods

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

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

Example:

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

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:

const [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:

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

Examples

Create Table and Indexes

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

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

Using useQuery

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

  if (error) return <Text>Error loading users: {error.message}</Text>;

  return (
    <View>
      {users.map(user => (
        <Text key={user.userId}>{user.userName}</Text>
      ))}
    </View>
  );
}

Listening for Changes in Tables

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

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.