### Quick Start with Bun

Source: https://bun.uptrace.dev/guide/golang-orm.html

A complete example demonstrating how to define a model, initialize a Bun database instance, create a table, and perform basic insert and select operations.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "github.com/uptrace/bun"
    "github.com/uptrace/bun/dialect/sqlitedialect"
    "github.com/uptrace/bun/driver/sqliteshim"
    "github.com/uptrace/bun/extra/bundebug"
)

type User struct {
    bun.BaseModel `bun:"table:users,alias:u"`
    ID    int64  `bun:",pk,autoincrement"`
    Name  string `bun:",notnull"`
    Email string `bun:",unique"`
}

func main() {
    ctx := context.Background()
    sqldb, _ := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
    db := bun.NewDB(sqldb, sqlitedialect.New())
    db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))
    _, _ = db.NewCreateTable().Model((*User)(nil)).IfNotExists().Exec(ctx)
    user := &User{Name: "John Doe", Email: "john@example.com"}
    _, _ = db.NewInsert().Model(user).Exec(ctx)
    var selectedUser User
    _ = db.NewSelect().Model(&selectedUser).Where("email = ?", "john@example.com").Scan(ctx)
    fmt.Printf("User: %+v\n", selectedUser)
}
```

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

### Initialize Bun Database Connection

Source: https://bun.uptrace.dev/guide

Demonstrates how to connect to different SQL databases using Bun ORM. Each example includes the specific dialect and driver setup required for PostgreSQL, SQLite, MySQL, and SQL Server.

```go
package main
import (
	"context"
	"database/sql"
	"fmt"
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/pgdialect"
	"github.com/uptrace/bun/driver/pgdriver"
	"github.com/uptrace/bun/extra/bundebug"
)
func main() {
	ctx := context.Background()
	dsn := "postgres://postgres:@localhost:5432/test?sslmode=disable"
	pgdb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))
	db := bun.NewDB(pgdb, pgdialect.New())
	db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))
	var rnd float64
	if err := db.NewSelect().ColumnExpr("random()").Scan(ctx, &rnd); err != nil {
		panic(err)
	}
	fmt.Println("Random number:", rnd)
}
```

```go
package main
import (
	"context"
	"database/sql"
	"fmt"
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/sqlitedialect"
	"github.com/uptrace/bun/driver/sqliteshim"
	"github.com/uptrace/bun/extra/bundebug"
)
func main() {
	ctx := context.Background()
	sqlite, err := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
	if err != nil {
		panic(err)
	}
	db := bun.NewDB(sqlite, sqlitedialect.New())
	db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))
	var rnd int64
	if err := db.NewSelect().ColumnExpr("random()").Scan(ctx, &rnd); err != nil {
		panic(err)
	}
	fmt.Println("Random number:", rnd)
}
```

```go
package main
import (
	"context"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/mysqldialect"
	"github.com/uptrace/bun/extra/bundebug"
)
func main() {
	ctx := context.Background()
	sqldb, err := sql.Open("mysql", "root:pass@/test")
	if err != nil {
		panic(err)
	}
	db := bun.NewDB(sqldb, mysqldialect.New())
	db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))
	var rnd float64
	if err := db.NewSelect().ColumnExpr("rand()").Scan(ctx, &rnd); err != nil {
		panic(err)
	}
	fmt.Println("Random number:", rnd)
}
```

```go
package main
import (
	"context"
	"database/sql"
	"fmt"
	_ "github.com/denisenkom/go-mssqldb"
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/mssqldialect"
	"github.com/uptrace/bun/extra/bundebug"
)
func main() {
	ctx := context.Background()
	sqldb, err := sql.Open("sqlserver", "sqlserver://sa:passWORD1@localhost:1433?database=test")
	if err != nil {
		panic(err)
	}
	db := bun.NewDB(sqldb, mssqldialect.New())
	db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))
	var rnd float64
	if err := db.NewSelect().ColumnExpr("rand()").Scan(ctx, &rnd); err != nil {
		panic(err)
	}
	fmt.Println("Random number:", rnd)
}
```

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

### Start Bun Application

Source: https://bun.uptrace.dev/guide/starter-kit.html

Demonstrates how to start a Bun application using the provided starter kit. It includes error handling and ensures the application is stopped gracefully using defer.

```go
func main() {
	ctx, app, err := bunapp.Start(ctx, "service_name", "environment_name")
	if err != nil {
		panic(err)
	}
	defer app.Stop()
}
```

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

### Configure Database Connections

Source: https://bun.uptrace.dev/guide/golang-orm.html

Examples for initializing Bun database connections for PostgreSQL, MySQL, and SQLite using their respective dialects and drivers.

```go
// PostgreSQL
sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN("postgres://user:password@localhost:5432/dbname?sslmode=disable")))
db := bun.NewDB(sqldb, pgdialect.New())

// MySQL
sqldb, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname?parseTime=true")
db := bun.NewDB(sqldb, mysqldialect.New())

// SQLite
sqldb, _ := sql.Open(sqliteshim.ShimName, "file:test.db?cache=shared&mode=rwc")
db := bun.NewDB(sqldb, sqlitedialect.New())
```

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

### Manage Database Migrations

Source: https://bun.uptrace.dev/guide

Provides an example of defining migration functions to create and drop tables using Bun's migration system.

```go
func init() {
    migrations.Add("20230101_create_users", func(ctx context.Context, db *bun.DB) error {
        _, err := db.NewCreateTable().
            Model((*User)(nil)).
            IfNotExists().
            Exec(ctx)
        return err
    }, func(ctx context.Context, db *bun.DB) error {
        _, err := db.NewDropTable().
            Model((*User)(nil)).
            IfExists().
            Exec(ctx)
        return err
    })
}
```

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

### Install and Use bundebug for Query Logging

Source: https://bun.uptrace.dev/guide/debugging.html

Installs the bundebug package and demonstrates how to add a query hook to the Bun DB instance. It covers printing failed queries by default, all queries with verbose mode, and enabling/disabling via environment variables.

```go
go get github.com/uptrace/bun/extra/bundebug
```

```go
import "github.com/uptrace/bun/extra/bundebug"

db := bun.NewDB(sqldb, dialect)
db.AddQueryHook(bundebug.NewQueryHook())
```

```go
bundebug.NewQueryHook(bundebug.WithVerbose(true))
```

```go
bundebug.NewQueryHook(
    // disable the hook
    bundebug.WithEnabled(false),

    // BUNDEBUG=1 logs failed queries
    // BUNDEBUG=2 logs all queries
    bundebug.FromEnv("BUNDEBUG"),
)
```

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

### Start, Commit, and Rollback SQL Transactions with Bun

Source: https://bun.uptrace.dev/guide/transactions.html

Demonstrates how to initiate a SQL transaction using `db.BeginTx` and subsequently commit or rollback the transaction using `tx.Commit` or `tx.Rollback`. This is the foundational step for managing atomic database operations.

```go
type Tx struct {
	*sql.Tx
	db *DB
}

tx, err := db.BeginTx(ctx, &sql.TxOptions{})

err = tx.Commit()

err = tx.Rollback()
```

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

### Install Bun and Database Drivers

Source: https://bun.uptrace.dev/guide/golang-orm.html

Commands to install the core Bun package and various database drivers required for PostgreSQL, SQLite, MySQL, or SQL Server.

```bash
go get github.com/uptrace/bun@latest
go get github.com/uptrace/bun/driver/pgdriver
go get github.com/uptrace/bun/driver/sqliteshim
go get github.com/go-sql-driver/mysql
go get github.com/denisenkom/go-mssqldb
```

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

### Golang Bun MERGE Query Example for MSSQL

Source: https://bun.uptrace.dev/guide/query-merge.html

Provides a practical example of creating a MERGE query for MSSQL using the Bun ORM in Golang. It illustrates defining models, using temporary data, and specifying WHEN MATCHED and WHEN NOT MATCHED clauses.

```go
type Model struct {
	ID    int64 `bun:",pk,autoincrement"`
	Name  string
	Value string
}

newModels := []*Model{
	{Name: "A", Value: "world"},
	{Name: "B", Value: "test"},
}

return db.NewMerge().
	Model(new(Model)).
	With("_data", db.NewValues(&newModels)).
	Using("_data").
	On("?TableAlias.name = _data.name").
	WhenUpdate("MATCHED", func(q *bun.UpdateQuery) *bun.UpdateQuery {
		return q.Set("value = _data.value")
	}).
	WhenInsert("NOT MATCHED", func(q *bun.InsertQuery) *bun.InsertQuery {
		return q.Value("name", "_data.name").Value("value", "_data.value")
	}).
	Returning("$action")
```

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

### Install bunotel for OpenTelemetry Instrumentation

Source: https://bun.uptrace.dev/guide/performance-monitoring.html

This command installs the `bunotel` module, which provides OpenTelemetry instrumentation for Bun database operations. It's a prerequisite for enabling tracing and metrics.

```go
go get github.com/uptrace/bun/extra/bunotel
```

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

### Perform Basic and Bulk Insertions with Bun

Source: https://bun.uptrace.dev/guide/query-insert.html

Demonstrates how to insert a single model instance or a slice of models into a database using Bun's InsertQuery. The bulk insert example also shows how auto-incremented IDs are scanned back into the model slice.

```go
book := &Book{Title: "hello"}
res, err := db.NewInsert().Model(book).Exec(ctx)

books := []Book{book1, book2}
res, err := db.NewInsert().Model(&books).Exec(ctx)
if err != nil {
	panic(err)
}

for _, book := range books {
	fmt.Println(book.ID)
}
```

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

### Select and Count Rows with Bun

Source: https://bun.uptrace.dev/guide/query-select.html

Examples for retrieving data into models, counting total rows, and using the combined ScanAndCount helper for efficient pagination.

```go
book := new(Book)
err := db.NewSelect().Model(book).Where("id = ?", 123).Scan(ctx)

count, err := db.NewSelect().Model((*User)(nil)).Count(ctx)

var users []User
count, err := db.NewSelect().Model(&users).Limit(20).ScanAndCount(ctx)
```

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

### Test with Multiple Databases using Build Tags and Interfaces

Source: https://bun.uptrace.dev/guide/drivers.html

This Go code demonstrates how to test application logic against multiple database backends (PostgreSQL, MySQL, SQLite). It uses build tags and a test function that iterates through different database setups.

```go
// +build integration

func TestUserOperations(t *testing.T) {
    databases := []struct {
        name string
        db   *bun.DB
    }{
        {"postgres", setupPostgreSQL(t)},
        {"mysql", setupMySQL(t)},
        {"sqlite", setupSQLite(t)},
    }

    for _, tc := range databases {
        t.Run(tc.name, func(t *testing.T) {
            testUserCRUD(t, tc.db)
        })
    }
}
```

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

### Executing a Drop Table Query in Golang

Source: https://bun.uptrace.dev/guide/query-drop-table.html

Provides an example of executing a DROP TABLE query for PostgreSQL/MySQL using the `bun` ORM. This snippet shows how to specify the model for the table to be dropped and includes the `IfExists` option to prevent errors if the table does not exist. Error handling is demonstrated.

```go
_,
 err := db.NewDropTable().Model((*Book)(nil)).IfExists().Exec(ctx)
if err != nil {
	panic(err)
}
```

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

### Implement Model Query Hooks

Source: https://bun.uptrace.dev/guide/hooks.html

Provides examples of various model-specific query hooks including Select, Insert, Update, Delete, and Table lifecycle hooks.

```go
var _ bun.BeforeSelectHook = (*Model)(nil)

func (*Model) BeforeSelect(ctx context.Context, query *bun.SelectQuery) error { return nil }

var _ bun.AfterSelectHook = (*Model)(nil)

func (*Model) AfterSelect(ctx context.Context, query *bun.SelectQuery) error { return nil }

var _ bun.BeforeInsertHook = (*Model)(nil)

func (*Model) BeforeInsert(ctx context.Context, query *bun.InsertQuery) error { nil }

var _ bun.AfterInsertHook = (*Model)(nil)

func (*Model) AfterInsert(ctx context.Context, query *bun.InsertQuery) error { return nil }

var _ bun.BeforeUpdateHook = (*Model)(nil)

func (*Model) BeforeUpdate(ctx context.Context, query *bun.UpdateQuery) error { return nil }

var _ bun.AfterUpdateHook = (*Model)(nil)

func (*Model) AfterUpdate(ctx context.Context, query *bun.UpdateQuery) error { return nil }

var _ bun.BeforeDeleteHook = (*Model)(nil)

func (*Model) BeforeDelete(ctx context.Context, query *bun.DeleteQuery) error { return nil }

var _ bun.AfterDeleteHook = (*Model)(nil)

func (*Model) AfterDelete(ctx context.Context, query *bun.DeleteQuery) error { return nil }
```

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

### Connect to Oracle Database with Bun

Source: https://bun.uptrace.dev/guide/drivers.html

Establishes a connection to an Oracle database using the oci8 driver. Requires CGO and Oracle Client libraries installed on the host system.

```go
import (
	"database/sql"
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/oracledialect"
	_ "github.com/mattn/go-oci8"
)

func connectOracle() *bun.DB {
	dsn := "user/password@localhost:1521/xe"
	sqldb, err := sql.Open("oci8", dsn)
	if err != nil {
		panic(err)
	}
	return bun.NewDB(sqldb, oracledialect.New())
}
```

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

### Bun Application Hooks

Source: https://bun.uptrace.dev/guide/starter-kit.html

Illustrates how to implement and manage hooks for starting and stopping a Bun application. Hooks allow for custom logic execution during app lifecycle events, such as defining HTTP routes or logging stop messages.

```go
func init() {
	bunapp.OnStart("hook.name", func(ctx context.Context, app *bunapp.App) error {
		app.Router().GET("/endpoint", handler)

		app.OnStop("hook.name", func(ctx context.Context, app *bunapp.App) error {
			log.Println("stopping...")
		})

		return nil
	})
}
```

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

### Manage Database Schema with Bun ORM in Go

Source: https://bun.uptrace.dev/guide/golang-orm.html

Provides examples for creating, dropping, and altering database tables and indexes using Bun's schema management methods. It covers single and multiple table creations, index management, and resetting models. Requires `context.Context`.

```Go
// Create a single table
_, err := db.NewCreateTable().
    Model((*User)(nil)).
    IfNotExists().
    Exec(ctx)
```

```Go
// Create table with indexes
_, err := db.NewCreateTable().
    Model((*User)(nil)).
    IfNotExists().
    Exec(ctx)

// Add index after table creation
_, err = db.NewCreateIndex().
    Model((*User)(nil)).
    Index("idx_users_email").
    Column("email").
    Exec(ctx)
```

```Go
// Drop table
_, err := db.NewDropTable().
    Model((*User)(nil)).
    IfExists().
    Cascade(). // Drop dependent objects
    Exec(ctx)
```

```Go
// Reset model (drop and recreate)
err := db.ResetModel(ctx, (*User)(nil))
```

```Go
// Create multiple tables with proper ordering
models := []interface{}{
    (*User)(nil),
    (*Profile)(nil),
    (*Post)(nil),
}

for _, model := range models {
    _, err := db.NewCreateTable().
        Model(model).
        IfNotExists().
        Exec(ctx)
    if err != nil {
        return err
    }
}
```

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

### Log Queries with Logrus using logrusbun

Source: https://bun.uptrace.dev/guide/debugging.html

Explains how to integrate Logrus for logging SQL queries executed by the Bun ORM. It includes installation instructions for the logrusbun package and configuration options for the query hook.

```go
go get github.com/oiime/logrusbun
```

```go
import "github.com/oiime/logrusbun"

db := bun.NewDB(sqldb, dialect)

log := logrus.New()
db.AddQueryHook(logrusbun.NewQueryHook(logrusbun.QueryHookOptions{Logger: log}))
```

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

### Define Model Relationships in Bun

Source: https://bun.uptrace.dev/guide/golang-orm.html

Examples of how to define common database relationships using Bun struct tags and how to query them using the Relation method.

```go
type Post struct {
    bun.BaseModel `bun:"table:posts"`
    ID       int64  `bun:",pk,autoincrement"`
    Title    string `bun:",notnull"`
    Content  string
    AuthorID int64  `bun:",notnull"`
    Author *User `bun:"rel:belongs-to,join:author_id=id"`
}

