### SQL Driver Configuration Example (Environment Variable)

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/sql.md

Example of configuring the SQL driver's dialect using an environment variable.

```sh
SQL_DIALECT=psql
```

--------------------------------

### SQL Driver Configuration Example (YAML)

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/sql.md

Example configuration for the SQL driver in a YAML file, specifying dialect and file pattern.

```yaml
sql:
  dialect: psql
  pattern: "schema/*.sql"
```

--------------------------------

### SQL Schema and Query Example

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/queries.md

Example of a SQL schema and a query that Bob can process to generate Go code.

```sql
CREATE TABLE users (
    id INT PRIMARY KEY NOT NULL,
    name TEXT
);

-- AllUsers
SELECT * FROM users WHERE id = ?;
```

--------------------------------

### Execute Queries with Different Fetch Methods

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/view.md

Shows examples of executing a query using One(), All(), Cursor(), Count(), and Exists() methods on a View model.

```go
// SELECT * FROM "users" LIMIT 1
userView.Query().One(ctx, db)
```

```go
// SELECT * FROM "users"
userView.Query().All(ctx, db)
```

```go
// Like All, but returns a cursor for moving through large results
userView.Query().Cursor(ctx, db)
```

```go
// SELECT count(1) FROM "users"
userView.Query().Count(ctx, db)
```

```go
// Like One(), but only returns a boolean indicating if the model was found
userView.Query().Exists(ctx, db)
```

--------------------------------

### Start an Update Query

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/table.md

Initiates an UPDATE query on the 'users' table, setting the 'kind' column and specifying that all columns should be returned.

```go
// UPDATE "users" SET "kind" = $1 RETURNING *
updateQ := userTable.Update(
    um.SetCol("kind").ToArg("Dramatic"),
    um.Returning("*"),
)
```

--------------------------------

### Import and Initialize SQLite Queries

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/sqlite/how-to-use.md

Import the SQLite package and its specific query modifier packages. This example shows how to initialize Select, Insert, Update, Delete, and Raw queries for SQLite.

```go
import (
    "github.com/stephenafamo/bob/dialect/sqlite"
    "github.com/stephenafamo/bob/dialect/sqlite/sm"
    "github.com/stephenafamo/bob/dialect/sqlite/im"
    "github.com/stephenafamo/bob/dialect/sqlite/um"
    "github.com/stephenafamo/bob/dialect/sqlite/dm"
)

func main() {
    sqlite.Select(
        sm.From("users"),
    )

    sqlite.Insert(
        im.Into("users"),
    )

    sqlite.Update(
        um.Table("users"),
    )

    sqlite.Delete(
        dm.From("users"),
    )

    sqlite.Raw()
}
```

--------------------------------

### Run Bob Gen with Configuration File

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/psql.md

Execute the PostgreSQL driver generation using a configuration file. This is useful for more complex setups or when managing multiple configurations.

```sh
# With configuration file
go run github.com/stephenafamo/bob/gen/bobgen-psql@latest -c ./config/bobgen.yaml
```

--------------------------------

### MySQL Insert with Optimizer Hints

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/mysql/examples/insert.md

Shows how to include optimizer hints within an INSERT statement to guide MySQL's query execution plan. Hints like MAX_EXECUTION_TIME and SET_VAR can be specified.

```go
mysql.Insert(
  im.Into("films"),
  im.MaxExecutionTime(1000),
  im.SetVar("cte_max_recursion_depth = 1M"),
  im.Values(mysql.Arg("UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 mins")),
)
```

--------------------------------

### Import MySQL Query Builder Packages

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/mysql/how-to-use.md

Import the necessary packages for the MySQL dialect and specific query types. This setup is required before constructing queries.

```go
import (
    "github.com/stephenafamo/bob/dialect/mysql"
    "github.com/stephenafamo/bob/dialect/mysql/sm"
    "github.com/stephenafamo/bob/dialect/mysql/im"
    "github.com/stephenafamo/bob/dialect/mysql/um"
    "github.com/stephenafamo/bob/dialect/mysql/dm"
)

func main() {
    mysql.Select(
        sm.From("users"),
    )

    mysql.Insert(
        im.Into("users"),
    )

    mysql.Update(
        um.Table("users"),
    )

    mysql.Delete(
        dm.From("users"),
    )

    mysql.Raw()
}
```

--------------------------------

### Generate UserPosts Query Function

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/README.md

This example shows the generated Go function for a SQL query. It demonstrates how to use the generated function to fetch posts for a specific user.

```sql
-- UserPosts
SELECT * FROM posts WHERE user_id = $1
```

```go
// UserPosts
userPosts, err := queries.UserPosts(1).All(ctx, db)
```

--------------------------------

### SQL Driver 'except' Configuration Example

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/sql.md

Demonstrates how to use the 'except' configuration to exclude specific tables, columns, or all columns with a certain name from ORM generation.

```yaml
sql:
  # Removes public.migrations table, the name column from the addresses table, and
  # secret_col of any table from being generated. Foreign keys that reference tables
  # or columns that are no longer generated may cause problems.
  except:
    public.migrations:
    public.addresses:
      - name
    "*":
      - secret_col
```

--------------------------------

### Querying relationships with mods

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/relationships.md

Add mods like Limit to relationship queries. This example limits the results to 20.

```go
jet, err := models.FindJet(ctx, db, 1)

// SELECT * FROM "pilots" WHERE "id" = $1 LIMIT 20
jetPilotQuery, err := jet.Pilots(ctx, db, sm.Limit(20))
```

--------------------------------

### Configure SQLite DSN in Configuration File

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/sqlite.md

Example of configuring the SQLite DSN within a `bobgen.yaml` configuration file. The `sqlite` section must contain the `dsn` key.

```yaml
sqlite:
  dsn: "file.db"
```

--------------------------------

### Configure Relationships with Static Values

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/configuration.md

