How to Connect SQLite Database in iOS

How do I access SQLite database instance on iPhone?

In Xcode select window->organizer and expand the node next to your application in the applications section on your phone. Select the black downward pointing arrow next to application data and save the file anywhere on your desktop. Your sqlite database should be in there somewhere.

As for how to go about getting it back on the phone once your done i have no clue.

Connecting SQLite 3 Database to Swift

This is because all applications in iOS are sandboxed. There is no way to avoid that. You can not save the full path. It will change every time you launch your App. What you need is to save only your file name and its parent directory. Use them to contruct a new fileURL and/or path every time you need to access them.

edit/update:

If your file is located inside your App Bundle:

let databaseURL = Bundle.main.url(forResource: "Database", withExtension: "db")!

Note: If you need the database path you have to get your fileURL path property not the absoluteString. Last but not least the Bundle is read only. You need to move/copy your database to another directory (application support) to be able to modify it.

let databasePath = databaseURL.path

Where to store a SQLite DB connection with iOS?

Is using UserDefaults here an appropriate case?

Definitely not. Like you said yourself: you want it to exist while the app is open. While UserDefaults is for things you want to store when app is not running.

Or EnvironmentObject?

You could, but semantically it's still wrong: Apple defines it as "A property wrapper type for an observable object supplied by a parent or ancestor view.", which doesn't really fit the DB connection. It's not an observable object with states.

Ideally you step back and look at a more generic architecture of your app.

Views want data in a specific format. They don't care where the data is coming from.
The fact that data is coming from DB is an implementation detail - tomorrow you may decide to retrieve it from remote server, and you don't want to change every single view because of that.

So what you really want is

View talks to some sort of "data provider" interface that defines an interface by which views can get their data regardless of where it's stored.
Your implementation of "data provider" is to talk to the local database (currently, but it can changed base don your needs).

In this structure the DB connection(s) are managed by data provider, and do not need to be shared with anyone. And your views will actually use Observable objects, except those observable objects are data itself, not the connection to database (and in fact views will not "know" where the data is coming from).

I will not go into details on how to make that model happen - there are many other details here (like what's overall architecture of your app), but this is the gist of the idea.

Accessing an SQLite Database in Swift

While you should probably use one of the many SQLite wrappers, if you wanted to know how to call the SQLite library yourself, you would:

Configure your Swift project to handle SQLite C calls. If using Xcode 9 or later, you can simply do:
```
import SQLite3
```

Create/open database.

let fileURL = try! FileManager.default
    .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
    .appendingPathComponent("test.sqlite")

// open database

var db: OpaquePointer?
guard sqlite3_open(fileURL.path, &db) == SQLITE_OK else {
    print("error opening database")
    sqlite3_close(db)
    db = nil
    return
}

Note, I know it seems weird to close the database upon failure to open, but the sqlite3_open documentation makes it explicit that we must do so to avoid leaking memory:

Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

Use sqlite3_exec to perform SQL (e.g. create table).

if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error creating table: \(errmsg)")
}

Use sqlite3_prepare_v2 to prepare SQL with ? placeholder to which we'll bind value.

var statement: OpaquePointer?

if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error preparing insert: \(errmsg)")
}

if sqlite3_bind_text(statement, 1, "foo", -1, SQLITE_TRANSIENT) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure binding foo: \(errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure inserting foo: \(errmsg)")
}

Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:

internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

Reset SQL to insert another value. In this example, I'll insert a NULL value:

if sqlite3_reset(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error resetting prepared statement: \(errmsg)")
}

if sqlite3_bind_null(statement, 1) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure binding null: \(errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure inserting null: \(errmsg)")
}

Finalize prepared statement to recover memory associated with that prepared statement:

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error finalizing prepared statement: \(errmsg)")
}

statement = nil

Prepare new statement for selecting values from table and loop through retrieving the values:

if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error preparing select: \(errmsg)")
}

while sqlite3_step(statement) == SQLITE_ROW {
    let id = sqlite3_column_int64(statement, 0)
    print("id = \(id); ", terminator: "")

    if let cString = sqlite3_column_text(statement, 1) {
        let name = String(cString: cString)
        print("name = \(name)")
    } else {
        print("name not found")
    }
}

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error finalizing prepared statement: \(errmsg)")
}

statement = nil

Close database:

if sqlite3_close(db) != SQLITE_OK {
    print("error closing database")
}

db = nil

For Swift 2 and older versions of Xcode, see previous revisions of this answer.

Establishing a Read-Write connection to a database in SQLite.swift

When database created in your document directory it will be there permanently till user delete the app or you delete that directory.

so read and write connection will occur when you save your sql file in this directory.

as you wanted I make a new example for you that I created recently.

import UIKit
import SQLite

class ViewController: UIViewController {
    
    let customer = Table("Customer")
    let id = Expression<Int64>("CustomerID")
    let name = Expression<String>("CustomerName")
    
    override func viewDidLoad() {
        super.viewDidLoad()
        
        let db = makeDBConnection()
        createTable(db: db)
        insertNewCustomer(db: db)
        fetchDatabase(db: db)
    }
    
    private func makeDBConnection() -> Connection {
        let path = NSSearchPathForDirectoriesInDomains(
            .documentDirectory, .userDomainMask, true
        ).first!
        
        let sourcePath = "\(path)/db.sqlite3"
        
        _ = copyDatabaseIfNeeded(sourcePath: sourcePath)

        return try! Connection(sourcePath)
    }
    
