How to bundle a pre populated database in swift

Question

I know there are many related posts but I could notice that none of them explain the way to do it, most says to look the documentation, I looked already but seems to have a lack of information on bringing a pre populated database to Swift

I have an old database .db with more than 60k lines three columns that I want to bring to my swift SQLite app using FMDB wrapper, the app is working in the iPhone, I tried to just drag the contacts.db to the app but I can not access it. The app when run in the device always start a new database.

I copied the database to the supporting files folder and tried in the app folder as well, could not access from neither

Is there anybody who alredy did that and willing to show me how to do it?

In summary, what I am looking is to insert my contacts.db into the app (bundle) so I can access in the device not in the simulator. I don't need to add delete or edit the contact.db in the device, the database is loaded with all information needed, the user will only for search and be able to display results.

class ViewController: UIViewController {

@IBOutlet weak var name: UITextField!
@IBOutlet weak var address: UITextField!

var databasePath = NSString()    

override func viewDidLoad() {
    super.viewDidLoad()

    if let resourceUrl = NSBundle.mainBundle().URLForResource("contacts", withExtension: "db") {
        if NSFileManager.defaultManager().fileExistsAtPath(resourceUrl.path!) 
    }
    let filemgr = NSFileManager.defaultManager()
    let dirPaths =
    NSSearchPathForDirectoriesInDomains(.DocumentDirectory,
            .UserDomainMask, true)

    let docsDir = dirPaths[0] as! String

    databasePath = docsDir.stringByAppendingPathComponent(
                    "contacts.db")

    if !filemgr.fileExistsAtPath(databasePath as String) {

        let contactDB = FMDatabase(path: databasePath as String)

        if contactDB == nil {
            println("Error: \(contactDB.lastErrorMessage())")
        }

        if contactDB.open() {
            let sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)"
            if !contactDB.executeStatements(sql_stmt) {
                println("Error: \(contactDB.lastErrorMessage())")
            }
            contactDB.close()
        } else {
            println("Error: \(contactDB.lastErrorMessage())")
        }
    }

}

Answer 1

Swift 2 Example

A few assumptions:

1. You have added libsqlite3.tbd to your project correctly.
2. You have added an sqlite database to your project correctly.
3. You have copied the required FMDB files to your project correctly.
4. You have a ViewController called ViewController.swift

In Xcode open your ViewController.swift file and find the following code:

 override func viewDidLoad() {
     super.viewDidLoad()
     // Do any additional setup after loading the view, typically from a nib.

Add the following code below the comment and please remember to change the name of the database in lines 4 & 5.

     // Start of Database copy from Bundle to App Document Directory
     let fileManager = NSFileManager.defaultManager()
     let documentsPath = NSURL(fileURLWithPath: NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0])
     let destinationSqliteURL = documentsPath.URLByAppendingPathComponent("sqlite.db")
     let sourceSqliteURL = NSBundle.mainBundle().URLForResource("sqlite", withExtension: "db")

     if !fileManager.fileExistsAtPath(destinationSqliteURL.path!) {
         // var error:NSError? = nil
         do {
             try fileManager.copyItemAtURL(sourceSqliteURL!, toURL: destinationSqliteURL)
             print("Copied")
             print(destinationSqliteURL.path)
         } catch let error as NSError {
             print("Unable to create database \(error.debugDescription)")
         }
     }


     // Let's print the path to the database on your Mac
     // so you can go look for the database in the Finder.
     print(documentsPath)

     let db = FMDatabase(path: destinationSqliteURL.path)

    // Let's open the database
    if !db.open() {
        print("Unable to open database")
        return
    }

    // Let's run some SQL from the FMDB documents to make sure
    // everything is working as expected.  
    if !db.executeUpdate("create table test(x text, y text, z text)", withArgumentsInArray: nil) {
        print("create table failed: \(db.lastErrorMessage())")
    }

    if !db.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", withArgumentsInArray: ["a", "b", "c"]) {
        print("insert 1 table failed: \(db.lastErrorMessage())")
    }

    if !db.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", withArgumentsInArray: ["e", "f", "g"]) {
        print("insert 2 table failed: \(db.lastErrorMessage())")
    }

    if let rs = db.executeQuery("select x, y, z from test", withArgumentsInArray: nil) {
        while rs.next() {
            let x = rs.stringForColumn("x")
            let y = rs.stringForColumn("y")
            let z = rs.stringForColumn("z")
            print("x = \(x); y = \(y); z = \(z)")
        }
    } else {
        print("select failed: \(db.lastErrorMessage())")
    }

    db.close()

You should be able to run the project now and have your database copied.

Previous answer in case it helps someone

The documentation for FMDB is pretty helpful on this topic. Using the example there, the code below should work assuming the following things:

1. You are using Swift 1.2 or later.
2. You have properly added FMDB to your project.
3. An SQLite database called contacts.db was properly added into the project.

4. In Build Phases, then Link Binary With Libraries that you have added libsqlite3.dylib.

   //
   //  ViewController.swift
   //
   
   import UIKit
   
   class ViewController: UIViewController {
   
       @IBOutlet weak var name: UITextField!
       @IBOutlet weak var address: UITextField!
   
       override func viewDidLoad() {
           super.viewDidLoad()
           // Do any additional setup after loading the view, typically from a nib.
           let filemgr = NSFileManager.defaultManager()
           let documentsFolder = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0] as! String
           let path = documentsFolder.stringByAppendingPathComponent("contacts.db")
           let database = FMDatabase(path: path)
   
           if !database.open() {
               println("Unable to open database")
               return
           }
   
           if !database.executeUpdate("create table test(x text, y text, z text)", withArgumentsInArray: nil) {
               println("create table failed: \(database.lastErrorMessage())")
           }
   
           if !database.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", withArgumentsInArray: ["a", "b", "c"]) {
               println("insert 1 table failed: \(database.lastErrorMessage())")
           }
   
           if !database.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", withArgumentsInArray: ["e", "f", "g"]) {
               println("insert 2 table failed: \(database.lastErrorMessage())")
           }
   
           if let rs = database.executeQuery("select x, y, z from test", withArgumentsInArray: nil) {
               while rs.next() {
                   let x = rs.stringForColumn("x")
                   let y = rs.stringForColumn("y")
                   let z = rs.stringForColumn("z")
                   println("x = \(x); y = \(y); z = \(z)")
               }
           } else {
               println("select failed: \(database.lastErrorMessage())")
           }
   
           database.close()
   
       }
   
       override func didReceiveMemoryWarning() {
           super.didReceiveMemoryWarning()
           // Dispose of any resources that can be recreated.
       }
   }
   

To test, just use the Build and Run button and you should see run the simulator and the following results in Xcode's console:

x = a; y = b; z = c
x = e; y = f; z = g 

To show the Console, type Shift + CMD +R or go to View -> Debug Area -> Activate Console