How do I create multiple tables in Swift using SQLite database?

Question

So I was able to do a tutorial to create one table but when I try to create another one, it doesn't show up in the DB Browser for SQLite which makes me think that it is not created.

Is there a way to make multiple tables within the same DB Management class?

    // Creating DB connection
    db = try Connection("/blahblahblah/harmonyMoodDB.sqlite3")
    
    // Creating table object
    meds = Table("medications")
    
    // Create instances of each column
    medID = Expression<Int64>("id")
    name = Expression<String>("name")
    dosage = Expression<Int64>("dosage")

    
    if (!UserDefaults.standard.bool(forKey: "is_db_created")) {
        
        // If not, then create the table
        try db.run(meds.create { (t) in
            t.column(medID, primaryKey: true)
            t.column(name)
            t.column(dosage)

    })
        
        
    // Set the value to true, so it will not attempt to create the table again
    UserDefaults.standard.set(true, forKey: "is_db_created")
}
    
    // Creating table object
    users = Table("users")
    
    // Create instances of each column
    userID = Expression<Int64>("userID")
    userName = Expression<String>("userName")


    
    if (!UserDefaults.standard.bool(forKey: "is_db_created")) {
        
        // If not, then create the table
        try db.run(users.create { (t) in
            t.column(userID, primaryKey: true)
            t.column(userName)

    })
    // Set the value to true, so it will not attempt to create the table again
    UserDefaults.standard.set(true, forKey: "is_db_created")
}
 
}
catch {
    // Show error message (if any)
    print(error.localizedDescription)
}
 
} // End of init

Where am I going wrong here?

It seems that you set is_db_created to true before creating the 2nd table. So, the code will not be executed. — Ptit Xav, Mar 04 '21 at 08:48
Ah so I would need to set it to false and then set the second statement I made after the second table to true? — Mauleen Ndlovu, Mar 04 '21 at 17:03
The if on is_db_create should start before creation of first table and end after creation of the second table . Then is_db_create created and set to true only when second table created. — Ptit Xav, Mar 04 '21 at 17:37
Can you comment code on how that would look? I am having a hard time figuring it out still. — Mauleen Ndlovu, Mar 07 '21 at 21:23

score 0 · Answer 1 · answered Mar 15 '21 at 19:41

As mentioned in the comments above, the issue is with storing is_db_created in the user defaults. Personally, I wouldn't use user defaults for this scenario anyway - if you end up using it anyway, make sure you only store it, if your complete initializing was successful! (Currently if your statement fails you still store that the db was created.

My preferred solution would be

// Creating table object
meds = Table("medications")

// Create instances of each column
medID = Expression<Int64>("id")
name = Expression<String>("name")
dosage = Expression<Int64>("dosage")

// CREATE TABLE "meds" IF NOT EXISTS -- ...
try db.run(meds.create(ifNotExists: true) { t in /* ... */ })

// same for users
/* ... */

This has also the advantage, that your code would be robust against "externally" created tables or db schemas etc. For more details see also doc

How do I create multiple tables in Swift using SQLite database?

1 Answers1