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
Local device storage managed by Expo SQLite
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.
The metadata TEXT field allows storing JSON or other structured data for extensibility.
Best Practices
- Use parameterized queries: Always use placeholders (
?) to prevent SQL injection
- Handle errors: Wrap database operations in try-catch blocks
- Maintain indexes: Consider adding indexes for frequently queried fields
- Backup data: Implement backup strategies for important user data
- 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]
);
}