    private func createTable(db: Connection) {
        //Define the columns of the table as expressions
        
        do {
            try db.run(customer.create(block: { table in
                table.column(id, primaryKey: true)
                table.column(name)
            }))
        } catch {
            // This tells you table already created for second time you running this code
        }
    }
    
    private func insertNewCustomer(db: Connection) {
        // This will insert a new customer into your table each time app runs
        let insert = customer.insert(name <- "Reza")
        try! db.run(insert)
    }
    
    private func fetchDatabase(db: Connection) {
        for customer in try! db.prepare(customer) {
            print("id: \(customer[id]), name: \(customer[name])")
        }
    }
    
    func copyDatabaseIfNeeded(sourcePath: String) -> Bool {
        let documents = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
        let destinationPath = documents + "/db.sqlite3"
        let exists = FileManager.default.fileExists(atPath: destinationPath)
        guard !exists else { return false }
        do {
            try FileManager.default.copyItem(atPath: sourcePath, toPath: destinationPath)
            return true
        } catch {
          print("error during file copy: \(error)")
            return false
        }
    }
}

the result is when I run the app each time:

Sample Image

Connect a SQLite Database in Swift

The problem was solve with delete and put sqlite again.

Thanks for the help.

Can I use an sqlite database created for android in IOS

Can I use an sqlite database created for android in IOS?

Yes, an SQLite database is a a file (or potentially 3 files if using Write-Ahead logging). It's simply a matter of copying the file(s).

Typically (i.e. if not specifying otherwise) an SQLite database is stored in data/data/your_package/database/the_database_name

where your_package is as per the App and the_database_name is the file name (may or may not have an extension).
- Note if the database uses Write-ahead logging then 2 other files may exist, these being the_database_name-wal and the_database_name-shm. If they exist and are not empty, they need to also be copied.

However,

and the users must be online to see the update in the database ?

Is a completely different matter though. SQLite is not suited to a client/server situation, as you would have to write all the code to provide this functionality. See Appropriate Uses For SQLite.

Firebase may be an option if you don't mind Google's policies reqgarding information privacy. Otherwise you are likely then looking at something like MySQL/MariaDB.

Use and Access Existing SQLite Database on iOS

SQLite database interaction can be made simple and clean by using FMDB Framework. FMDB is an Objective-C wrapper for the SQLite C interface.

Reference worth reading:

FMDB Framework Docs

Sample Project With Storyboard

Initial Setup

Add the SQLite DB like any other file in your application's bundle then copy the database to documents directory using the following code then use the database from the documents directory

First download the FMDB framework
Extract the framework now copy all the file from src/fmdb folder (not the src/sample or src/extra folders).
Click your project in the left column of Xcode.
Click the main target in the middle column.
Click the “Build Phases” tab.
Expand the arrow next to “Link Binary With Libraries”.
Click the “+” button.
Search for libsqlite3.0.dylib and double click it.

Copying your existing database into app's document in didFinishLaunchingWithOptions: and maintain the database path through out the application.

In your AppDelegate add the following code.

AppDelegate.m

#import "AppDelegate.h"

@implementation AppDelegate

// Application Start
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    
    // Function called to create a copy of the database if needed.
    [self createCopyOfDatabaseIfNeeded];
       
    return YES;
}

#pragma mark - Defined Functions

// Function to Create a writable copy of the bundled default database in the application Documents directory.
- (void)createCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    // Database filename can have extension db/sqlite.
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *appDBPath = [documentsDirectory stringByAppendingPathComponent:@"database-name.sqlite"];
    
    success = [fileManager fileExistsAtPath:appDBPath];
    if (success) {
        return;
    }
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"database-name.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:appDBPath error:&error];
    NSAssert(success, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}

YourViewController.m

Select Query

#import "FMDatabase.h"

- (void)getAllData {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *sqlSelectQuery = @"SELECT * FROM tablename";

    // Query result 
    FMResultSet *resultsWithNameLocation = [database executeQuery:sqlSelectQuery];
    while([resultsWithNameLocation next]) {
        NSString *strID = [NSString stringWithFormat:@"%d",[resultsWithNameLocation intForColumn:@"ID"]];
        NSString *strName = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Name"]];
        NSString *strLoc = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Location"]];

        // loading your data into the array, dictionaries.
        NSLog(@"ID = %d, Name = %@, Location = %@",strID, strName, strLoc);
    }
    [database close];   
}

Insert Query

#import "FMDatabase.h"

- (void)insertData {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];    
    NSString *insertQuery = [NSString stringWithFormat:@"INSERT INTO user VALUES ('%@', %d)", @"Jobin Kurian", 25];
    [database executeUpdate:insertQuery];   
    [database close];
}

Update Query

- (void)updateDate {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"fmdb-sample.sqlite"];
    
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];    
    NSString *insertQuery = [NSString stringWithFormat:@"UPDATE users SET age = '%@' WHERE username = '%@'", @"23", @"colin" ];
    [database executeUpdate:insertQuery];
    [database close];
}

Delete Query

#import "FMDatabase.h"

- (void)deleteData {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *deleteQuery = @"DELETE FROM user WHERE age = 25";
    [database executeUpdate:deleteQuery];   
    [database close];
}

Addition Functionality

Getting the row count

Make sure to include the FMDatabaseAdditions.h file to use intForQuery:.

#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"

- (void)gettingRowCount {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSUInteger count = [database intForQuery:@"SELECT COUNT(field_name) FROM table_name"];
    [database close];
}