00:04hey everyone hope you're doing well so
00:06today i'm going to be having a look at
00:07database migrations we're going to look
00:09at how to version control your database
00:11and we're going to focus on basically
00:14you know what database migrations are
00:15how they work and we're going to demo
00:17using java and flyway but the great
00:19thing about you know what we're going to
00:20go over today is that the concept of
00:21database migrations is the exact same
00:24across any language any tool the
00:26implementation details will differ
00:28slightly but we're not going to focus on
00:29those anyways we're going to focus on
00:30the high level concept so if you enjoy
00:32this kind of content please consider
00:33subscribing and let's get right into it
00:35so first things first let's try to
00:37understand what the problem is here what
00:39problem we're trying to solve and then
00:40we'll go into the the tools are solving
00:43when you're building an application of
00:44course over time your application
00:46changes in terms of the shape of the
00:48application the models the functions
00:50everything changes and usually you keep
00:52this in source control so you might use
00:53git and you know you might push your
00:55changes up to github and basically what
00:56that does is it gives you you know a few
00:58really big benefits um first of all it
01:00keeps your you know changes so you don't
01:02lose them locally of course um but more
01:04importantly it allows you to share your
01:06code with other developers that come on
01:07and they can you know take the latest
01:09version of the application they can also
01:11go back and debug you know
01:13if certain commits are pointing to
01:15certain environments they can go back
01:16and debug that and of course for things
01:18like continuous deployment um you need a
01:20way to to have different versions of
01:22your code so that way when you've got a
01:23new version you can just upload it and
01:25your environments can just take the new
01:27version they know what the version they
01:28were on before they know the new version
01:30they can take that and deploy the code
01:32so if we just had an application with no
01:35we had no external dependencies that's
01:38now as soon as you start doing something
01:40a bit more complicated let's say in this
01:41in this scenario i'm building an
01:43application with a list of users you
01:45know maybe these are um subscribers to
01:49an email list and i want to send them
01:50emails well now i need to start storing
01:52those that data in the database now as
01:55soon as we add this dependency we have
01:57this kind of external
01:59but explicit dependency that we need to
02:01track as well which is basically the
02:02schema of the database right so this
02:05version of the application you can see
02:06here i have a an insert into
02:10the user i'm inserting the email this is
02:12only going to work if my database has a
02:14certain schema which is this here a user
02:16might have an id might have an email if
02:18it doesn't have that it's not going to
02:19work the application is going to fail so
02:20these these two are basically tightly
02:22coupled so the question then becomes how
02:24do we make sure that the state of the
02:27database or the schema database matches
02:29the required state of the application
02:32and this is basically the the issue that
02:34database migrations solve so you could
02:36try to do this manually where you know i
02:39build the application i i manually
02:40insert some sql and i create this user
02:42and then you know the developer comes
02:44and joins the team and i send them a sql
02:46script or a bunch of sql scripts i
02:48export that um but then of course if i'm
02:50working on different versions and i've
02:52added fields that change things locally
02:54then you know i need to somehow reset it
02:55and then of course continuous deployment
02:57doesn't work at all because you know how
02:58are we meant to tell the environments
03:00that the change of the database do we
03:01you know log on to the databases
03:03themselves and update the scripts
03:04manually well if you make a mistake
03:06there's a whole kind of range of issues
03:08that can kind of stand for trying to do
03:09this manually so the the solution for
03:12this here is basically database
03:14migrations um and we'll basically see
03:16that as we go below another issues of
03:18course as this uh this data evolves so i
03:21have email now i change the application
03:23now i'm gonna add a name here um again
03:26how do you share those changes how do
03:28you make sure that everyone has the
03:29updated version um yeah all of these
03:32things this is what the the issue solves
03:36what exactly a migration is what
03:37migration tools do and how they solve
03:39this issue well a migration all is when
03:42somebody says migration it's just a
03:43script that's it's just a sql script or
03:46it could be a bash script or a
03:47javascript or python script it could be
03:48any sort of script but its sole purpose
03:50is to take the database from one state
03:53to another to migrate the schema the
03:56state of one database from uh state a to
03:58state b right so in this case here we
04:01are we've got this create user script
04:03and we can see this version zero so this
04:05is the first ever migration that we have
04:07in the uh the database and all we're
04:08doing is we're creating a user table
04:10we're adding an id we're adding an email
04:12that's in migration right and then later
04:14on we might add a name so we're altering
04:16the table uh we're adding the name so
04:18these are very similar to like commits
04:20in a github repo right this is every
04:22action that was taken in order to get
04:24the the state of the database from zero
04:26to where we need to now
04:28and of course um in addition to this is
04:31one other step that's needed which is
04:33basically capturing the the state um i.e
04:37where we are in the kind of in the
04:38migration life cycle so if we imagine if
04:41we just move this aside here and let's
04:43say we move this one aside here so we're
04:44just focusing on a brand new database if
04:47i run the migration here it runs the
04:49first one so now we have let's just go
04:52this user table so now we have a user
04:54table in this database so great now the
04:57next time we add a new script we
04:58basically need a way of knowing you know
05:01which scripts have we run already
05:02because we don't want to re-run the
05:03initial script because then we need to
05:05start to complicate things with sql
05:06checking if things exist
05:08so we always need to know where we are
05:10in the migration history basically and
05:12this is typically why most migration
05:14tools will also maintain within usually
05:16the same database uh some sort of
05:20and this is basically the state this is
05:21where we are so when we're on the first
05:23one we can say hey we're on version zero
05:25so we've run the first script and then
05:28when the second script comes along when
05:29we we say okay now we can migrate
05:32you know it can check the database and
05:33it can say well we've already done
05:34version zero so we're ready on version
05:36zero we don't need to rerun this one but
05:38we can see a new version one so we're
05:39gonna apply the full name so then it
05:41might you know take all these you know
05:43apply the full name and update this to
05:44one and so on so forth so conceptually
05:47this is all we need to know this is
05:49database migrations in a nutshell so
05:50what we're going to do now is we're
05:52an example like i said we're going to be
05:55we're going to be using flyway and java
05:57but the concepts here are going to be
05:59the same for any language so if you're
06:00working with java and flyweight this
06:01throat is going to be good enough for
06:02you to to get started with it but
06:04if you are used to javascript or python
06:06or whatever it is just don't focus too
06:07much on the implementation details uh
06:10but the concepts will be the same
06:11anywhere you go so yeah let's get right
06:13into it so i've got my very simple
06:15application running here and you can see
06:16very simple java application i'm using
06:18gradle as the build tool and i've just
06:20got one class which is this app class so
06:22i'm just going to close this for a
06:23moment all this is doing is i've got a
06:25main method here it's getting a
06:26connection to the database which you
06:28might do in many different ways i'm
06:30using something called juke to to build
06:32my sql queries so that's what this uh
06:34it's initializing basically the the uh
06:37the juke context and then i'm just
06:39saying hey insert into the user table
06:41this email read at example.com execute
06:44that and then i'm just getting the
06:45results select star or select asterisks
06:47from user fetch and then i'm just
06:48printing out the results so that's
06:50that's all i'm doing now on the right
06:52hand side here we can see basically my
06:54my postgres database and as you can see
06:56in my postgres database um we don't have
06:59any database so if i go down to my
07:01database connection string here we're
07:03we have uh low closed uh postgres in the
07:06schema here because i'm using postgres
07:08we have a schema but we're going to be
07:10on we're going to be using the
07:11migrations tutorial schema so we don't
07:14have that schema there so i could go you
07:15know right click here or you know write
07:17some sql and create this schema manually
07:19but we don't want to do that we want to
07:21some sort of database migration tool
07:23right of course if i try to run this
07:24application now because there's no
07:26schema because there's nothing there
07:27it's just gonna fail it's gonna say hey
07:29you know the table user doesn't exist in
07:30fact your schema doesn't exist at all so
07:33let's bring in flyway uh in this case
07:35and let's start to to yeah to see how
07:37the migrations work so
07:39because i'm using gradle what i'm gonna
07:40do is i'm gonna jump over to the build
07:42gradle file and all i need to do here is
07:44i'm gonna add a plugin so with flyway
07:47you can run this in many different ways
07:49so you can run it as a command line
07:50application um you can run it as a you
07:52know directly encoded with java you can
07:54use maven or griddle because i've got
07:55gradle here i'm just going to use the
07:56gradle plugin and i just like the plugin
07:58it just means i can keep it all inside
08:00my application you don't need to install
08:01any external dependencies but again
08:03really doesn't matter so i'm just going
08:05um the gradle plugin here and the one
08:08other thing it requires is it just needs
08:11the connection so basically needs you to
08:12configure gradle so this is what you
08:14would otherwise maybe pass in to the
08:16command line arguments you need these
08:18details somewhere so that it knows how
08:19to connect to your database so for
08:21flyway i'm just telling it i'm just
08:22using you know postgres driver i'm
08:25saying here's the url to the database
08:27here's the user here's the password and
08:29here's the schema and the default schema
08:31one specific thing i think this is
08:33flyway specific is that flyway will
08:35automatically create the
08:37default schema for you i've played
08:39around with some other migration tool
08:41that don't do that by default they
08:42expect you to handle that you know as
08:44part of the uh you know as part of the
08:46um infrastructure pipeline in this case
08:48this is going to create the the this is
08:50going to create the schema for us so
08:52that's perfect so once we have flyaway
08:54or whatever tool it is you have
08:55installed i'm going to close that all
08:56you need to do is run your migration
08:58script so i've not actually got any
08:59migrations yet but in this case because
09:02i'm using flywheel i just want to show
09:03you how it kind of starts to track
09:04things if i run flyway migrate here and
09:08set up you can see here uh it started to
09:10log you know there's a bit of a mess but
09:12there's a few things to to to focus on
09:16um migration through it's up to date
09:17there's no there's no migrations and you
09:19can see here skipping file system that's
09:20because flyway as well as all migration
09:23tool will expect you to have a certain
09:25directory in this case db slash
09:27migrations with all your migration
09:28scripts and the migration skills will be
09:30in a certain format but it's not found
09:32now we haven't added that anywhere this
09:33is a brand new application we don't have
09:34that so it's just basically said hey we
09:36don't have a migration to run but i've
09:38done what i can and in this case what
09:40flyaway can is basically it's created
09:43the schema so i can see now i've got
09:45five migrations tutorial that's it's
09:47added that and if i go into migration
09:50we can see there's this flyway schema
09:52history table we didn't create that and
09:54that's because flyweight created it for
09:55us similarly again any tool that you
09:57have will create its own version of this
09:59and you can see here that the version
10:01here is null so we've not actually run
10:03any migrations we're on we're on null
10:04version we haven't done anything but all
10:06we have done is we've run the
10:09flywheel internally has run this
10:10migrations script um
10:12to create the uh the schema which is the
10:15migrations tutorial schema so this is
10:16basically the starting point for flyway
10:18your other languages might be slightly
10:20different might be the exact same but
10:21conceptually all we're doing here is
10:23initializing and initializing the
10:25migration tool cool so let's go add some
10:27migration so uh i've got migration here
10:30um on the right hand side which i'll
10:33we'll shall basically copy in
10:35fly away looks for a specific directory
10:40migration so we'll add that in and we're
10:42just going to copy this file here so
10:44this file note it's v0 that's exactly
10:46what we started on underscore underscore
10:48and then there's a little description
10:49create user table dot sql so this is
10:51very specific to flyway
10:53any tool that you're working with might
10:55be slightly different but ultimately
10:56there will be some sort of naming
10:58convention whether it's in the file um
11:00the file name or it's whether it's
11:01within the file i think you know i think
11:04python's alembic and they have the the
11:07version inside the file
11:10this is the way that fireway does it and
11:11the good thing about this one is because
11:13you have the basically the version and
11:14then the number and it's an incremental
11:16number you get to see all the different
11:17changes in basically in chronological
11:20order in your file system so i like that
11:22about flyway but you know whatever tool
11:24you have will be similar so all we're
11:25doing here is we're creating the user
11:29i'm going to go into gradle i'm going to
11:31run flyway migrate and you can see here
11:33again in the logs it's um it's validated
11:36two migrations because we have the
11:38schema migration and we of course have
11:39this this new migration um
11:42yeah and that's basically it's finished
11:43applying this one migration so if we go
11:45to our database and i refresh this guy
11:47we can see hey we have the user table
11:49it's got the id and the email so if i
11:52now run my application and all it's
11:54doing again is inserting this uh this
11:57email oops let's get pilot trying to be
12:01it's going to be happy it's going to
12:02succeed it's going to say hey
12:03everything's worked and if it's trying
12:05at the bottom it's saying hey we've got
12:07a new we've got a user read example.com
12:09and here's the id of one cool so we've
12:11got our basic setup now let's just look
12:14at one other scenario which is you know
12:15what happens if you need to change the
12:16table we need to alter it and
12:18one common thing you face is what
12:20happens if i need to add a non-nullable
12:22field right this is quite a common thing
12:24there's a lot of kind of
12:25you know different strategies etc that
12:27you might face and you'll you'll learn
12:28those over times but i think this is
12:30possibly one of the most one of the most
12:31common ones so what we're going to do um
12:33is inside the code if i of course just
12:35add all right well now i want to add a
12:37name so let's say i want to add um
12:45and i'm going to add blue here so that's
12:49of course now that shouldn't work right
12:51we don't have a name it doesn't exist uh
12:53we need to migrate so that's gonna fail
12:54we don't have the name that's fine let's
12:56add our migration so i'm gonna copy over
12:58again another migration
13:00into this now it's gonna be v1 we we're
13:02up one version and this is just gonna be
13:04called we're just adding adding a name
13:06and the most uh kind of thing that i'll
13:09stand out here let me just fix up this
13:14is green instead of instead of red so
13:16we're altering the table user and we're
13:17adding the column name
13:19and if i just comment this out and if i
13:22take this out actually intellij it gives
13:23gives us a bit of a a clue so we're
13:27it's not null at this point so it's not
13:28going to cause an issue because we
13:29already have data in our database but it
13:31can be null as soon as i add not null um
13:34intellij here is actually going to
13:35complain it's going to say
13:37actually you know we can't do that um
13:40because you've already got data on your
13:42database so it can't it can't be null
13:43would you want to do so this is a point
13:45where we need to figure out different
13:46strategies right so you may you know if
13:48you're more familiar more advanced with
13:50with sql you might start to write you
13:52know triggers or functions that go and
13:54you know maybe use default the email
13:56column into the name i'm just going to
13:58keep something very simple here which i
14:00had previously and i'm just going to say
14:01actually if i don't know what the the
14:02name is i think i had unknown but i'm
14:04just going to pop in front right so
14:06we'll just default everything to friend
14:07we'll sort out how how they enter the
14:09name later on maybe the application
14:10that's a different concern
14:13the most important thing here is we
14:16defaulted after this change so if i if i
14:19keep this default here and i don't add
14:21this in then we can it's always going to
14:23default to friend what we want to do is
14:24we want to force the application to
14:26always add a name now going forward so
14:29you know it's quite a
14:30it's quite a significant change and this
14:32is why we have this second part here
14:33which is alter the table uh and actually
14:36now remove remove the default so
14:38everything before now gets friend
14:40everything in the future you need to
14:41explicitly pass in the name so if i go
14:43to my app here again final one
14:46obviously got blue example blue
14:48i run this and what i should basically
14:50see oops of course i forgot to run the
14:52migration so if i just run the migration
14:55that's migrated the database
14:59i can run the application
15:02uh let me just run up here
15:04there we go and now if we look at the
15:07output we can see that the red example
15:10has been defaulted to friend and blue at
15:14has been added blue and of course now
15:16going forward with app we can't not add
15:17the name so it's kind of explicitly
15:19having adding a kind of a hard and fast
15:22rule there and i mean in terms of kind
15:24of database migrations that's the
15:25absolute basic that's going to be enough
15:27to get you very very far um there's a
15:29few other things that's probably worth
15:31mentioning with database migrations
15:32you'll see lots of tools that may have
15:34other commands such as undoing
15:37undoing you know all these repair
15:39validate i've never had to touch any of
15:40these so typically when you want to undo
15:43this is similar to undoing a commit
15:44you're going to fail forward that's kind
15:46of the general advice um if you if
15:48you've made a mistake you need to drive
15:50fix it by failing forward just add
15:51another migration and essentially revert
15:53it and the only other thing that's worth
15:55knowing is the the baseline which is i'm
15:58not sure the terminology is common but
16:00it's the same concept which is if you
16:01already have a database um if you
16:04already have a database and you want to
16:06add migrations you already have a bunch
16:07of tables of course you need to
16:09basically add a baseline you need to say
16:10well i'm not starting from scratch but
16:12i'm starting from a certain point and
16:13conceptually the way you would do that
16:15imagine we had no migrations here but we
16:17had you know this this user table what
16:20you do is you basically need to extract
16:22uh basically the sql that would create
16:24your entire database so uh here in my
16:26case i can just go to sql scripts and i
16:28can copy the the dll to the clipboard so
16:30if i look at what that looks like um if
16:33i just minimize all these
16:35if i copy everything it basically takes
16:37the shape of the entire schema or every
16:39table in the schema and it posts it into
16:40one big sql file i know that flyway
16:42creates this one automatically for
16:43example so i can remove that but this
16:45will leave everything else so you can
16:46see that just got the user table with
16:47the email on the name and then what you
16:49would do is you just add that to a
16:51file and then you basically perform like
16:53a a baseline command which basically
16:55updates manually updates this uh this
16:57table which if i refresh now you can see
17:00the state you basically manually update
17:02this table to say actually i've added
17:04all these uh sql scripts but you know
17:06you can skip them so update the the stay
17:08here we can skip them because we've
17:09already run them um and then going
17:11forward you need to follow the standard
17:13state pattern here but i think that's
17:14everything i wanted to cover in this
17:17yeah i'll wrap it up there thank you
17:19very much for watching have a good day
17:20and i shall see you in the next one