Define relationships based on matching columns and static values. This example relates teams to verified users.

```yaml
relationships:
  users:
    - name: 'users_to_videos_through_teams'
      sides:
        - from: 'teams'
          to: 'users'
          columns: [[id, team_id]]
          to_where:
            - column: 'verified'
              sql_value: 'true'
              go_value: 'true'
```

--------------------------------

### Generate Multiple Database Entries with Factories

Source: https://github.com/stephenafamo/bob/blob/main/README.md

Illustrates using generated factories to quickly create multiple related database entries for testing purposes. This example creates 10 comments, automatically generating associated posts and users.

```go
// Quickly create a 10 comments (posts and users are created appropriately)
comments, err := f.NewComment().CreateMany(ctx, db, 10)
```

--------------------------------

### PostgreSQL Code Generation Configuration

Source: https://context7.com/stephenafamo/bob/llms.txt

Example `bob.yaml` configuration for PostgreSQL code generation. Specifies DSN, output paths, package names, table inclusions/exclusions, type replacements, relationship configurations, and plugin outputs.

```yaml
# bob.yaml — example for PostgreSQL
psql:
  dsn: "postgres://user:pass@localhost/mydb?sslmode=disable"
  output: "./models"
  pkg: "models"

  # optional: only include specific tables
  only:
    - users
    - posts
    - comments

  # optional: exclude tables
  except:
    - schema_migrations

  # Configure type replacements
  types:
    uuid:
      imports:
        - '"github.com/google/uuid"'
      type: "uuid.UUID"
      null_type: "uuid.NullUUID"
      random_expr: 'func() uuid.UUID { return uuid.Must(uuid.NewV4()) }()'

  # Explicitly configure relationships
  relationships:
    users:
      - name: active_posts
        sides:
          - from: users
            to: posts
            from_columns: [id]
            to_columns: [user_id]
        where_expr: '"posts"."status" = ''published'''

  # Control generated plugin outputs
  plugins:
    - models
    - factory
    - where
    - joins
    - loaders
    - dberrors
    - dbinfo

  # Enum format: title_case (default) or screaming_snake_case
  enum_format: "title_case"

  # Factory output directory (default: ./factory)
  outputs:
    factory:
      out_folder: "./internal/factory"
      pkg_name: "factory"
```

--------------------------------

### SQL Query for Retrieving Related Data

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/queries.md

Example SQL query demonstrating how to retrieve related data using specific column naming conventions for to-one and to-many relationships. The `--prefix` annotation helps in organizing nested structures.

```sql
-- Nested
SELECT
    users.*
    --prefix:videos.
    videos.*
    --prefix:videos.sponsor__
    sponsors.*
FROM users
LEFT JOIN videos ON videos.user_id = users.id
INNER JOIN sponsors ON videos.sponsor_id = sponsors.id
WHERE users.id IN ($1);
```

--------------------------------

### Define Database Table Schema

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/usage.md

Example SQL DDL for creating a 'pilots' table with an auto-incrementing ID, first name, last name, and a unique constraint on first and last names.

```sql
CREATE TABLE pilots (
    id serial PRIMARY KEY NOT NULL,
    first_name text NOT NULL,
    last_name text NOT NULL,
    UNIQUE (first_name, last_name)
);
```

--------------------------------

### Building Queries with Build Method

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/using-queries.md

Demonstrates how to use the Build method to obtain the SQL query string and its arguments.

```go
queryString, args, err := psql.Select(...).Build(ctx)
```

--------------------------------

### Use PostgreSQL Concat Starter

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/psql/how-to-use.md

Demonstrates how to use the `Concat` starter function for joining multiple expressions with '||' in PostgreSQL.

```go
psql.Concat("a", "b", "c")
```

--------------------------------

### Building Queries with MustBuild Method

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/using-queries.md

Shows how to use the MustBuild method, which panics if an error occurs during query construction. Useful for initializing queries once.

```go
myquery, myargs := psql.Insert(...).MustBuild(ctx)
```

--------------------------------

### Configure Pluralization Inflections

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/configuration.md

Control singular/plural generation rules with inflections. This example defines a rule to convert 'ium' suffixes to 'ia'.

```yaml
inflections:
  plural:
    ium: ia
  plural_exact:
```

--------------------------------

### Bob: Get user by primary key

Source: https://github.com/stephenafamo/bob/blob/main/website/vs/jet.md

Retrieves a user model by its primary key using Bob's generated functions.

```go
user, err := models.FindUser(ctx, db, 1)
```

--------------------------------

### Wrap Existing *sql.DB with bob.NewDB

Source: https://context7.com/stephenafamo/bob/llms.txt

Wrap an existing `*sql.DB` instance with `bob.NewDB` to use Bob's features. Alternatively, use `bob.Open` or `bob.OpenDB` to open a new connection.

```go
import "github.com/stephenafamo/bob"

// From an existing *sql.DB
sqlDB, err := sql.Open("postgres", dsn)
db := bob.NewDB(sqlDB)

// Or open directly
db, err := bob.Open("postgres", dsn)

// Run operations in a transaction (auto-commit or rollback)
err = db.RunInTx(ctx, nil, func(ctx context.Context, tx bob.Executor) error {
    _, err := bob.Exec(ctx, tx, psql.Insert(
        im.Into("orders"),
        im.Values(psql.Arg(userID, productID, qty)),
    ))
    return err
})
```

--------------------------------

### Configure Multi-Table Relationships

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/configuration.md

Define relationships that span multiple tables by specifying multiple 'sides'. This example shows how users can be related to videos through teams.

```yaml
relationships:
  users:
    - name: 'users_to_videos_through_teams'
      sides:
        - from: 'users'
          to: 'teams'
          columns: [[team_id, id]]
        - from: 'teams'
          to: 'videos'
          columns: [[id, team_id]]
```

--------------------------------

### Generate SQL Query Code

Source: https://github.com/stephenafamo/bob/blob/main/README.md

Example of generating a Go function for a SQL query. The generated function takes parameters based on the query placeholders.

```sql
-- UserPosts
SELECT * FROM posts WHERE user_id = $1
```

--------------------------------

### Database Connection Wrappers

Source: https://context7.com/stephenafamo/bob/llms.txt

Shows how to wrap standard `*sql.DB` instances or open new connections using Bob's `bob.DB` type.

```APIDOC
## Database Connection Wrappers

### `bob.NewDB` / `bob.Open` / `bob.OpenDB` — Wrap `*sql.DB`

Bob wraps the standard `*sql.DB` in a `bob.DB` type that implements `bob.Executor`.

#### Usage
```go
import "github.com/stephenafamo/bob"

// From an existing *sql.DB
sqlDB, err := sql.Open("postgres", dsn)
db := bob.NewDB(sqlDB)

// Or open directly
db, err := bob.Open("postgres", dsn)

// Run operations in a transaction (auto-commit or rollback)
err = db.RunInTx(ctx, nil, func(ctx context.Context, tx bob.Executor) error {
    _, err := bob.Exec(ctx, tx, psql.Insert(
        im.Into("orders"),
        im.Values(psql.Arg(userID, productID, qty))
    ))
    return err
})
```
```

--------------------------------

### Run Bob Gen SQL Driver with Configuration File

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/sql.md

Execute the SQL driver generation using a configuration file for settings.

```sh
# With configuration file
go run github.com/stephenafamo/bob/gen/bobgen-sql@latest -c ./config/bobgen.yaml
```

--------------------------------

### Register a Hook

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/hooks.md

Hooks are added to a model's hook collection using the Add method. For example, BeforeUpdateHooks can be registered with a specific hook function.

```go
userTable.BeforeUpdateHooks.Add(myHook)
```

--------------------------------

### Use NameAs() for Aliased Table Name

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/view.md

Demonstrates using the NameAs() method to get a quoted table name with an alias, suitable for query construction.

```go
import (
	"github.com/stephenafamo/bob/dialect/psql"
	"github.com/stephenafamo/bob/dialect/psql/sm"
)
query := psql.Select(sm.From(userView.NameAs()))
```

--------------------------------

### Converting *sql.DB to Bob Executor

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/sql-executor/why-not.md

Demonstrates how to convert a standard *sql.DB or *sql.Tx object into Bob's Executor interface using bob.NewDB and bob.NewTx.

```go
db, err := sql.Open("postgres", "postgres://...")
bobExec := bob.NewDB(db)

// For Transactions
tx, err := db.Begin()
bobExec = bob.NewTx(tx) // using the transaction
```

--------------------------------

### Create a Table Model

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/table.md

Defines the structure for a User and its corresponding Setter, then initializes a new Table model for the 'users' table.

```go
type User struct {
    ID        int     `db:",pk"` // needed to know the primary key when updating
    VehicleID int
    Name      string
    Email     string
}

// UserSetter must implement orm.Setter
type UserSetter struct {
    ID        omit.Val[int]
    VehicleID omit.Val[int]
    Name      omit.Val[string]
    Email     omit.Val[string]
}

var userTable = psql.NewTable[any, User, UserSetter]("public", "users")
```

--------------------------------

### Execute Non-Returning Query

Source: https://context7.com/stephenafamo/bob/llms.txt

Executes a SQL statement that does not return rows, such as INSERT, UPDATE, or DELETE. Returns a `sql.Result` which can be used to get the number of affected rows.

```go
result, err := bob.Exec(ctx, db, psql.Delete(
    dm.From("sessions"),
    dm.Where(psql.Quote("expires_at").LT(psql.Arg(time.Now()))),
))
if err != nil {
    return err
}
n, _ := result.RowsAffected()
fmt.Printf("deleted %d expired sessions\n", n)
```

--------------------------------

### Using Raw SQL Queries

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/building-queries.md

Demonstrates how to embed raw SQL queries directly into your Bob query construction, either as a top-level query using `RawQuery()` or within a clause using `Raw()`. Placeholders in raw SQL should be question marks `?`.

```go
// SELECT * from users WHERE id = $1 AND name = $2
// args: 100, "Stephen"

psql.RawQuery(`SELECT * FROM USERS WHERE id = ? and name = ?`, 100, "Stephen")
// -----
// OR
// -----
psql.Select(
	sm.From("users"),
	sm.Where(psql.Raw("id = ? and name = ?", 100, "Stephen")),
)
```

--------------------------------

### Executing Built Queries

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/using-queries.md

Illustrates how to execute a built query using a standard Go database connection (*sql.DB or *sql.Tx) with ExecContext.

```go
ctx := context.Background()

// Build the query
myquery, myargs := psql.Insert(...).MustBuild(ctx)

// Execute the query
err := db.ExecContext(ctx, myquery, myargs...)
```

--------------------------------

### Create Custom Modifier with ModFunc

Source: https://context7.com/stephenafamo/bob/llms.txt

Custom query modifiers can be created using `bob.ModFunc`. This example shows how to add `UPPER(name)` to the column list in a select query.

```go
// Custom mod using ModFunc
uppercaseNames := bob.ModFunc[*dialect.SelectQuery](func(q *dialect.SelectQuery) {
    // add UPPER(name) to column list
})

q := psql.Select(
    sm.From("users"),
    uppercaseNames,
    sm.Where(psql.Quote("active").EQ(psql.Arg(true))),
)
```

```go
// Convert a typed slice of mods to []Mod[T]
setters := []*models.UserSetter{setter1, setter2}
users, err := models.Users.Insert(bob.ToMods(setters)).All(ctx, db)
```

--------------------------------

### Prepare Query Statements

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/sql-executor/prepare.md

Illustrates how to prepare and execute statements that are expected to return rows, using `bob.PrepareQuery` or `bob.PrepareQueryx`.

```APIDOC
## Query Statements

Statements that are expected to return rows, are instead prepared with `bob.PrepareQuery` or `bob.PrepareQueryx`.

In addition to `Exec`, this statement also has `One`, `All` and `Cursor` methods.

```go
q := psql.Select(...)

// Prepare the statement
stmt, err := bob.PrepareQuery(ctx, db, q, scan.StructMapper[userObj]())
if err != nil {
    // ...
}

// Use our prepared statement
users, err := stmt.All(ctx)
if err != nil {
    // ...
}
```
```

--------------------------------

### Building Complex Expressions

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/building-queries.md

Illustrates how to construct complex SQL expressions using chained starter functions and operator methods, or by using the `And()` function for combining multiple conditions.

```go
// Query: ($1 >= 50) AND ("name" IS NOT NULL)
// Args: 'Stephen'
psql.Arg("Stephen").GTE(psql.Raw(50)).
	And(psql.Quote("name").IsNotNull())

// OR

psql.And(
	psql.Arg("Stephen").GTE(psql.Raw(50)),
	psql.Quote("name").IsNotNull(),
)
```

--------------------------------

### Execute Query and Get Cursor

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/sql-executor/cursor.md

Use `bob.Cursor` to execute a query and obtain a cursor for iterating over results. This is ideal for large datasets. Ensure the cursor is closed after use.

```go
type userObj struct {
    ID int
    Name string
}

ctx := context.Background()
db, err := bob.Open("postgres", "...")
if err != nil {
    // ...
}

q := psql.Select(...)

// user is of type userObj{}
cursor, err := bob.Cursor(ctx, db, q, scan.StructMapper[userObj]())
if err != nil {
    // ...
}
deferr cursor.Close() // make sure to close

for cursor.Next() {
    user, err := cursor.Get() // scan the next row into the concrete type
}
```

--------------------------------

### Load Direct Count of Jets for Multiple Pilots

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/relationships.md

Load the count of jets for multiple pilot model instances in a slice. This is an efficient way to get counts for a collection.

```go
pilots, err := models.Pilots().All(ctx, db)

// Load jet counts for all pilots
err = pilots.LoadCountJets(ctx, db)
```

--------------------------------

### Executing a Generated Query

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/queries.md

Demonstrates how to create an instance of a generated query function and execute it using one of the provided finishers like `One()`, `All()`, or `Cursor()`.

```go
query := AllUsers(1)

// Example of using a finisher:
// query.One(ctx, db) -> AllUsersRow
// query.All(ctx, db) -> []AllUsersRow
// query.Cursor(ctx, db) -> scan.ICursor[AllUsersRow]
```

--------------------------------

### Use pgx/v5 with Bob

Source: https://context7.com/stephenafamo/bob/llms.txt

Integrate `pgx/v5` connection pools with Bob using the `drivers/pgx` package. The pool directly implements `bob.Executor`.

```go
import bobpgx "github.com/stephenafamo/bob/drivers/pgx"

pool, err := bobpgx.New(ctx, "postgres://user:pass@localhost/mydb")
if err != nil {
    log.Fatal(err)
}
defer pool.Close()

// pool implements bob.Executor directly
users, err := bob.All(ctx, pool,
    psql.Select(sm.Columns("id", "name")), sm.From("users")),
    scan.StructMapper[User](),
)

// Transactions
tx, err := pool.Begin(ctx)
if err != nil {
    log.Fatal(err)
}
defer tx.Rollback(ctx)

_, err = bob.Exec(ctx, tx, psql.Insert(
    im.Into("events"),
    im.Values(psql.Arg("login", userID)),
))
if err != nil {
    return err
}
return tx.Commit(ctx)
```

--------------------------------

### Jet: Get user by primary key

Source: https://github.com/stephenafamo/bob/blob/main/website/vs/jet.md

Retrieves a user record by primary key using Jet's query builder. Requires manual mapping and explicit WHERE clause.

```go
var user *model.Users
err = postgres.
    SELECT(table.Users.AllColumns).
    FROM(table.Users).
    WHERE(table.Users.ID.EQ(postgres.Int(1))).
    QueryContext(ctx, db, user)
```

--------------------------------

### Create Jet with Database Interaction

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/factories.md

Create a new jet or a slice of jets from a template, inserting them into the database. Any required relations will also be created. Variants exist to handle errors by returning them, panicking, or failing the test/benchmark.

```go
// Create a new jet from the template
jet, err := jetTemplate.Create(ctx, db)

// Create a slice of 5 jets using the template
jets, err := jetTemplate.CreateMany(ctx, db, 5)

// Must variants panic on error
jet := jetTemplate.MustCreate(ctx, db)
jets := jetTemplate.MustCreateMany(ctx, db, 5)

// OrFail variants will fail the test or benchmark if an error occurs
jet := jetTemplate.CreateOrFail(t, db)
jets := jetTemplate.CreateManyOrFail(t, db, 5)
```

--------------------------------

### SQLite dialect query builders (Select, Insert, Update, Delete)

Source: https://context7.com/stephenafamo/bob/llms.txt

Uses a Mod-based API for SQLite queries, employing '?' placeholders and double-quote identifiers. This example demonstrates a SELECT statement.

```go
import (
    "github.com/stephenafamo/bob/dialect/sqlite"
    "github.com/stephenafamo/bob/dialect/sqlite/sm"
)

q := sqlite.Select(
    sm.Columns("id", "name"),
    sm.From("users"),
    sm.Where(sqlite.Quote("active").EQ(sqlite.Arg(true))),
    sm.Limit(sqlite.Arg(20)),
)
// sql:  SELECT id, name FROM users WHERE ("active" = ?) LIMIT ?
// args: [true, 20]
```

