Go structs that represent SQL tables

I am pretty new to Go and I am trying to find the best way to set up my db communication. Essentially I remember from my previous workplaces that in PHP you can create a class that represents a SQL table and when you need to insert data into your db you would create an object of that class with all the necessary data, call insert(), pass your object and it would insert that data into a corresponding table without you writing any SQL code, update() works in a very similar way except it would update instead of inserting. Unfortunately, I don't remember the name of that PHP framework but maybe someone knows a way to achieve something like that in Go or is it not a thing?

Lets say I have a struct:

type Patients struct {
	ID              int
	Name            string 
	Image           string    
}

Now I want to have a function that takes Patients objet as a parameter and inserts it into a patients postgres table automatically converting patient into what postgres expects:

func (patients *Patients) insert(patient Patients) {

}

And then update() would take a Patients object and basically perform this chunk of code without me writing it:

stmt := `update patients set
	name = $1,
	image = $2,
    where id = $3
`

_, err := db.ExecContext(ctx, stmt,
	patient.Name,
	patient.Image,
    patient.ID
)

You are looking for something called an ORM (Object Relational Mapper). There are a few in Go, but the most popular is GORM. It's a bit of a controversial topic, but I think it's a good idea to use an ORM if you're new to Go and/or databases. It will save you a lot of time and effort.

The alternative is to use the database/sql package and write your own SQL queries. This is a good idea if you're an experienced Go developer and/or database administrator. It will give you more control over your queries and will be more efficient. Recommended reading: https://www.alexedwards.net/blog/organising-database-access. Recommended libraries for this approach include sqlx and pgx.

Here is what your struct would look like as a GORM model:

type Patient struct {
    ID              int `gorm:"primaryKey"`
    Name            string 
    Image           string    
}

And here is an example program for how to insert a patient into the database:

package main

import (
    "fmt"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Patient struct {
    ID              int `gorm:"primaryKey"`
    Name            string 
    Image           string    
}

func main() {
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable TimeZone=UTC"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    db.AutoMigrate(&Patient{})

    patient := Patient{
        Name: "John Smith",
        Image: "https://example.com/image.png",
    }

    result := db.Create(&patient)
    if result.Error != nil {
        panic(result.Error)
    }

    fmt.Println(patient)
}

If instead you wanted to use sqlx, you would write something like this:

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

type Patient struct {
    ID              int
    Name            string 
    Image           string    
}

func main() {
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable TimeZone=UTC"
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

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

    patient := Patient{
        Name: "John Smith",
        Image: "https://example.com/image.png",
    }

    _, err = db.Exec(`
        INSERT INTO patients (name, image) VALUES ($1, $2)
    `, patient.Name, patient.Image)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println(patient)
}

Of course, managing your database schema is a bit more complicated with an ORM. You can use migrations, but I prefer to use a tool called goose. It's a bit of a pain to set up, but it's very powerful and flexible. Here is an example of how to use it:

package main

import (
    "fmt"
    "log"

    "github.com/pressly/goose"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Patient struct {
    ID              int `gorm:"primaryKey"`
    Name            string 
    Image           string    
}

func main() {
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable TimeZone=UTC"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    goose.SetDialect("postgres")
    goose.SetTableName("schema_migrations")

    err = goose.Run("up", db.DB(), "migrations")
    if err != nil {
        log.Fatal(err)
    }

    patient := Patient{
        Name: "John Smith",
        Image: "https://example.com/image.png",
    }

    result := db.Create(&patient)
    if result.Error != nil {
        panic(result.Error)
    }

    fmt.Println(patient)
}

where your migrations directory looks like this:

migrations/
    00001_create_patients.up.sql
    00001_create_patients.down.sql

and your migrations look like this:

-- 00001_create_patients.up.sql
CREATE TABLE patients (
    id SERIAL PRIMARY KEY,
    name TEXT,
    image TEXT
);

-- 00001_create_patients.down.sql
DROP TABLE patients;

I hope this helps! Let me know if you have any questions.

I think what you're looking for is an ORM. An ORM is a library that essentially does this, taking language structures and automatically handling the SQL logic for you.

The most popular library for this in Go is GORM. Here's a link to their home page: https://gorm.io/. I've used it heavily in production and it's been a good experience!

The docs have a good example of what it'll look like.

Hope this helps.

Other answers mention both GORM and a few lower level tools like database/sql, sqlx and pgx, which are great.

But I do see some issues with both GORM and the lower level tools, I do explain a little about these issues I see in this article:

https://medium.com/better-programming/golang-sql-problems-with-existing-libraries-145a037261b8

In this same article I am also promoting a solution I wrote called KSQL (not the Kafka one), that is a lot simpler than GORM and a lot more powerful than database/sql, sqlx and pgx, you might like it.

The idea is to offer a few helper functions for common operations like Insert(), Update() and Delete() but for everything else KSQL just let's you write the query yourself, so knowing SQL is enough to use this library, while with GORM you need to learn a lot about the GORM library itself before using it well.

You can find it here:

https://github.com/vingarcia/ksql

And here is a short example of how one would go about inserting a patient into the database using KSQL:

package main

import (
	"context"
	"fmt"

	"github.com/vingarcia/ksql"
	"github.com/vingarcia/ksql/adapters/kpgx"
)

type Patient struct {
	ID    int    `ksql:"id"`
	Name  string `ksql:"name"`
	Image string `ksql:"image"`
}

// Just a simple struct containing the table name and
// the name of the ID column or columns:
var PatientsTable = ksql.NewTable("patients", "id")

func main() {
	ctx := context.Background()
	dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable TimeZone=UTC"
	db, err := kpgx.New(ctx, dsn, ksql.Config{})
	if err != nil {
		panic("failed to connect database")
	}

	patient := Patient{
		Name:  "John Smith",
		Image: "https://example.com/image.png",
	}

	err = db.Insert(ctx, PatientsTable, &patient) // (this call will retrieve the inserted ID automatically)
	if err != nil {
		panic(err)
	}

	fmt.Printf("%+v\n", patient) // {ID:1 Name:John Smith Image:https://example.com/image.png}
}