Database Migrations

[paulkagiri]

Is there plan to have database migrations in this great tool?

It can either be inbuilt or integrate with a tool like Flyway.

[Tapac]

I guess it's a little bit out of the scope of Exposed as orm/dao framework. We have functionality to automatically add columns and indices (and log differences which can't be resolved).

If you have a vision how we can integrate Flyaway (sorry, but I never used it) please share or PR.

[fyn-dev]

Yes, I'm agree database migration tool is really important to have. I have developing notification service which is separate from website (not jvm and kotlin) and I want to use Exposed with existing database, so how I suppose to mange my model? Create classes manually? Not efficient. We need such a tool which is automatically generate table classes code from existing database.

[paulkagiri]

@Tapac This is what I had in mind
I was thinking of creating a database migrations tool such as https://bitbucket.org/zzzeek/alembic/src/master/

The basic version would do the following given the list of models to be managed, while at the same time storing the current version identifier in the database:

• Initially just generate code to create the models and store the revision identifier (someIdentifier) in the database, something like:

   val downRevision = null
  
   fun upgrade(models: Array<Table>) {
       SchemaUtils.create(*models)
   }
  
   fun downgrade(models: Array<Table>) {
       SchemaUtils.drop(*models)
   }
  

• Every time one changes their models, one would run a command that would check which tables/indexes/columns have been added and which ones have been removed and then form a migration file, also store the revision identifier (someOtherIdentifier) in the database, like:

   val revision = "someOtherIdentifier"
   val downRevision = "someIdentifier"
  
   fun upgrade(models: Array<Table>) {
       //Alter tables and add required columns - maybe using SchemaUtils.createMissingTablesAndColumns(*models)
   }
  
   fun downgrade(models: Array<Table>) {
       //Alter tables and drop removed columns
   }
  

This way, at some point in future if one desires to go to a particular database state, one can do so by just providing an identifier of the revision or by calling downgrade until one gets to the desired state

[silviorp]

I agree that database migration and version control tools is very important, especially when the java options are really old-school style, needing you to write SQL, XML, etc. If you guys were thinking on developing something like Django migrations inside Exposed, you can count on me for any help.

[Kabbura]

@shrpereira I am really interested in developing something like this. I am currently working with Jooq and Flyway. It is working, but as you said it is old-school. The only thing stopping me from using Exposed in production is the lack of db migration tool.
Can we start drafting a plan for how the migration tool will work and how to integrate it with Exposed?

[johanvergeer]

Even though I really would not know where to begin (except for checking out the Django library), I would love to help out with this. Sounds awesome and a great addition to the library. Please let me know if I can help.

ps. I am a former Django dev.

[KenjiOhtsuka]

I've been developing such a migration tool.

https://github.com/KenjiOhtsuka/harmonica

it doesn't use Exposed, but I'd like to.
Exposed can be used in its migration.
I'm stuck in connection handling ...

[MRezaNasirloo]

If you guys are going to develop such a tool, please take a look at other frameworks to see how other people have implemented this, a good example is Laravel Migration.
Java is far behind in database migration and the best tool we got is Flyway a SQL based migration tool, Who would want to write SQL functions when all of those functions are already written. Why wouldn't we use our Repositories in them?

@KenjiOhtsuka keep up the good work, it's really cool.

other JVM based migration tools
https://github.com/nafg/slick-migration-api
https://github.com/padarom/java-migrations

[RobertMenke]

I'd much prefer the way that Android does migrations with Rooms to Laravel's migrations. Laravel relies upon file names following a specific format so that they can iterate over migrations files, derive class names from the file names, and then dynamically call methods they expect to be there with no interface contract (I had to basically re-implement Laravel migrations on a project using Eloquent without Laravel).

Rooms, on the other hand, is very safe, highly testable, and extremely easy to follow and implement.
https://developer.android.com/training/data-storage/room/migrating-db-versions

[raderio]

orangy/squash#9

[IP14Y3RI]

@Tapac , Are there any updates on this topic?

Also,

How can I alter-tables?

I have defined a table

object Users : IntIdTable("User") {
    val email: Column<String> = Users.varchar("Email", 100).uniqueIndex()
    val password: Column<String?> = Users.varchar("Password", 1000).nullable()
}

