nativescript-sqlite.md

NativeScript-Community/SQLite Common API Documentation

Link to repo

Overview

This document outlines the common API methods available for the NativeScript SQLite wrapper from the nativescript-community/sqlite package. These are the cross-platform methods that can be used on both iOS and Android.

Type Definitions

Basic Types

export type SqliteParam = null | number | string | ArrayBuffer | any;
export type SqliteParams = SqliteParam | SqliteParam[];
export type Db = any;
export type SqliteUpgrade = (db: Db) => void;

SqliteRow Interface

export interface SqliteRow {
    [name: string]: SqliteParam;
}

SQLiteDatabase Interface

The main interface for interacting with SQLite databases:

export interface SQLiteDatabase {
    // Database version management
    getVersion(): number;
    setVersion(version: number): void;
    
    // Connection status
    isOpen: boolean;
    
    // Database operations
    close(): void;
    
    // Query methods that return multiple rows
    select(query: string, params?: SqliteParams): Promise<SqliteRow[]>;
    selectArray(query: string, params?: SqliteParams): Promise<SqliteParam[][]>;
    
    // Query methods that return single row
    get(query: string, params?: SqliteParams): Promise<SqliteRow>;
    getArray(query: string, params?: SqliteParams): Promise<SqliteParam[]>;
    
    // Execute commands (INSERT, UPDATE, DELETE, CREATE, etc.)
    execute(query: string, params?: SqliteParams): Promise<void>;
    
    // Transaction support
    transaction<T = any>(action: (cancel?: () => void) => Promise<T>): Promise<T>;
    
    // Streaming/callback-based query execution
    each(
        query: string, 
        params: SqliteParams, 
        callback: (error: Error, result: SqliteRow) => void, 
        complete: (error: Error, count: number) => void
    ): Promise<number>;
}

Core Functions

Database Management

// Open or create a database
export function openOrCreate(filePath: string, flags?: number): SQLiteDatabase;

// Delete a database file
export function deleteDatabase(filePath: string): boolean;

Utility Functions

// Check if a value is null or undefined
export function isNothing(x: any): boolean;

// Convert parameters to string format suitable for SQLite
export function paramToString(p: SqliteParam): any;

// Convert parameter arrays to string arrays
export function paramsToStringArray(params?: SqliteParams): any[];

// Throw formatted SQLite errors
export function throwError(msg: string): void;

Database Path Management

Using NativeScript File System for Proper Database Paths

When creating SQLite databases in NativeScript, it's important to use valid writable paths. Use the knownFolders and path utilities from @nativescript/core to ensure cross-platform compatibility:

import { openOrCreate, deleteDatabase } from '@nativescript-community/sqlite';
import { knownFolders, path } from '@nativescript/core';

// Get the documents folder (writable on both iOS and Android)
const documentsFolder = knownFolders.documents();
const dbPath = path.join(documentsFolder.path, 'myapp.db');

// Alternative: Use the app data folder
const appDataFolder = knownFolders.currentApp();
const dbPath2 = path.join(appDataFolder.path, 'data', 'myapp.db');

// For temporary databases
const tempFolder = knownFolders.temp();
const tempDbPath = path.join(tempFolder.path, 'temp.db');

Available Known Folders

• knownFolders.documents() - User documents folder (recommended for user data)
• knownFolders.currentApp() - Application installation folder
• knownFolders.temp() - Temporary files folder
• knownFolders.ios.library() - iOS Library folder (iOS only)
• knownFolders.ios.cachesDirectory() - iOS Caches folder (iOS only)

Usage Examples

Basic Database Operations with Proper Paths

import { openOrCreate, deleteDatabase } from '@nativescript-community/sqlite';
import { knownFolders, path } from '@nativescript/core';

// Create database in documents folder
const documentsFolder = knownFolders.documents();
const dbPath = path.join(documentsFolder.path, 'gamedata.db');

// Open/create database
const db = openOrCreate(dbPath);

// Check if database is open
if (db.isOpen) {
    console.log('Database is open');
}

