Detailed Notes
Database testing is fraught with challenges. We have to find new ways to create a database replica, match the data in size and meaning as well as validate changes. These obstacles add up to this sort of testing requires automation and careful consideration. In this session, we look at the pitfalls and ways to properly test and validate your database.
Database Testing And Automation We cover this topic through looking at the issues, looking at the basic tools, and then what automation is needed. The discussion stays surface level and does not go deep into any specific tools. That way you can find this presentation useful no matter which database or environment you find your self facing.
The Mentor-Mastermind Group This series comes from our mentoring/mastermind classes. These classes are virtual meetings that focus on how to improve our technical skills and build our businesses. The goals of each member vary. However, this diversity makes for great discussions and a ton of educational value every time we meet. We hope you enjoy viewing this series as much as we enjoy creating it. As always, this may not be all new to you, but we hope it helps you be a better developer. Drop us a line to find out when the next one is so you can join our group.
Transcript Text
[Music] so key steps if you're trying to test a database some of the key things that we've already started talked about but i want to make sure that we laid these out in a little shorter sort of summary you need to verify that the data and require relationships are correct so again the idea of if i save something i want to be able to load it back and it be the same what i what i save i need to be able to get the same thing back in a load we need to test the key the three key actions are creating updating and deleting data so those need to be in our mind as we are testing is what happens when i create a record if it's if that's feasible if that's possible what happens when i update a record if that's possible and what happens when i delete data that's possible and deleting can be a little bit of a challenge because in some cases it deletes and removes that data in some cases we just have some sort of a value like a you know is deleted flag that you have to check to see whether the data was technically deleted or not uh or maybe like an active flag or something like that so deleting maybe actually remove the data but it may also be just marking data accordingly from a testing point of view it doesn't matter too much other than you just need to know what does a deleted record look like or i guess not look like if it doesn't exist once we run our tests we need to be able to have some way to clean up so that we can that we're not doing essentially damage to the data we're doing our testing and then the challenge that often is reproducing those tests is building our regression scripts sometimes we can do those within uh normal means like maybe a web interface we can use like a selenium or something like that sometimes we're going to have to write code down at the whatever level it is you know whether it's a c sharp app or java app or maybe even within the database itself we may have some scripts that are run that essentially go through and for example in some cases you have scripts that your test user ids are known or you're consistent with those and so this there's a script that you can run after your test that goes out and finds everything that was touched by those user ids and cleans it all up and varies from from situation situation but that's again where you know it's just it's a little bit extra challenge for the database to to get those regression pieces done it's not just regression against your test but now i need to be able to do a regression of cleaning up again i got to mention if you could just do an image restore so just back it up run your test and then restore the backup that's that's like ideal that's going to be the easiest way to ensure that you didn't miss something going through a whole bunch we've had already some good questions and comments along the way but now i want to open it up for anything else questions comments on this this might not be related to this topic here but um since i'm working with the database do that the dba the database administrators uh can they write sql or can i mean are they very good with the volume or vice verse candy sql developer be a dba uh they can be sometime but it tends to the those two roles tend to be slightly different typically a database developer both of them are going to be able to write sql probably quite a bit of it database developers are more likely going to be able to work with saving and loading and updating data dbas are more about being able to create the structures you create a table sometimes they're they're the ones that really do the creation of triggers and constraints where database developers may create stored procedures okay dbas often have more of the actual database engine configuration skills so they it's not just what's the the logical database how you interact with code but the physical database of where does that live on you know what server is that on how is that actually configured within the server and the storage and so it's a it's a little bit more of a dbas are a little bit more of a configuration type of job whereas the database developers are more using the database as opposed to dba that's creating the database if that if that helps clarify it maybe a little bit okay other questions and comments thank you excellent all right cool so we will move here so what do we learn hopefully ideally uh one and i think we knew that it's one of those things that i don't know if we learned this i think everybody sort of knows it at some level from the start database testing has got unique challenges it is different from code it's as we've listed it out it's it's not the same so being able to test the database is a whole different level or style of testing we're often going to need to learn about the requirements beyond just the records of the data we've talked about all these relationships and constraints and triggers and permissions and all that kind of good stuff that we're going to have to make sure that the requirements are you know cover those that they are at that level of detail if we can use images with stage images or testing or whatever you want to call them of the database itself database snapshots if we use those they're gonna be a lot easier for us to uh to restore and then keep our fingerprints off of the database in the world of cloud that is it can be expensive but sometimes that's a nice thing is that there's it's easier to spin up uh just like it is virtual machines it's easier to spin up a replica replication of that database that you can go test and then you can just drop it delete it when you're done there's a lot of scripting that we can use as part of dealing with our our database testing but we need to be thorough about it we need to remember that when i write a script i'm making these changes and i need to be able to unmake them uh and or clean up afterwards and also i need to be which is really just dealing with databases in general we need to make sure that we are particularly with the script we're affecting or impacting the things only that we want to impact um it's you know this is a little bit of really more database developer vba kinds of concerns but you know if you delete a record you don't want to delete all the records in the table you've got usually you know a specific record or specific types of records that you want to delete in database testing is the same thing as we need to be very aware of the impact and the scope of a script or an action that we're taking as always thank you for your time for for listening for you know the questions the feedback if you have any others as you know we always do at two in the morning you sometimes have that thought that says oh rats i wanted to ask this question you can reach us you can send us an email at info developer.com you can put a form entry for the contact us out on developer.com you can always check us out follow us tweet us whatever at developerdoor out on twitter and then we do still have a facebook page facebook.com developer norm we just want to keep working on these things spending our time investing time getting together so that every we can make every developer better uh everybody wants to join us uh whether it's us or those that we work we want to make everybody a little bit better thanks a lot so dave in particular because you mention this is that was this was that a helpful that was sort of the kind of thing you were hoping to to get that sort of a presentation oh yeah yeah definitely yeah i i think it gives me a broader view about what goes into the database and yeah okay good that was that was like i said you were sort of the primary customer for that so i want to make sure that that did cover so some of the stuff that you were looking to get to yeah he did he did good listen are we good for the next one to be two weeks it'll be february the 5th yeah um february what the fifth yep yeah i'm good i'm good with that and i think we get to [Music] michael is doing uh now that we have our handy dandy schedule here so that's how you get rest assured i think is an extra one we've got on our list there now i was going to ask you are you going to do um i mean do another series on database testing like um i mean the approach because i know usually as a test i usually i end up getting uh just only the view view access yeah there's we'll probably touch that a little bit i'm gonna get into and that's not going to come from and it'll be a few weeks where it starts showing up okay there's going to be a youtube series that will be it's basically it's on really focused on database development and there's going to be some of the there's going to be some scripting and some automation and some things like that that will be part of those uh that series um i'm trying to think i'm not sure where else we've actually spent much time in it so that's probably gonna be the first time we have some we go a little deeper into some of those pieces but that and i'll mention that as we get you know closer into that so probably the next or maybe two mentor sessions out it's probably about the time that we'll start seeing that okay and you're good for doing the rest of shirt next time right michael i think you said you're pretty close on that yeah i should be ready for that um also i think if you go back to building the product catalog i think i had some database examples in there i think you did i think if you go back yeah building a beta database uh what do we call that but it's basically that those go way back those are out on vimeo they're also on you can probably search um i think it was the if you go look at like the become a better developer oh here it is build a picture yeah build a product okay if you go look at that one then you'll see that we had um yeah there we go is that there's a whole series and it included i think there was like one [Music] uh testing our application but i think there was one contest in the database as well um yeah so there's a couple that are specifically related to that data so that may help you as well david by the way i apparently my mic hiccup during the presentation i had a quick comment on when you were talking about the timestamp so one thing to be careful of when dealing with dates and times especially with databases so when you store your date into the database you need to be conscious of or at least have your requirements as to what date time and time zone if you're dealing with us-based dates because if you just store the date and then pull the date out and try to display it if you're in a different time zone if you're not converting that date properly the date that you're storing or the date that you're pointing out is not going to match yeah and that's actually that's that's a good point and that's not even that's not just us that's just any time zone data is making sure that the the conversions as needed or are done accordingly because you may have and particularly this goes with cloud computing you may save your date you may have two different servers that things get saved on and if they if there isn't you know utc time or something to tie those stamps together then it can break some of your that's that's one of the relationships the data integrity issues you have to worry about is making sure that if i have a time stamp on one record that says that i did it at nine o'clock today then the other record should also say that that update was done at nine o'clock today not at you know ten o'clock or eight o'clock or something like that which you can run into when you don't have uh consistent timestamp zones across the data and more and more you're seeing that it will happen is the data will be stored in utc time and then the uh the loads will convert data or the display will convert it to your whatever your specific time zone is so good point time is one of those time stamps uh dates and times is one of the common data issues that you run into or confusions you run into yeah we're dealing with that at work right now yeah that's a that's again it's a good point definitely one that we run into on a regular basis any other questions comments anything else we want to talk about going once going twice well excellent then we basically on time and uh we'll wrap this one up and we'll talk to you guys again in a couple weeks you
Transcript Segments
[Music]
so key steps if you're trying to test a
database
some of the key things that we've
already started talked about but i want
to make sure that
we laid these out in a little shorter
sort of summary
you need to verify that the data and
require relationships are correct
so again the idea of if i save something
i want to be able to load it back and
it be the same what i what i save i need
to be able to get the same thing back in
a load
we need to test the key the three key
actions are creating updating and
deleting data so those need to be in our
mind as we are testing
is what happens when i create a record
if it's
if that's feasible if that's possible
what happens when i update a record if
that's possible and what happens when i
delete data that's possible
and deleting
can be a little bit of a challenge
because in some cases
it deletes and removes that data in some
cases we just have some sort of a value
like a you know is deleted flag that you
have to check to see whether the data
was technically deleted or not
uh or maybe like an active flag or
something like that so deleting maybe
actually remove the data but it may also
be just marking data accordingly
from a testing point of view
it doesn't matter too much other than
you just need to know what does a
deleted record
look like or i guess not look like if it
doesn't exist
once we run our tests we need to be able
to have some way to clean up
so that we can
that we're not doing essentially damage
to the data we're
doing our testing
and then the challenge that often is
reproducing those tests is building our
regression scripts
sometimes we can do those within
uh normal means like maybe a web
interface we can use like a selenium or
something like that
sometimes we're going to have to write
code down at the
whatever level it is you know whether
it's a c sharp app or java app or maybe
even within the database itself we may
have some scripts that are run
that essentially go through and
for example in some cases
you have scripts that
your test user ids
are
known or you're consistent with those
and so this there's a script that you
can run after your test that goes out
and finds everything that was touched by
those user ids and cleans it all up
and varies from from situation situation
but
that's again where
you know it's just it's a little bit
extra challenge for the database to
to get those regression pieces done it's
not just regression against your test
but now i need to be able to do a
regression of cleaning up
again i got to mention if you could just
do an image restore so just back it up
run your test and then restore the
backup
that's
that's like ideal that's going to be the
easiest way to ensure that you didn't
miss something
going through a whole bunch we've had
already some good questions and comments
along the way but now i want to open it
up for anything else questions comments
on this
this might not be related to this topic
here but um
since i'm working with the database
do that the dba the database
administrators
uh
can they write
sql or can i mean are they very good
with the volume or vice verse candy sql
developer be a dba
uh
they can be sometime but it tends to the
those two roles
tend to be slightly different typically
a database developer
both of them are going to be able to
write sql
probably quite a bit of it database
developers are more likely going to be
able to
work with saving and loading and
updating
data dbas
are more about
being able to create the structures you
create a table
sometimes they're they're the ones that
really do the creation of triggers and
constraints
where database developers may create
stored procedures
okay dbas
often have more of the
actual database engine configuration
skills so they it's not just what's the
the logical database how you interact
with code but the physical database of
where does that
live on you know what server is that on
how is that actually configured within
the server and the storage
and so it's a
it's a little bit more of a dbas are a
little bit more of a configuration type
of job whereas the database developers
are more using the database
as opposed to dba that's creating the
database if that if that helps clarify
it maybe a little bit
okay
other questions and comments
thank you
excellent all right cool so we will move
here
so what do we learn hopefully ideally uh
one and i think we knew that it's one of
those things that
i don't know if we learned this i think
everybody sort of knows it at some level
from the start database testing has got
unique challenges it is different from
code
it's
as we've listed it out it's it's not the
same so being able to test the database
is a whole different
level or style of testing
we're often going to need to learn about
the requirements beyond just the records
of the data we've talked about all these
relationships and constraints and
triggers and permissions and all that
kind of good stuff
that we're going to have to
make sure that the requirements are
you know cover those that they
are at that level of detail
if we can use images with stage images
or testing or whatever you want to call
them
of the database itself
database snapshots
if we use those they're gonna be a lot
easier for us to
uh to restore and then
keep our fingerprints off of the
database
in the world of cloud that is
it can be expensive but sometimes that's
a nice thing is that there's it's easier
to spin up uh just like it is virtual
machines it's easier to spin up a
replica replication of that database
that you can go test and then you can
just drop it delete it when you're done
there's a lot of scripting that we can
use as part of dealing with
our our database testing
but
we need to
be thorough about it we need to remember
that when i write a script i'm making
these changes and i need to be able to
unmake them
uh and or clean up afterwards and also i
need to be which is
really just dealing with databases in
general we need to make sure that we are
particularly with the script we're
affecting or impacting the things only
that we want to impact
um it's
you know this is a little bit of really
more database developer vba kinds of
concerns but you know if you delete a
record you don't want to delete all the
records in the table you've got usually
you know a specific record or specific
types of records that you want to delete
in database testing is the same thing as
we need to be very aware of the impact
and the scope of
a script or an action that we're
taking
as always thank you for your time for
for listening for
you know the questions the feedback
if you have any others as you know we
always do at two in the morning you
sometimes have that thought that says oh
rats i wanted to ask this question
you can reach us you can send us an
email at info developer.com
you can put a
form entry for the contact us out on
developer.com you can always check us
out follow us tweet us whatever at
developerdoor out on twitter
and then we do still have a facebook
page facebook.com developer norm
we just want to keep working on these
things spending our time investing time
getting together
so that every we can make every
developer better uh everybody wants to
join us uh whether it's us or those that
we work we want to make everybody a
little bit better
thanks a lot
so dave in particular because you
mention this is that was this was that a
helpful that was sort of the kind of
thing you were hoping to to get that
sort of a presentation oh yeah yeah
definitely yeah i i think it gives me a
broader view about
what goes into the database and
yeah
okay good that was that was like i said
you were sort of the primary customer
for that so i want to make sure that
that did cover so some of the stuff that
you were looking to get to yeah he did
he did
good listen
are we good for the next one to be
two weeks it'll be february the 5th
yeah um february what the fifth yep yeah
i'm good i'm good with that and i think
we get to
[Music]
michael is doing
uh
now that we have our handy dandy
schedule here
so that's how you get rest assured i
think is an extra one we've got on our
list there
now i was going to ask you are you going
to do
um
i mean
do another series on database testing
like um i mean the approach because i
know usually as a test i usually i end
up getting uh just only the view view
access
yeah there's
we'll probably touch that a little bit
i'm gonna get into
and that's not going to come from and
it'll be a few weeks where it starts
showing up okay there's going to be a
youtube series that will be it's
basically it's on really focused on
database development and there's going
to be some of the there's going to be
some scripting and some automation and
some things like that that will be
part of those uh that series
um
i'm trying to think
i'm not sure where else we've actually
spent much time in it so that's probably
gonna be the first time we have some we
go a little deeper into
some of those pieces
but that and i'll mention that as we get
you know closer into that so probably
the next or maybe
two mentor sessions out it's probably
about the time that we'll start seeing
that
okay
and you're good for doing the rest of
shirt next time right michael i think
you said you're pretty close on that
yeah i should be ready for that um
also
i think if you go back to building the
product catalog i think i had some
database examples in there
i think you did
i think if you go back yeah building a
beta database
uh what do we call that but it's
basically that those go way back those
are out on vimeo they're also on you can
probably search
um
i think it was the
if you go look at like the become a
better developer oh here it is build a
picture yeah build a product
okay if you go look at that one then
you'll see that we had um
yeah there we go is that there's a whole
series and it included i think there was
like one
[Music]
uh testing our application but i think
there was one contest in the database as
well
um
yeah so there's a couple that are
specifically related to that data so
that may help you as well
david
by the way i apparently my mic hiccup
during the presentation i had a quick
comment on when you were talking about
the timestamp
so one thing to be careful of when
dealing with dates and times especially
with databases so when you store your
date into the database you need to be
conscious of or at least have your
requirements as to what
date time and time zone if you're
dealing with us-based dates
because if you just store the date and
then pull the date out
and try to display it if you're in a
different time zone if you're not
converting that date properly the
date that you're storing or the date
that you're pointing out is not going to
match
yeah and that's actually that's that's a
good point and that's not even that's
not just us that's just any time zone
data is making sure that the
the conversions as needed or are done
accordingly because you may have and
particularly this goes with
cloud computing
you may save your date you may have two
different servers that things get saved
on and if they if there isn't you know
utc time or something to tie those
stamps together
then it can break some of your that's
that's one of the relationships the data
integrity issues you have to worry about
is making sure that if i have a
time stamp on one record that says that
i did it at nine o'clock today then the
other record
should also say that that update was
done at nine o'clock today not at you
know ten o'clock or eight o'clock or
something like that which you can run
into when you don't have
uh consistent timestamp zones across the
data
and more and more you're seeing that it
will happen is the data will be stored
in utc time
and then the uh
the loads will convert data or the
display
will convert it to your whatever your
specific time zone is so good point time
is one of those
time stamps uh dates and times is
one of the common
data issues that you run into or
confusions you run into
yeah we're dealing with that at work
right now
yeah that's a that's again it's a good
point definitely one that we run into on
a regular basis
any other questions comments anything
else we want to talk about
going once going twice well excellent
then we
basically on time
and uh we'll wrap this one up and we'll
talk to you guys again in a couple weeks
you