Right know I am using @KenjiOhtsuka 's Harmonica to write a migration file. Now I want to alter the table User, for example by adding a few columns and dropping some others. How can I achieve this?

[Tapac]

@Wnzebkhan , there is no updates or plans to implement it in the near future, only if someone will provide a PR.
At the moment you can use SchemaUtils.createMissingTablesAndColumns(), please read the docs before using it.

[Maximilian73]

I'm Symfony developer and I'd like to write in Kotlin/Java. But realy, php frameworks has perfect migration scheme. For Example, DoctrineMigration. Its very reliable and convenient tool for migrations. And we have DoctrineFixture for data filling. And we have Doctrine - it UnitOfWork/DataMapper ORM like Hibernate. Java/Kotlin - where are you? Unfortunately.

[Tapac]

@Maximilian73 , there are DB migration tools in Java too, e.g. https://flywaydb.org/ , https://www.liquibase.org/ . But Exposed doesn't cover that part of a development process only DSL/ORM part.

[Maximilian73]

I know about flyway and liquibase, but even liquibase (on my opinion) much weaker than doctrine migrations. And I need in EntityManager for my project. That's why I will have to use Jooby and Hibernate... And probably Liquibase and Guice...

[CharlieTap]

@Tapac I might create this and PR it, from a functionality perspective I suppose we would simply need:

• Somewhere standardised to put migrations,
• Migrations that state how they are both applied and unapplied
• Some method of tracking which migrations have been applied and order to them
• Migrations in the form of Exposed DSL and raw SQL?

Any other ideas?

[Tapac]

@CharlieTap , it would be great if you could make a PR with a migration feature.
The main problem is how to track DSL versioning and build diff between versions.

[Kabbura]

@CharlieTap , exactly as @Tapac said " The main problem is how to track DSL versioning and build diff between versions." Currently, exposed has SchemaUtils.createMissingTablesAndColumns() function and it finds the diff (not perfectly) between the schema automatically. I don't think there are functions to create tables, columns, constraints manually.
I am not sure if we should let the diff and versioning happen automatically as createMissingTablesAndColumns does, or if it is better to have explicit functions to create migrations manually.
I am in favor of the second option.

[CharlieTap]

@Tapac @Kabbura By versioning I guess you mean, if a new version of the library comes out then its important old migrations work? But I'm not sure how that's different from any piece of code?

My current plan is to:

• Make a standard folder structure, and standardised file names for migrations (Unfortunately we have no CLI so this would be done manually)
• Make a DB table to track migrations
• Every time a migration is ran, create a record
• Create an API for managing migrations, both applying and unapplying
• Make a distinction between structural migrations and data migrations (Table Seeders)

Some clever ideas for potential future extensions:

• Store hashes of data structures for tables and the schema as a whole, use this to diff new versions of the schema and potentially auto create migrations using the delta. Not sure how far we could go with this but would certainly be possible for minor changes, addition or removal of columns/indexs/constraints etc

[Aditya94A]

Any updates on this? What's the recommended way to handle migrations with Exposed?

[CharlieTap]