// Execute DDL statements
await db.execute(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
`);

// Insert data
await db.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['John Doe', '[email protected]']
);

// Select multiple rows
const users = await db.select('SELECT * FROM users WHERE name LIKE ?', ['%John%']);

// Select single row
const user = await db.get('SELECT * FROM users WHERE id = ?', [1]);

// Select as array format
const userArray = await db.getArray('SELECT name, email FROM users WHERE id = ?', [1]);

// Close database
db.close();

// Delete database file when needed
deleteDatabase(dbPath);

Complete Database Setup Example

import { openOrCreate, deleteDatabase, SQLiteDatabase } from '@nativescript-community/sqlite';
import { knownFolders, path, Folder } from '@nativescript/core';

class DatabaseService {
    private db: SQLiteDatabase;
    private dbPath: string;

    constructor() {
        // Ensure data directory exists
        const documentsFolder = knownFolders.documents();
        const dataFolder = documentsFolder.getFolder('data');
        this.dbPath = path.join(dataFolder.path, 'app.db');
    }

    async initialize(): Promise<void> {
        try {
            // Open/create database
            this.db = openOrCreate(this.dbPath);
            
            // Initialize database schema
            await this.createTables();
            
            console.log('Database initialized at:', this.dbPath);
        } catch (error) {
            console.error('Failed to initialize database:', error);
            throw error;
        }
    }

    private async createTables(): Promise<void> {
        await this.db.execute(`
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `);
    }

    async close(): Promise<void> {
        if (this.db && this.db.isOpen) {
            this.db.close();
        }
    }

    async reset(): Promise<void> {
        await this.close();
        const success = deleteDatabase(this.dbPath);
        if (success) {
            await this.initialize();
        }
    }
}

// Usage
const dbService = new DatabaseService();
await dbService.initialize();

Transaction Example

import { openOrCreate } from '@nativescript-community/sqlite';
import { knownFolders, path } from '@nativescript/core';

// Setup database path
const documentsFolder = knownFolders.documents();
const dbPath = path.join(documentsFolder.path, 'transactions.db');
const db = openOrCreate(dbPath);

await db.transaction(async (cancel) => {
    try {
        await db.execute('INSERT INTO users (name, email) VALUES (?, ?)', ['Jane Doe', '[email protected]']);
        await db.execute('UPDATE users SET email = ? WHERE name = ?', ['[email protected]', 'Jane Doe']);
        // Transaction will commit automatically if no errors
    } catch (error) {
        cancel(); // Rollback transaction
        throw error;
    }
});

Streaming Data with each()

import { openOrCreate } from '@nativescript-community/sqlite';
import { knownFolders, path } from '@nativescript/core';

// Setup database path
const documentsFolder = knownFolders.documents();
const dbPath = path.join(documentsFolder.path, 'streaming.db');
const db = openOrCreate(dbPath);

let count = 0;
await db.each(
    'SELECT * FROM users',
    [],
    (error, row) => {
        if (error) {
            console.error('Row error:', error);
            return;
        }
        console.log('User:', row.name, row.email);
        count++;
    },
    (error, totalCount) => {
        if (error) {
            console.error('Query error:', error);
            return;
        }
        console.log(`Processed ${count} rows out of ${totalCount} total`);
    }
);

Version Management

import { openOrCreate } from '@nativescript-community/sqlite';
import { knownFolders, path } from '@nativescript/core';

// Setup database path
const documentsFolder = knownFolders.documents();
const dbPath = path.join(documentsFolder.path, 'versioned.db');
const db = openOrCreate(dbPath);

// Get current database version
const currentVersion = db.getVersion();

// Set database version (useful for migrations)
db.setVersion(2);

Parameter Types

The SQLite wrapper supports various parameter types:

• null: SQL NULL value
• number: Integer or float values
• string: Text values
• ArrayBuffer: Binary data
• Arrays: For multiple parameters in prepared statements

Error Handling

All async methods return Promises and should be wrapped in try-catch blocks:

import { openOrCreate } from '@nativescript-community/sqlite';
import { knownFolders, path } from '@nativescript/core';

// Setup database path
const documentsFolder = knownFolders.documents();
const dbPath = path.join(documentsFolder.path, 'errorhandling.db');
const db = openOrCreate(dbPath);

try {
    const result = await db.select('SELECT * FROM users');
    console.log('Users:', result);
} catch (error) {
    console.error('Database error:', error);
}

Notes

• All query methods support parameterized queries to prevent SQL injection
• The database automatically handles platform-specific implementations (Android/iOS)
• Transactions provide ACID compliance for multiple operations
• The each() method is useful for processing large result sets without loading everything into memory
• Always close the database connection when done to free resources
• Use knownFolders and path from @nativescript/core for proper cross-platform file paths
• Documents folder is recommended for user data that should persist across app updates
• Temp folder is suitable for cache databases that can be recreated
• Always handle file system errors when creating database paths