react-native-sqlite-table

Type-safe SQLite table wrapper for React Native.
Built on top of react-native-sqlite-storage, this library provides schema management, CRUD utilities, and React Hook support with full TypeScript integration.

✨ Features

- Type-safe column definitions
Define interfaces and column specs in one place, ensuring both runtime and compile-time validation.

- Simple CRUD utilities
Use insert, update, delete, query, and more with concise APIs.

- Schema versioning & migration
Manage table lifecycle automatically using DDLOption, with built-in version tracking and migrations.

- React Hook support
useSQLiteTable automatically handles DB open/close and keeps table instances stable across re-renders.

- Advanced type support
Built-in handling for BLOB, BOOLEAN, and automatic JSON serialization.

📱 Supported Platforms

| Platform | Support | Notes |
|----------|---------|-------|
| iOS | ✅ | Fully supported |
| Android | ✅ | Fully supported |
| Windows | ✅ | Fully supported |
| macOS | ✅ | Fully supported |

---

📦 Installation

``bash
npm install react-native-sqlite-table react-native-sqlite-storage

or

yarn add react-native-sqlite-table react-native-sqlite-storage
`

> Note: react and react-native are peer dependencies and must already be installed.

🚀 Quick Start

$3

`ts
// message.types.ts
export interface Message {
roomId: string;
text: string;
timestamp: number;
edited?: boolean;
}

export const messageColumns = {
roomId: 'TEXT',
text: { type: 'TEXT', nullable: false },
timestamp: 'INTEGER',
edited: { type: 'BOOLEAN', default: false },
};
`

$3

`ts
import { SQLiteTable } from 'react-native-sqlite-table';
import type { Message } from './message.types';

const table = new SQLiteTable(
'MyAppSQLiteDB', // Database file name
'messages', // Table name
messageColumns, // Column specs
{ version: 1 }, // (optional) DDL options
true // (optional) Debug mode
);

await table.insert({
roomId: 'lobby',
text: 'Hello world!',
timestamp: Date.now(),
});

const rows = await table.all();
console.log(rows);
`

$3

`tsx
import React from 'react';
import { useSQLiteTable } from 'react-native-sqlite-table';
import { messageColumns, Message } from './message.types';

export function Chat() {
const table = useSQLiteTable({
tableName: 'messages',
columns: messageColumns,
});

const send = async (text: string) => {
await table.insert({ roomId: 'lobby', text, timestamp: Date.now() });
};

// DB connection is automatically closed when the component unmounts
return ;
}
`

🗂 Column Spec Format

Each column can be defined as an object or shorthand string ('TEXT', 'INTEGER', etc.).

| Property | Type | Description |
|----------|----------------------------------------------|--------------------------------------|
| type | 'TEXT' \| 'INTEGER' \| 'BOOLEAN' \| 'BLOB' | SQLite column type (required) |
| nullable | boolean | Whether NULL values are allowed |
| default | string \| number \| boolean \| object | Default value |
| unique | boolean | Create a unique index |
| check | string | Add a CHECK(...) constraint |

⚡ Schema & Migration

Use DDLOption for automated schema lifecycle management.

`ts
const chatDDL = {
version: 2,
beforeCreateNoTxn: [['PRAGMA auto_vacuum=INCREMENTAL']],
afterCreateTxn: [
['CREATE INDEX IF NOT EXISTS idx_chat_room_ts ON messages(room_id, timestamp)']
],
afterCreateNoTxn: [['PRAGMA journal_mode=WAL']],
onEveryOpen: [['PRAGMA foreign_keys=ON']],
migrationSteps: [
{
to: 2,
txn: [['ALTER TABLE messages ADD COLUMN edited BOOLEAN DEFAULT 0']],
}
]
};
`

- version: Target schema version (positive integer)
- beforeCreateNoTxn: Commands before table creation (outside transaction)
- afterCreateTxn: Commands after table creation (inside transaction)
- afterCreateNoTxn: Commands after table creation (outside transaction)
- onEveryOpen: Commands on every database open
- migrationSteps: Define version upgrade steps

📖 API Reference

$3

| Method | Description |
|---------------------------------|-------------------------------------|
| open() / close() | Open or close the database connection |
| insert(row) / insertMany(rows) | Insert rows |
| update(where, changes) | Update rows matching condition |
| delete(where) | Delete rows |
| all() | Fetch all rows |
| findByKeyValue(obj) | Find by key-value pairs |
| query(sql, params?) | Run a custom SELECT query |
| queryWithPK(sql, params?) | SELECT query including row_id |
| run(sql, params?) | Run a custom non-SELECT query |

$3

React Hook that accepts:
- dbName
- tableName
- columns
- ddlOption

It automatically opens/closes DB with the component lifecycle.

🧑‍💻 Additional Examples

$3

`ts
// Update a message by rowId
await table.update({ row_id: 1 }, { text: 'Edited text', edited: true });

// Delete all messages in a room
await table.delete({ roomId: 'lobby' });
`

$3

`ts
// Custom SELECT
const results = await table.query(
'SELECT * FROM messages WHERE roomId = ? ORDER BY timestamp DESC LIMIT ?',
['lobby', 50]
);
`

$3

`tsx
function ChatList() {
const table = useSQLiteTable({
tableName: 'messages',
columns: messageColumns,
});

const [messages, setMessages] = React.useState([]);

React.useEffect(() => {
table.all().then(setMessages);
}, [table]);

return ;
}
`

---

🔄 Migration Example

`ts
const ddl = {
version: 3,
migrationSteps: [
{
to: 2,
txn: [
['ALTER TABLE messages ADD COLUMN edited BOOLEAN DEFAULT 0']
]
},
{
to: 3,
txn: [
['ALTER TABLE messages ADD COLUMN sender TEXT'],
['CREATE INDEX IF NOT EXISTS idx_messages_room_sender ON messages(room_id, sender)']
]
}
]
};
`

- v1 → v2: add edited column
- v2 → v3: add sender` column + index

> ⚠️ Always test migration with existing data to avoid accidental loss.

---

🧩 TypeScript First-Class Support

- Compiler ensures your TypeScript interface is consistent with the defined columns.
- Insert, update, and select queries are fully type-safe at compile time.
- Query results are automatically typed as your entity interface.
- If you don’t pass a generic type, the type is still inferred automatically from the provided column definitions.

📄 License

MIT

🤝 Contributing

Contributions are welcome!
Feel free to open issues or submit PRs for bug reports, feature requests, or improvements.