create_tables.sql

CREATE TABLE t1 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), "name" STRING);
CREATE TABLE t2 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), "name" STRING, t1_id UUID REFERENCES t1 (id));

INSERT INTO t1 (name) VALUES ('bacon');
INSERT INTO t2 (name, t1_id) VALUES ('bacon t2', (SELECT id FROM t1 LIMIT 1));
INSERT INTO t2 (name) VALUES ('facon');

tables.out

root@localhost:26257/triton> SHOW COLUMNS FROM t1;
+-------+--------+-------+-------------------+-------------+
| Field |  Type  | Null  |      Default      |   Indices   |
+-------+--------+-------+-------------------+-------------+
| id    | UUID   | false | gen_random_uuid() | {"primary"} |
| name  | STRING | true  | NULL              | {}          |
+-------+--------+-------+-------------------+-------------+
(2 rows)

Time: 19.854253ms

root@localhost:26257/triton> SHOW COLUMNS FROM t2;
+-------+--------+-------+-------------------+---------------------------------------------+
| Field |  Type  | Null  |      Default      |                   Indices                   |
+-------+--------+-------+-------------------+---------------------------------------------+
| id    | UUID   | false | gen_random_uuid() | {"primary","t2_auto_index_fk_t1_id_ref_t1"} |
| name  | STRING | true  | NULL              | {}                                          |
| t1_id | UUID   | true  | NULL              | {"t2_auto_index_fk_t1_id_ref_t1"}           |
+-------+--------+-------+-------------------+---------------------------------------------+
(3 rows)

test.out

$ go run uuid_example.go
t1 id: f36753a8-e924-4e0e-b471-cbc9ba831ed9
t1 name: bacon

t2 id: 98f87429-1d15-46e1-bd05-2297f0683aba
t2 name: bacon t2
t2 t1_id: f36753a8-e924-4e0e-b471-cbc9ba831ed9

t2 id: 970d3bc5-299b-4f13-82d6-acbc905afe70
t2 name: facon
t2 t1_id: 00000000-0000-0000-0000-000000000000

uuid_example.go

package main

import (
	"context"
	"fmt"

	"github.com/jackc/pgx"
	"github.com/jackc/pgx/pgtype"
)

func newTestDB() (*pgx.ConnPool, error) {
	connPool, err := pgx.NewConnPool(pgx.ConnPoolConfig{
		MaxConnections: 5,
		AfterConnect:   nil,
		AcquireTimeout: 0,
		ConnConfig: pgx.ConnConfig{
			Host:     "localhost",
			Database: "triton",
			Port:     26257,
			User:     "root",
		},
	})
	if err != nil {
		return nil, err
	}

	return connPool, nil
}

type Store struct {
	pool *pgx.ConnPool
}

type T1Row struct {
	ID   string
	Name string
}

type T2Row struct {
	ID   string
	Name string
	T1ID string
}

// NewStore returns a new store object.
func NewStore(pool *pgx.ConnPool) *Store {
	return &Store{
		pool: pool,
	}
}

func BytesToUUID(b [16]byte) string {
	return fmt.Sprintf("%x-%x-%x-%x-%x", b[0:4], b[4:6], b[6:8], b[8:10], b[10:])
}

// FindByName finds an account by a specific account_name.
func (s *Store) FindT1ByName(ctx context.Context, t1Name string) (*T1Row, error) {
	var (
		id   pgtype.UUID
		name string
	)

	query := `SELECT id, name FROM t1 WHERE name = $1;`
	err := s.pool.QueryRowEx(ctx, query, nil, t1Name).Scan(
		&id,
		&name,
	)
	if err != nil {
		return nil, err
	}

	return &T1Row{
		ID:   BytesToUUID(id.Bytes),
		Name: name,
	}, nil
}

// FindByName finds an account by a specific account_name.
func (s *Store) FindT2ByName(ctx context.Context, t2Name string) (*T2Row, error) {
	var (
		id   pgtype.UUID
		name string
		t1Id pgtype.UUID
	)

	query := `SELECT id, name, t1_id FROM t2 WHERE name = $1;`
	err := s.pool.QueryRowEx(ctx, query, nil, t2Name).Scan(
		&id,
		&name,
		&t1Id,
	)
	if err != nil {
		return nil, err
	}

	return &T2Row{
		ID:   BytesToUUID(id.Bytes),
		Name: name,
		T1ID: BytesToUUID(t1Id.Bytes),
	}, nil
}

func main() {
	pool, err := newTestDB()
	if err != nil {
		panic(err)
	}

	store := NewStore(pool)

	t1Row, err := store.FindT1ByName(context.Background(), "bacon")
	if err != nil {
		panic(err)
	}

	fmt.Println("t1 id:", t1Row.ID)
	fmt.Println("t1 name:", t1Row.Name)

	t2Row, err := store.FindT2ByName(context.Background(), "bacon t2")
	if err != nil {
		panic(err)
	}

	fmt.Println()

	fmt.Println("t2 id:", t2Row.ID)
	fmt.Println("t2 name:", t2Row.Name)
	fmt.Println("t2 t1_id:", t2Row.T1ID)

	t22Row, err := store.FindT2ByName(context.Background(), "facon")
	if err != nil {
		panic(err)
	}

	fmt.Println()

	fmt.Println("t2 id:", t22Row.ID)
	fmt.Println("t2 name:", t22Row.Name)
	fmt.Println("t2 t1_id:", t22Row.T1ID)
}