// Query with relationship
var posts []Post
err := db.NewSelect().Model(&posts).Relation("Author").Where("post.status = ?", "published").Scan(ctx)
```

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

### Bun Model Structure with Base Model and Ignored Fields in Go

Source: https://bun.uptrace.dev/guide/models.html

Demonstrates a Go User model using bun.BaseModel for table configuration and includes examples of exported fields mapping to columns and unexported fields being ignored by Bun.

```go
type User struct {
    bun.BaseModel `bun:"table:users,alias:u"`

    // Exported fields become database columns
    ID       int64     `bun:"id,pk,autoincrement"`
    Name     string    `bun:"name,notnull"`
    Email    string    `bun:"email,unique"`
    IsActive bool      `bun:"is_active,default:true"`

    // Unexported fields are ignored by Bun
    password string
    cache    map[string]interface{}
}

```

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

### Bun ORM: Single and Composite Primary Keys in Go

Source: https://bun.uptrace.dev/guide/models.html

Illustrates how to define single and composite primary keys for Bun models in Go using the `pk` struct tag. The first example shows a single auto-incrementing ID, while the second shows a composite key for a UserRole model.

```go
// Single primary key
type User struct {
    ID int64 `bun:"id,pk,autoincrement"`
}

// Composite primary key
type UserRole struct {
    UserID int64 `bun:"user_id,pk"`
    RoleID int64 `bun:"role_id,pk"`
}

```

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

### Integrate Bun ORM with Existing Database Transactions in Go

Source: https://bun.uptrace.dev/guide/golang-orm.html

Shows how to use the Bun query builder within an existing transaction started by the standard `database/sql` package. This allows mixing raw SQL execution and ORM operations seamlessly. It requires a `*sql.Tx` object and `context.Context`.

```Go
// Start a transaction with database/sql
tx, err := sqldb.Begin()
if err != nil {
    panic(err)
}
defer tx.Rollback()

// Execute existing SQL code
if _, err := tx.Exec("UPDATE users SET last_login = NOW() WHERE id = ?", userID); err != nil {
    return err
}

// Use Bun query builder with the same transaction
user := &User{Name: "New User"}
_, err = db.NewInsert().
    Conn(tx). // Use existing transaction
    Model(user).
    Exec(ctx)
if err != nil {
    return err
}

// Commit the transaction
return tx.Commit()
```

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

### Log Queries with Zap using bunzap

Source: https://bun.uptrace.dev/guide/debugging.html

Details the integration of Zap logger for SQL query logging with the Bun ORM. It provides the installation command for bunzap and an example of setting up the query hook with custom options like slow query duration.

```go
go get github.com/alexlast/bunzap
```

```go
import "github.com/alexlast/bunzap"
import "go.uber.org/zap"
import "time"

db := bun.NewDB(sqldb, dialect)

logger, err := zap.NewProduction()
db.AddQueryHook(bunzap.NewQueryHook(bunzap.QueryHookOptions{
    Logger:       logger,
    SlowDuration: 200 * time.Millisecond, // Omit to log all operations as debug
}))
```

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

### Implement Manual Query Execution

Source: https://bun.uptrace.dev/guide/hooks.html

Demonstrates the recommended approach of manually handling logic before and after database operations instead of relying on hooks for business logic.

```go
func InsertUser(ctx context.Context, db *bun.DB, user *User) error {
	// before insert

	if _, err := db.NewInsert().Model(user).Exec(ctx); err != nil {
		return err
	}

	// after insert

	return nil
}
```

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

### Golang: Create Table with Bun ORM

Source: https://bun.uptrace.dev/guide/query-create-table.html

Demonstrates the basic syntax for creating a table using Bun's CreateTableQuery. It shows how to specify table names, column types, foreign keys, partitioning, and tablespaces. The `Exec` method is used to execute the query.

```go
db.NewCreateTable().

	Model(&strct).

	Table("table1"). // quotes table names
	TableExpr("table1"). // arbitrary unsafe expression
	ModelTableExpr("table1"). // overrides model table name

	Temp().
	IfNotExists().
	Varchar(100). // turns VARCHAR into VARCHAR(100)

	WithForeignKeys().
	ForeignKey(`(fkey) REFERENCES table1 (pkey) ON DELETE CASCADE`).
	PartitionBy("HASH (id)").
	TableSpace("fasttablespace").

	Exec(ctx)
```

```go
_, err := db.NewCreateTable().
	Model((*Book)(nil)).
	ForeignKey(`("author_id") REFERENCES "users" ("id") ON DELETE CASCADE`).
	Exec(ctx)
if err != nil {
	panic(err)
}
```

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

### MSSQL MERGE Query Example

Source: https://bun.uptrace.dev/guide/query-merge.html

This example demonstrates how to construct a MERGE query specifically for MSSQL, including defining a data model, providing source data, and specifying update and insert actions.

```APIDOC
## MSSQL MERGE Query Example

### Description
This example shows a practical implementation of a MERGE query for MSSQL using the Bun ORM. It defines a `Model` struct, prepares new data, and configures the `MERGE` statement with `ON`, `WHEN MATCHED` (for updates), and `WHEN NOT MATCHED` (for inserts) clauses.

### Method
`db.NewMerge()`

### Endpoint
N/A (This is a client-side ORM operation)

### Parameters
- **Model struct**: Defines the structure of the data being merged.
- **newModels**: A slice of structs containing the data to be merged.
- **db.NewValues(&newModels)**: Creates a values structure from the provided slice.
- **Using("_data")**: Specifies the source of the data for the merge.
- **On("?TableAlias.name = _data.name")**: Sets the condition for matching rows between the target table and the source data.
- **WhenUpdate("MATCHED", func(q *bun.UpdateQuery) *bun.UpdateQuery { ... })**: Defines the update logic when a match is found.
- **WhenInsert("NOT MATCHED", func(q *bun.InsertQuery) *bun.InsertQuery { ... })**: Defines the insert logic when no match is found.
- **Returning("$action")**: Specifies what to return after the merge operation.