I got some way into a POC but ultimately had to stop due to other commitments :(
If I have time in the new year I'll try to pick it up again

[Aditya94A]

Any thoughts @Tapac? How do you recommend doing migrations? This is a critical feature for putting Exposed in production.

[etca2z]

This blog post demonstrates how to use Flyway as migration tool with Exposed. Basically you use Flyway for creating tables and migrations, don't use the one by Exposed. No integration, you have to manually make sure the Exposed table definition matches the one created by Flyway. FYI

[Aditya94A]

Hey @yannikyeo which blog post are you talking about?

[etca2z]

Hey @yannikyeo which blog post are you talking about?

Sorry here's the link https://www.thebookofjoel.com/kotlin-ktor-exposed-postgres

[andreas-mausch]

I couldn't get harmonica running without gradle (I use maven), so I started out to develop a very basic migration tool just for my personal needs.
Maybe it is useful to somebody else.

https://gitlab.com/andreas-mausch/exposed-migrations

[cha55son]

Just dropping a note here as I was discussing this with some co-workers. What may be a better use of time is to have a compile step that generates table classes based on liquibase/flyway changesets. This is done with other ORMs via maven/gradle plugins.

I agree with the sentiment that having to manually line up your migration schema and exposed is error prone. But adding migration functionality to exposed is major scope creep. I'd rather have some glue plugins to bridge the gap between a mature migration library and exposed.

[Tapac]

@cha55son, we are working on gradle-plugin which will at least generate Table/Entity classes from an existing database scheme and I thinkg it will be possible to run it against SQL scripts.

[DuchGhast]

@Tapac Is there any update on the state of the plugin?

[Tapac]

@DuchGhast , yes, the 0.1 version is almost ready to go public. Need to fix documentation and a plugin publication flow.

I will share the project link in that issue.

[mbolotov]

I will share the project link in that issue.

@Tapac do you mean this plugin?
https://github.com/JetBrains/exposed-intellij-plugin

[milgner]

Conceptually related question: since Exposed does not maintain the schema, why are there so many functions to describe it? How does it actually benefit from knowing that a column is indexed by an index of a certain name for example?
When I saw the powerful DSL to describe tables, I got the initial impression that this information about the schema would also be used to maintain the schema.

[solidogen]

So how exactly do I make sure my flyway migrations match Exposed mappings?

Hibernate has
spring.jpa.hibernate.ddl-auto=validate
for this if I recall correctly.

I'm in for a workaround like writing insert+fetch unit tests to make sure columns are in sync, but maybe someone has a better idea

[solidogen]

This works for me as a workaround for schema validation:
https://github.com/Solidogen/weight-reductor-mpp/blob/develop/backend/src/test/java/schema/SchemaValidationTest.kt

I run this test before each application run (I run backend app by shell script) and as a pre-commit hook, so I can never commit non-matching schema, if my flyway migrations do not match exposed tables.

Just make sure to create a throwaway database when in unit test mode, as I did.

[ESchouten]

@Tapac is the plugin still coming?

[dotCipher]

@Tapac also interested in the update here, would love to use Exposed but db migrations are a hard requirement for me

[bettercoding-dev]

I have written a tutorial on how to set up Exposed with Flyway and how to use the Gradle plugin to generate Exposed table definitions: https://bettercoding.dev/kotlin/tutorial-exposed-generation-flyway/

Hope this helps some of you :)

[victororlyk]

maybe for someone this repo will be of some use https://github.com/raharrison/kotlin-ktor-exposed-starter

[AriaMoradi]

I couldn't get harmonica running without gradle (I use maven), so I started out to develop a very basic migration tool just for my personal needs. Maybe it is useful to somebody else.

https://gitlab.com/andreas-mausch/exposed-migrations

I added some features and updated and improved the code a little bit here: https://github.com/Suwayomi/exposed-migrations

The library is used in my Javalin web app Tachidesk-Server which you can use for reference:
(The entry point for migrations is databaseUp)
https://github.com/Suwayomi/Tachidesk-Server/tree/master/server/src/main/kotlin/suwayomi/tachidesk/server/database

My opinion might be biased but this library is much more flexible and lighter than harmonica as it doesn't use Exposed internals.

[paulkagiri]

One of the ways that has worked for me is using a combination of Exposed and Flyway by utilizing the SchemaUtils.statementsRequiredToActualizeScheme to inform on the changes that need to be done to the schema to update it such as this - note that this does not work for dropped columns:

// database connection

// configure Flyway and run migrations - these are just sql files saved under resources for instance `resources/db/migration/V1_1_0__database_initialization.sql`
val flyway = Flyway.configure().dataSource(dataSource).load()
flyway.migrate()

// check if there any new updates - this does not work for dropped columns
transaction {
	// print the new table updates if debug mode
	if (debugMode) {
                val tablesList = arrayOf(Users) // the different tables in use on the system
		val changes = SchemaUtils.statementsRequiredToActualizeScheme(*tablesList).joinToString("\n")
		logger.info(if (changes.isNotBlank()) "Database schema needs to be updated with the following changes:\n$changes" else "Database schema is up to date")
	}
}

[landsman]

Yes! I would love to see a light-weight migration tool build-in.

I would love to write migrations in Kotlin. Please make it possible without workarounds and 3rd-party libs.