Library

FMDB

Overview

FMDB is "a thin Objective-C wrapper around SQLite's C API" developed as a long-beloved SQLite wrapper in iOS development. With the concept of "SQLite for humans," it provides complex SQLite C API operations through a simple and intuitive Objective-C interface. Comprehensively supporting features necessary for iOS app development such as transaction management, custom functions, and thread safety, it has established a solid position as a lightweight database solution that is not as heavy as Core Data.

Details

FMDB 2025 edition boasts mature APIs and high reliability as a veteran library for iOS SQLite operations with over 15 years of development experience. While providing direct access to all SQLite functionality, it automates memory management and error handling to offer an interface familiar to Objective-C developers. Simple design with three core classes (FMDatabase, FMResultSet, FMDatabaseQueue) enables safe and efficient database operations while leveraging SQL knowledge. Swift compatibility is also good, supporting modern iOS development.

Key Features

• Lightweight Wrapper: Thin wrapper around SQLite's C API, performance-focused
• Objective-C Native: Native API optimized for iOS development
• Thread Safety: Safe concurrent processing through FMDatabaseQueue
• SQL Parameter Binding: Prevention of SQL injection attacks
• Custom Function Support: Block-based custom function implementation
• Transaction Management: Automatic transaction processing and rollback support

Pros and Cons

Pros

• Direct access to all SQLite functionality without restrictions
• Lighter than Core Data with lower learning cost, leveraging SQL skills
• High stability and few bugs due to over 15 years of track record
• Support for multiple installation methods: CocoaPods, Carthage, SPM
• Works seamlessly in Swift projects
• Memory efficient with high performance

Cons

• No ORM functionality, requires manual SQL writing and object mapping
• Manual and complex migration management when models change
• Relationship and object graph management requires custom implementation
• Developers must be conscious of SQL injection prevention
• Complex queries can generate large amounts of boilerplate code
• May be less efficient for development compared to modern ORM libraries

Reference Pages

• FMDB GitHub Repository
• SQLite Official Documentation
• iOS Core Data vs FMDB Comparison

Code Examples

Setup

// CocoaPods
// Podfile
pod 'FMDB'
pod 'FMDB/SQLCipher'   // For encryption if needed

// Carthage
// Cartfile
github "ccgus/fmdb"

// Swift Package Manager
// Package.swift
.package(url: "https://github.com/ccgus/fmdb.git", from: "2.7.0")

// Import
#import <FMDB/FMDB.h>

// For Swift usage
import SQLite3  // May be needed in some cases

Basic Usage

// Database creation and connection
@interface DatabaseManager : NSObject
@property (nonatomic, strong) FMDatabase *database;
@end

@implementation DatabaseManager

- (instancetype)init {
    self = [super init];
    if (self) {
        [self setupDatabase];
    }
    return self;
}

- (void)setupDatabase {
    // Get Documents directory path
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database.sqlite"];
    
    // Open database
    self.database = [FMDatabase databaseWithPath:dbPath];
    
    if ([self.database open]) {
        NSLog(@"Database opened successfully");
        [self createTables];
    } else {
        NSLog(@"Failed to open database: %@", [self.database lastErrorMessage]);
    }
}