### Request Example
```go
type Model struct {
	ID    int64 `bun:",pk,autoincrement"`
	Name  string
	Value string
}

newModels := []*Model{
	{Name: "A", Value: "world"},
	{Name: "B", Value: "test"},
}

return db.NewMerge().
	Model(new(Model)).
	With("_data", db.NewValues(&newModels)).
	Using("_data").
	On("?TableAlias.name = _data.name").
	WhenUpdate("MATCHED", func(q *bun.UpdateQuery) *bun.UpdateQuery {
		return q.Set("value = _data.value")
	}).
	WhenInsert("NOT MATCHED", func(q *bun.InsertQuery) *bun.InsertQuery {
		return q.Value("name", "_data.name").Value("value", "_data.value")
	}).
	Returning("$action")
```

### Response
N/A (This is a client-side ORM operation, the ORM handles response parsing)

### Response Example
N/A
```

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

### Handle Database Migrations

Source: https://bun.uptrace.dev/guide/golang-orm.html

Explains how to define and execute schema migrations using the bun/migrate package to manage table creation and destruction.

```go
migrations := migrate.NewMigrations()
migrations.MustRegister(func(ctx context.Context, db *bun.DB) error {
    _, err := db.NewCreateTable().Model((*User)(nil)).Exec(ctx)
    return err
}, func(ctx context.Context, db *bun.DB) error {
    _, err := db.NewDropTable().Model((*User)(nil)).Exec(ctx)
    return err
})
migrator := migrate.NewMigrator(db, migrations)
migrator.Migrate(ctx)
```

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

### Using Basic and Positional Placeholders

Source: https://bun.uptrace.dev/guide/placeholders.html

Demonstrates how to use standard '?' placeholders and positional arguments like '?0' to safely inject values into SQL queries.

```go
// SELECT 'foo', 'bar'
db.ColumnExpr("?, ?", 'foo', 'bar')

// SELECT 'foo', 'bar', 'foo'
db.ColumnExpr("?0, ?1, ?0", 'foo', 'bar')
```

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

### Common Table Expressions (CTEs)

Source: https://bun.uptrace.dev/guide/golang-orm.html

Provides an example of using recursive CTEs for hierarchical data.

```APIDOC
## Common Table Expressions (CTEs)

### Description
Shows how to define and use Common Table Expressions, including recursive CTEs.

### Recursive CTE Example
```go
// Recursive CTE
cte := db.NewSelect().
    With("RECURSIVE user_hierarchy", db.NewSelect().
        ColumnExpr("id, name, manager_id, 0 as level").
        Model((*User)(nil)).
        Where("manager_id IS NULL").
        UnionAll(
            db.NewSelect().
                ColumnExpr("u.id, u.name, u.manager_id, uh.level + 1").
                TableExpr("users u").
                Join("JOIN user_hierarchy uh ON u.manager_id = uh.id"),
        ),
    ).
    Table("user_hierarchy").
    Column("*"

var hierarchy []struct {
    ID        int64  `bun:"id"`
    Name      string `bun:"name"`
    ManagerID *int64 `bun:"manager_id"`
    Level     int    `bun:"level"`
}

err := cte.Scan(ctx, &hierarchy)
```
```

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

### Build and Execute Select Queries with Bun

Source: https://bun.uptrace.dev/guide/queries.html

Demonstrates how to use Bun's query builder to construct a SELECT statement with expressions and placeholders, and how to execute it to scan results into a model.

```go
err := db.NewSelect().
	Model(book).
	ColumnExpr("lower(name)").
	Where("? = ?", bun.Ident("id"), "some-id").
	Scan(ctx)
```

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

### Scan Rows Manually

Source: https://bun.uptrace.dev/guide/queries.html

Provides examples for scanning custom query results and iterating over rows using the Rows() method.

```go
// Scan row by row
rows, err := db.NewSelect().Model((*User)(nil)).Rows(ctx)
if err != nil {
	panic(err)
}
defer rows.Close()

for rows.Next() {
	user := new(User)
	if err := db.ScanRow(ctx, rows, user); err != nil {
		panic(err)
	}
}
```

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

### Implement Global Query Hooks

Source: https://bun.uptrace.dev/guide/hooks.html

Demonstrates how to create a global query hook for logging and performance monitoring by implementing the QueryHook interface.

```go
type QueryHook struct{}

func (h *QueryHook) BeforeQuery(ctx context.Context, event *bun.QueryEvent) context.Context {
	return ctx
}

func (h *QueryHook) AfterQuery(ctx context.Context, event *bun.QueryEvent) {
	fmt.Println(time.Since(event.StartTime), string(event.Query))
}

db.AddQueryHook(&QueryHook{})
```

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

### Using Bun QueryBuilder

Source: https://bun.uptrace.dev/guide/query-where.html

Shows how to use the QueryBuilder interface to create reusable query components and how to unwrap them to execute the final query.

```go
func addWhere(q bun.QueryBuilder) bun.QueryBuilder {
    return q.Where("id = ?", 123)
}
qb := db.NewSelect().QueryBuilder()
addWhere(qb)
qb = db.NewSelect().QueryBuilder().Where("id = ?", 123)
selectQuery := qb.Unwrap().(*bun.SelectQuery)
```

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

### Deleting a Single Row

Source: https://bun.uptrace.dev/guide/query-delete.html

A simple example showing how to delete a single row based on a specific ID using the Bun DeleteQuery builder.

```go
res, err := db.NewDelete().Where("id = ?", 123).Exec(ctx)
```

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

### Using Bun's IDB Interface for Transactional Operations

Source: https://bun.uptrace.dev/guide/transactions.html

Demonstrates how the `bun.IDB` interface allows methods to work seamlessly with both `*bun.DB` and `bun.Tx`. The `InsertUser` function is shown to accept `bun.IDB`, enabling it to perform operations both directly on the database and within transactions.

```go
// Insert single user using bun.DB.
err := InsertUser(ctx, db, user1)