--------------------------------

### Test All Go Modules

Source: https://github.com/stephenafamo/bob/blob/main/README.md

Command to run Go tests for all modules within the repository. This provides a comprehensive test of the entire project.

```bash
$ go test ./...
```

--------------------------------

### Build Complex SQL Queries with Bob

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/principles.md

Demonstrates building a complex SQL query with window functions, aliasing, and arithmetic operations using Bob's Go API. Supports literal strings for simpler parts.

```go
// Assuming we're building the following query
/*
SELECT status,
    LEAD(created_date, 1, NOW())
    OVER(PARTITION BY presale_id ORDER BY created_date) -
    created_date AS "difference"
FROM presales_presalestatus
*/

// different ways to express "SELECT status"
psql.Select(sm.Columns("status")) // SELECT status
psql.Select(sm.Columns(sm.Quote("status"))) // SELECT "status"

// Ways to express LEAD(created_date, 1, NOW())
"LEAD(created_date, 1, NOW()"
psql.F("LEAD", "created_date", 1, "NOW()")
psql.F("LEAD", "created_date", 1, sm.F("NOW"))

// Ways to express PARTITION BY presale_id ORDER BY created_date
"PARTITION BY presale_id ORDER BY created_date"
sm.Window("").PartitionBy("presale_id").OrderBy("created_date")

// Expressing LEAD(...) OVER(...)
"LEAD(created_date, 1, NOW()) OVER(PARTITION BY presale_id ORDER BY created_date)"
psql.F("LEAD", "created_date", 1, psql.F("NOW")).
    Over().
    PartitionBy("presale_id").
    OrderBy("created_date")

// The full query
psql.Select(
    sm.Columns(
        "status",
        psql.F("LEAD", "created_date", 1, psql.F("NOW")).
            Over().
            PartitionBy("presale_id").
            OrderBy("created_date").
            Minus("created_date").
            As("difference")),
    sm.From("presales_presalestatus")),
)

```

--------------------------------

### Compile Query to SQL and Arguments

Source: https://context7.com/stephenafamo/bob/llms.txt

Compiles a Bob query into an SQL string and a slice of arguments. Use `bob.Build` for general use or `bob.MustBuild` to panic on error. `bob.BuildN` allows custom argument numbering.

```go
import "github.com/stephenafamo/bob"

q := psql.Select(
    sm.Columns("id"),
    sm.From("users"),
    sm.Where(psql.Quote("active").EQ(psql.Arg(true))),
)

ctx := context.Background()
sql, args, err := bob.Build(ctx, q)
// sql:  SELECT id FROM users WHERE ("active" = $1)
// args: [true]

// Panics on error — useful for package-level initialization
sql, args = bob.MustBuild(ctx, q)

// Build with arg numbering starting at a custom index
sql, args, err = bob.BuildN(ctx, q, 5)
// → "active" = $5
```

--------------------------------

### Update Multiple Tables in MySQL

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/mysql/examples/update.md

Update records across multiple tables by listing them in the FROM clause and specifying join conditions in the WHERE clause. This example increments a sales count based on employee and account names.

```go
mysql.Update(
  um.Table("employees, accounts"),
  um.SetCol("sales_count").To("sales_count + 1"),
  um.Where(mysql.Quote("accounts", "name").EQ(mysql.Arg("Acme Corporation"))),
  um.Where(mysql.Quote("employees", "id").EQ(mysql.Quote("accounts", "sales_person"))),
)
```

--------------------------------

### Run Custom Generator

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/plugins/writing-custom-plugins.md

After adding your custom plugin, run your generator using `go run` instead of the default `bobgen-psql` command.

```bash
go run ./cmd/my-generator -c bob.yaml
```

--------------------------------

### Generated Factory

Source: https://context7.com/stephenafamo/bob/llms.txt

Demonstrates how to use generated factories like `factory.NewUser` to create model instances, with options for customization.

```APIDOC
### Generated factory — `factory.NewUser`

#### Usage
```go
// Create 10 comments; users and posts are created automatically
f := factory.New()
comments, err := f.NewComment().CreateMany(ctx, db, 10)

// Customise the factory template
user, err := f.NewUser(
    factory.UserMods.Name.Set("Bob"),
    factory.UserMods.Age.Set(25),
).Create(ctx, db)
```
```

--------------------------------

### Bob PostgreSQL Generator Main Function

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/plugins/writing-custom-plugins.md

This is the main entry point for a custom Bob generator, similar to `bobgen-psql`. It sets up context, parses CLI arguments, loads configuration, and initiates the generation process using `gen.Run`.

```go
package main

import (
    "context"
    "fmt"
    "os"
    "os/signal"
    "syscall"

    "github.com/stephenafamo/bob/gen"
    helpers "github.com/stephenafamo/bob/gen/bobgen-helpers"
    "github.com/stephenafamo/bob/gen/bobgen-psql/driver"
    "github.com/stephenafamo/bob/gen/plugins"
    "github.com/urfave/cli/v2"
)

func main() {
    ctx, cancel := signal.NotifyContext(
        context.Background(),
        syscall.SIGINT,
        syscall.SIGTERM,
    )
    defer cancel()

    app := &cli.App{
        Name:      "bobgen-psql",
        Usage:     "Generate models and factories from your PostgreSQL database",
        UsageText: "bobgen-psql [-c FILE]",
        Version:   helpers.Version(),
        Flags: []cli.Flag{
            &cli.StringFlag{
                Name:    "config",
                Aliases: []string{"c"},
                Value:   helpers.DefaultConfigPath,
                Usage:   "Load configuration from `FILE`",
            },
        },
        Action: run,
    }

    if err := app.RunContext(ctx, os.Args); err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
}

func run(c *cli.Context) error {
    config, driverConfig, pluginsConfig, err := helpers.GetConfigFromFile[any, driver.Config](c.String("config"), "psql")
    if err != nil {
        return fmt.Errorf("failed to load configuration: %w", err)
    }

    outputPlugins := plugins.Setup[any, any, driver.IndexExtra](
        pluginsConfig, gen.PSQLTemplates,
    )

    state := &gen.State[any]{Config: config}
    return gen.Run(c.Context, state, driver.New(driverConfig), outputPlugins...)
}

```

