Detailed Notes
After our series of building a python application to synchronize databases at the schema and data level we have lessons learned. There are areas where we need to be intentional as we decide how to maintain and enhance our databases. This is the final part.
Learn More at https://develpreneur.com
Transcript Text
thank you [Music] and it becomes which is why it became a really fun combo project because while there's a lot of little pythony things that we ran into uh really a lot of it is a is an exploration of what my sequel in particular um stores and where it stores it about the database itself and it it's different if you're in Oracle or uh SQL server or db2 but generally speaking the problem the uh the properties and details do exist within the database itself so you can query it almost in a reflection kind of mode create about it so that you can actually pull that and with some thought you generate SQL and then execute that SQL against your uh whatever your target or your your destination is and what you end up getting is something where you know probably what you're what you want to do and what you want to think through if you're going to synchronize your databases how how detailed do I want it to be is it good enough for me to have just the tables the tables the table structures and the stored procedures and if the tables are basically all empty of data is that okay in some cases it is because you can you can go in and maybe follow it up with some little like quick uh test script or something like that particularly if you have automated testing and regression testing then it may be something where you say hey all I need to do is just have an empty database that I'm going to create and then I'm going to run this test script and it's going to put some data in there for me and I can maybe even script a couple of bonus things there and that becomes extremely useful if you run in a situation where which I've been to a couple of those where you're at a job you're on a project and you say hey I want to be able to like see the I want to be able to create from scratch the database and somebody says oh that doesn't exist we don't have that SQL now you can in some cases you can do a database dump and get that SQL where you can do it without the data um but sometimes it's something like this would be a little bit better you know approach where you can bring it in clean into your development environment make some tweaks if you need to and then you know export it or Point your documentation tool towards it so that was sort of a you know there's a lot of high level stuff there but the you know with the key being to sort of highlight some of the things to think about if you want to synchronize or if you're putting together processes for promoting databases through your your system and so that being said questions comments so have you actually looked at something called liquid base which might kind of simplify that I don't know if it's just Java based only but we use Liquid based in that allows you to basically build the data or I guess the table structures in that in the code so every time you deploy it deploys the SQL yeah there's I mean there are tools out there that do stuff like this um and it really is it's a if you're doing it right from the start you can like because liquid base is a great tool for that and you can also do reverse engineering uh like we mentioned right Django is one if you're using some of the tools out there like I think even hibernate was one that had um ways to generate a database I know there's other you know object relational mappers like that where it may if you do it right from the start then it shouldn't be an issue you should have scripts and changes and you know migration steps and things like that that allow you to maintain your database but this is also dealing with data and things like that that look like a liquid base is really a structural it's much more structural thing and not a way to potentially copy data so you know it's this is really like taking well you can use the first bits of this and use it strictly for structural content the challenge becomes when you want to do more than this when you want to actually have data to move from one place to another it's like how do I how do I do that it's not like copying a file from you know one system to another because now you're like grabbing just bits and pieces and not the whole thing or at least you know if you do the whole thing it's it's like dump and restore if you want to do pieces of it it gets a little more complicated yeah that makes sense yeah good point though I mean because like there are some really cool tools out there for that piece of it other questions and comments only other thing is uh have you also thought about moving your SQL out into like a properties file and load it in that way uh I'm not sure I understand what do you do so like within your python code you had all the different selects inputs and all that if you uh put that into like a properties file you could inject your SQL uh wherever you wanted kind of like a store procedure and then just uh update tokens or update uh you know the inject the data you need uh that's sort of what it that's sort of what it is if you um did it come up okay so like for example here these are you could this this could easily be that's that would be a way to do it because right now and it's to get into a little bit is it like this is the MySQL synchronized script so it has uh my SQL specific stuff the thought being that if I had a Oracle then it would be the same stuff but it would be uh you know oracle synchronized and then I'd do the Oracle specific code within this now I could within here say each of these and say hey this is going to be a um you know I could extract that out and say hey this is you know property X for my SQL and I could just swap out my settings files it's you could do that but it seems like it's an extra level that doesn't uh that wouldn't make I don't know I'm gonna say I was gonna say it wouldn't make it as readable um because it's nice to be able to see right here it's like oh here's what it's doing you know this is where I'm getting the data this isn't data I'm pulling from it um and I'm not reusing these too terribly often um so it's yeah it could be extracted out I'm not sure that that's I'm not sure if I if I see enough of a benefit for it other than if you want to do it a little differently as far as how you um are able to abstract and set stuff up I mean in this case I'm it's done through like hey I've got I'll have the thought is I would have different classes for different databases versus just different setting files for different databases which would be possible for itself as well right no I was just looking at your Source destination it's like you're duplicating the SQL so why not just put all the SQL in one class and that like here here's all your here's all the SQL in one place and you just go read it there and then you apply it wherever you need to apply it that's true and that may be um you know that maybe in particular because it's like it's usually in the same place but you do see there are like duplicates here so it's like the you know the really the difference between these two are just the name um so it could be something like just you know slap that in there and then give it a different uh like create that as a template and then slap the names in um and maybe it could be reused in places and that may be something that's part of the cleanup that ends up coming as we get further into it is it maybe like hey let's go ahead and take some of these things where we see repeated SQL and clean that up and make that you know give that a Slicker little interface so good point yeah the reason I mentioned that is because you said like that's for my sequel well if you want to go to Oracle some of those queries might change but you could essentially take the same queries just tweak them for Oracle and then just apply it to a different utility yeah and that may be something too where it's one of those where it's useful sort of like a um a resource bundle kind of approach is that even though it's different classes and then if you take the um if you take those seek those SQL things and say hey these These are the quick and take all those and Abstract them up to you know like up on the class level and hear you know where you'd have like you know uh uh so you'd have like well like SQL one SQL two whatever it is whatever you call those and so then if you're when you create the Oracle synchronized class then you just you see right there here's where all my SQL is so I can just go right here and go through my say the SQL and change it over to use Oracle stuff but the thought also is that Oracle or SQL Server is going to be different enough that you're probably going to have to change several of the things that are done in particular here because you're going through and you're like you know you're pulling results out and then it's those specific results which may or may not look the same if you do it in Oracle because it was left to be more of a a logical approach but there's definitely some you know there's definitely stuff like that that structurally could make it easier to uh maintain and extend other good questions and comments going once all right hearing none so we learned uh there is value which I think we probably knew it before we came in here there's some very good value to being able to synchronize databases uh particularly essentially on the Fly uh they can also be tedious and complicated that's why the best solution is to consider these things from the start and to with your databases also have just like you would any other source code a uh some development procedures and standards that say this is how we do it this is how we Version Control it this is how we store it this is how we propagate it so that bringing in a new developer it's just a matter of say Hey you know go to this repository download this stuff execute these things and boom you're off and running uh particularly when you get in the ideas of testing and regression testing then it it may be where it's just like hey you know go load this database kick off these regression tests part of what they do is they build out all your data and you're you're off and running and if you're in that kind of environment it's actually really nice to be able to essentially create on the fly from scratch a database that's got enough data to do some testing to do some development and then it can wipe it out afterwards or you can utilize it to do your uh your basic you know development and and have something that's data on the back end that's small and not you know burying your your whatever your storage devices there's also levels of synchronization and modeling that you you need to think about is do I care about um synchronizing to development my indexes and and some of the things that are really more performance related uh do I need all the data or just some of the data can I just make it up or do I really want to pull data from my source um you can do it simply and and not have as many of those details that you're going to worry about but if you do and it could be very quick to do so and make it easy to do a quick uh replication or you know shadow of a database but sometimes because you're not limiting the data that's brought across it could take up too much space or it may be limiting because it doesn't have you're not syncing the things that are suddenly an issue because things like foreign keys and uh indexes and store procedures obviously can be critical to your application running correctly so once again thank you for your time for for going through all of this stuff for you know just spending some time to to work on becoming a better developer if you have any questions or comments or anything that you missed that you know like just go through more you can always reach us email info at developmentor.com we have a contact us form out on developreneur.com YouTube there's a link here and there'll be in the show notes YouTube is adding things called handles and we have the developreneur handle so as that which would be like at developing or sort of like a handle that you would have on Twitter or something like that as those become uh fully functional and available that'll be probably an easier way to get a hold of our page uh we're out on Vimeo vimeo.com developmentor we've got a Facebook page the developer page there and twitter.com at developmentor where we do our regular posts and updates and things of that nature so thank you for your time again hope you go out there and have yourself a great day and I just remember you know this is time you're investing in yourself our goal is making every developer better and you are helping not only make every other developer better but yourself as well have a good day thank you
Transcript Segments
thank you
[Music]
and it becomes which is why it became a
really fun combo project because while
there's a lot of little pythony things
that we ran into
uh really a lot of it is a is an
exploration of what
my sequel in particular
um stores and where it stores it about
the database itself and it it's
different if you're in Oracle or uh SQL
server or db2 but generally speaking the
problem the uh
the properties and details do exist
within the database itself so you can
query it almost in a reflection kind of
mode create about it so that you can
actually pull that and with some thought
you generate SQL and then execute that
SQL against your uh
whatever your target or your your
destination is and what you end up
getting is something where you know
probably what you're what you want to do
and what you want to think through if
you're going to synchronize your
databases how
how detailed do I want it to be is it
good enough for me to have just the
tables the tables the table structures
and the stored procedures and if the
tables are basically all empty of data
is that okay in some cases it is because
you can you can go in and maybe follow
it up with some little like quick uh
test script or something like that
particularly if you have automated
testing and regression testing then it
may be something where you say hey all I
need to do is just have an empty
database
that I'm going to create and then I'm
going to run this test script and it's
going to put some data in there for me
and I can maybe even script a couple of
bonus things there
and that becomes extremely useful
if you run in a situation where which
I've been to a couple of those where
you're at a job you're on a project and
you say hey I want to be able to like
see the I want to be able to create from
scratch the database and somebody says
oh that doesn't exist we don't have that
SQL
now you can in some cases you can do a
database dump
and get that SQL where you can do it
without the data
um but sometimes it's something like
this would be a little bit better you
know approach where you can bring it in
clean into your development environment
make some tweaks if you need to and then
you know export it or Point your
documentation tool towards it
so
that was sort of a you know there's a
lot of high level stuff there but the
you know with the key being to sort of
highlight
some of the things to think about if you
want to synchronize or if you're putting
together processes for promoting
databases through your your system
and so that being said questions
comments
so have you actually looked at something
called liquid base which might
kind of simplify that I don't know if
it's just Java based only but we use
Liquid based in that allows you to
basically build the
data or I guess the table structures in
that in the code so every time you
deploy it deploys the SQL
yeah there's I mean there are tools out
there that
do stuff like this
um
and it really is it's a
if you're doing it right from the start
you can like because liquid base is a
great tool for that and you can also do
reverse engineering uh like we mentioned
right Django is one if you're using some
of the tools out there like I think even
hibernate was one that had
um ways to generate a database I know
there's other you know object relational
mappers like that where
it may if you do it right from the start
then it shouldn't be an issue you should
have scripts and changes and you know
migration steps and things like that
that allow you to
maintain your database but this is also
dealing with data and things like that
that look like a liquid base is really a
structural it's much more structural
thing
and not a way to potentially copy data
so you know it's this is really like
taking well you can use the first bits
of this and use it strictly for
structural content the challenge becomes
when you want to do more than this when
you want to actually have data to move
from one place to another it's like how
do I how do I do that it's not like
copying a file from you know one system
to another because now you're like
grabbing just bits and pieces and not
the whole thing or at least you know if
you do the whole thing it's it's like
dump and restore if you want to do
pieces of it it gets a little more
complicated
yeah that makes sense yeah good point
though I mean because like there are
some really cool tools out there for
that piece of it
other questions and comments
only other thing is uh have you also
thought about moving your SQL out into
like a properties file and load it in
that way
uh I'm not sure I understand what do you
do so like within your python code you
had all the different selects inputs and
all that if you uh put that into like a
properties file you could inject your
SQL
uh wherever you wanted kind of like a
store procedure and then just uh update
tokens or update uh you know the inject
the data you need
uh that's sort of what it
that's sort of what it is if you um
did it come up okay so like for example
here these are
you could this this could easily be
that's that would be a way to do it
because right now and it's to get into a
little bit is it like this is the MySQL
synchronized script so it has uh my SQL
specific stuff
the thought being that if I had a Oracle
then it would be the same stuff but it
would be uh you know oracle synchronized
and then I'd do the Oracle specific code
within this now I could within here say
each of these and say hey this is going
to be a
um you know I could extract that out and
say hey this is you know property X for
my SQL and I could just swap out my
settings files
it's you could do that but it seems like
it's an extra level that doesn't uh that
wouldn't make I don't know I'm gonna say
I was gonna say it wouldn't make it as
readable
um because it's nice to be able to see
right here it's like oh here's what it's
doing you know this is where I'm getting
the data this isn't data I'm pulling
from it
um
and I'm not reusing these too terribly
often
um so it's
yeah it could be extracted out I'm not
sure that that's I'm not sure if I if I
see enough of a benefit for it other
than if you want to do it a little
differently as far as how you
um are able to abstract and set stuff up
I mean in this case I'm it's done
through like hey I've got I'll have the
thought is I would have different
classes for different databases versus
just different setting files for
different databases which would be
possible for itself as well
right no I was just looking at your
Source destination it's like you're
duplicating the SQL so why not just put
all the SQL in one class and that like
here here's all your
here's all the SQL
in one place and you just go read it
there and then you apply it wherever you
need to apply it that's true and that
may be
um
you know that maybe in particular
because it's like
it's usually in the same place but you
do see there are like duplicates here so
it's like the you know the really the
difference between these two are just
the name
um so it could be something like just
you know slap that in there and then
give it a different uh like create that
as a template and then slap the names in
um and maybe it could be reused in
places and that may be something that's
part of the cleanup that ends up coming
as we get further into it is it maybe
like hey let's go ahead and take some of
these things where we see repeated SQL
and clean that up and make that you know
give that a Slicker little interface so
good point yeah the reason I mentioned
that is because you said like that's for
my sequel well if you want to go to
Oracle some of those queries might
change but you could essentially take
the same queries just tweak them for
Oracle and then just apply it to a
different utility
yeah and that may be something too where
it's one of those where it's useful sort
of like a um a resource bundle kind of
approach is that even though it's
different classes and then if you take
the
um if you take those seek those SQL
things and say hey these These are the
quick and take all those and Abstract
them up to you know like up on the class
level and hear you know where you'd have
like you know uh uh so you'd have like
well like SQL one SQL two whatever it is
whatever you call those and so then if
you're when you
create the Oracle synchronized class
then you just you see right there here's
where all my SQL is so I can just go
right here and go through my say the SQL
and change it over to use Oracle stuff
but the thought also is that Oracle or
SQL Server is going to be different
enough that you're probably going to
have to change several of the things
that are done in particular here because
you're going through and you're like you
know you're pulling results out and then
it's those specific results which may or
may not look the same if you do it in
Oracle because it was left to be more of
a
a logical approach but there's
definitely some you know there's
definitely stuff like that that
structurally could make it easier to uh
maintain and extend
other good questions and comments
going once
all right hearing none
so we learned uh there is value which I
think we probably knew it before we came
in here there's some very good value to
being able to synchronize databases uh
particularly essentially on the Fly
uh they can also be tedious and
complicated that's why the best solution
is to consider these things from the
start and to with your databases also
have just like you would any other
source code a uh some development
procedures and standards that say this
is how we do it this is how we Version
Control it this is how we store it this
is how we propagate it so that
bringing in a new developer it's just a
matter of say Hey you know go to this
repository download this stuff execute
these things and boom you're off and
running uh particularly when you get in
the ideas of testing and regression
testing then it it may be where it's
just like hey you know go load this
database kick off these regression tests
part of what they do is they build out
all your data and you're you're off and
running and if you're in that kind of
environment it's actually really nice to
be able to
essentially create on the fly from
scratch a database that's got enough
data to do some testing to do some
development and then it can wipe it out
afterwards or you can utilize it to do
your uh your basic you know development
and and have something that's data on
the back end that's small and not you
know burying your your whatever your
storage devices
there's also levels of synchronization
and modeling that you you need to think
about is do I care about
um synchronizing to development my
indexes and and some of the things that
are really more performance related uh
do I need all the data or just some of
the data
can I just make it up or do I really
want to pull data from my source
um you can do it simply
and and not have as many of those
details that you're going to worry about
but if you do and it could be very quick
to do so and make it easy to do a quick
uh replication or you know shadow of a
database
but sometimes because you're not
limiting the data that's brought across
it could take up too much space or
it may be limiting because it doesn't
have you're not syncing the things that
are suddenly an issue because things
like foreign keys and uh indexes and
store procedures obviously can be
critical
to your application running correctly
so once again thank you for your time
for for going through all of this stuff
for you know just spending some time to
to work on becoming a better developer
if you have any questions or comments or
anything that you missed that you know
like just go through more you can always
reach us email info at developmentor.com
we have a contact us form out on
developreneur.com
YouTube there's a link here and there'll
be in the show notes YouTube is adding
things called handles and we have the
developreneur handle so as that which
would be like at developing or sort of
like a handle that you would have on
Twitter or something like that
as those become uh fully functional and
available that'll be probably an easier
way to get a hold of our page uh we're
out on Vimeo vimeo.com developmentor
we've got a Facebook page the developer
page there and twitter.com at
developmentor where we do our regular
posts and updates and things of that
nature
so thank you for your time again hope
you go out there and have yourself a
great day and I just remember you know
this is time you're investing in
yourself our goal is making every
developer better and you are helping not
only make every other developer better
but yourself as well
have a good day
thank you