Implementing a Basic ORM for PostgreSQL in Go

  • Go
  • Gabriele Romanato

In this article, we will see how to build a simple ORM (Object-Relational Mapping) in Go to interface with a PostgreSQL database. The goal is to understand the fundamental concepts of an ORM: mapping Go structs to SQL tables, dynamically generating queries, and handling results.

Requirements

  • Go 1.18 or higher
  • PostgreSQL database
  • database/sql library and lib/pq driver

Connecting to the Database

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

var db *sql.DB

func Connect() {
  var err error
  db, err = sql.Open("postgres", "user=postgres dbname=testdb sslmode=disable")
  if (err != nil) {
    log.Fatal(err)
  }
}

Basic Model Structure

We define a base structure from which other models will inherit:

type Model interface {
  TableName() string
}

type User struct {
  ID    int
  Name  string
  Email string
}

func (u User) TableName() string {
  return "users"
}

Function to Save a Record

A generic function to insert a record into the database:

import (
  "fmt"
  "reflect"
  "strings"
)

func Insert(model Model) error {
  v := reflect.ValueOf(model)
  t := reflect.TypeOf(model)

  var columns []string
  var placeholders []string
  var values []interface{}

  for i := 0; i < t.NumField(); i++ {
    field := t.Field(i)
    columns = append(columns, strings.ToLower(field.Name))
    placeholders = append(placeholders, fmt.Sprintf("$%d", i+1))
    values = append(values, v.Field(i).Interface())
  }

  query := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)",
    model.TableName(),
    strings.Join(columns, ", "),
    strings.Join(placeholders, ", "),
  )

  _, err := db.Exec(query, values...)
  return err
}

Usage Example

func main() {
  Connect()

  user := User{
    Name:  "Mario Rossi",
    Email: "mario@example.com",
  }

  err := Insert(user)
  if err != nil {
    log.Fatal(err)
  }
}

Creating the PostgreSQL Table

In the PostgreSQL database, make sure you have the appropriate table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT
);

Conclusion

We have seen how to implement a simplified version of an ORM in Go. This approach can be extended to include features such as updates, deletions, filtered queries, validations, and management of relationships between entities. However, for real-world projects, it is recommended to use mature ORMs such as GORM or SQLBoiler.

Back to top