As you continue your Golang learning journey, it becomes almost inevitable that you will have to interact with some form of database.
In this tutorial I’ll be demonstrating how you can connect to a MySQL database and perform basic SQL statements using Go.
MySQL is one of the most well-known and well-used database technologies available to developers at the present point in time. It has an absolutely massive community around it and it’s quite possibly powering half the web as the main database technology for Wordpress.
It’s incredibly easy to spin up a MySQL instance locally and thus it’s perfect for building some decent applications on top of.
Note - Technology choice shouldn’t be based on popularity, there may be scenarios where you need to consider alternatives such as CockroachDB or NoSQL databases.
If you prefer following a video, then this tutorial is available in video format here:
In order to do this we’ll be using https://github.com/go-sql-driver/mysql as our MySQL driver.
Go-SQL-Driver is a lightweight and fast MySQL driver that supports connections over
Unix domain sockets or custom protocols and features automatic handling of broken connections.
Github Repo: go-sql-driver/mysql
If you are building high-performance database applications, connection-pooling is an absolute must.
Thankfully, the opensource package that we’ll be using for the basis of this tutorial features automatic connection-pooling thanks to it’s use of of the
database/sql standard package.
This essentially means that, every time you query your database, you are using a connection from a pool of connections that have been set up on application startup. These connections are reused, time and time again, and this subsequently means you aren’t creating and destroying a new connection every time you perform a query.
We’ll begin by connecting to a database we’ve set up on our local machine and then go on to perform some basic insert and select statements.
Connecting to a MySQL database
Let’s create a new
main.go file. Within this, we’ll import a few packages and set up a simple connection to an already running local database. For the purpose of this tutorial, I’ve started MySQL using phpmyadmin and I’ve created a database called
test to connect to and create tables within.
sql.Open to connect to our database and set up our automatic connection pool, this will return either
db or an
err that we can handle.
Performing Basic SQL Commands
So, now that we’ve created a connection, we need to start submitting queries to the database.
db.Query(sql) allows us to perform any SQL command we so desire. We can simply construct the query string and pass it in as a parameter.
Populating Structs from Results
Retrieving a set of results from the database is all well and good, but we need to be able to read these results or populating existing
structs so that we can parse them and modify them easily. In order to parse a number of rows we can use the
.Scan(args...) method which takes in any number of arguments and allows us to populate a composite object.
In this example we retrieved 2 columns from the tags database and then used .Scan to populate our tag object.
Note - If you retrieve 3 fields from the database and Scan only has 2 parameters, it will fail. They need to match up exactly.
Querying a Single Row
Say we wanted to query a single row this time and had an ID and again wanted to populate our struct. We could do that like so:
In this tutorial, we managed to set up a connection to a MySQL and then perform some simple queries to that database and marshal the returned responses into a
struct or an array of
structs. This should hopefully give you everything you need in order to take things further and build your own Go applications on top of MySQL.
If you found this tutorial helpful or require more assistance then please don’t hesitate to let me know in the comments section below: