PGX Support

KSQL works on top of existing and battle-tested SQL driver implementations including pgx.

Currently, KSQL supports 2 major versions of PGX:

• PGX 4 with the adapter available at: github.com/vingarcia/ksql/adapters/pgx
• PGX 5 with the adapter available at: github.com/vingarcia/ksql/adapters/pgx5

Both adapters provide 2 constructors with the same names and signatures:

• New(ctx context.Context, connectionString string, config ksql.Config) (db ksql.DB, err error)
• NewFromPgxPool(pool *pgxpool.Pool) (db ksql.DB, err error)

The first constructor hides most options, while the second constructor allows you to provide a fully configured pgxpool instance, meaning you can use all supported pgxpool configurations and still use KSQL on top of it.

PGX Special Types

When using the Query, QueryOne and QueryChunks functions all arguments passed after the query as well as all the attributes of the input structs are directly forwarded to the underlying database adapter, in this case pgx. This means that you can actually use any supported pgx.

The example query below also available here illustrates one possible use-case:

func checkIfUserBelongsToTeams(ctx context.Context, db ksql.Provider, userID int, teamIDs []int) {
	// Check if user belongs to either of the input teams:
	var row struct {
		Count pgtype.Int8 `ksql:"c"`
	}
	err := db.QueryOne(ctx, &row,
		`SELECT count(*) as c
		FROM users AS u
		JOIN team_members AS tm
			ON u.id = tm.user_id
		WHERE u.id = $1
			AND tm.team_id = ANY($2)`,
		userID,
		[]int{1, 2, 42}, // Int slices are supported by PGX
	)
	if err != nil {
		log.Fatalf("unexpected error: %s", err)
	}

	fmt.Printf("Count: %+v\n", row.Count.Int)
}