Spring Data & Migrations

[00:00:00]
>> Josh Long: So now we're gonna talk about data. And I think it's important to at this point, we need to have a discussion about my muse, okay? The thing that drives me. I think about two things all the time. Their names are Peanut and Prancer. And see, normally when I look for when I wanted to do demonstrations I look for things I can use onto which people can latch, something that's familiar, something that's natural.

[00:00:34]
And I naturally tend to look at things like the Spring Pet Clinic, right? The Spring Pet Clinic is a sample application that demonstrates the moving parts in a typical spring application. And this application was written, I don't know, well, it's been updated, look at that, 11 hours ago, but it originated from 20 years ago, right?

[00:00:49]
It's a sample application, right? So close that. Here we go. So, Spring project, Spring Pet Clinic sample application. There you go. And there's Even a whole GitHub organization called Spring Pet Clinic, right? And there's just dozens of different versions of the Pet Clinic written in terms of like Spring AI or the REST API or Spring Data, JDBC or Angular or Kotlin, or Vaadin Flow or LangChain or Microservices or Hilla or React JS or istio htmx.

[00:01:19]
Yay, and all this kind of stuff, right? All these different implementations of basically the same domain. And I think it's a really approachable domain because come on, it's animals, right? It's pets and dogs and cats and the like and who doesn't love a good animal, a good pet, right?

[00:01:33]
I love animals. Everybody loves animals, right? Surely, you all like animals. Who doesn't love animals? Let's look at some data stuff, okay? We're gonna look at data here and you've already seen some of these pieces. We're gonna use the postgres support. We're gonna use Docker compose, we're gonna use Spring Data JDBC here, okay?

[00:01:54]
And I think we want to do database migration. So we use Flyway and of course, graalvm, right? And we use Spring Batch, okay? We're going to look at that. I'm going to open this up as an application. Let me delete all that. Okay, so we're going to look at it.

[00:02:15]
We're going to start from very simple, basic stuff and progress all the way to sort of a batch centric workload, okay? So we have a Docker compose file. I'm just going to start it up and let it run in the background there. I'm not going to have spring boot started up for me, but I do want to make sure it's the only thing running.

[00:02:29]
So, kill all Docker containers. Okay, so now docker compose up minus D. So there's my spring data source. Okay, let's go and hide the docker compose support, okay. Command shift I reload. Okay, so let's say I've got an application, and this application, it's going to need some SQL, some schema.

[00:02:57]
So I showed you earlier, but let's just rehash. We're gonna create two files here, data.sql, and we'll have another file called schema.sql. So create table if not exists, dog id serial primary key name text not null, okay? Owner text null and description text not null. So we're going to have a table of dogs that we can adopt eventually, okay?

[00:03:30]
So this is our dog table. And we want to insert some sample data in there. We can do it in the data.sql file, but for now let's just, I don't know, do we actually want to do. We can create some, what else do I want to put in there?

[00:03:46]
Maybe we could put a person object as well. So create table, person id, serial primary key, and then that's it. So we can do that. And then the person will have a collection of dogs, I guess. So we have to have a foreign key, yeah, in this over here.

[00:04:04]
So we'll say person, big int, references person id. I think that'll be okay, right? That's a foreign key as long as I can remember. So now spring boot will automatically run these two files. There's nothing in the data file right now. Let's comment that out for now. It'll run that file if you're using an embedded database like H2 or HSQL or Derby.

[00:04:29]
But I'm not using an embedded database, I'm using postgres, which is on a separate host and port. So I need to tell IT to run those initialization scripts always, right? You can do always, you can do never, you can do embedded. I'm gonna do always, okay? So now if I do that, let's see.

[00:04:45]
We've done nothing else. Let's just see if that runs, okay. It's not gonna work cuz I haven't specified my connectivity credentials, of course, same, so we'll do that again. So spring data source password is secret, username is my user URL, JDBC, PostgreSQL, Localhost, MyDatabase, okay? And what did I do wrong now?

[00:05:15]
My user? Person doesn't exist. Does it have to be one before the other? I guess it does? So let's go down over here, over there. Get rid of that, okay. Take five, okay. I don't have any web server. It doesn't matter. So now, if we go to our property file, one of the nice things about Intellij Ultimate Edition is you can click on that little icon there, hit the little button, it gives you this.

[00:05:46]
Go to there, go over here, go to your database, go there, go to there, go to your tables, and there you go. You got the schema for dog and you got the table. You got the table for dog and the table for person. There's no data in there as such, but we're getting there.

[00:06:00]
Okay, so Spring Boot can help you. If this is all you want to do, right, this is probably fine. But this is day one, right? In the real application, you're going to probably do more changes to the schema. And then this gets into a question of database migrations.

[00:06:17]
Here there's a lot of good choices in the JVM community. There's Liquibase, there's Flyway. I happen to like Flyway. And the way it works is you just create a database migration folder, you enable the Flyway plugin in your build, which I already have here, Flyway, right? I got that all by choosing Flyway Migration on the initializer, and then you put your schema file in here.

[00:06:38]
I'm going to create a new file here called V1__setup.sql. Okay, I'll move that to this, move that to that. Okay, now I've got these three tables. I'm going to delete everything and I'm going to delete schema, SQL. Okay, I'm going to say baseline true, okay? Let's restart the program, okay?

[00:07:10]
Refresh tables, three tables. So we've got dog and person, they've both been created. And you can see that the migration table here in Flyway that is created for us knows which ones have been run already. It knows which files already been run, okay, and so it won't run that again.

[00:07:27]
If I change it, it'll bark at me. So if I change this file, delete from Dog or something like that data application, it says, hey, you've done something to the file, it's no longer consistent. The hash is inconsistent because it wants to give you a way to know what changes have been applied to the database so that it doesn't run them again.

[00:07:48]
So it computes a hash and it thinks it's seen this file before, but the hash doesn't match, okay? So you're encouraged to be very deliberate about where you make changes. So I'm going to do rollback. I think that's it. Okay, so good. Now if I want to make changes, let's say I want to insert a record, okay?

[00:08:09]
V2 person.sql. Okay, I'll say insert into person name values JLo. Okay, so I'm going to insert that. This is just and the scheme is just V underscore, V number and then whatever into column name. What did I call it over here then? I don't even have a name.

[00:08:37]
Okay, text. So I can't do that. Look at that. Actually, that's not going to be allowed. So I have to go over here, alter table, person, add column name text, not null. Okay, and then run this again. Okay, so now does that person table have that new column?

[00:09:02]
Yes, it does. And it's got the new record. Now it's an insert though. So if I run again, we only have the one record, it doesn't rerun the same thing over and over. If you want to roll these changes back, if something should go wrong, if there's an exception, you create a new file called R to --person.sql.

[00:09:21]
That's the rollback script. It'll undo the change if something went wrong to put it back in a consistent state. If there are things that need to happen for every, sorry, no, it's called U2 underscore, underscore, person SQL undo. If there's some things that need to happen all the time, I think it's a resource like R, dot, whatever, setup SQL, right?

[00:09:42]
So either way, very, very, very powerful system to do database migrations.