Accessing a relational database

Task Done

• Set up a database.
• Import the database driver.
• Get a database handle and connect.
• Query for multiple rows.
• Query for a single row.
• Add data.

Notes

Go has standard package library for sql database/sql. For more details check Go: accessing relational database

Go also supports ORM libraries: GORM, ent

Drivers for different Relational Database can be found here SQLDrivers

For mysql, we will be using go-sql-driver/mysql

Steps

Create Database and populate data

mysql -u root -p -h 127.0.0.1 -P 3306

mysql> create database recordings;
Query OK, 1 row affected (0.00 sec)

mysql> use recordings;
Database changed

-- create database using sql file
mysql> source ./create-tables.sql
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- verify changes
mysql> select * from album;

Import the database driver.

Import our mysql driver go-sql-driver/mysql

Get a database handle and connect.

Connect to our local mysql database that we just created using go-sql-driver/mysql.

Ping to verify that db is connected.

Query for multiple rows.

Create a struct, with same structure as database created from sql earlier

-- used earlier
mysql> source ./create-tables.sql

db.Query: retrieve multiple rows

rows.Scan: copy rows to []struct

Query for a single row.

db.QueryRow: retrieves single row

Add data.

db.Exec: Execute insert query, where data is provided from variable of struct (matches database)