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:

  al downRevision = null
  
  un upgrade(models: Array<Table>) {
     SchemaUtils.create(*models)
  
  
  un 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:

  al revision = "someOtherIdentifier"
  al downRevision = "someIdentifier"
  
  un upgrade(models: Array<Table>) {
     //Alter tables and add required columns - maybe using SchemaUtils.createMissingTablesAndColumns(*models)
  
  
  un 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]

https://github.com/orangy/squash/issues/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.