mysqlpostgresqlgosqlx

Embedded struct with Go and sqlx

I am facing an error with NULL values comming from SQL DB.

Here is the schemas:

CREATE TABLE product (
  id INT not null,
  name text not null,
  price int not null
);
INSERT INTO product (id, name, price) VALUES (1, 'PR1', 100);
INSERT INTO product (id, name, price) VALUES (2, 'PR2', 200);

CREATE TABLE discount (
  id INT not null,
  percentage int not null,
  product_id int not null
);
INSERT INTO discount (id, percentage, product_id) VALUES (1, 10, 1);

As you see, there should not be any null values in db.

Here is my structs

type Product struct{
 Id int `db:"id"`
 Name string `db:"name"`
 Price int `db:"price"`
}

type Discount struct {
 Id int `db:"id"`
 Percentage int `db:"percentage"`
 Product_id int `db:"product_id"`
}

HERE IS MY QUESTION:

How should I embed my structs so that I can handle JOIN queries.

  1. With INNER JOIN there is no problem
  2. With LEFT JOIN there is problem with NULL values. Here is the query enter image description here

ALSO, I DO NOT WANT TO USE POINTERS EVERYWHERE, IS THERE ANY ELEGANT WAY OF SOLVING THIS.

ONE MORE THING, THERE IS ALSO COALESC in PostgreSQL, how can I solve it even without it.

Solution

  • Maybe the sql.NullInt64 (and other Null* types) is what u want to use? See the godoc: https://pkg.go.dev/database/sql#NullInt64

    Also, you could have some DTO struct with pointers, Null-types and so on to receive data from a database and then convert it into the required models.