hi there sergeant arigilal here in this episode of smart go we're going to look at
the way go handles working with databases by way of its native database sql package
for the sake of this video i'm going to assume you know something about the basics of how databases
work and how they might be accessible in other languages what i'm focusing on here is how go's
standard library gives you access to databases and what kind of behaviors and patterns to expect
now to use a database and go you need two things one is the database sql package this
provides all the high level abstractions for working with a database like connections and
queries the other is a database driver which sits between the sql package and the database itself
for the sake of this example i'll use mysql as the database since it's common and well
understood and it has a third party go driver available for it on github now note that when
we add the database driver to our application's imports we're eliasing it with an underscore
we don't ever actually interact with the mysql driver so we don't need access to it
in the program's namespace we can use the underscore alias as a way to do just that
the first thing we do with any database is set up the database abstraction object for it and
we do this with the sql open method where we pass in the name of the driver we want to use and the
connection string here i'm using a local instance of mysql with a username and password i don't use
for anything else and i'm accessing the database name test which i've already set up ahead of time
also note that once i've set up the object and check for errors i also set up a deferred close
action this is important because we always want to release database resources when we're done with
them if we mistakenly let databases connections pile up that can cause the database to run out of
open connections and starve on the other hand we don't want to open and close the database object
or the connections more than once for application run because we don't need to do that it'll be a
waste of resources and it'll be slow besides now next we're going to make a query from the database
this database isn't very complex it has one table named users with two columns id and user
so our query string is simple when we use a query object to make a query like this it returns a rows
object which we can iterate over note also that we want to close the rows object when we're done
with it so again we will defer this until exit and after we verify that there are no errors
iterating over a rows object is also pretty simple we have a for loop and the rows next method and
then use the rows.scan method to send the data for each column into the id and user variables
and then finally when we're done iterating we check the rows object for any errors
we do this when we're done because let's say we make a query which fails completely
that failure won't be recorded in checking for errors in any one row because there
might not be any rows so we'll need to make a check for errors on the entire operation
let's run this code and as you can see we only have a few results returned from the database
since this database table doesn't have much data in it that's fine but what if we want to do more
sophisticated querying what for for instance what if we want a query with with parameters
now here's a variant of the same program where we have a parameterized query
it's not hard at all we just use the query method but with placeholder characters in
our query string where we want our parameters to go and the parameters passed in after the query
note that the question mark in the query string is a placeholder for a parameter
that is specific to mysql other database systems may have a different placeholder system
so this is something specific to the database that we're using it's not something that is specific to
the sql package in go now if we run this we will get back a single row now note that if you're only
getting back a single row and you know this you can skip iterating by using a query row object
and just perform scan on that this also cuts down on some of the error handling boilerplate and we
don't need to close the rows object either since we don't have one any errors that we have are
going to be reported immediately now some queries don't actually return rows so they need special
handling for instance insert or update or other modification actions don't return rows with data
instead they return things like the last id that was inserted or the number of rows affected
in cases like this you don't want to use a query you want to use an exec so here's
how this works an exec statement doesn't return rows but instead a sql.result object and result
objects contain metadata about the last operation performed like the last id that was inserted here
and the number of rows affected for updates and deletes so if we run this insert statement
we get back an id number for an inserted row and the total number of rows affected
now because different databases have different capabilities and quirks i've kept this discussion
limited to mysql and to the most common operations you perform with the database but it does give you
an idea of the basic way the sql package works you'll want to set up and close connections and
resources in their proper scopes typically with a defer statement after you've checked for errors
and you'll want to use the right constructions for creating a row set for a single row or for
sending commands that don't even return rows so that's it for this video if you liked it please
leave a comment below don't forget to subscribe to the tech talk channel on youtube and for smart go
and more dev serdar be sure to follow me on facebook youtube and infoworld.com