// Insert several users in a transaction.
err := db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
    if err := InsertUser(ctx, tx, user1); err != nil {
        return err
    }
    if err := InsertUser(ctx, tx, user2); err != nil {
        return err
    }
    return nil
})

func InsertUser(ctx context.Context, db bun.IDB, user *User) error {
	_, err := db.NewInsert().Model(user).Exec(ctx)
	return err
}
```

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

### Execute Raw SQL Queries

Source: https://bun.uptrace.dev/guide/query-select.html

Demonstrates how to execute arbitrary raw SQL queries using Bun's NewRaw method with parameter binding for safety.

```go
err := db.NewRaw(
	"SELECT id, name FROM ? LIMIT ?",
	bun.Ident("users"), 100,
).Scan(ctx, &users)
```

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

### Load Fixtures in Go

Source: https://bun.uptrace.dev/guide/fixtures.html

Initialize the fixture loader and load data from a filesystem into the database. Includes options for recreating or truncating tables during the load process.

```go
db.RegisterModel((*User)(nil), (*Org)(nil))
fixture := dbfixture.New(db)
err := fixture.Load(ctx, os.DirFS("testdata"), "fixture.yml")

// Options for table management
fixture := dbfixture.New(db, dbfixture.WithRecreateTables())
fixture := dbfixture.New(db, dbfixture.WithTruncateTables())
```

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

### Create Separate *bun.DB Instances for Multiple Databases

Source: https://bun.uptrace.dev/guide/drivers.html

This Go code shows how to manage multiple databases within the same application by creating separate `*bun.DB` instances. Each instance is configured with its specific database connection and dialect.

```go
pgDB := bun.NewDB(pgSQLDB, pgdialect.New())
mysqlDB := bun.NewDB(mySQLDB, mysqldialect.New())
```

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

### Schema Management

Source: https://bun.uptrace.dev/guide/golang-orm.html

Provides examples for managing database schema, including creating, dropping, and altering tables, as well as adding indexes using Bun's schema management methods.

```APIDOC
## Schema Management

### Description
Manage your database schema using Bun's ORM. This section covers creating and dropping tables, adding indexes, and handling multiple tables with dependencies.

### Creating and Dropping Tables
```go
// Create a single table
_, err := db.NewCreateTable().
    Model((*User)(nil)).
    IfNotExists().
    Exec(ctx)

// Create table with indexes
_, err := db.NewCreateTable().
    Model((*User)(nil)).
    IfNotExists().
    Exec(ctx)

// Add index after table creation
_, err = db.NewCreateIndex().
    Model((*User)(nil)).
    Index("idx_users_email").
    Column("email").
    Exec(ctx)

// Drop table
_, err := db.NewDropTable().
    Model((*User)(nil)).
    IfExists().
    Cascade(). // Drop dependent objects
    Exec(ctx)

// Reset model (drop and recreate)
err := db.ResetModel(ctx, (*User)(nil))
```

### Multiple Tables and Dependencies
```go
// Create multiple tables with proper ordering
models := []interface{}{
    (*User)(nil),
    (*Profile)(nil),
    (*Post)(nil),
}

for _, model := range models {
    _, err := db.NewCreateTable().
        Model(model).
        IfNotExists().
        Exec(ctx)
    if err != nil {
        return err
    }
}
```
```

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

### Execute Raw SQL Queries with Bun Scanning in Go

Source: https://bun.uptrace.dev/guide/golang-orm.html

Demonstrates how to execute raw SQL queries using Bun's `NewRaw` method and scan the results directly into Go structs. This is useful for complex queries or when migrating from existing SQL code. It requires the `bun` package and a `context.Context`.

```Go
type User struct {
    ID   int64
    Name string
    CreatedAt time.Time
}

users := make([]User, 0)

// Use Bun's powerful scanning with raw SQL
err := db.NewRaw(
    "SELECT id, name, created_at FROM ? WHERE status = ? ORDER BY created_at DESC LIMIT ?",
    bun.Ident("users"), "active", 100,
).Scan(ctx, &users)
```

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

### Define Custom Time Type in Go

Source: https://bun.uptrace.dev/guide/custom-types.html

Defines a custom 'Time' struct that embeds time.Time and sets a specific time format for database interactions. This is the initial setup for creating a custom scanner and valuer.

```go
const timeFormat = "15:04:05.999999999"

type Time struct {
	time.Time
}
```

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

### Go Cursor Struct and Constructor

Source: https://bun.uptrace.dev/guide/cursor-pagination.html

Defines the Go struct for 'Cursor' to hold the start and end IDs for pagination. The NewCursor function constructs a Cursor object from a slice of entries, capturing the first and last IDs.

```go
type Cursor struct {
    Start *int64 `json:"start,omitempty"` // First item ID for previous page
    End   *int64 `json:"end,omitempty"`   // Last item ID for next page
}

func NewCursor(entries []Entry) Cursor {
    cursor := Cursor{}
    if len(entries) > 0 {
        cursor.Start = &entries[0].ID
        cursor.End = &entries[len(entries)-1].ID
    }
    return cursor
}
```

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

### Performance Optimization: Indexing and Selective Loading

Source: https://bun.uptrace.dev/guide/models.html

Demonstrates how to define index-friendly models and use lightweight structs to perform selective field loading for better performance.

```go
type UserSummary struct {
    ID       int64  `bun:"id"`
    Username string `bun:"username"`
    Email    string `bun:"email"`
}

