generics.txt

ideas to implement when go gets generics

q := sq.NewSelect[User](config...)

users, err := q.
    From(u).
    Join(s, s.USER_ID.Eq(u.USER_ID)).
    Where(s.SESSION_HASH.EqString("1234")).
    Selectx(func (row *sq.Row) (User, error) {
        var user User
        user.UserID = row.Int64(u.USER_ID)
        user.Username = sq.Get[string](row, u.USERNAME)
        return user, nil
    }).
    FetchSlice(db)

user, err := q.
    From(u).
    Join(s, s.USER_ID.Eq(u.USER_ID)).
    Where(s.SESSION_HASH.EqString("1234")).
    Selectx(func (row *sq.Row) (User, error) {
        var user User
        user.UserID = row.Int64(u.USER_ID)
        user.Username = sq.Get[string](row, u.USERNAME)
        return user, nil
    }).
    Fetch(db)

db := sq.NewDB(DB, sq.WithLogger(logger, sq.LVerbose))
db := sq.NewPgxDB(pgxDB, sq.WithLogger(logger, sq.LStats))
// This means that if you don't use the db to make queries, you won't be logging
// anything. If you use sq as a pure query builder, you'll have to log the
// resultant queries yourself (which makes my job a lot easier).
q2 := sq.From(u).Join(s, s.USER_ID.Eq(u.USER_ID)).Where(s.SESSION_HASH.EqString("1234"))
q3 := sq.QueryLiteral(`FROM users AS u JOIN sessions AS s USING (user_id) WHERE s.session_hash = ?`, "1234")

user, err := db.FetchOne(q2, func (row *sq.Row) (User, error) {
    var user User
    user.UserID = row.Int64(u.USER_ID)
    user.Username = sq.Get[string](row, u.USERNAME)
    return user, nil
})

user, err := db.FetchOne(q2, user.RowMapper(u))

user, err := db.FetchOne(
    sq.From(u).Join(s, s.USER_ID.Eq(u.USER_ID)).Where(u.USER_ID.EqInt(3)),
    func(row *sq.Row) (User, error) {
        var user User
        user.userID = row.Int64(u.USER_ID)
        user.Username = row.String(u.USERNAME)
        return user, nil
    },
)

x, y := X{}, Y{}
_, err := db.FetchOne(
    sq.From(u).Join(s, s.USER_ID.Eq(u.USER_ID)).Where(u.USER_ID.EqInt(3)),
    func(row *sq.Row) (User, error) {
        x.userID = row.Int64(u.USER_ID)
        y.Username = row.String(u.USERNAME)
        return User{}, nil
    },
)

users, err := sq.FetchMany(q2, func (row *sq.Row) (User, error) {
    var user User
    user.UserID = row.Int64(u.USER_ID)
    user.Username = sq.Get[string](row, u.USERNAME)
    return user, nil
})

users, err := db.FetchMany(q2, user.RowMapper(u))

var users []User
err := sq.Fetch(q2, func(row *sq.Row) error {
    var user User
    user.UserID = row.Int64(u.USER_ID)
    user.Username = sq.Get[string](row, u.USERNAME)
    if row.Count() == 0 {
        users = append(users, user)
    }
    return nil
})

type USERS struct {
    sq.TableInfo
    USER_ID sq.NumberField `sq:"INTEGER PRIMARY KEY"`
    USERNAME sq.StringField `sq:"TEXT NOT NULL"`
    PASSWORD_HASH sq.StringField `sq:"TEXT"`
}

func newUsers(alias string) USERS {
    u := USERS{TableInfo: sq.TableInfo{
        Name:   "users",
        Schema: "public",
        Alias: alias,
    }}
    u.USER_ID = sq.NewNumberField("user_id", u.TableInfo)
    u.USERNAME = sq.NewStringField("username", u.TableInfo)
    u.PASSWORD_HASH = sq.NewStringField("password_hash", u.TableInfo)
    return u
}

func newUsers(alias string) USERS {
    u := sq.NewTable(&USERS{}) // is Go able to enforce pointer arguments?
    u.TableInfo.Alias = alias
    return u
}

u, s := sq.New(&tables.USERS{}), sq.New(&tables.SESSIONS{})

func main() {
    err := sq.EnsureTables(
        sq.NewTable(&tables.USERS{}),
        sq.NewTable(&tables.SESSIONS{}),
        sq.NewTable(&tables.USER_POSTS{}),
    )
}

# return a collection with all users
func (user User) RowMapper(u tables.USERS) func(*sq.Row) (User, error) {
    return func(row *sq.Row) (User, error) {
        user.UserID = row.Int64(u.USER_ID)
        user.Username = row.String(u.USERNAME)
        user.CreatedAt = row.Time(u.CREATED_AT)
        return user, nil
    }
}
users = User.all
users, err := db.FetchMany(sq.Postgres.From(u), user.RowMapper(u))