--------------------------------

### bob.Build / bob.MustBuild - Compile a query to SQL string and args

Source: https://context7.com/stephenafamo/bob/llms.txt

Compiles a query object into an executable SQL string and its corresponding arguments.

```APIDOC
## bob.Build / bob.MustBuild

### Description
Compile a query to SQL string and args.

### Usage
```go
import "github.com/stephenafamo/bob"

q := psql.Select(
    sm.Columns("id"),
    sm.From("users"),
    sm.Where(psql.Quote("active").EQ(psql.Arg(true))),
)

ctx := context.Background()
sql, args, err := bob.Build(ctx, q)
// sql:  SELECT id FROM users WHERE ("active" = $1)
// args: [true]

// Panics on error — useful for package-level initialization
sql, args = bob.MustBuild(ctx, q)

// Build with arg numbering starting at a custom index
sql, args, err = bob.BuildN(ctx, q, 5)
// → "active" = $5
```
```

--------------------------------

### Execute and Fetch Update Query Results

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/table.md

Executes an UPDATE query and demonstrates fetching the results using Exec(), One(), All(), and Cursor() methods.

```go
rowsAffected, _ := updateQ.Exec(ctx, db)
user, _ := updateQ.One(ctx, db)
users, _ := updateQ.All(ctx, db)
userCursor, _ := updateQ.Cursor(ctx, db)
```

--------------------------------

### Build SQL Query with Mods

Source: https://github.com/stephenafamo/bob/blob/main/README.md

Demonstrates building a SQL SELECT query using query mods with the PostgreSQL dialect. Each mod represents a clause or condition.

```go
psql.Select(
    sm.From("users"), // This is a query mod
    sm.Where(psql.Quote("age").GTE(psql.Arg(21))), // This is also a mod
)
```

--------------------------------

### Build Comparison Expressions

Source: https://context7.com/stephenafamo/bob/llms.txt

Demonstrates common expression chain methods for building SQL comparisons like GTE, Like, In, Between, IsNull, and EQ. These methods can be chained with `As` to alias the resulting expression.

```go
psql.Quote("age").GTE(psql.Arg(18))           // "age" >= $1
psql.Quote("name").Like(psql.S("Alice%"))     // "name" LIKE 'Alice%'
psql.Quote("id").In(psql.Arg(1, 2, 3))        // "id" IN ($1, $2, $3)
psql.Quote("score").Between(psql.Arg(0), psql.Arg(100)) // "score" BETWEEN $1 AND $2
psql.Quote("deleted_at").IsNull()             // "deleted_at" IS NULL
psql.Quote("status").EQ(psql.Arg("active")).As("is_active")
// ("status" = $1) AS "is_active"
```

--------------------------------

### Run Bob Gen SQL Driver with Environment Variable

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/sql.md

Execute the SQL driver generation using an environment variable to specify the database dialect.

```sh
# With env variable
SQL_DIALECT=psql go run github.com/stephenafamo/bob/gen/bobgen-sql@latest
```

--------------------------------

### Prepare Exec Statements

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/sql-executor/prepare.md

Demonstrates how to prepare and execute a statement that does not return rows.

```APIDOC
## Prepare Exec Statements

A statement is created with `bob.Prepare()`.

```go
ctx := context.Background()
db, err := bob.Open("postgres", "...")
if err != nil {
    // ...
}

q := psql.Update(...)

// Prepare the statement
stmt, err := bob.Prepare(ctx, db, q)
if err != nil {
    // ...
}
```

Prepared statements can then be reused as many times as we want.

```go
// Use our prepared statement
_, err := stmt.Exec(ctx)
if err != nil {
    // ...
}
```
```

--------------------------------

### Configure SQL Query Generation

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/queries.md

Specify the folders containing your SQL files in the `queries` configuration option. Bob will generate Go code for each `.sql` file found.

```yaml
sqlite:
  dsn: file.db
  queries:
    - ./path/to/folder/containing/sql/files
    - ./another/folder
```

--------------------------------

### Query Debugging Wrappers

Source: https://context7.com/stephenafamo/bob/llms.txt

Explains how to use `bob.Debug`, `bob.DebugToWriter`, and `bob.DebugToPrinter` to log SQL queries and their arguments before execution.

```APIDOC
## Debugging

### `bob.Debug` / `bob.DebugToWriter` / `bob.DebugToPrinter`

Wraps an `Executor` to print every SQL query and its arguments before execution.

#### Usage
```go
import "github.com/stephenafamo/bob"

// Print to stdout
debugDB := bob.Debug(db)

// Print to any io.StringWriter (e.g., a logger buffer)
var buf strings.Builder
debugDB = bob.DebugToWriter(db, &buf)

// Use a custom DebugPrinter
type myPrinter struct{}
func (p myPrinter) PrintQuery(query string, args ...any) {
    log.Printf("SQL: %s | ARGS: %v", query, args)
}
debugDB = bob.DebugToPrinter(db, myPrinter{})

// Use as a normal executor — queries are logged automatically
user, err := bob.One(ctx, debugDB,
    psql.Select(sm.Columns("id")), sm.From("users"), sm.Where(psql.Quote("id").EQ(psql.Arg(1)))
    scan.StructMapper[User]()
)
// stdout: SELECT id FROM users WHERE ("id" = $1)
//         0: int: 1
```
```

--------------------------------

### Prepare a Query Statement

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/sql-executor/prepare.md