var users []UserSummary
err := db.NewSelect().Model(&users).Limit(100).Scan(ctx)
```

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

### Constructing a Drop Table Query in Golang

Source: https://bun.uptrace.dev/guide/query-drop-table.html

Demonstrates how to build a DROP TABLE query using the `bun` ORM. It shows various methods for specifying the table to be dropped, including using a model, a table name string, or an arbitrary SQL expression. Options like `IfExists`, `Cascade`, and `Restrict` can be chained.

```go
db.NewDropTable().

	Model(&strct).

	Table("table1"). // quotes table names
	TableExpr("table1"). // arbitrary unsafe expression
	ModelTableExpr("table1"). // overrides model table name

	IfExists().

	Cascade().
	Restrict().

	Exec(ctx)
```

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

### Enable OpenTelemetry Tracing for Bun Queries

Source: https://bun.uptrace.dev/guide/performance-monitoring.html

To enable tracing for Bun database queries, you must ensure that an active OpenTelemetry span context is present when executing queries. This example shows how to retrieve the context from a request and use it with a `db.NewSelect` operation.

```go
ctx := req.Context()
err := db.NewSelect().Scan(ctx)
```

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

### Configure Default Values and NullZero Tag

Source: https://bun.uptrace.dev/guide/models.html

Demonstrates how to use the nullzero tag to convert Go zero values to SQL NULL and define complex default expressions.

```go
type User struct {
    ID        int64     `bun:"id,pk,autoincrement"`
    Name      string    `bun:"name,nullzero,notnull,default:'Anonymous'"`
    CreatedAt time.Time `bun:"created_at,nullzero,notnull,default:current_timestamp"`
    UUID      string    `bun:"uuid,type:uuid,default:gen_random_uuid()"`
}
```

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

### Implement Window Functions

Source: https://bun.uptrace.dev/guide/golang-orm.html

Shows how to use SQL window functions like ROW_NUMBER and RANK within Bun queries using ColumnExpr.

```go
err := db.NewSelect().Model(&results).ColumnExpr("*, ROW_NUMBER() OVER (PARTITION BY status ORDER BY created_at) as row_num").ColumnExpr("RANK() OVER (ORDER BY post_count DESC) as post_rank").Scan(ctx)
```

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

### Raw Queries with Bun Scanning

Source: https://bun.uptrace.dev/guide/golang-orm.html

Demonstrates how to execute raw SQL queries using Bun's `NewRaw` method and scan the results into Go structs. It also shows the generated SQL for clarity.

```APIDOC
## Raw Queries with Bun Scanning

### Description
Execute raw SQL queries and scan results into Go structs using Bun's `NewRaw` method. This example shows how to select data from a 'users' table with filtering and ordering.

### Method
`db.NewRaw(...).Scan(...)

### Endpoint
N/A (Direct database operation)

### Parameters
#### Query Parameters
- **"users"** (bun.Ident) - Placeholder for table name.
- **"active"** (string) - Filter condition for the 'status' column.
- **100** (int) - Limit for the number of results.

### Request Example
```go
type User struct {
    ID   int64
    Name string
    CreatedAt time.Time
}

users := make([]User, 0)

err := db.NewRaw(
    "SELECT id, name, created_at FROM ? WHERE status = ? ORDER BY created_at DESC LIMIT ?",
    bun.Ident("users"), "active", 100,
).Scan(ctx, &users)
```

### Response
#### Success Response (200)
- **users** ([]User) - A slice of User structs containing the queried data.

#### Response Example
```sql
SELECT id, name, created_at FROM "users" WHERE status = 'active' ORDER BY created_at DESC LIMIT 100
```
```

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

### Configure Query Debugging in Bun

Source: https://bun.uptrace.dev/guide/golang-orm.html

Demonstrates how to enable query logging using the bundebug hook or by implementing a custom hook to monitor execution duration and query strings.

```go
import "github.com/uptrace/bun/extra/bundebug"

db.AddQueryHook(bundebug.NewQueryHook(
    bundebug.WithVerbose(true),
    bundebug.FromEnv("BUNDEBUG"),
))

type DebugHook struct{}
func (h *DebugHook) BeforeQuery(ctx context.Context, event *bun.QueryEvent) context.Context { return ctx }
func (h *DebugHook) AfterQuery(ctx context.Context, event *bun.QueryEvent) {
    fmt.Printf("Query: %s\nDuration: %s\n", event.Query, event.Dur)
}
db.AddQueryHook(&DebugHook{})
```

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

### Manage Bun Migrations

Source: https://bun.uptrace.dev/guide/pg-migration.html

Commands to initialize, mark as applied, and check the status of database migrations using the Bun CLI tool.

```bash
go run cmd/bun/main.go -env=dev db init
go run cmd/bun/main.go -env=dev db mark_applied
go run cmd/bun/main.go -env=dev db status
```

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

### Many-to-Many Relationship in Go

Source: https://bun.uptrace.dev/guide/relations.html

Illustrates how to define a many-to-many relationship using Bun ORM. This involves using the `m2m` tag on the relationship field, defining an intermediary model (e.g., `OrderToItem`), and registering this intermediary model with the database. The example shows an `Order` model related to `Item` models through `OrderToItem`.

```go
func init() {
    // Register many to many model so bun can better recognize m2m relation.
    // This should be done before you use the model for the first time.
    db.RegisterModel((*OrderToItem)(nil))
}

type Order struct {
	ID    int64  `bun:",pk"`
    // Order and Item in join:Order=Item are fields in OrderToItem model
	Items []Item `bun:"m2m:order_to_items,join:Order=Item"`
}

type Item struct {
	ID int64 `bun:",pk"`
}

type OrderToItem struct {
	OrderID int64  `bun:",pk"`
	Order   *Order `bun:"rel:belongs-to,join:order_id=id"`
	ItemID  int64  `bun:",pk"`
	Item    *Item  `bun:"rel:belongs-to,join:item_id=id"`
}
```

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

