Connecting to a Database using database/sql in Golang (Make Your App Remember Stuff!)

Golang

""Learning Golang is like learning to cook with a simple recipe but yielding maximum results—with clean syntax, fast performance, and multitasking capabilities through goroutines that make you feel like a master chef, handling multiple tasks at once without a hitch!""

So, you’ve built a Go app, but it’s got the memory of a goldfish.  Every time you restart it, poof—data’s gone! Let’s fix that by connecting it to a database using Go’s database/sql package. 

Why Use database/sql?

The database/sql package is the standard way to work with SQL databases in Go. It provides:

• A consistent API for multiple databases (PostgreSQL, MySQL, SQLite, etc.)
• Connection pooling (faster queries!)
• Prepared statements (more secure SQL!)

Installing a Database Driver

Since database/sql is just an interface, we need a driver. Let’s use PostgreSQL as an example.

Install the driver:

go get github.com/lib/pq

Then, import it in your code:

import (
    "database/sql"
    _ "github.com/lib/pq"
)

The underscore (_) means we’re importing it for its side effects (registering itself with database/sql).

Connecting to the Database

Let’s establish a connection:

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/lib/pq"
)

const dbConnection = "user=postgres password=yourpassword dbname=mydb sslmode=disable"

func main() {
    db, err := sql.Open("postgres", dbConnection)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Check if the connection works
    err = db.Ping()
    if err != nil {
        log.Fatal("Cannot connect to the database!", err)
    }
    fmt.Println("Connected to the database successfully!")
}

Now your app can talk to the database! 

Creating a Table

Let’s create a simple users table.

_, err = db.Exec(`CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE
)`) 
if err != nil {
    log.Fatal(err)
}

Boom! A users table, ready to store data.

Inserting Data

Let’s add a user.

_, err = db.Exec("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com")
if err != nil {
    log.Fatal(err)
}

Now Alice is in the database!

Querying Data

Want to get users? Here’s how:

rows, err := db.Query("SELECT id, name, email FROM users")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var id int
    var name, email string
    rows.Scan(&id, &name, &email)
    fmt.Println(id, name, email)
}

Now your app can fetch data

Closing Thoughts

With database/sql, Go apps can store, retrieve, and manipulate data easily.