Why saving data to SQLite database doesn't work with Swift 2?

Question

I'm using SQLite.swift and I'm using these code from demo of SQLite.swift.

import UIKit
import SQLite

class ViewController: UIViewController {
    override func viewDidLoad() {
        super.viewDidLoad()
        let db = try! Connection()
        let users = Table("users")
        let id = Expression<Int64>("id")
        let email = Expression<String>("email")
        let name = Expression<String?>("name")
        try! db.run(users.create { t in
            t.column(id, primaryKey: true)
            t.column(email, unique: true, check: email.like("%@%"))
            t.column(name)
        })
        try! db.run(users.insert(email <- "alice@mac.com"))
        for user in db.prepare(users) {
            print("id: \(user[id]), email: \(user[email])")
        }
    }
}

The first time I ran it, the output was:

("SELECT * FROM \"users\"", [])
id: 1, email: alice@mac.com

Then I removed the line 17 ( try! db.run(users.insert(email <- "alice@mac.com")) ) and run again, debugger output changed to:

("SELECT * FROM \"users\"", [])

Looks like alice@mac.com didn't save to database. So where I did it wrong? Or how to save it into SQLite database?

P.S. I'm using Xcode 7 beta 5, Swift 2.0

Please check the database. The record is available in database? — Ashish Kakkad, Aug 20 '15 at 03:56
You have given path to store the database at document's directory path right? @David ? — Ashish Kakkad, Aug 20 '15 at 03:59
Just print that path in your console. and go to that database by using finder. open that database in to sqlite browser and check the data is available or not. — Ashish Kakkad, Aug 20 '15 at 04:00
http://stackoverflow.com/questions/24133022/ios-8-store-sqlite-file-location-core-data Just use println() or print() in swift2. — Ashish Kakkad, Aug 20 '15 at 04:02
and close the db after inserting the data. db.close() should be function with swift — Ashish Kakkad, Aug 20 '15 at 04:04
I found that dir path using that link @iAshish, but I didn't find that database file — David, Aug 20 '15 at 04:11

Martin R · Answer 1 · 2015-08-20T05:37:13.130

let db = try! Connection()

creates an in-memory database, as you can see from the API documentation:

/// Initializes a new SQLite connection.
///
/// - Parameters:
///
///   - location: The location of the database. Creates a new database if it
///     doesn’t already exist (unless in read-only mode).
///
///     Default: `.InMemory`.
///
///   - readonly: Whether or not to open the database in a read-only state.
///
///     Default: `false`.
///
/// - Returns: A new database connection.
public init(_ location: Location = .InMemory, readonly: Bool = false) throws

The data is not persisted to a file, and the database is always initially empty.

For a persistent database, use

/// Initializes a new connection to a database.
///
/// - Parameters:
///
///   - filename: The location of the database. Creates a new database if
///     it doesn’t already exist (unless in read-only mode).
///
///   - readonly: Whether or not to open the database in a read-only state.
///
///     Default: `false`.
///
/// - Throws: `Result.Error` iff a connection cannot be established.
///
/// - Returns: A new database connection.
public convenience init(_ filename: String, readonly: Bool = false) throws

instead, for example

// Compute file path for database in Documents directory:
let docsDir = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last!
let dbPath = (docsDir as NSString).stringByAppendingPathComponent("database.sqlite")

do {
    let db = try Connection(dbPath)
    // ... use database

} catch (error) {
    // Could not create or open database, print error
}

score 0 · Answer 2 · answered Aug 20 '15 at 04:55

The viewDidLoad first creates the table, then inserts.

I don't know the behavior of the table create function when the table already exists, but if it does not throw any errors on you, it probably recreates the table again. If that's the case, after calling the viewDidLoad with the insert commented out, you probably end up with a fresh new blank table.

Try changing the logic making sure that the table is not recreated every time.

Kingxlayer · Answer 3 · 2015-10-08T16:39:57.223

You could have something like this (Obviously everybody else could improve the code):

Create the model of your table:

class TUsers {
static let TABLE_NAME : String = “users”
static let ID = Expression<Int64>("id")
static let EMAIL = Expression<String>(“email”)
static let NAME = Expression<String>(“name”)

class func addUser(email : String!, name : String!) -> Bool{

    let users = Table(TABLE_NAME)

    do {

        try DBHelper.instance.db.run(users.insert(
            TUsers.EMAIL <- email,
            TUsers.NAME <- name
        ))

        return true
    } catch {
        print("Insert failed")
        return false
    }
}

class func getAllUsers() -> [User] { // you must create the model user
    let users = Table(TUsers.TABLE_NAME)

    let query = users.select(*)
        //.filter(TUsers.EMAIL == “whatever@pomberobota.com”)

    var listOfUsers : [Users] = []

    for user in DBHelper.instance.db.prepare(query) {
        // Do whatever you need to instantiate the model User


        listOfUsers.append(User(email: users[TUsers.EMAIL], name: users[TUsers.NAME])

    }

    return listOfUsers
}
}

Create a database helper

class DBHelper {

static let instance = DBHelper()

var db : Connection

init() {
    do {
        self.db = try Connection("\(Util.getDBPath())/db.sqlite3")
        createTablesIfNotExists()
    } catch {
        print("Could not create a connection to database")
    }
}

func createTablesIfNotExists() {

    let users = Table(TUsers.TABLE_NAME)
    do {
        try db.run(logs.create(ifNotExists: true) { t in
                t.column(TUsers.ID, primaryKey: true)
                t.column(TUsers.EMAIL)
                t.column(TUsers.NAME)
        })
    } catch {
        print(“Could not create table users”)
    }
}
}

And finally (here you don't need to import SQLite).

class ViewController: UIViewController {
override func viewDidLoad() {
    super.viewDidLoad()

   TUsers.addUser(“user@ndetavysho.com”, “John Capuchon”)

    let users = TUsers.getAllUsers()

    for user in users {
        // do something
    }
}

}

Why saving data to SQLite database doesn't work with Swift 2?

3 Answers3