### Polymorphic Has-Many Relationship in Go

Source: https://bun.uptrace.dev/guide/relations.html

Defines a polymorphic has-many relationship where comments can belong to different models (e.g., Article, Post). It uses a 'type' virtual column and the 'polymorphic' option to manage the relationship. The example shows how to store comments in a single table while linking them to their respective parent models.

```go
type Article struct {
	ID   int64
	Name string

	Comments []Comment `bun:"rel:has-many,join:id=trackable_id,join:type=trackable_type,polymorphic"`
}

type Post struct {
	ID   int64
	Name string

	Comments []Comment `bun:"rel:has-many,join:id=trackable_id,join:type=trackable_type,polymorphic"`
}

type Comment struct {
	TrackableID   int64  // Article.ID or Post.ID
	TrackableType string // "article" or "post"
	Text          string
}
```

```go
type Article struct {
	ID   int64
	Name string

	Comments []Comment `bun:"rel:has-many,join:id=trackable_id,join:type=trackable_type,polymorphic:mycomment"`
}
```

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

### Bun Database Migrations CLI

Source: https://bun.uptrace.dev/guide/starter-kit.html

Provides an overview of the Bun CLI commands for managing database migrations. It includes available commands for initialization, migration, rollback, unlocking, and creating new migration files (both Go and SQL).

```bash
go run cmd/bun/main.go

NAME:
   bun db - manage database migrations

USAGE:
   bun db [global options] command [command options] [arguments...]

COMMANDS:
   init        create migration tables
   migrate     migrate database
   rollback    rollback the last migration group
   unlock      unlock migrations
   create_go   create a Go migration
   create_sql  create a SQL migration
   help, h     Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --help, -h  show help (default: false)

```

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

### SQL Naming Conventions and Keyword Handling

Source: https://bun.uptrace.dev/guide/models.html

Provides guidelines for using snake_case in Bun tags and demonstrates how to handle SQL reserved keywords by quoting them.

```sql
-- PostgreSQL/SQLite
CREATE TABLE "order" ("user" TEXT);

-- MySQL
CREATE TABLE `order` (`user` TEXT);
```

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

### Optimize Queries with Dialect Checking

Source: https://bun.uptrace.dev/guide/drivers.html

Demonstrates how to perform direct dialect checks to apply database-specific query hints or optimizations. This is useful for advanced tuning that goes beyond standard feature detection.

```go
import "github.com/uptrace/bun/dialect"

func optimizeQuery(db *bun.DB, query *bun.SelectQuery) *bun.SelectQuery {
	switch db.Dialect().Name() {
	case dialect.PG:
		return query.QueryHint("/*+ IndexScan(table_name idx_name) */")
	case dialect.MySQL:
		return query.QueryHint("USE INDEX (idx_name)")
	case dialect.SQLite:
		return query
	case dialect.MSSQL:
		return query.QueryHint("OPTION (RECOMPILE)")
	default:
		return query
	}
}
```

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

### Bun SelectQuery API Overview

Source: https://bun.uptrace.dev/guide/query-select.html

Demonstrates the fluent interface for building complex SELECT queries in Bun, including CTEs, column selection, filtering, grouping, and ordering.

```go
db.NewSelect().
	With("cte_name", subquery).
	Model(&strct).
	Model(&slice).
	Column("col1", "col2").
	ColumnExpr("count(*)").
	Table("table1", "table2").
	Join("JOIN table2 AS t2 ON t2.id = t1.id").
	Where("id = ?", 123).
	Limit(100).
	Scan(ctx)
```

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

### Constructing WHERE Clauses in Bun

Source: https://bun.uptrace.dev/guide/query-where.html

Demonstrates how to use raw strings and safe placeholders with bun.Ident to build dynamic WHERE clauses in Bun queries.

```go
q = q.Where("column LIKE 'hello%'")
q = q.Where("? LIKE ?", bun.Ident("mycolumn"), "hello%")
```

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

### Discover SQL Migrations with Bun

Source: https://bun.uptrace.dev/guide/migrations.html

Embeds SQL migration files and discovers them using the Migrations.Discover method. Supports transactional migrations with `.tx.up.sql` extension and multi-statement migrations using `--bun:split`.

```sql
SELECT 1

--bun:split

SELECT 2
```

```go
//go:embed *.sql
var sqlMigrations embed.FS

func init() {
	if err := Migrations.Discover(sqlMigrations); err != nil {
		panic(err)
	}
}
```

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

### Define User Profile with Relationships

Source: https://bun.uptrace.dev/guide/models.html

Demonstrates how to define Bun models with 'belongs-to' and 'has-many' relationships using struct tags.

```go
type User struct {
    bun.BaseModel `bun:"table:users,alias:u"`

    ID       int64  `bun:"id,pk,autoincrement"`
    Username string `bun:"username,unique,notnull"`
    Email    string `bun:"email,unique,notnull"`

    // Profile relationship
    ProfileID *int64  `bun:"profile_id"`
    Profile   *Profile `bun:"rel:belongs-to,join:profile_id=id"`

    // Posts relationship
    Posts []Post `bun:"rel:has-many,join:id=user_id"`
}

type Profile struct {
    bun.BaseModel `bun:"table:profiles,alias:prof"`

    ID        int64   `bun:"id,pk,autoincrement"`
    FirstName string  `bun:"first_name,notnull"`
    LastName  string  `bun:"last_name,notnull"`
    Bio       *string `bun:"bio"`
    Avatar    *string `bun:"avatar"`
}
```