Use `bob.PrepareQuery` to create a prepared statement for operations that are expected to return rows. This statement provides methods like `One`, `All`, and `Cursor` in addition to `Exec`.

```go
q := psql.Select(...)

// Prepare the statement
stmt, err := bob.PrepareQuery(ctx, db, q, scan.StructMapper[userObj]())
if err != nil {
    // ...
}
```

```go
// Use our prepared statement
users, err := stmt.All(ctx)
if err != nil {
    // ...
}
```

--------------------------------

### Create a New View Model

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/view.md

Define a User struct and create a new psql View model named 'userView' for the 'users' table.

```go
import (
	"github.com/stephenafamo/bob"
	"github.com/stephenafamo/bob/expr"
	"github.com/stephenafamo/bob/dialect/psql"
)

type User struct {
	ID	int
	Name  string
	Email string
}

var userView = psql.NewView[*User, bob.Expression]("public", "users", expr.ColsForStruct[User]("users"))
```

--------------------------------

### Run Bob Code Generation

Source: https://context7.com/stephenafamo/bob/llms.txt

Commands to execute Bob code generation using a configuration file. Use `bobgen-psql` for PostgreSQL or `bobgen-sql` for SQL schema files.

```bash
# Run code generation
bobgen-psql -c bob.yaml

# Or for SQL schema files
bobgen-sql -c bob.yaml
```

--------------------------------

### pgx/v5 Connection Pool Wrapper

Source: https://context7.com/stephenafamo/bob/llms.txt

Demonstrates how to use the `drivers/pgx` package to wrap a `pgx/v5` connection pool, making it compatible with `bob.Executor`.

```APIDOC
### `pgx.New` / `pgx.NewPool` — pgx/v5 connection pool

The `drivers/pgx` package provides a `bob.Executor`-compatible wrapper for `pgx/v5` connection pools.

#### Usage
```go
import bobpgx "github.com/stephenafamo/bob/drivers/pgx"

pool, err := bobpgx.New(ctx, "postgres://user:pass@localhost/mydb")
if err != nil {
    log.Fatal(err)
}
defer pool.Close()

// pool implements bob.Executor directly
users, err := bob.All(ctx, pool,
    psql.Select(sm.Columns("id", "name")), sm.From("users")),
    scan.StructMapper[User]()
)

// Transactions
tx, err := pool.Begin(ctx)
if err != nil {
    log.Fatal(err)
}
defer tx.Rollback(ctx)

_, err = bob.Exec(ctx, tx, psql.Insert(
    im.Into("events"),
    im.Values(psql.Arg("login", userID))
))
if err != nil {
    return err
}
return tx.Commit(ctx)
```
```

--------------------------------

### Run Bob Gen SQLite with Configuration File

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/sqlite.md

Execute the Bob code generator for SQLite using a configuration file. The `-c` flag specifies the path to the configuration file.

```sh
# With configuration file
go run github.com/stephenafamo/bob/gen/bobgen-sqlite@latest -c ./config/bobgen.yaml
```

--------------------------------

### Prepare an Exec Statement

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/sql-executor/prepare.md

Use `bob.Prepare` to create a prepared statement for operations that do not return rows. This statement can be executed multiple times using its `Exec` method.

```go
ctx := context.Background()
db, err := bob.Open("postgres", "...")
if err != nil {
    // ...
}

q := psql.Update(...)

// Prepare the statement
stmt, err := bob.Prepare(ctx, db, q)
if err != nil {
    // ...
}
```

```go
// Use our prepared statement
_, err := stmt.Exec(ctx)
if err != nil {
    // ...
}
```

--------------------------------

### Simple Select with Limit and Offset Arguments

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/psql/examples/select.md

Paginate query results by specifying LIMIT and OFFSET using arguments.

```go
psql.Select(
  sm.Columns("id", "name"),
  sm.From("users"),
  sm.Offset(psql.Arg(15)),
  sm.Limit(psql.Arg(10)),
)
```

--------------------------------

### Register a New Output with StatePlugin

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/plugins/writing-custom-plugins.md

Implement the `StatePlugin` interface to register a new output. This is useful for generating entirely separate packages like REST handlers or validation code.

```go
//go:embed templates
var myTemplates embed.FS

type myPlugin[C any] struct{}

func (myPlugin[C]) Name() string {
    return "my-plugin"
}

func (myPlugin[C]) PlugState(state *gen.State[C]) error {
    templates, err := fs.Sub(myTemplates, "templates")
    if err != nil {
        return fmt.Errorf("failed to load templates: %w", err)
    }

    state.Outputs = append(state.Outputs, &gen.Output{
        Key:       "my-output",   // unique identifier, used by other plugins to find this output
        OutFolder: "myoutput",    // directory where generated files are written
        PkgName:   "myoutput",    // Go package name for generated files
        Templates: []fs.FS{templates}, // Go templates to render
    })

    return nil
}
```

--------------------------------

### Create Query Arguments

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/starters.md

Use `Arg` to represent one or more arguments in a query. These are replaced with placeholders and the arguments are returned separately.

```go
psql.Arg("a", "b", "c")
```

--------------------------------

### Bob: Retrieve user with relations

Source: https://github.com/stephenafamo/bob/blob/main/website/vs/jet.md

Retrieves a user and eagerly loads associated videos using Bob's relationship loading capabilities.

```go
// User will contain the videos
user, err := models.Users(
    models.SelectWhere.Users.ID.EQ(1),
    models.SelectThenLoad.User.Videos(),
).One(ctx, db)
```

--------------------------------

### Using Parameters in Bob Queries

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/parameters.md

Use `sm.Arg()` to safely include parameters in your SQL queries. This generates the correct placeholder for different SQL dialects (Postgres, SQLite, MySQL) and returns the values in the argument slice.

