Is there a way to dynamically modify tables? Inste...

# exposed

[JP]

Is there a way to dynamically modify tables? Instead of adding a new column in the table definition, can I do something like

transaction{
   Table.addColumn(...)
}

?

[JP]

For context, I'm trying to natively migrate my database. Currently, if I change the table definition, it won't matter because exposed will detect the table already exists and won't do anything. But I can't drop the table without losing all of the data. I know exposed doesn't have any define way of migrating databases, but I'm hoping it maybe has some way to change schemas like this?

[redenergy]

If my memory serves me well, exposed actually allows to create missing columns even if table already exists. It can just compare list of columns from database with registered columns in Table definition in code and execute ALTER statements (depending on the vendor) for columns which are not present in database. Wont it be sufficient?

[JP]

Could you share an example? I tried adding the column to the table definition, but it didn't seem to modify the underlying schema

[JP]

I also didn't access it anywhere, so it could be that it's lazying loading the column (not until I reference it), but I doubt it. That seems too dangerous or over engineered to be the case (I would hope)

[redenergy]

Simple example

Suppose I have the following program with table definition:

import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.StdOutSqlLogger
import org.jetbrains.exposed.sql.addLogger
import org.jetbrains.exposed.sql.transactions.DEFAULT_REPETITION_ATTEMPTS
import org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManager
import org.jetbrains.exposed.sql.transactions.transaction
import java.sql.Connection

object SampleAddressTable: IntIdTable("sample") {

    val name = varchar("name", 64)
    val address = varchar("address", 128)

}

fun main(args: Array<String>) {
    Database.connect("jdbc:sqlite:sample.db",
        manager = { ThreadLocalTransactionManager(it, Connection.TRANSACTION_SERIALIZABLE, DEFAULT_REPETITION_ATTEMPTS) })
    transaction {
        addLogger(StdOutSqlLogger)
        SchemaUtils.createMissingTablesAndColumns(SampleAddressTable)
    }
}

Upon execution it outputs (notice db requests logging):

[2020-11-17, 10:12:21] [INFO] Preparing create tables statements took 18ms
SQL: CREATE TABLE IF NOT EXISTS sample (id INTEGER PRIMARY KEY AUTOINCREMENT, "name" VARCHAR(64) NOT NULL, address VARCHAR(128) NOT NULL)
[2020-11-17, 10:12:21] [INFO] Executing create tables statements took 17ms
[2020-11-17, 10:12:21] [INFO] Extracting table columns took 2ms
[2020-11-17, 10:12:21] [INFO] Preparing alter table statements took 3ms
[2020-11-17, 10:12:21] [INFO] Executing alter table statements took 0ms
[2020-11-17, 10:12:21] [INFO] Checking mapping consistence took 6ms

and creates specified table in database (see attached image)

Now I'll add another column and table definition will look like this:

object SampleAddressTable: IntIdTable("sample") {

    val name = varchar("name", 64)
    val address = varchar("address", 128)
    val description = varchar("description", 128).nullable()

}

When program executed again and SchemaUtils.createMissingTablesAndColumns is called Exposed will create our new column:

[2020-11-17, 10:15:33] [INFO] Preparing create tables statements took 10ms
[2020-11-17, 10:15:33] [INFO] Executing create tables statements took 0ms
[2020-11-17, 10:15:33] [INFO] Extracting table columns took 2ms
[2020-11-17, 10:15:33] [INFO] Preparing alter table statements took 9ms
SQL: ALTER TABLE sample ADD description VARCHAR(128) NULL
[2020-11-17, 10:15:33] [INFO] Executing alter table statements took 13ms
[2020-11-17, 10:15:33] [INFO] Checking mapping consistence took 6ms

[JP]

Thank you @redenergy ! That's a great example. I couldn't seem to get it working the same with my project, but Tapac had a great answer that seemed to fix my situation.