Database

SQLite database implementation and configuration

Overview

Simple Manager Mobile uses SQLite as its local database, providing offline-first data storage through the Expo SQLite library.

Database Configuration

The database is initialized using Expo SQLite with a synchronous connection:

import * as SQLite from "expo-sqlite";

export const db = SQLite.openDatabaseSync("simple_manager.db");

Database Details

Database Name · string

simple_manager.db

Storage Location · string

Local device storage managed by Expo SQLite

Connection Type · string

Synchronous connection using openDatabaseSync()

Schema

The database contains a single primary table for storing records:

Records Table

CREATE TABLE records (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    subtitle TEXT,
    metadata TEXT,
    type TEXT NOT NULL,
    userId TEXT,
    createdAt TEXT,
    updatedAt TEXT,
    isDeleted INTEGER
);

Usage

Once the database connection is established, you can use it throughout your application:

import { db } from './infraestructure/database/database';

// Execute a query
const result = db.getAllSync('SELECT * FROM records WHERE isDeleted = 0');

// Insert a record
db.runSync(
    'INSERT INTO records (id, title, type, createdAt, updatedAt, isDeleted) VALUES (?, ?, ?, ?, ?, ?)',
    [id, title, type, now, now, 0]
);

// Update a record
db.runSync(
    'UPDATE records SET title = ?, updatedAt = ? WHERE id = ?',
    [newTitle, now, id]
);

// Soft delete a record
db.runSync(
    'UPDATE records SET isDeleted = 1, updatedAt = ? WHERE id = ?',
    [now, id]
);

Key Features

Offline-First

All data is stored locally on the device, allowing the app to function without an internet connection.

Synchronous Operations

Using openDatabaseSync() provides a simpler API for database operations without dealing with promises or callbacks.

Soft Deletes

Records are not permanently deleted but marked with isDeleted = 1, enabling data recovery and audit trails.

Flexible Metadata

The metadata TEXT field allows storing JSON or other structured data for extensibility.

Best Practices

1. Use parameterized queries: Always use placeholders (?) to prevent SQL injection
2. Handle errors: Wrap database operations in try-catch blocks
3. Maintain indexes: Consider adding indexes for frequently queried fields
4. Backup data: Implement backup strategies for important user data
5. Migration strategy: Plan for schema changes in future versions

Example: Complete CRUD Operations

import { db } from './infraestructure/database/database';
import { v4 as uuidv4 } from 'uuid';

// Create
function createRecord(title: string, type: string) {
    const id = uuidv4();
    const now = new Date().toISOString();
    
    db.runSync(
        'INSERT INTO records (id, title, type, createdAt, updatedAt, isDeleted) VALUES (?, ?, ?, ?, ?, ?)',
        [id, title, type, now, now, 0]
    );
    
    return id;
}

// Read
function getActiveRecords() {
    return db.getAllSync(
        'SELECT * FROM records WHERE isDeleted = 0 ORDER BY createdAt DESC'
    );
}

// Update
function updateRecord(id: string, title: string) {
    const now = new Date().toISOString();
    
    db.runSync(
        'UPDATE records SET title = ?, updatedAt = ? WHERE id = ?',
        [title, now, id]
    );
}

// Delete (soft)
function deleteRecord(id: string) {
    const now = new Date().toISOString();
    
    db.runSync(
        'UPDATE records SET isDeleted = 1, updatedAt = ? WHERE id = ?',
        [now, id]
    );
}

Related Topics

• Records
• Data Model
• Validation