```go
// args: 100, "Stephen"
// Postgres: SELECT * from users WHERE "id" = $1 AND "name" = $2
// SQLite: SELECT * from users WHERE "id" = ?1 AND "name" = ?2
// MySQL: SELECT * from users WHERE "id" = ? AND "name" = ?
psql.Select(
    sm.From("users"),
    sm.Where(psql.Quote("id").EQ(psql.Arg(100))),
    sm.Where(psql.Quote("name").EQ(psql.Arg("Stephen"))),
)
```

--------------------------------

### Extend an Existing Output with Custom Templates

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/plugins/writing-custom-plugins.md

Modify the `PlugState` method to find an existing output by its key and append your custom templates to it. This allows adding functionality to already generated packages.

```go
func (m myPlugin[C]) PlugState(state *gen.State[C]) error {
    for _, output := range state.Outputs {
        if output.Key == "models" {
            output.Templates = append(output.Templates, myTemplates)
            break
        }
    }

    return nil
}
```

--------------------------------

### Run Bob Gen with Environment Variable

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/psql.md

Execute the PostgreSQL driver generation using an environment variable for the DSN. This is a quick way to provide connection details.

```sh
# With env variable
PSQL_DSN=postgres://user:pass@host:port/dbname go run github.com/stephenafamo/bob/gen/bobgen-psql@latest
```

--------------------------------

### Test Single Go Module

Source: https://github.com/stephenafamo/bob/blob/main/README.md

Command to run Go tests for a specific module. Useful for testing individual components of the project.

```bash
$ go test ./dialect/psql/
```

--------------------------------

### Configure Bob Plugins

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/configuration.md

Use the `plugins` section in your bobgen.yaml file to enable, disable, and configure built-in plugins. The `plugins_preset` key offers a quick way to set all plugins to 'all', 'default', or 'none'.

```yaml
plugins_preset: 'all' # Valid values are "default", "all" or "none".
plugins:
  dbinfo:
    disabled: false
    pkgname: 'dbinfo'
    destination: 'dbinfo'
  enums:
    disabled: false
    pkgname: 'enums'
    destination: 'enums'
  models:
    disabled: false
    pkgname: 'models'
    destination: 'models'
  factory:
    disabled: false
    pkgname: 'factory'
    destination: 'factory'
  dberrors:
    disabled: false
    pkgname: 'dberrors'
    destination: 'dberrors'
  where:
    disabled: false
  loaders:
    disabled: false
  joins:
    disabled: false
  counts:
    disabled: false
```

--------------------------------

### Build Jet Setter from Template

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/factories.md

Build a new jet setter or a slice of jet setters from a template. These methods do not interact with the database.

```go
// Build a new jet setter from the template
// naturally, this ignores relationships
jetSetter := jetTemplate.BuildSetter()

// Build a slice of 5 jet setters using the template
// also ignores relationships
jetSetters := jetTemplate.BuildManySetter(5)
```

--------------------------------

### SELECT from a Function

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/query-builder/sqlite/examples/select.md

Demonstrates selecting data generated by a SQL function, aliasing the result for further use.

```go
sqlite.Select(
  sm.From(sqlite.F("generate_series", 1, 3)).As("x"),
)
```

--------------------------------

### Implement Lifecycle Hooks with bob.Hooks

Source: https://context7.com/stephenafamo/bob/llms.txt

Define and append lifecycle hooks for queries and models using `bob.Hooks`. Hooks can be skipped selectively using context manipulation.

```go
import "github.com/stephenafamo/bob"

// Define a hook: log every INSERT
var insertHooks bob.Hooks[*dialect.InsertQuery, bob.SkipQueryHooksKey]

insertHooks.AppendHooks(func(ctx context.Context, exec bob.Executor, q *dialect.InsertQuery) (context.Context, error) {
    log.Println("inserting row...")
    return ctx, nil
})

// Skip hooks for a specific context
ctx = bob.SkipHooks(ctx)
// or selectively:
ctx = bob.SkipQueryHooks(ctx)
ctx = bob.SkipModelHooks(ctx)
```

--------------------------------

### Build a SELECT Query with WHERE and LIMIT

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/models/view.md

Constructs a SELECT query using the Query() method on a View model, applying WHERE and LIMIT clauses.

```go
import (
	"github.com/stephenafamo/bob/dialect/psql"
	"github.com/stephenafamo/bob/dialect/psql/sm"
)

query := userView.Query(
	sm.Where(
		psql.Quote("name").In(
			psql.Arg("Ayan", "Rudra", "Ila")
		),
		sm.Limit(10), // LIMIT 10
	)
)
```

--------------------------------

### Load Custom Plugin in Run Function

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/plugins/writing-custom-plugins.md

Add your custom plugin to the `run` function alongside built-in plugins. Plugins are executed in the order they are passed, so ensure dependencies are met.

```go
func run(c *cli.Context) error {
    config, driverConfig, pluginsConfig, err := helpers.GetConfigFromFile[any, driver.Config](c.String("config"), "psql")
    if err != nil {
        return fmt.Errorf("failed to load configuration: %w", err)
    }

    builtinPlugins := plugins.Setup[any, any, driver.IndexExtra](
        pluginsConfig,
        gen.PSQLTemplates,
    )

    // Add your custom plugin after the built-in ones
    allPlugins := append(builtinPlugins, &myPlugin[any]{})

    state := &gen.State[any]{Config: config}
    return gen.Run(c.Context, state, driver.New(driverConfig), allPlugins...)
}
```

--------------------------------

### Run Bob Gen MySQL Driver with Configuration File

Source: https://github.com/stephenafamo/bob/blob/main/website/docs/code-generation/mysql.md

Execute the MySQL code generation tool specifying a configuration file path. This is useful for managing complex or multiple configurations.

```sh
# With configuration file
go run github.com/stephenafamo/bob/gen/bobgen-mysql@latest -c ./config/bobgen.yaml
```