How do I check if a column exist before adding it

Question

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?

Answer 1

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))
}

Answer 2

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.