foreign
here for infoworld in this episode of smart go I'm going to take a look at gorm an orm
or object relation mapping package for go an orm is a way to work with databases that
doesn't involve writing queries by hand you use native metaphors in whatever language
you're writing in to describe the data that you want to store and how to work with that data
Gorham is one of a number of orms for go but it stands out for a couple of reasons one it's
compatible with a wide variety of common databases and two it's very easy to set up and get running
in this example I'm going to use gorm to work with a local sqlite database Gorham also works
with mySQL postgresql and a number of other popular databases but we'll be using sqlite for
this demo because it's convenient and fast enough for our use the first thing we do with a Gorham
power database is create a model for the data how we want to represent the databases data in go so
for that we create a struct and the first field of that struct is going to be the gorham.modelobject
we then add other fields like string for freeform text or unsigned int for numbers
we can also use tags on these fields to tell Gorham how to handle them for instance the name
field here has the unique index constraint on it and the year field is non-nullable so when
we create a record that means that the name field will be indexed in such a way that there could be
no duplicates in that particular table and the year field must be provided now each database
has its own peculiar way of handling these things so one of the reasons you would use
something like Gorham is to abstract away all the database specific details about how to do that
also one of the other things one of the other things Gorham does for you is automatically add
elements common to most database tables such as an ID column and also time stamps for when
rows are created updated or deleted these are all implicit in the gorham.model struct but you
can opt out or rename those fields if you need to for this exercise that we're going to leave
them in because they are useful next we come to the body of the program where we initialize
the database and create the tables inside it and that's done through these commands
gorm.open which returns a database object DB and db.automigrate where we pass in the structures
that we want to either initialize or migrate from an earlier version of the same database
now let's create some actual data to do this we've created instance of the movie struct we populate
its fields and we pass that to the db.create method now we've created three movies here but
there's a couple of things wrong with them the second movie doesn't have the correct year and
the third movie doesn't even exist at all we're going to do something about each of these things
in a little bit next we perform a query we create an empty instance of movie to receive the data
for the query and we use DB DOT first to get the first row in that table ordered by ID if we were
performing a query that returned multiple rows by the way we would use a slice of instances of movie
but what would be more useful is to perform an actual query to do more than just retrieve the
first records that are available and that's what this next section here is for so we clear out the
contents of the movie variable and we use DB dot where to construct a query and in this query we're
going to look for something with a field name matches Blade Runner and so we use this syntax
we use the where Clause as it might appear in a conventional SQL query and we use a question mark
for a data placeholder and then after that the data that we will provide for those placeholders
and so from that we will get the first record returned and then we will place that into the
movie variable now you might be wondering why did we clear out the movie variable before making
another request well one of the things about gorm that's worth knowing is that when you use a model
based struct as the receiver for a query if you have any primary key data already in that struct
it will be used to construct the query when you make the query
so in this next example I take the movie.id field which is the primary key for this table and I set
it to zero before using it to receive the next query I could do this or I could replace the
struct with a completely new empty instance if I wanted to either way I get the same results I
get a completely new query instead of one that may be based on the existing ID that is already
lingering inside movie so this is something you need to be aware of in gorm if you're reusing the
same struct object to receive multiple queries you can also explicitly use a struct for querying and
another struct to receive the results of the query so for instance in this example we zero out movie
which will receive the results of the query and we use movie query to actually perform the query
now one of the other things that we're doing here is updating an existing row we take our
movie struct and we use its update method to change the year for Blade Runner 2049
to the correct year that the movie was released 2017.
and finally we have a query where we search for movie by its year 2084. that brings us the bogus
Blade Runner 2084 movie that we mentioned before which doesn't exist and we don't want
that in our database so we'll get rid of that we're going to use the delete method to do that
and that brings me to an important thing to understand about deleting in gorm
by default gorm performs what are called Soft deletes remember earlier how I mentioned tables
and gorm automatically have time stamps for creating updating and deleting rows
well when you delete a row the default behavior is not to erase the row from the database instead
it's to fill in the deleted timestamp to indicate when the row was deleted any queries performed on
tables through gorm will automatically leave out any rows that have been marked as deleted
any rows that have a deleted timestamp that's not null
this is a reflection of of current best practices with databases you want to Mark things for
deletion rather than hard delete them this lets you roll back deletes if they're made by mistake
and it lets you perform hard deletes later on down the line say after a number of days when
you've confirmed that everything that you have marked for deletion should in fact be removed
now if you do want to perform hard deletes where rows are removed entirely
then you use a different command you use db.unscoped.delete as I have indicated here
now when we run this code we're going to get output to the console for all our queries when
we query the first row in our movie tables we get a row that has the id1 that's the first row
that we inserted the movie Blade Runner when we query for that row by name we get that row again
and next we query for a different movie one with the wrong data which we then
update with the correct data and we re-query and then finally we have our delete action
now Gorham also provides ways to perform lots of other common database query and update actions but
for the sake of brevity I'm only going to look at one more of them retrieving data from two
tables that have a join so here in this revised example I have a movie table and a role table
the roles that are that are performed by actors in the movie and I'm populating the
first one with a row for a given movie and then I add rows in the
second table that link back to the first one a one-to-many relationship as this is called
so when I want to query the movie and get all of the related roles with it as well we use
gorm's preload method to fetch the data from roles that would match what we would also find in movie
as an aside again when we retrieve multiple rows with a query as we do here with movie.rolls they
just show up as a slice of structs and so we can iterate over them as we would any other slice
as we can see when we run this code and we generate the results in the console
so to sum up Gorham provides us with programmatic ways to work with databases as orms do generally
it uses goes struct type as the basic way to describe database tables and to provide a place to
store returned query data it also includes ways to express things that are specific to databases but
can be abstracted away through the orm interface such as how to do things like require that
particular columns be populated in a row so that's it for this video if you like it please leave a
comment below and don't forget to subscribe to the tech talk Channel on YouTube and for more smart go
and more Dev with sertar be sure to follow me on Facebook YouTube and infoworld.com foreign