ksql

KSQL provides a high-level API for common database operations. You can use Insert, Delete, QueryOne, Query, and Patch to manage records.

Common Operations:

• Insert: Use db.Insert(ctx, table, &struct) to add new records. You can also insert inline.
• Delete: Use db.Delete(ctx, table, id) to remove a record by its primary key.
• Query One: Use db.QueryOne(ctx, &struct, "FROM table WHERE ...", args...) to retrieve a single record. If you omit the SELECT part, KSQL automatically builds it based on the struct fields.
• Query Many: Use db.Query(ctx, &slice, "FROM table LIMIT ...") to retrieve multiple records into a slice. Note: Always use a LIMIT to avoid memory issues; for very large datasets, use QueryChunks.
• Update (Patch): Use db.Patch(ctx, table, data) to update records. You can perform partial updates using two techniques:
  1. Anonymous Struct: Pass a struct containing only the ID and the fields you wish to change.
  2. Pointer/Nullable Fields: Use a struct where fields are pointers (or use nullable.Int, etc.). If a pointer is nil, that field will not be updated in the database.

// Inserting
err = db.Insert(ctx, UsersTable, &alison)

// Deleting
err = db.Delete(ctx, UsersTable, alison.ID)

// Querying one
var cris User
err = db.QueryOne(ctx, &cris, "FROM users WHERE name = ?", "Cristina")

// Querying many
var users []User
err = db.Query(ctx, &users, "FROM users LIMIT 10")

// Partial Update (Technique 1: Anonymous Struct)
err = db.Patch(ctx, UsersTable, struct {
    ID  int `ksql:"id"`
    Age int `ksql:"age"`
}{ID: cris.ID, Age: 28})

// Partial Update (Technique 2: Pointers/Nullable)
err = db.Patch(ctx, UsersTable, PartialUpdateUser{
    ID:  cris.ID,
    Age: nullable.Int(28),
})

KSQL supports transactions via the db.Transaction method. You provide a function that receives a ksql.Provider.

• If the function returns an error, KSQL automatically performs a rollback.
• If the function returns nil, KSQL commits the transaction.

Warning: If you panic inside the transaction function, KSQL will not catch it to perform a rollback; it will re-panic to avoid hiding errors from the caller. Ensure you return errors for expected failure paths.

err = db.Transaction(ctx, func(db ksql.Provider) error {
    var cris2 User
    err := db.QueryOne(ctx, &cris2, "FROM users WHERE id = ?", cris.ID)
    if err != nil {
        return err // Automatic rollback
    }

    err = db.Patch(ctx, UsersTable, PartialUpdateUser{
        ID:  cris2.ID,
        Age: nullable.Int(29),
    })
    if err != nil {
        return err // Automatic rollback
    }

    return nil // Commit
})

KSQL allows for flexible querying. You can query specific attributes into custom structs or load entire entities by omitting the SELECT part of the query if you provide a ksql.Table definition.

Placeholder Syntax by Database:

• Postgres: Use $1, $2, etc.
• MySQL/SQLite: Use ?.
• SQLServer: Use @p1, @p2, etc.

Example: Querying into a custom struct

var count []struct {
	Count int    `ksql:"count"`
	Type  string `ksql:"type"`
}
err = db.Query(ctx, &count, "SELECT type, count(*) as count FROM users GROUP BY type")

Example: Loading entities (omitting SELECT)

var adminUsers []User
err = db.Query(ctx, &adminUsers, "FROM users WHERE type = $1", "admin")

Example: Joining tables Use the tablename tag in your result struct to map joined rows to specific entities.

var rows []struct {
	OneUser    User    `tablename:"users"`
	OneAddress Address `tablename:"addr"`
}
err = db.Query(ctx, &rows, `FROM users JOIN addresses addr ON users.user_id = addr.user_id`)

// Querying custom attributes
var count []struct {
	Count int    `ksql:"count"`
	Type  string `ksql:"type"`
}
err = db.Query(ctx, &count, "SELECT type, count(*) as count FROM users GROUP BY type")

// Loading entities by omitting SELECT
var adminUsers []User
err = db.Query(ctx, &adminUsers, "FROM users WHERE type = $1", "admin")

// Joining tables
var rows []struct {
	OneUser    User    `tablename:"users"`
	OneAddress Address `tablename:"addr"`
}
err = db.Query(ctx, &rows, `FROM users JOIN addresses addr ON users.user_id = addr.user_id`)

The Mock struct implements the ksql.Provider interface, allowing you to simulate database behavior in unit tests.

To mock a specific method, overwrite its corresponding Fn attribute (e.g., InsertFn for the Insert method).

Best Practices:

• Instantiate locally: Create a new Mock instance inside each unit test rather than using a global variable.
• Capture values, don't assert inside mocks: Instead of performing assertions inside the mocked function, use closures to capture input values and assert against them in the test's assertion stage. This follows the (1) setup, (2) run, (3) assert pattern.
• Handling multiple calls: To track multiple calls, use a closure to append captured values to a slice.

// Capturing a single value
var insertRecord interface{}
dbMock := ksql.Mock{
	InsertFn: func(ctx context.Context, table ksql.Table, record interface{}) error {
		insertRecord = record
	},
}

// Capturing multiple calls
var insertRecords []interface{}
dbMock := ksql.Mock{
	InsertFn: func(ctx context.Context, table ksql.Table, record interface{}) error {
		insertRecords = append(insertRecords, record)
	},
}

You can use ksql.InjectLogger to enable query logging for all subsequent database operations performed with a specific context.Context. This is a debugging tool that forces KSQL to log the query string, the parameters used, and any resulting errors whenever a query is executed.

To use it, pass your context and a LoggerFn (such as the built-in ksql.Logger or ksql.ErrorLogger) to InjectLogger. The function returns a new context containing the logger, which you must then use for your database calls.

// After injecting a logger into `ctx` all subsequent queries
// that use this context will be logged.
ctx = ksql.InjectLogger(ctx, ksql.Logger)

// All the calls below will cause KSQL to log the queries:
var user User
db.Insert(ctx, usersTable, &user)

user.Name = "NewName"
db.Patch(ctx, usersTable, &user)

var users []User
db.Query(ctx, &users, someQuery, someParams...)
db.QueryOne(ctx, &user, someQuery, someParams...)

db.Delete(ctx, usersTable, user.ID)