- (void)createTables {
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS users ("
                              @"id INTEGER PRIMARY KEY AUTOINCREMENT, "
                              @"name TEXT NOT NULL, "
                              @"email TEXT UNIQUE NOT NULL, "
                              @"age INTEGER, "
                              @"created_at DATETIME DEFAULT CURRENT_TIMESTAMP)";
    
    if ([self.database executeUpdate:createTableSQL]) {
        NSLog(@"Table created successfully");
    } else {
        NSLog(@"Failed to create table: %@", [self.database lastErrorMessage]);
    }
}

@end

Query Execution

// Data insertion
- (BOOL)insertUserWithName:(NSString *)name email:(NSString *)email age:(NSInteger)age {
    NSString *insertSQL = @"INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
    
    BOOL success = [self.database executeUpdate:insertSQL, name, email, @(age)];
    
    if (success) {
        NSLog(@"User inserted with ID: %lld", [self.database lastInsertRowId]);
    } else {
        NSLog(@"Failed to insert user: %@", [self.database lastErrorMessage]);
    }
    
    return success;
}

// Data retrieval
- (NSArray *)getAllUsers {
    NSMutableArray *users = [[NSMutableArray alloc] init];
    NSString *selectSQL = @"SELECT * FROM users ORDER BY created_at DESC";
    
    FMResultSet *results = [self.database executeQuery:selectSQL];
    
    while ([results next]) {
        NSDictionary *user = @{
            @"id": @([results intForColumn:@"id"]),
            @"name": [results stringForColumn:@"name"],
            @"email": [results stringForColumn:@"email"],
            @"age": @([results intForColumn:@"age"]),
            @"created_at": [results dateForColumn:@"created_at"]
        };
        [users addObject:user];
    }
    
    [results close];
    return [users copy];
}

// Conditional search
- (NSArray *)getUsersWithAgeGreaterThan:(NSInteger)age {
    NSMutableArray *users = [[NSMutableArray alloc] init];
    NSString *selectSQL = @"SELECT * FROM users WHERE age > ? ORDER BY name";
    
    FMResultSet *results = [self.database executeQuery:selectSQL, @(age)];
    
    while ([results next]) {
        NSDictionary *user = @{
            @"id": @([results intForColumn:@"id"]),
            @"name": [results stringForColumn:@"name"],
            @"email": [results stringForColumn:@"email"],
            @"age": @([results intForColumn:@"age"])
        };
        [users addObject:user];
    }
    
    [results close];
    return [users copy];
}

// Data update
- (BOOL)updateUserEmail:(NSString *)newEmail forUserId:(NSInteger)userId {
    NSString *updateSQL = @"UPDATE users SET email = ? WHERE id = ?";
    
    BOOL success = [self.database executeUpdate:updateSQL, newEmail, @(userId)];
    
    if (!success) {
        NSLog(@"Failed to update user: %@", [self.database lastErrorMessage]);
    }
    
    return success;
}

// Data deletion
- (BOOL)deleteUserWithId:(NSInteger)userId {
    NSString *deleteSQL = @"DELETE FROM users WHERE id = ?";
    
    BOOL success = [self.database executeUpdate:deleteSQL, @(userId)];
    
    if (!success) {
        NSLog(@"Failed to delete user: %@", [self.database lastErrorMessage]);
    }
    
    return success;
}

Data Operations

// Usage example
- (void)demonstrateUsage {
    DatabaseManager *dbManager = [[DatabaseManager alloc] init];
    
    // Add users
    [dbManager insertUserWithName:@"John" email:@"[email protected]" age:25];
    [dbManager insertUserWithName:@"Jane" email:@"[email protected]" age:30];
    [dbManager insertUserWithName:@"Bob" email:@"[email protected]" age:22];
    
    // Get all users
    NSArray *allUsers = [dbManager getAllUsers];
    NSLog(@"All users: %@", allUsers);
    
    // Conditional search
    NSArray *adultUsers = [dbManager getUsersWithAgeGreaterThan:24];
    NSLog(@"Users older than 24: %@", adultUsers);
    
    // Update user information
    if ([allUsers count] > 0) {
        NSDictionary *firstUser = allUsers[0];
        NSInteger userId = [firstUser[@"id"] integerValue];
        [dbManager updateUserEmail:@"[email protected]" forUserId:userId];
    }
    
    // Delete user
    if ([allUsers count] > 1) {
        NSDictionary *secondUser = allUsers[1];
        NSInteger userId = [secondUser[@"id"] integerValue];
        [dbManager deleteUserWithId:userId];
    }
}

Configuration and Customization

// Transaction management
- (BOOL)performBulkInsert:(NSArray *)userData {
    __block BOOL success = YES;
    
    [self.database inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (NSDictionary *user in userData) {
            NSString *insertSQL = @"INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
            
            if (![db executeUpdate:insertSQL, user[@"name"], user[@"email"], user[@"age"]]) {
                NSLog(@"Failed to insert user: %@", [db lastErrorMessage]);
                success = NO;
                *rollback = YES;
                return;
            }
        }
    }];
    
    return success;
}

