8

I have a db, and I want to add a column to it if it doesn't exist. How do I do that with sqlite.swift API?

user2496373
  • 105
  • 2
  • 8

2 Answers2

8

Generally you'll want to have a migration path if you're adding new columns to existing tables. You can use the userVersion attribute to manage versions of your database schema:

if db.userVersion < 1 {

    db.create(table: users) { t in
        t.column(id, primaryKey: true)
        t.column(email, unique: true)
    }

    db.userVersion = 1
}

if db.userVersion < 2 {

    db.alter(table: users, add: name)
    db.alter(table: users, add: age)

    db.userVersion = 2
}

You can also, as Max suggested, use ifNotExists: at the create(table:…) level:

 db.create(table: users, ifNotExists: true) { t in
    t.column(id, primaryKey: true)
    t.column(email, unique: true)
 }

But for adding new columns, you have to parse an unwieldy PRAGMA statement:

 let tableInfo = Array(db.prepare("PRAGMA table_info(users)"))
 if tableInfo.filter { col in col[1] == "name" } == nil {
    db.alter(table: users, add: name)
 }
 if tableInfo.filter { col in col[1] == "age" } == nil {
    db.alter(table: users, add: age)
 }

Not nearly as readable (or recommended), but if you're dealing with a legacy database, maybe necessary.

Be sure to read the ALTER TABLE documentation for more complicated alterations.

stephencelis
  • 4,954
  • 2
  • 29
  • 22
  • I know this is a year ago, but how do I get userVersion from sqlite.swift? I'm trying to add columns to tables that may or may not already exists but I am not too sure how to do that.. How to check if table exist? because using `table.create(ifNotExists: true)` I can't do a else exist, check if column exists. Also how do I check if column exist? – stephw Apr 01 '16 at 09:58
  • @stephencelis Is it possible to add a `addColumn(:ifExists:)` function like createTable does? – CodeBrew Oct 31 '20 at 15:02
3

The correct way for swift 2.0 is following:

let tableInfo = Array(db.prepare("PRAGMA table_info(users)"))

let foundColumn = tableInfo.filter {
    col in col[1] as! String == "name"
}

if(foundColumn.count == 0){
    try! db.run(users.addColumn(name))
}
letsdev-cwack
  • 99
  • 4
  • 10