在Postgresql数据库中存储递归go结构

Question

I have two structs (Person and Tenant) that reference each other recursively.

I have no experience with 'SQL' and Im trying to use the https://github.com/jmoiron/sqlx library to store these structs in a way that they keep referencing each other, so that I can retrieve them again as structs.

I don't know which type should the tables be created with or how I am supposed to insert the objects to get it work.

Also If there is any other go library that can handle this case easily i'm open to any suggestions.

Thanks in advance.

type Tenant struct {
    Id     int      `db:"id"`
    Name   string   `db:"name"`
    Person []Person `db:"person"`
}
type Person struct {
    Id       int       `db:"id"`
    Username string    `db:"username"`
    Tenants  *[]Tenant `db:"tenants"`
}
func main() {

    var schema = `
                       CREATE TABLE IF NOT EXISTS person (
                           id int,
                           username text
                           tenants []text //-> type????
                       );

                       CREATE TABLE IF NOT EXISTS tenant (
                           id int,
                           name text,
                           person []text //-> type????
                       )`

    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
        "password=%s dbname=%s sslmode=%s",
        host, port, user, password, dbname, sslmode)

    db, err := sqlx.Open("postgres", psqlInfo)
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err)
    }

    fmt.Println("Successfully connected!")

    db.MustExec(schema)

    var tenant1 Tenant
    var person1 Person
    tenant1 = Tenant{1, "newtenant", []Person{person1}}
    person1 = Person{1, "newuser", &[]Tenant{tenant1}}

    tx := db.MustBegin()

    tx.NamedExec("INSERT INTO tenant (id,name,person) VALUES (:id,:name, :person)", &tenant1)
    tx.Commit()
    out := []Tenant{}
    db.Select(&out, "SELECT * FROM tenant ORDER BY name ASC")

    fmt.Println(out)
}

Answer 1

NOTE: This is not a real answer, just a longer comment on the SQL part of the question. Unfortunately I have no experience with sqlx so I cannot help you with that.

---

What you have there seems to be a many-to-many relationship. A Person can belong to multiple Tenants and a Tenant can have multiple Persons.

In SQL this is usually handled by, what's sometimes called, a linking or junction table.

-- postgresql flavor of SQL

CREATE TABLE IF NOT EXISTS person (
    id serial PRIMARY KEY,
    username text NOT NULL
);

CREATE TABLE IF NOT EXISTS tenant (
    id serial PRIMARY KEY,
    name text NOT NULL
);

-- the linking table
CREATE TABLE IF NOT EXISTS person_tenant (
    person_id integer NOT NULL REFERENCES person (id),
    tenant_id integer NOT NULL REFERENCES tenant (id),
    PRIMARY KEY(person_id, tenant_id)
);