Analytics

Sunday, August 28, 2011

Database Migration in Grails

This blog covers the Grails Database Migration Plugin, the official plugin created by Spring Source and based on the popular Liquibase framework. Examples will demonstrate how database migrations can be controlled, managed, and executed. 

Database migrations are an important facet of web development. Preserving existing data while seamlessly adding new functionality and tables is critical when making incremental database changes to production applications. Without a tool to manage database migrations, teams rely on manual sql, error prone communication processes, and costly risk management to implement solutions.

What is a Database Migration

First let's define database migration. Simply put, database migrations are changes to a database that is already running in production, and the customer wants to retain the data for future releases. If this is not the case, then it is sufficient to not really consider it a database migration, and thus, you can rely on GORMs dbCreate configuration.

GORM dbCreate has Problems

If you haven't already realized it, hibernate's dbCreate feature has its problems. It does not support database migrations robustly. Some basic database changes work, such as adding a completely new table or adding new columns, but that is it. Briefly, here are some problems with dbCreate. I will leave in depth research up to the reader:
  • Does not delete columns
  • No conversion of datatypes
  • No data migration
  • Cannot change a constraint on a column
  • Cannot add a column with not-null constraint on existing table
    • Rows read and updated will not be able to update ever again
Why use a tool for data migrations?

Manually managing database migration is a flat out nightmare. Believe me, I have lived it and I realize it fully now that a tool is being used to manage migrations. Coordination between developers becomes huge overhead, as well as the communication between the developers and the DBA. Manually generating sql is a pain, mired in the fact that you are often creating the same type of sql over and over again. In addition, some of the sql will become messy and cryptic and thus hard to understand over time. Then you have the burden of ordering separate sql files for execution, or the alternate solution of creating some very large sql file that is impossible to understand.

The Case for a Controlled Database Migration plan

Adopting a tool adds structure and process to managing database changes. Teams can rely on a tool for communication of changes, rather then email. We also need to treat databases with version control. This is the same respect we give to code, and databases deserve it as well. Most importantly, database migrations enable agility and meeting customer needs, and we want to perform them well.

Examples

In this blog, we will run through three sample migrations. The sample domain objects used are Person and Address. We will start off with a Person object and eventually end up with a Person object that has many Addresses.
  • Migration 1 - make column nullable
  • Migration 2 – add address columns
  • Migration 3 – Add Address Table  
Installation

Let's assume we have a grails app up and running. To install the plugin, execute

grails install-plugin database-migration 
Let's also assume we have a basic Person class with a few attributes. The app is already in production and has data in it. We now would like to migrate.

Migration 1 - make column nullable

















The change we want to make is to make age nullable. But before we make the change, we need to do some initialization for liquibase.

Initialization Step
  1. Remove the dbCreate attribute from DataSource.groovy. Hibernate will no longer handle db changes.
  2. Create a baseline changeset.
    1. grails dbm-generate-gorm-changelog changelog.groovy
  3. Establish the baseline in persistence. This will store the version history of the database in a table. This is a one time setup.
    1. grails dbm-changelog-sync
Make Domain Change

Now we can proceed with domain object changes. The Person class should be edited to become this:

package dbmigration.poc
class Person {
    String name
    Integer age
    static constraints = {
      name()
      age(nullable: true)      
    }
} 

The next step is to create a changeset file and append it to changelog.groovy. The '--add' parameter appends an include statement into the changelog.groovy

grails dbm-gorm-diff change-age-constraint-to-nullable.groovy --add

Configure Automigration on startup

Up to this point, we have only created DSL files with migration scripts. We have not actually done any migrations. We have the option to execute data migration on startup. This is very convenient as no DBA has to be involved when deploying war. The plugin will automatically execute the migration script. In order to do so, we must configure these properties in Config.groovy