# return the first user
user = User.first
user, err := db.FetchOne(sq.Postgres.From(u).Limit(1), user.RowMapper(u))

# return the first user named David
david = User.find_by(name: 'David')
david, err := db.FetchOne(sq.Postgres.From(u).Where(u.NAME.EqString("David")), user.RowMapper(u))

# find all users named David who are Code Artists and sort by created_at in reverse chronological order
users = User.where(name: 'David', occupation: 'Code Artist').order(created_at: :desc)
users, err := db.FetchMany(sq.Postgres.
    From(u).
    Where(
        u.NAME.EqString("David"),
        u.OCCUPATION.EqString("Code Artist"),
    ).
    OrderBy(
        u.CREATED_AT.Desc(),
    ),
    user.RowMapper(u),
)

u := sq.NewTable(&USERS{})
cte := sq.Postgres.Select(u.USER_ID, u.DISPLAYNAME, u.EMAIL).From(u).Where(u.USER_ID.LtInt(5)).CTE("cte")
users, err := db.FetchMany(ctx, sq.Postgres.
    From(cte).
    Where(cte["displayname"].Eq(cte["email"])),
    func(row *sq.Row) (user User, err error) {
        err = row.ScanInto(&user.UserID, cte["user_id"])
        if err != nil {
            return user, err
        }
        row.ScanInto(&user.Displayname, cte["displayname"])
        if err != nil {
            return user, err
        }
        return user, nil
    },
)

func Exec(ctx context.Context, db DB, q Query, execflag ExecFlag) (lastInsertID, rowsAffected int64, err error)
func Fetch(ctx context.Context, db DB, q Query, func(*Row) error) error
func FetchOne[Item any](ctx context.Context, db DB, q Query, func(*Row) (Item, error)) (Item, error)
func FetchSlice[Item any](ctx context.Context, db DB, q Query, mapper func(*Row) (Item, error)) ([]Item, error)
func FetchAccumulate[Item, Items any](ctx context.Context, db DB, q Query, mapper func(*Row) (Item, error), accumulator func(Item, Items) Items) (Items, error)

Is it possible to keep sq.Fetch/FetchOne/FetchMany database agnostic? The main issue is that the column signatures will have to be fixed because row.Int64/etc will have to accept a NumberField from a specific package. If type lists get accepted into Go, I can simply type list over pgsq/mysq/sqlt.NumberField. Otherwise I will have to think about splitting everything into its own package -again- (which means I cannot support pagemanager, which must be parametric over sqlite/postgres and maybe mysql).

ctx.select(
      AUTHOR.FIRST_NAME,
      AUTHOR.LAST_NAME,
      BOOK.ID,
      BOOK.TITLE)
   .from(BOOK)
   .join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
   .where(BOOK.ID.eq(1))
   .and(BOOK.TITLE.isNull().or(BOOK.TITLE.ne(randomValue)));

AUTHOR := sq.NewAliasedTable(&tables.AUTHOR{}, "a")
BOOK := sq.NewAliasedTable(&tables.BOOKS{}, "b")

book, err := sq.FetchOne(db, sq.Postgres.
    From(BOOK).
    Join(AUTHOR, BOOK.AUTHOR_ID.Eq(AUTHOR.ID)).
    Where(
        sq.Eq(BOOK.ID, sq.Param("id", 1)),
        sq.Or(
            BOOK.TITLE.IsNull(),
            sq.Ne(BOOK.TITLE, sq.Param("randomValue", randomValue))
        ),
    ),
    func(row *sq.Row) (Book, error) {
        var book Book
        book.AuthorFirstName = row.String(AUTHOR.FIRST_NAME)
        book.AuthorLastName = row.String(AUTHOR.LAST_NAME)
        book.ID = row.Int64(BOOK.ID)
        book.Title = row.String(BOOK.TITLE)
        return book, nil
    },
)

type CompiledFetch[T any] struct { ... }
var q sq.CompiledFetch
q, err = sq.CompileFetch(sq.Postgres.
    From(BOOK).
    Join(AUTHOR, BOOK.AUTHOR_ID.Eq(AUTHOR.ID)).
    Where(
        sq.Eq(BOOK.ID, sq.Param("id", 1)),
        sq.Or(
            BOOK.TITLE.IsNull(),
            sq.Ne(BOOK.TITLE, sq.Param("randomValue", randomValue))
        ),
    ),
    func(row *sq.Row) (Book, error) {
        var book Book
        book.AuthorFirstName = row.String(AUTHOR.FIRST_NAME)
        book.AuthorLastName = row.String(AUTHOR.LAST_NAME)
        book.ID = row.Int64(BOOK.ID)
        book.Title = row.String(BOOK.TITLE)
    },
)
book, err := q.FetchOne(sq.Params{"id": 2, "randomValue": "yeet"})
books, err := q.FetchSlice(sq.Params{ "id": 3, "randomValue": "abcd"})