// Thread-safe database operations
@interface ThreadSafeDatabaseManager : NSObject
@property (nonatomic, strong) FMDatabaseQueue *databaseQueue;
@end

@implementation ThreadSafeDatabaseManager

- (instancetype)init {
    self = [super init];
    if (self) {
        [self setupDatabaseQueue];
    }
    return self;
}

- (void)setupDatabaseQueue {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"thread_safe_database.sqlite"];
    
    self.databaseQueue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
    
    [self.databaseQueue inDatabase:^(FMDatabase *db) {
        NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS products ("
                                  @"id INTEGER PRIMARY KEY AUTOINCREMENT, "
                                  @"name TEXT NOT NULL, "
                                  @"price REAL, "
                                  @"category TEXT)";
        
        if (![db executeUpdate:createTableSQL]) {
            NSLog(@"Failed to create products table: %@", [db lastErrorMessage]);
        }
    }];
}

- (void)insertProductsAsync:(NSArray *)products completion:(void(^)(BOOL success))completion {
    [self.databaseQueue inDatabase:^(FMDatabase *db) {
        BOOL allSuccess = YES;
        
        for (NSDictionary *product in products) {
            NSString *insertSQL = @"INSERT INTO products (name, price, category) VALUES (?, ?, ?)";
            
            if (![db executeUpdate:insertSQL, product[@"name"], product[@"price"], product[@"category"]]) {
                NSLog(@"Failed to insert product: %@", [db lastErrorMessage]);
                allSuccess = NO;
            }
        }
        
        dispatch_async(dispatch_get_main_queue(), ^{
            if (completion) {
                completion(allSuccess);
            }
        });
    }];
}

@end

Error Handling

// Comprehensive error handling
@interface SafeDatabaseManager : NSObject
@property (nonatomic, strong) FMDatabase *database;
@end

@implementation SafeDatabaseManager

- (BOOL)executeQuery:(NSString *)sql withParameters:(NSArray *)parameters resultHandler:(void(^)(FMResultSet *results))resultHandler {
    if (!self.database.isOpen) {
        NSLog(@"Database is not open");
        return NO;
    }
    
    FMResultSet *results = [self.database executeQuery:sql withArgumentsInArray:parameters];
    
    if (!results) {
        NSLog(@"Query execution failed: %@", [self.database lastErrorMessage]);
        NSLog(@"SQL: %@", sql);
        NSLog(@"Parameters: %@", parameters);
        return NO;
    }
    
    if (resultHandler) {
        resultHandler(results);
    }
    
    [results close];
    return YES;
}

- (BOOL)executeUpdate:(NSString *)sql withParameters:(NSArray *)parameters {
    if (!self.database.isOpen) {
        NSLog(@"Database is not open");
        return NO;
    }
    
    BOOL success = [self.database executeUpdate:sql withArgumentsInArray:parameters];
    
    if (!success) {
        NSLog(@"Update execution failed: %@", [self.database lastErrorMessage]);
        NSLog(@"SQL: %@", sql);
        NSLog(@"Parameters: %@", parameters);
        NSLog(@"Last error code: %d", [self.database lastErrorCode]);
    }
    
    return success;
}

// Custom function creation
- (void)setupCustomFunctions {
    // Custom function definition (example: function that returns string length)
    [self.database makeFunctionNamed:@"STRLEN" maximumArguments:1 withBlock:^(FMDatabase *db, NSArray *args) {
        if ([args count] > 0 && args[0] != [NSNull null]) {
            NSString *str = args[0];
            return [NSNumber numberWithInteger:[str length]];
        }
        return [NSNumber numberWithInteger:0];
    }];
    
    // Custom function usage example
    NSString *testSQL = @"SELECT name, STRLEN(name) as name_length FROM users";
    [self executeQuery:testSQL withParameters:nil resultHandler:^(FMResultSet *results) {
        while ([results next]) {
            NSString *name = [results stringForColumn:@"name"];
            NSInteger nameLength = [results intForColumn:@"name_length"];
            NSLog(@"Name: %@, Length: %ld", name, (long)nameLength);
        }
    }];
}

@end