So sometimes in go, you'll find yourself needing to interface with a
database. May be a small database like SQL light for some sort of local
storage or maybe a large database like say postgres or
MySQL.
Go itself provides a standard interface for dealing with SQL
databases databases such as SQL, which provides the number of functions for opening
and closing databases, doing queries, preparing statements,
executing them Etc, and it's extremely easy to use interface.
All you have to do is include it, import it and also import the
appropriate driver for your database. So to give an example about
how this is pretty easy to do. I've created a little SQL Lite, three
database, and I
So, brittle program, there's a query on it to show you the basics of how this works. So let's just take a
look at it. First of all, you're going to include database
/ sequel. So database SQL, that is the standard go interface for doing
it and an appropriate driver. In this case, I'm using
the go SQL Lite, driver by Matt n, which is very
widely used for sqlite3 and I've prepared a little database
file. It's called scientists dot DB, and if I load it up
into
Sqlite then I can see if we just go
there's a table called names and I can say, I just pull everything out
of there. I've got Charles Babbage, Ada
Lovelace, Michael Faraday, and Brunel in here. So just a few little simple names in the
database and I'm going to open that database
in go and do a query on it just to get everybody out of it. Did give you the
basics of how you do things. So although we're using this particular
driver here, you'll notice this underscore in the
And which indicates that we're not actually directly in this module here using any of
the imports from it. So we don't want to go to complain about it. It's actually database
SQL, which will deal with that. And if we go down, you'll see the way this operates
is that I'm doing a sequel open here.
This can be used to open any type of database. So if you for SQL Lite, you're opening a file path
because it's stored in a file. If you might be connected to say postgres
which is running across a TCP socket, then you would have the appropriate connection.
In here, perhaps with user names passwords the address of where that is this
can be used for any sort of database, you're connecting to and I'm using defer so
that when the program terminates, I close out the database itself.
Then I'm going to do a query. I'm going to do a simple query here. Just using
DB dot query is going to give me the a list of rows back
which I can then iterate over. And I'm also going to close that set of rows
when I'm done and notice the course that because of the ordering of defers, the
Rose will close before the database. So there's
Worried about those being in the wrong order and then I'm just going to do a for
Loop and the for Loop is going to iterate over all the returned Rose.
And for each row, I'm going to use this scan function. It will
match each field. That's been returned against one of
these variables here and if you look in the original
database over here, there's an ID which is just an auto-generated integer and
two strings. So doing this will actually match each row one by
one into those variables.
Owls. And I'm going to feed one, just print out the first name, and last name of the
program of the person, and when the program terminates, The Rose will get close. The database will
get it closed. So we can just go ahead and
build this guy.
Like that and then run it. And there it is done. It is coach to the databases open
did then the query and print it out that information right there. So
that's the very basics of accessing a database be sqlite, or something more
complex, like, postgres from within ago program. So the
standard sort of database, access querying. Updating preparing
statements is pretty obvious and it's the same for every
drive you are using, but it also provides the ability to do.
Actions. I've just modified this slightly so that we have a concept of a
transaction used here. And I also modified the database a little bit. So if we just
take a look and I have a look at the
names, I didn't have a field at the end and Boolean, which is thought of the 0 and 1 in
SQL light, as to whether any of these scientists are still alive. And in
accurately, the database says, the Royal eye towards we're gonna have to fix that in the go code by
setting those to false. So I've modified to go slightly
to bring in that.
At Boolean see whether somebody's will live. Although I'm not actually going to use it other than print it
out and I've moved that code that actually query the database
into a function called show, which will just dump out the names table because I'm going to use it a couple of times.
And immediately on starting, I show the database, so show the current state of it and then
I'm going to create a transaction as the way you do that is you call DB dot
begin, which starts a transaction on the database and I'm using prepare
here to prepare this statement to update everybody to have.
Live Boolean set to 0 or false because they no longer alive. And once I've done that, I'm
actually going to execute that. So that it's done. And before
committing the transaction, I'll dump out the database again to verify that nothing has changed.
Then I'll commit it and then I'll dump out the database again. So you should be able to see that there's twice,
it'll say they were alive and the third time, the transaction will actually have been committed so
let's just check that that builds
And then let's run it. So the first time show was
called, let's find where that is, is here, it shows that everybody
in the database had that field Martyrs true. So they're all were thought to be
alive and then started the transaction prepared statement
executed it, but had not committed it yet and show the
database. Again, the database has not been updated and then as soon as the transaction was
committed, everything's been updated to be false. So within the standard
database SQL,
Interface, you've got standard things. You can do with queries, and updating data,
bases, inserts updates. All the things you can do within seek or preparing statements
with fields, that you can actually insert safely with the correct sort of
escaping and as well as transaction support. So, really, within database, SQL
with the appropriate driver, you should be able to access any database you need to
from within go with ease.