Skip to main content

JHipster, Liquibase, MySQL, and initializing data, including booleans!

When generating a data model from JHipster JDL, we will often declare entities with Boolean fields.  I have so far abandoned H2 as a database because of liquibase issues, and both my dev and production databases will be MySQL.  This is relevant to the Boolean field desire there is a long history in software development of how to store Boolean data types in a SQL database whose standards classically do not support Boolean.

In the current JHipster/Liquibase incarnation, tables in MySQL are generated for us, which is really nice.  The Boolean data types are stored as BIT (1).  This is not a problem so far -- most developers seem to agree now that as a best practice, we should store values in databases as false = 0 and true = 1, and a BIT(1) is a great, simple way to do that.

An issue arises when we try to use liquibase to set/update our database to the desired starting state.  For my project, I've chosen gradle instead of maven as a build tool, and gradle has a plugin for liquibase.  (JHipster automatically includes it, hurray!)  To update my database to create tables as well as initialize data into those tables, I have to:

  1. add more CSV files into the /main/resources/config/liquibase directory for each table that needs initial data.
  2. modify the associated table's XML file so that it includes a loadData element to find and insert the data in the CSV.
  3. run the liquibaseUpdate gradle target

As I tried to do these things, I ran into problems.

Problem #1: Database connection information

Spring boot allows me to differentiate environments using different application property files.  JHipster helps with this by automatically generating YML files -- one for dev and one for prod.  The problem is that updating these files is not enough.  Liquibase is separate/distinct from spring boot, and it uses connection information inside of the gradle targets, so I had to update the connection information in the liquibase activities main and diffLog settings, including url, username, password, and defaultSchemaName.  Once I made these updates, the connection worked.

url 'jdbc:mysql://<URL/IP CONNECTION INFO>:3306'
username 'username'

password 'password'
defaultSchemaName '<DATABASE SCHEMA/NAME>'

Problem #2: Boolean data as BIT values

In the second point, I ran into errors because the data in the CSV for boolean fields were being read as strings instead of as bits/booleans.  As a result, I had to specify that the columns were type="boolean" so that liquibase would know that the values in the CSV were bits, not strings.  The loadData element looks like this:

<loadData encoding="UTF-8" 
    <column name="BOOLEAN_COLUMN_NAME_1" type="boolean"/> 
    <column name="BOOLEAN_COLUMN_NAME_2" type="boolean"/>

At this point, I delete all tables in the database and run the liquibaseUpdate gradle target (I typically do this via my IDE's gradle tool), and my database now has all of the tables and data I want.


Popular posts from this blog

Spring Security - Authority vs Role

I have spent a lot of time recently trying to understand the difference between Authority and Role in Spring Security.  This is a brief review of what I found. When creating a UserDetailsService or overriding configure(AuthenticationManagerBuilder auth) in the security config class that extends WebSecurityConfigurerAdapter, I basically get complete control over what I populate inside of the UserDetails that is used/returned.  This is important because the UserDetails interface really only cares about how to return one thing: Collection<? extends GrantedAuthority> getAuthorities(); A GrantedAuthority just seems like a glorified String wrapper that names some thing.  The question is... what is that thing? This is where the subtle difference between Authority and Role comes into play. I think that Role is an older thought/construct that automatically gets plugged into Authority if we just create a user with a Role.  But completely forget about the code and classes for a mi

Gmail internal application, Two-Legged OAuth2, Server to Server authentication, and Google API versions

I am working on a little tool at home in my free time to put some skills into practice.  The general idea (nothing novel) is this: I have some financial alerts sent to a new email address I have spun up on my domain.  I am creating an AWS Lambda that will wake up on an hourly schedule, read those emails, and publish SNS messages with parsed financial transaction information.  I then will have an SQS queue listen to the SNS message topic that is consumed by a Step Function.  The Step Function will: store the financial transaction information into a database send an SMS to me if the transaction is above a certain threshold. I could later extend this to do some aggregation reporting, etc if I wanted, too.  This will only work for my own financial transactions, and the data being gathered/stored will be sufficiently vague, so I am not really concerned about financial security for this project. The biggest hurdle I have run into so far is connecting to Gmail securely.  I was ab