The docs say that it can be done with following code

sq.DefaultDialect.Store(&sq.DialectPostgres)

but it doesn't work, because

invalid operation: cannot take address of sq.DialectPostgres (untyped string constant "postgres")

Which is a fairly common Go "gotcha" ;-).

Can be mitigated with

    d := sq.DialectPostgres
    sq.DefaultDialect.Store(&d)

It is not as "pleasant", though.

The official support for the MSSQL driver has moved to Microsoft. It might be a good idea to switch to the new repository: https://github.com/microsoft/go-mssqldb

I've been trying to create a dynamic query to do a simple count as:

sq.Postgres.From(tbl).Select(sq.CountStar().As("count"))

The SQL generated from a CompileExec call is correct:

SELECT COUNT(*) AS count FROM table

but I can't seem to retrieve the result with a rowmapper function. I'm using for rowmapper

func(row *sq.Row) int {
	return row.Int("count")
}

but the fetch call fails with an error (column "count" does not exist (SQLSTATE 42703)).

What's the correct way to do aggregations at the top level without specifying columns and group by clauses?

Thanks!

Latest release fails for us with cannot call JSONField for static queries. The query is using a mapper that looks something like this:

func mapper(r *sq.Row) json.RawMessage {
	var pref json.RawMessage
	r.JSONField(&pref, table.Value)
	return pref
}

Did not see anything in the docs, but thought I'd ask before going the DIY route and handling the join tables manually.

Hi,

Thanks for this awesome lib.

There's only one thing I don't like, is that SetDialect needs to be called for all queries. Is there any way to remove this, and need to set it only once per project?

I'm thinking of either something like

type SqWrap struct {
  *sql.DB
  Dialect string
}

or storing it in a global in sq like sq.SetDialect and it would mean for all queries.

Thanks again.

How do I add "RETURNING" support for MariaDB? Introduce a separate dialect?

Hi,

I'd like some help with the following:

qry := "SELECT COUNT(*) FROM customers WHERE id IN {id}"
values := []any{sql.Named("id", []int{1})}
cnt, err := sq.FetchOneContext(context.Background(), sq.Log(db), sq.Postgres.Queryf(qry, values...), rowToCount)

Result:
SELECT COUNT(*) FROM customers WHERE id IN 1; err={pq: syntax error at or near "$1"}

I'd expect adding parentheses. If I have multiple values in that param, I get very similar:
SELECT COUNT(*) FROM customers WHERE id IN 1, 2; err={pq: syntax error at or near "$1"}

I would like to replace pq with pgx. Is it possible or would you consider a PR to replace pq?

	a := sq.New[models.ACTOR]("a")
	actors, err := sq.FetchAll(lr.Db, sq.
		From(a).
		Where(a.FIRST_NAME.EqString("DAN")).
		SetDialect(sq.DialectPostgres),
		func(row *sq.Row) Actor {
			return Actor{
				ActorID:   row.IntField(a.ACTOR_ID),
				FirstName: row.StringField(a.FIRST_NAME),
				LastName:  row.StringField(a.LAST_NAME),
			}
		},
	)
	
	package models

import (
"database/sql"
"time"

"github.com/bokwoon95/sq"

)

type ACTOR struct {
sq.TableStruct sq:"Actor"
ACTOR_ID sq.NumberField sq:"ActorID"
FIRST_NAME sq.StringField sq:"FirstName"
LAST_NAME sq.StringField sq:"LastName"
LAST_UPDATE sq.TimeField sq:"LastUpdate"
}

undefined: ActorcompilerUndeclaredName

trying to change it to something else leads to:

cannot use row.IntField(a.ACTOR_ID) (value of type int) as sq.NumberField value in struct literalcompilerIncompatibleAssign

	a := sq.New[models.ACTOR]("a")
	actors, err := sq.FetchAll(lr.Db, sq.
		From(a).
		Where(a.FIRST_NAME.EqString("DAN")).
		SetDialect(sq.DialectPostgres),
		func(row *sq.Row) models.ACTOR {
			return models.ACTOR {
				ACTOR_ID:   row.IntField(a.ACTOR_ID),
				FirstName: row.StringField(a.FIRST_NAME),
				LastName:  row.StringField(a.LAST_NAME),
			}
		},
	)

docs seem confusing and i cant figure out how to properly fetch something