grails.plugin.databasemigration.updateOnStart = true
grails.plugin.databasemigration.updateOnStartFileNames = ['changelog.groovy‘]

Run the war and you will see that the data table for the Person.age column become nullable.

Migration 2 - add address columns

The second example for migration demonstrates adding columns to a single table. Let's assume the Person needs address fields. We will simply add them to the current table. The naive assumption is that a Person will only have address. This obviously is not true in most circumstances, but it sets us up nicely for a more complicated third migration.


Make Domain Change

Edit the Person class to become this:

package dbmigration.poc
class Person {
    String name
    Integer age
    String streetName
    String city
    String zipCode
    static constraints = {
      name()
      age(nullable: true)
      streetName(nullable:true)
      city(nullable:true)
      zipCode(nullable:true)
    }
} 
Create a changeset file:

grails dbm-gorm-diff add-address-fields-to-person.groovy --add
Now you can deploy the war again.

Migration 3 - add Address table

To demonstrate a slightly more complicated scenario, let's assume we need to expand the domain to allow a Person to have multiple Addresses. Thus, Person to Address will have a one to many relationship.


















The domain classes:

package dbmigration.poc
class Person {
    static hasMany = [addresses: Address]
    String name
    Integer age
    static constraints = {
      name()
      age(nullable: true)
    }
}

package dbmigration.poc
class Address {
    Person person 
    String streetName
    String city
    String zipCode
    static belongsTo = [person: Person]
    static constraints = {
      streetName(nullable:true)
      city(nullable:true)
      zipCode(nullable:true)
    }
}
Generate the changset:

grails dbm-gorm-diff create-address-table.groovy –add

Consider Migration of Data

Now we must consider the data that remains in the Person table. It must be transferred to the Address table. Liquibase cannot predict the intent of data migration. Liquibase has no way knowing the semantic meaning of data, or where you intend to migrate it. This type of information has to be provided by the developer. Thus, right after the creation of the address table in the create-address-table.groovy script, we have to add this:

changeSet(author: "a488338 (generated)", id: "migrate-person-data") {
        sql("""insert into address (id, version, person_id, street_name, city, zip_code)
              select hibernate_sequence.NEXTVAL, 0, id, street_name, city, zip_code from person""")
            }
Notice we used the sequence value for id and also added the version value to the sql.

Steps Illustrated

In summary we can illustrate the steps a developer must do in order to conduct migration with the plugin.

















The plugin will become part of Grails 2.0, according to the roadmap.

95 comments:

  1. Nirav I noticed the reverse of the syntax for add change log file:
    grails dbm-gorm-diff create-address-table.groovy –add

    should be

    grails dbm-gorm-diff -–add create-address-table.groovy

    do you agree?

    ReplyDelete
  2. Will this work with Grails 1.3.x relase?

    ReplyDelete
  3. Really these data are helpful one for one who is doing study on data migration.Thanks for the post.
    Data Migration

    ReplyDelete
  4. It is very informative and helpful keep updates we also provide online training on
    SAP OS DB MIGRATION ONLINE TRAINING.anybody search os db migration details please click on above link.

    ReplyDelete
  5. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Best Devops Training in pune
    Microsoft azure training in Bangalore
    Power bi training in Chennai

    ReplyDelete
  6. Thanks a lot for sharing us about this update. Hope you will not get tired on making posts as informative as this. 
    python Training institute in Chennai
    python Training institute in Bangalore
    python Training in Pune

    ReplyDelete
  7. cerisea medica
    amounts. Fifth, you should eat mostly frequent normal water. You want to minimize the volume of alcohol you take in and avoid getting all drinks that contain sugar. Low alcohol content drinks do have some advantages for our systems. A glass of red wine will last your circulatory system and a beer once a
    https://newsletterforhealth.com/cerisea-medica/

    ReplyDelete
  8. Keto fit pro execute out routine and diet strategy strategy control. Massage can help you with both these aspects. Massage And Pressure Reduction One of the major causes of overeating and binging on high-calorie, high-sugar refined meals are pressure. Whenever individuals are overworked or under severe emotional advantages, and this makes it an exceptional inclusion to any motivation and attempt. •
    https://newsletterforhealth.com/keto-fit-pro/

    ReplyDelete
  9. south beach diet
    kbrown fat, while kynurenic acidity in the immune cells enhanced anti-inflammatory properties. These two aspects, the growth in metabolically active fat and decreased inflammation, both assist the body’s ability to get rid of fat as energy and avoid excessive fat storage. Sounds promising! Lace up those shoes!
    https://newsletterforhealth.com/south-beach-diet/

    ReplyDelete
  10. cerisea medica execute out system smoothly and get your health and fitness and health goals quicker. Improved Blood stream Circulation There is another crucial relation between massage treatment and weight-loss. As mentioned above, it improves your veins flow. Meaning that during execute out, you have a better
    https://newsletterforhealth.com/cerisea-medica/

    ReplyDelete
  11. slim quick keto
    has a key role in weight-loss. Then this research popped up online2, giving us new insight into what might be going on with our metabolic procedure when we execute out. Researchers from Karolinska Institute in Sweden found mechanisms in mice by which execute out counteracted fat storage and
    https://newsletterforhealth.com/slim-quick-keto

    ReplyDelete
  12. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete
  13. ਖੁਸ਼ੀ ਅਤੇ ਖੁਸ਼ੀ ਤੁਹਾਡੇ ਕੋਲ ਆਵੇਗੀ, ਤੁਹਾਡਾ ਬਹੁਤ ਧੰਨਵਾਦ

    bon mat xa

    máy ngâm chân giải độc

    bồn matxa chân

    bồn mát xa chân

    ReplyDelete
  14. Vi lägger alltid kundförmåner först.
    Våra produkter är: Van điện từ Tân Bình

    ReplyDelete
  15. thanks for your details it's very useful and amazing.your article is very nice and excellentweb design company in velachery

    ReplyDelete
  16. thanks for your information really good and very nice web design company in velacheryQbigpro branding solution is the best web design company in velachery web design company in velachery.we will create the web site and managing the site.we will help for all business.website is very important for all business.

    ReplyDelete

  17. The blog is absolutely fantastic, I’ll learn many new stuff right here! Thanks for sharing this information,this is useful to me. Good luck for the next post buddy!!
    machine learning course

    ReplyDelete

  18. Your post is very informative. It is helpful to develop my skills in the right way. Persist the great workweb design company in velachery

    ReplyDelete
  19. Thenutritionsclinic Some steamers are designed to help restore sufficient moisture to the lungs and respiration tract. Many steamers have plastic, contoured hoods to cradle the face. Water is going into the unit's base wherein a heater gently boils it. Heat, wet air is directed proper into the nose and throat to help relieve nasal, sinus, and chest congestion. Adjustable controls on the steamer lets in you to control the quantity of steam.
    https://thenutritionsclinic.com/

    ReplyDelete
  20. Very useful and information content has been shared out here, Thanks for sharing it.php training in bangalore

    ReplyDelete
  21. Your articles really impressed for me,because of all information so nice.angularjs training in bangalore

    ReplyDelete
  22. Linking is very useful thing.you have really helped lots of people who visit blog and provide them use full information.angular 2 training in bangalore

    ReplyDelete
  23. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck…

    Upgrade your career LearnAWS Trainingfrom industry experts get Complete hands-on Training, Interview preparation, and Job Assistance at Bangalore Training Academy Located in BTM Layout.

    ReplyDelete
  24. Such a great information for blogger i am a professional blogger thanks…

    Learn Best PEGA Training in Bangalore from Experts. Softgen Infotech offers the Best PegaTraining in Bangalore.100% Placement Assistance, Live Classroom Sessions, Only Technical Profiles, 24x7 Lab Infrastructure Support.

    ReplyDelete
  25. ketogenicpedia The parents that are typically knowledgeable in a way to use this machine are emergency medical technicians (emts), police, fireplace employees, and medical institution personnel. In a few times, other trained humans can be requested to apply them.
    https://ketogenicpedia.com/

    ReplyDelete
  26. Awesome blog. I enjoyed reading your articles. This is truly a great read for me and I am looking forward to reading new articles. Keep up the good work!
    Machine Learning Courses

    ReplyDelete
  27. Awesome Article....Thanks for sharing the useful information...
    pega training in bangalore

    ReplyDelete
  28. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here

    Big Data Analytics Training In Hyderabad
    Big Data Analytics Course In Hyderabad

    ReplyDelete
  29. Thanks for the informative article About Selenium. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  30. Hi there! This article couldn’t be written much better! Reading through this post reminds me of my previous roommate! He continually kept talking about this. I'll send this information to him. Pretty sure he'll have a very good read. I appreciate you for sharing https://kbcofficialwinner.com/jio-lottery-winner-2020-jio-kbc-lottery-lucky-draw/

    ReplyDelete
  31. This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this..
    Business Analytics Training | Business Analytics Course In Hyderabad

    ReplyDelete
  32. great blog.thanks for posting like this with us. great article. We at Fuel digital marketing give you the best E-commerce website development services in Chennai. Which will give you and your customers a one-stop solution and best-in-class services.

    best e commerce website development services in chennai|best woocommerce development company | ecommerce website designing company in chennai | website designing company in chennai | website development company in chennai | digital marketing company in chennai | seo company in chennai

    ReplyDelete
  33. The KBC Jio Winner is an appealing-looking cellular mobile phone which has the functionality to get an notable following among clients.
    https://luckywinnerlist.com/

    ReplyDelete
  34. Your work is very good, and I appreciate you and hopping for some more informative posts
    <a href="https://www.excelr.com/business-analytics-training-in-pune/”> Courses in Business Analytics</a>
    It is perfect time to make some plans for the future and it is time to be happy. I’ve read this post and if I could I desire to suggest you few interesting things or tips. Perhaps you could write next articles referring to this article. I want to read more things about it!

    ReplyDelete
  35. Thanks for sharing this informative content , Great work
    Leanpitch provides online training in Advanced Scrum Master during this lockdown period everyone can use it wisely.
    Advanced Scrum Master Training Online

    ReplyDelete
  36. Thanks for sharing this informative content , Great work
    Leanpitch provides online training inScrum Master during this lockdown period everyone can use it wisely.
    Advanced Scrum Master Training

    ReplyDelete
  37. Machine Learning Courses in Pune I really enjoy reading and also appreciate your work. I read that Post and got it fine and informative. Please share more like that...
    I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site.

    ReplyDelete
  38. Egronomic office chair in india is one of the most powerful and most adjustable office chair that you can have.

    ReplyDelete
  39. Excellent blog with great information found very valuable and knowledgeable thanks for sharing.
    Data Science Training in Hyderabad

    ReplyDelete
  40. Thanks For Sharing The Information The Information Shared Is Very Valuable Please Keep Updating Us Time Just Went On Reading The article
    by cognex AWS Training in chennai

    ReplyDelete
  41. Amazing to peruse this sorts of blog, continue giving the new updates. Continue sharing Thanks... Regards Activated carbon manufacturing companies in India

    ReplyDelete
  42. Actually I read it yesterday but I had some thoughts about it and today I wanted to read it again because it is very well written.
    data science training hyderabad

    ReplyDelete
  43. love this article of yours so much. Hey recently I learned that you can literally make any office look really beautiful by just adding some amazing office table and chairs which has really good design. And trust me you will fall in love with them easily.

    ReplyDelete
  44. You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog.

    Data Science Institute in Bangalore

    ReplyDelete
  45. Excellent exchange of information ... I am very happy to read this article ... thank you for giving us information. Fantastic. I appreciate this post.
    Data Analytics Courses in Bangalore

    ReplyDelete
  46. Wonderful illustrated information. Thank you. It will certainly be very useful for my future projects. I would love to see more articles on the same topic!
    Data Science In Bangalore

    ReplyDelete
  47. Thanks for the lovely blog. It helped me a lot. I'm glad I found this blog. Thanks for sharing with us, I too am always learning something new from your post.
    Data Science Training in Pune

    ReplyDelete
  48. I am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place. ExcelR Data Analyst Course

    ReplyDelete

  49. Really impressed! Everything is very open and very clear clarification of issues. It contains true facts. Your website is very valuable. Thanks for sharing.

    Data Science Training in Hyderabad

    ReplyDelete
  50. hanks For Sharing The Information The Information Shared Is Very Valuable Please Keep Updating Us Time Just Went On Reading The article

    ReplyDelete
  51. Really nice article it was well composed

    ReplyDelete

  52. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also.

    digital marketing courses in hyderabad with placement

    ReplyDelete
  53. I can set up my new thought from this post. It gives inside and out data. A debt of gratitude is in order for this significant data for all,
    data scientist training and placement

    ReplyDelete
  54. Very informative blog and useful article thank you for sharing with us,
    by cognex is the AWS Training in chennai

    ReplyDelete
  55. Fantastic website! I'd want to express my gratitude for the time and effort you put into producing this article. I expect the same level of excellence from you in the future. I wanted to express my gratitude for the excellent website! Thank you for sharing your knowledge.

    digital marketing training in hyderabad
    digital marketing course in ameerpet

    ReplyDelete
  56. I truly like your composing style, incredible data, thank you for posting.
    Best Data Science courses in Hyderabad

    ReplyDelete
  57. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work
    Best Data Science courses in Hyderabad

    ReplyDelete
  58. Gangaur Realtech is a professionally managed organisation specializing in real estate services where integrated services are provided by professionals to its clients seeking increased value by owning, occupying or investing in real estate. data scientist course in noida

    ReplyDelete
  59. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.360digitmg-data science course
    cloud computing course fee in hyderabad

    ReplyDelete
  60. I think about it is most required for making more on this get engaged artificial intelligence training institute in noida

    ReplyDelete
  61. Nice knowledge gaining article. This post is really the best on this valuable topic.
    cloud computing in hyderabad

    ReplyDelete
  62. You re in point of fact a just right webmaster. The website loading speed is amazing. It kind of feels that you're doing any distinctive trick. Moreover, The contents are masterpiece. you have done a fantastic activity on this subject! cyber security course in delhi

    ReplyDelete
  63. Your site is truly cool and this is an extraordinary moving article. cloud computing training institute in gurgaon

    ReplyDelete
  64. I like this post,And I figure that they having a great time to peruse this post,they might take a decent site to make an information,thanks for sharing it to me ethical hacking course in gurgaon

    ReplyDelete
  65. Super site! I am Loving it!! Will return once more, Im taking your food additionally, Thanks. data analytics training in delhi

    ReplyDelete
  66. Well, this got me thinking what other workouts are good for those of us who find ourselves on the road or have limited equipment options. cloud computing training in delhi

    ReplyDelete
  67. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post. ethical hacking institute in noida

    ReplyDelete
  68. I am looking for and I love to post a comment that "The content of your post is awesome" Great work! Data Analytics Course in Chennai

    ReplyDelete
  69. I have read your excellent post. This is a great job. I have enjoyed reading your post first time. I want to say thanks for this post. Thank you... business analytics course in kanpur

    ReplyDelete
  70. Гадание на судьбу и будущее - это простейший метод предсказать будущее с применением всевозможных предметов и методов. Таинственные силы и разного рода варианты предсказания судьбы учеными не описаны, при том различные люди верят в это. Номенклатура вариантов, направленных на прогнозирование жизненного пути, именуют гадание.

    ReplyDelete
  71. Shreeja Health Care is leading manufacturer of Oil Making Machine Manufacturer. Shreeja Oil Extraction Machine is able to extract oil from various seeds like peanuts, Coconut, Sesame, Soybean, macadamia nuts, walnuts, sunflower seeds, vegetable seeds flaxseed etc.

    ReplyDelete
  72. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

    ReplyDelete
  73. I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts. business analytics course in kanpur

    ReplyDelete
  74. The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought you have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention. data science course in mysore

    ReplyDelete
  75. Don’t let a pandemic affect your career growth, start your Data Science Online training today with 360DigiTMg and give your career the well-deserved boost it needs. Enroll now!

    Data Scientist Course in Delhi

    ReplyDelete
  76. Nowadays, every company is running its business on the data. That is why data is considered the most crucial factor of any organization.
    data science training in gorakhpur

    ReplyDelete
  77. The author's ability to present complex information in a concise and understandable manner is impressive data analyst course in chennai..

    ReplyDelete
  78. Blogs can serve as a platform for niche hobbies and interests Deal Of NordVPN connecting enthusiasts from different parts of the world who share similar passions.

    ReplyDelete
  79. Unified communications and Ip Pbx includes the connection of various communication systems both for the collaboration tools as the digital workforce.

    ReplyDelete