### Open and Configure New SQLite Database

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Opens a new SQLite database file and sets the page size. This is the initial setup for the database.

```sqlite
.open -new testdb01.db
PRAGMA page_size=512;
```

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

### Example: Create Vector Search Virtual Table

Source: https://github.com/modernc-org/sqlite/blob/master/README.md

Demonstrates creating a virtual table for vector search. The module reads arguments like dimension and metric, declares the table schema, and implements search logic.

```sql
CREATE VIRTUAL TABLE vec_docs USING vec(dim=128, metric="cosine")
```

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

### Implement Virtual Tables with Go using modernc.org/sqlite/vtab

Source: https://context7.com/modernc-org/sqlite/llms.txt

This example shows how to create a virtual table in SQLite backed by a Go data structure. It requires implementing the vtab.Module, vtab.Table, and vtab.Cursor interfaces.

```go
package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "modernc.org/sqlite"
    "modernc.org/sqlite/vtab"
)

// A simple in-memory key-value virtual table
type kvModule struct{}
type kvTable struct{ data map[string]string }
type kvCursor struct {
    keys []string
    vals []string
    pos  int
}

func (m *kvModule) Create(ctx vtab.Context, args []string) (vtab.Table, error) {
    ctx.Declare(fmt.Sprintf("CREATE TABLE %s(key TEXT, value TEXT)", args[2]))
    return &kvTable{data: map[string]string{
        "host": "localhost",
        "port": "5432",
        "db":   "myapp",
    }},
il
}
func (m *kvModule) Connect(ctx vtab.Context, args []string) (vtab.Table, error) {
    return m.Create(ctx, args)
}

func (t *kvTable) BestIndex(info *vtab.IndexInfo) error {
    for i := range info.Constraints {
        c := &info.Constraints[i]
        if c.Usable && c.Column == 0 && c.Op == vtab.OpEQ {
            c.ArgIndex = 0
            c.Omit = true
            info.IdxNum = 1
            return nil
        }
    }
    info.IdxNum = 0
    return nil
}
func (t *kvTable) Open() (vtab.Cursor, error) {
    keys := make([]string, 0, len(t.data))
    vals := make([]string, 0, len(t.data))
    for k, v := range t.data {
        keys = append(keys, k)
        vals = append(vals, v)
    }
    return &kvCursor{keys: keys, vals: vals},
il
}
func (t *kvTable) Disconnect() error { return nil }
func (t *kvTable) Destroy() error    { return nil }

func (c *kvCursor) Filter(idxNum int, idxStr string, vals []vtab.Value) error {
    c.pos = 0
    return nil
}
func (c *kvCursor) Next() error           { c.pos++; return nil }
func (c *kvCursor) Eof() bool             { return c.pos >= len(c.keys) }
func (c *kvCursor) Rowid() (int64, error) { return int64(c.pos + 1), nil }
func (c *kvCursor) Close() error          { return nil }
func (c *kvCursor) Column(col int) (vtab.Value, error) {
    if c.pos >= len(c.keys) {
        return nil, nil
    }
    if col == 0 {
        return c.keys[c.pos], nil
    }
    return c.vals[c.pos], nil
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := vtab.RegisterModule(db, "config", &kvModule{}); err != nil {
        log.Fatal(err)
    }
    db.Exec(`CREATE VIRTUAL TABLE cfg USING config()`)

    rows, err := db.Query(`SELECT key, value FROM cfg ORDER BY key`)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        var k, v string
        rows.Scan(&k, &v)
        fmt.Printf("%s = %s\n", k, v)
    }
    // Output (order may vary):
    // db = myapp
    // host = localhost
    // port = 5432

    var host string
    db.QueryRow(`SELECT value FROM cfg WHERE key = 'host'`).Scan(&host)
    fmt.Println("host:", host) // host: localhost
}
```

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

### Example: Create CSV Loader Virtual Table

Source: https://github.com/modernc-org/sqlite/blob/master/README.md

Illustrates creating a virtual table to load data from a CSV file. The module reads the file header to define columns and streams rows using a cursor.

```sql
CREATE VIRTUAL TABLE csv_users USING csv(filename="/tmp/users.csv", delimiter=",", header=true)
```

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

### Register Connection Hook (Package-level and Driver Method)

Source: https://context7.com/modernc-org/sqlite/llms.txt

Registers a callback function that executes once for each newly opened connection. This is useful for per-connection setup, such as enabling foreign keys or setting PRAGMAs. The package-level hook applies to the default driver, while the `(*Driver).RegisterConnectionHook` method applies to a custom driver instance.

```go
package main

import (
    "database/sql"
    "fmt"
    "log"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

func init() {
    sqlite.RegisterConnectionHook(func(conn sqlite.ExecQuerierContext, dsn string) error {
        // Enable full-text search tokenizer or any per-connection PRAGMA
        _, err := conn.ExecContext(nil, "PRAGMA temp_store = MEMORY", nil)
        _ = err // ignore for brevity
        fmt.Println("new connection to:", dsn)
        return nil
    })
}

func main() {
    // Using a named custom driver with connection hooks
    var d sqlite.Driver
    d.RegisterConnectionHook(func(conn sqlite.ExecQuerierContext, dsn string) error {
        // Enforce foreign keys on every connection
        _, err := conn.ExecContext(nil, "PRAGMA foreign_keys = ON", nil)
        return err
    })
    sql.Register("sqlite-fk", &d)

    db, err := sql.Open("sqlite-fk", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    var fkEnabled int
    db.QueryRow("PRAGMA foreign_keys").Scan(&fkEnabled)
    fmt.Println("foreign_keys:", fkEnabled) // foreign_keys: 1
}

```

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

### RegisterConnectionHook (package-level and Driver method)

Source: https://context7.com/modernc-org/sqlite/llms.txt

Register a callback that runs once per newly opened connection, after all DSN parameters are applied. Useful for per-connection setup such as registering hooks or running custom PRAGMAs.

```APIDOC
## RegisterConnectionHook (package-level and Driver method)

Register a callback that runs once per newly opened connection, after all DSN parameters are applied. Useful for per-connection setup such as registering hooks or running custom PRAGMAs. The package-level `RegisterConnectionHook` applies to the default driver instance; `(*Driver).RegisterConnectionHook` applies to a custom `Driver` instance.

### Example Usage:
```go
package main

import (
    "database/sql"
    "fmt"
    "log"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

func init() {
    sqlite.RegisterConnectionHook(func(conn sqlite.ExecQuerierContext, dsn string) error {
        // Enable full-text search tokenizer or any per-connection PRAGMA
        _, err := conn.ExecContext(nil, "PRAGMA temp_store = MEMORY", nil)
        _ = err // ignore for brevity
        fmt.Println("new connection to:", dsn)
        return nil
    })
}

func main() {
    // Using a named custom driver with connection hooks
    var d sqlite.Driver
    d.RegisterConnectionHook(func(conn sqlite.ExecQuerierContext, dsn string) error {
        // Enforce foreign keys on every connection
        _, err := conn.ExecContext(nil, "PRAGMA foreign_keys = ON", nil)
        return err
    })
    sql.Register("sqlite-fk", &d)

    db, err := sql.Open("sqlite-fk", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    var fkEnabled int
    db.QueryRow("PRAGMA foreign_keys").Scan(&fkEnabled)
    fmt.Println("foreign_keys:", fkEnabled) // foreign_keys: 1
}
```
```

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

### Handle SQLite Errors with ErrorCodeString in Go

Source: https://context7.com/modernc-org/sqlite/llms.txt

Demonstrates how to catch and interpret SQLite-specific errors in Go. Use errors.As to extract the *sqlite.Error and sqlite.ErrorCodeString to get a human-readable description of the error code.

```go
package main

import (
    "database/sql"
    "errors"
    "fmt"
    "log"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT NOT NULL)`)

    // Trigger a NOT NULL constraint violation
    _, err = db.Exec(`INSERT INTO t(id, val) VALUES (1, NULL)`)
    if err != nil {
        var sqliteErr *sqlite.Error
        if errors.As(err, &sqliteErr) {
            fmt.Println("code:", sqliteErr.Code())
            fmt.Println("description:", sqlite.ErrorCodeString[sqliteErr.Code()])
            fmt.Println("full message:", sqliteErr.Error())
        } else {
            fmt.Println("generic error:", err)
        }
    }
    // Output:
    // code: 1299  (SQLITE_CONSTRAINT_NOTNULL extended)
    // description: Abort due to constraint violation (SQLITE_CONSTRAINT)
    // full message: constraint failed: NOT NULL constraint failed: t.val (1299)

    // Trigger a UNIQUE constraint violation
    db.Exec(`INSERT INTO t(id, val) VALUES (2, 'hello')`)
    _, err = db.Exec(`INSERT INTO t(id, val) VALUES (2, 'world')`)
    if err != nil {
        var e *sqlite.Error
        if errors.As(err, &e) {
            fmt.Printf("UNIQUE violation, code %d\n", e.Code())
        }
    }
}
```

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

### Open SQLite Database with Go

Source: https://context7.com/modernc-org/sqlite/llms.txt

Demonstrates opening both in-memory and on-disk SQLite databases using the standard database/sql API. Ensure the driver is imported for registration.

```go
package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    _ "modernc.org/sqlite"
)

func main() {
    // In-memory database
    mem, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer mem.Close()

    // On-disk database
    db, err := sql.Open("sqlite", "/tmp/myapp.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if _, err := db.Exec(`
        CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
        INSERT INTO users(name) VALUES('Alice'),('Bob');
    `); err != nil {
        log.Fatal(err)
    }

    rows, err := db.Query("SELECT id, name FROM users ORDER BY id")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%d: %s\n", id, name)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    // Output:
    // 1: Alice
    // 2: Bob

    fi, _ := os.Stat("/tmp/myapp.db")
    fmt.Println("size:", fi.Size())
}
```

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

### Implement Virtual Table Planning in Go

Source: https://github.com/modernc-org/sqlite/blob/master/README.md

In the `BestIndex` method, inspect `info.Constraints`, `info.OrderBy`, and `info.ColUsed` to determine the optimal query plan. Set `ArgIndex` to specify the order of arguments for `Filter` and `Omit` to indicate fully handled constraints.

```go
info.Constraints
info.OrderBy
info.ColUsed
info.ArgIndex
info.Omit
```

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

### Register Custom Aggregate/Window Function (Product)

Source: https://context7.com/modernc-org/sqlite/llms.txt

Registers a custom aggregate function named 'product' that calculates the product of values. It requires `Step`, `WindowInverse`, `WindowValue`, and `Final` methods. Ensure the function is deterministic if applicable.

```go
package main

import (
    "database/sql"
    "database/sql/driver"
    "fmt"
    "log"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

type productAgg struct{ product float64 }

func (a *productAgg) Step(_ *sqlite.FunctionContext, args []driver.Value) error {
    if v, ok := args[0].(float64); ok {
        a.product *= v
    } else if v, ok := args[0].(int64); ok {
        a.product *= float64(v)
    }
    return nil
}
func (a *productAgg) WindowInverse(_ *sqlite.FunctionContext, args []driver.Value) error {
    if v, ok := args[0].(float64); ok && v != 0 {
        a.product /= v
    }
    return nil
}
func (a *productAgg) WindowValue(_ *sqlite.FunctionContext) (driver.Value, error) {
    return a.product, nil
}
func (a *productAgg) Final(_ *sqlite.FunctionContext) {}

func init() {
    err := sqlite.RegisterFunction("product", &sqlite.FunctionImpl{
        NArgs:         1,
        Deterministic: true,
        MakeAggregate: func(ctx sqlite.FunctionContext) (sqlite.AggregateFunction, error) {
            return &productAgg{product: 1.0}, nil
        },
    })
    if err != nil {
        panic(err)
    }
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE nums (n REAL)`)
    db.Exec(`INSERT INTO nums VALUES (2.0), (3.0), (4.0)`)

    var result float64
    db.QueryRow(`SELECT product(n) FROM nums`).Scan(&result)
    fmt.Println("product:", result) // product: 24
}

```

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

### Vacuum and Convert Database to C-code

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Performs a VACUUM operation to optimize the database file and then uses the 'bin2c' utility to convert the 'testdb01.db' file into a C-language byte array.

```shell
VACUUM;
.shell bin2c testdb01.db
```

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

### Retrieve Column Metadata with ColumnInfo

Source: https://context7.com/modernc-org/sqlite/llms.txt

Prepares a query and returns metadata for each output column, including declared type, source table, and origin column name. Access this functionality via `(*sql.Conn).Raw` and casting to the `columnInfoer` interface.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

type columnInfoer interface {
    ColumnInfo(query string) ([]sqlite.ColumnInfo, error)
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, note TEXT)`)

    ctx := context.Background()
    conn, _ := db.Conn(ctx)
    defer conn.Close()

    query := "SELECT id, amount, amount * 1.1 AS taxed, note FROM orders WHERE id = 1"

    conn.Raw(func(dc any) error {
        ci, ok := dc.(columnInfoer)
        if !ok {
            return fmt.Errorf("ColumnInfo not supported")
        }
        infos, err := ci.ColumnInfo(query)
        if err != nil {
            return err
        }
        for _, info := range infos {
            fmt.Printf("Name=%-8s DeclType=%-8s Table=%-8s Origin=%s\n",
                info.Name, info.DeclType, info.TableName, info.OriginName)
        }
        return nil
    })
    // Output:
    // Name=id       DeclType=INTEGER  Table=orders   Origin=id
    // Name=amount   DeclType=REAL     Table=orders   Origin=amount
    // Name=taxed    DeclType=         Table=         Origin=
    // Name=note     DeclType=TEXT     Table=orders   Origin=note
}
```

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

### Online Database Backup with NewBackup

Source: https://context7.com/modernc-org/sqlite/llms.txt

Performs an online backup of a live SQLite database to a destination URI. Uses Step(n) to copy pages incrementally, allowing hot backups without exclusive locks. Requires the database connection to support the backup interface.

```go
package main

import (
    "context"
    "fmt"
    "log"
    "os"

    "database/sql"
    _ "modernc.org/sqlite"
)

type backupConn interface {
    NewBackup(dstUri string) (interface {
        Step(int32) (bool, error)
        Finish() error
    }, error)
}

func main() {
    src, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer src.Close()

    src.Exec(`CREATE TABLE data (id INTEGER PRIMARY KEY, payload TEXT)`)
    for i := 0; i < 1000; i++ {
        src.Exec(`INSERT INTO data(payload) VALUES(?)`, fmt.Sprintf("row-%d", i))
    }

    dstPath := "/tmp/backup.db"
    defer os.Remove(dstPath)

    ctx := context.Background()
    conn, _ := src.Conn(ctx)
    defer conn.Close()

    conn.Raw(func(dc any) error {
        type backuper interface {
            NewBackup(string) (interface {
                Step(int32) (bool, error)
                Finish() error
            }, error)
        }
        bc, ok := dc.(backuper)
        if !ok {
            return fmt.Errorf("no backup support")
        }
        bk, err := bc.NewBackup(dstPath)
        if err != nil {
            return err
        }
        // Copy 5 pages at a time (non-blocking incremental backup)
        for {
            more, err := bk.Step(5)
            if err != nil {
                bk.Finish()
                return err
            }
            if !more {
                break
            }
        }
        return bk.Finish()
    })

    // Verify backup
    bdb, _ := sql.Open("sqlite", dstPath)
    defer bdb.Close()
    var count int
    bdb.QueryRow("SELECT COUNT(*) FROM data").Scan(&count)
    fmt.Println("backed up rows:", count) // backed up rows: 1000
}
```

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

### Create and Populate Table t1

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Creates a table 't1' with an integer primary key and four other integer columns. It then populates 't1' with 50 rows of data using a recursive CTE and random values.

```sqlite
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<50)
INSERT INTO t1(a,b,c,d,e) SELECT x,abs(random()%51),
   abs(random()%100), abs(random()%51), abs(random()%100) FROM c;
```

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

### Create and Populate Table t3 with NULLs

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Creates a table 't3' with five columns and populates it with data from 't1', including rows with NULL values in various columns. This table is used to test handling of NULLs.

```sqlite
CREATE TABLE t3(a,b,c,d,e);
INSERT INTO t3 SELECT a,b,c,d,e FROM t1 ORDER BY random() LIMIT 5;
INSERT INTO t3 SELECT null,b,c,d,e FROM t1 ORDER BY random() LIMIT 5;
INSERT INTO t3 SELECT a,null,c,d,e FROM t1 ORDER BY random() LIMIT 5;
INSERT INTO t3 SELECT a,b,null,d,e FROM t1 ORDER BY random() LIMIT 5;
INSERT INTO t3 SELECT a,b,c,null,e FROM t1 ORDER BY random() LIMIT 5;
INSERT INTO t3 SELECT a,b,c,d,null FROM t1 ORDER BY random() LIMIT 5;
INSERT INTO t3 SELECT null,null,null,null,null FROM t1 LIMIT 5;
```

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

### Serialize and Deserialize SQLite Database

Source: https://context7.com/modernc-org/sqlite/llms.txt

Snapshot an entire SQLite database into a byte slice using Serialize, and restore a connection's database from such a snapshot using Deserialize. Both are accessed via (*sql.Conn).Raw.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    _ "modernc.org/sqlite"
)

type serializer interface {
    Serialize() ([]byte, error)
    Deserialize([]byte) error
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE kv (k TEXT PRIMARY KEY, v TEXT)`)
    db.Exec(`INSERT INTO kv VALUES ('greeting', 'hello')`)

    ctx := context.Background()
    conn, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
    }

    var snapshot []byte
    err = conn.Raw(func(dc any) error {
        s, ok := dc.(serializer)
        if !ok {
            return fmt.Errorf("driver does not support Serialize")
        }
        var err error
        snapshot, err = s.Serialize()
        return err
    })
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("snapshot: %d bytes\n", len(snapshot))
    conn.Close()

    // Restore into a new in-memory database
    db2, _ := sql.Open("sqlite", ":memory:")
    defer db2.Close()
    conn2, _ := db2.Conn(ctx)
    defer conn2.Close()
    conn2.Raw(func(dc any) error {
        s, ok := dc.(serializer)
        if !ok {
            return fmt.Errorf("driver does not support Deserialize")
        }
        return s.Deserialize(snapshot)
    })

    var val string
    db2.QueryRow("SELECT v FROM kv WHERE k = 'greeting'").Scan(&val)
    fmt.Println("restored:", val) // restored: hello
}
```

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

### Create and Populate Table t5

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Creates a table 't5' with an integer primary key and a unique text column 'b'. It populates 't5' with a list of 50 unique words.

```sqlite
CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT UNIQUE,c,d,e);
INSERT INTO t5(b) VALUES
   ('truth'),
   ('works'),
   ('offer'),
   ('can'),
   ('anger'),
   ('wisdom'),
   ('send'),
   ('though'),
   ('save'),
   ('between'),
   ('some'),
   ('wine'),
   ('ark'),
   ('smote'),
   ('therein'),
   ('shew'),
   ('morning'),
   ('dwelt'),
   ('begat'),
   ('nothing'),
   ('war'),
   ('above'),
   ('known'),
   ('sacrifice'),
   ('tell'),
   ('departed'),
   ('thyself'),
   ('places'),
   ('bear'),
   ('part'),
   ('while'),
   ('gone'),
   ('cubits'),
   ('walk'),
   ('long'),
   ('near'),
   ('serve'),
   ('fruit'),
   ('doth'),
   ('poor'),
   ('ways'),
   ('child'),
   ('temple'),
   ('angel'),
   ('inhabitants'),
   ('oil'),
   ('died'),
   ('six'),
   ('tree'),
   ('wrath');
```

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

### ColumnInfo (via conn.Raw)

Source: https://context7.com/modernc-org/sqlite/llms.txt

The ColumnInfo method, accessible via `(*sql.Conn).Raw`, prepares a query and returns metadata for each output column, including its declared type, source database, table, and origin column name.

```APIDOC
## ColumnInfo (via conn.Raw)

`ColumnInfo` prepares a query without executing it and returns metadata for every output column: declared type, source database, table, and origin column name. Access it via `(*sql.Conn).Raw`.

### Usage

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

type columnInfoer interface {
    ColumnInfo(query string) ([]sqlite.ColumnInfo, error)
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, note TEXT)`)

    ctx := context.Background()
    conn, _ := db.Conn(ctx)
    defer conn.Close()

    query := "SELECT id, amount, amount * 1.1 AS taxed, note FROM orders WHERE id = 1"

    conn.Raw(func(dc any) error {
        ci, ok := dc.(columnInfoer)
        if !ok {
            return fmt.Errorf("ColumnInfo not supported")
        }
        infos, err := ci.ColumnInfo(query)
        if err != nil {
            return err
        }
        for _, info := range infos {
            fmt.Printf("Name=%-8s DeclType=%-8s Table=%-8s Origin=%s\n",
                info.Name, info.DeclType, info.TableName, info.OriginName)
        }
        return nil
    })
    // Output:
    // Name=id       DeclType=INTEGER  Table=orders   Origin=id
    // Name=amount   DeclType=REAL     Table=orders   Origin=amount
    // Name=taxed    DeclType=         Table=         Origin=
    // Name=note     DeclType=TEXT     Table=orders   Origin=note
}
```
```

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

### Serialize and Deserialize Database

Source: https://context7.com/modernc-org/sqlite/llms.txt

Snapshot an entire SQLite database into a byte slice or restore a connection's database from such a snapshot. Both are accessed via `(*sql.Conn).Raw` to obtain the underlying driver connection.

```APIDOC
## Serialize and Deserialize (conn.Raw)

`Serialize` snapshots an entire SQLite database into a `[]byte`. `Deserialize` restores a connection's database from such a snapshot. Both are accessed via `(*sql.Conn).Raw` to obtain the underlying driver connection.

### Serialize

Serializes the entire database into a byte slice.

### Deserialize

Restores the database from a byte slice snapshot.

#### Example Usage:

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    _ "modernc.org/sqlite"
)

type serializer interface {
    Serialize() ([]byte, error)
    Deserialize([]byte) error
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE kv (k TEXT PRIMARY KEY, v TEXT)`)
    db.Exec(`INSERT INTO kv VALUES ('greeting', 'hello')`)

    ctx := context.Background()
    conn, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
    }

    var snapshot []byte
    err = conn.Raw(func(dc any) error {
        s, ok := dc.(serializer)
        if !ok {
            return fmt.Errorf("driver does not support Serialize")
        }
        var err error
        snapshot, err = s.Serialize()
        return err
    })
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("snapshot: %d bytes\n", len(snapshot))
    conn.Close()

    // Restore into a new in-memory database
    db2, _ := sql.Open("sqlite", ":memory:")
    defer db2.Close()
    conn2, _ := db2.Conn(ctx)
    defer conn2.Close()
    conn2.Raw(func(dc any) error {
        s, ok := dc.(serializer)
        if !ok {
            return fmt.Errorf("driver does not support Deserialize")
        }
        return s.Deserialize(snapshot)
    })

    var val string
    db2.QueryRow("SELECT v FROM kv WHERE k = 'greeting'").Scan(&val)
    fmt.Println("restored:", val) // restored: hello
}
```
```

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

### Configure SQLite DSN with Query Parameters in Go

Source: https://context7.com/modernc-org/sqlite/llms.txt

Configures SQLite connection parameters like journal mode, foreign keys, busy timeout, time formats, and timezone via DSN query parameters. Also demonstrates exclusive transaction locking.

```go
package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "modernc.org/sqlite"
)

func main() {
    // Enable WAL mode, foreign keys, busy timeout; store times in sqlite format; use New York timezone
    dsn := "file:///tmp/app.db"
        "?_pragma=journal_mode(WAL)"
        "&_pragma=foreign_keys(1)"
        "&_pragma=busy_timeout(5000)"
        "&_time_format=sqlite"
        "&_timezone=America/New_York"

    db, err := sql.Open("sqlite", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, ts DATETIME)`)

    now := time.Now()
    db.Exec(`INSERT INTO events(ts) VALUES(?)`, now)

    var ts time.Time
    db.QueryRow("SELECT ts FROM events LIMIT 1").Scan(&ts)
    fmt.Println("stored:", ts)

    // Exclusive transaction lock
    dsnExclusive := "/tmp/exclusive.db?_txlock=exclusive"
    db2, _ := sql.Open("sqlite", dsnExclusive)
    defer db2.Close()
    tx, _ := db2.Begin()
    tx.Rollback()
}
```

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

### Manage SQLite Resource Limits with Limit

Source: https://context7.com/modernc-org/sqlite/llms.txt

Queries or sets per-connection resource limits using the sqlite3_limit function. Pass -1 to query the current value without changing it. Useful for controlling SQL length, expression depth, and attached databases.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    "modernc.org/sqlite"
    sqlite3lib "modernc.org/sqlite/lib"
    _ "modernc.org/sqlite"
)

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    ctx := context.Background()
    conn, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()

    // Query current SQLITE_LIMIT_SQL_LENGTH (default 1_000_000_000)
    old, err := sqlite.Limit(conn, sqlite3lib.SQLITE_LIMIT_SQL_LENGTH, -1)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("default SQL length limit:", old)

    // Restrict maximum SQL length to 64 KB
    prev, err := sqlite.Limit(conn, sqlite3lib.SQLITE_LIMIT_SQL_LENGTH, 65536)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("previous limit was:", prev)

    // Restrict maximum number of attached databases
    _, err = sqlite.Limit(conn, sqlite3lib.SQLITE_LIMIT_ATTACHED, 2)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("attached DB limit set to 2")
}
```

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

### Register Custom Scalar Functions (SHA256, Concat_WS)

Source: https://context7.com/modernc-org/sqlite/llms.txt

Register Go functions as SQL scalar functions. Use -1 for nArg to create variadic functions. Must be called before opening any database connection. MustRegisterScalarFunction panics on error.

```go
package main

import (
    "crypto/sha256"
    "database/sql"
    "database/sql/driver"
    "encoding/hex"
    "fmt"
    "log"
    "strings"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

func init() {
    // sha256(text) → hex string
    sqlite.MustRegisterScalarFunction("sha256", 1,
        func(ctx *sqlite.FunctionContext, args []driver.Value) (driver.Value, error) {
            s, ok := args[0].(string)
            if !ok {
                return nil, fmt.Errorf("sha256: expected string, got %T", args[0])
            }
            h := sha256.Sum256([]byte(s))
            return hex.EncodeToString(h[:]), nil
        },
    )

    // concat_ws(sep, a, b, ...) variadic
    sqlite.MustRegisterScalarFunction("concat_ws", -1,
        func(ctx *sqlite.FunctionContext, args []driver.Value) (driver.Value, error) {
            if len(args) < 2 {
                return "", nil
            }
            sep, _ := args[0].(string)
            parts := make([]string, 0, len(args)-1)
            for _, a := range args[1:] {
                if a != nil {
                    parts = append(parts, fmt.Sprint(a))
                }
            }
            return strings.Join(parts, sep), nil
        },
    )
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    var hash string
    db.QueryRow(`SELECT sha256('hello')`).Scan(&hash)
    fmt.Println("sha256('hello'):", hash)
    // sha256('hello'): 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824

    var joined string
    db.QueryRow(`SELECT concat_ws(', ', 'Alice', 'Bob', 'Carol')`).Scan(&joined)
    fmt.Println("concat_ws:", joined)
    // concat_ws: Alice, Bob, Carol
}
```

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

### Register Virtual Table Module in Go

Source: https://github.com/modernc-org/sqlite/blob/master/README.md

Use `vtab.RegisterModule` to register a custom virtual table module with a database connection. Registration only affects new connections.

```go
vtab.RegisterModule(db, name, module)
```

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

### Create Various Views

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Defines a series of views (v00 through v52) that select data from the created tables ('t1' through 't5'). These views demonstrate different SQL features like filtering, ordering, grouping, aggregation, and set operations.

```sqlite
CREATE VIEW v00(a,b,c,d,e) AS SELECT 1,1,1,1,'one';
CREATE VIEW v10(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 WHERE a<>25;
CREATE VIEW v20(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 WHERE a<>25;
CREATE VIEW v30(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 WHERE a<>25;
CREATE VIEW v40(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 WHERE a<>25;
CREATE VIEW v50(a,b) AS SELECT a,b FROM t5 WHERE a<>25;
CREATE VIEW v11(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 ORDER BY b LIMIT 10;
CREATE VIEW v21(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 ORDER BY b LIMIT 10;
CREATE VIEW v31(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 ORDER BY b LIMIT 10;
CREATE VIEW v41(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 ORDER BY b LIMIT 10;
CREATE VIEW v51(a,b) AS SELECT a,b FROM t5 ORDER BY b LIMIT 10;
CREATE VIEW v12(a,b,c,d,e) AS
  SELECT sum(a), avg(b), count(*), min(d), e FROM t1 GROUP BY 5;
CREATE VIEW v22(a,b,c,d,e) AS
  SELECT sum(a), avg(b), count(*), min(d), e FROM t2 GROUP BY 5
    HAVING count(*)>1 ORDER BY 3, 1;
CREATE VIEW v32(a,b,c,d,e) AS
  SELECT sum(a), avg(b), count(*), min(d), e FROM t3 GROUP BY 5
    HAVING count(*)>1 ORDER BY 3, 1;
CREATE VIEW v42(a,b,c,d,e) AS
  SELECT sum(a), avg(b), count(*), min(d), e FROM t4 GROUP BY 5
    HAVING min(d)<30 ORDER BY 3, 1;
CREATE VIEW v52(a,b,c,d,e) AS
  SELECT count(*), min(b), substr(b,1,1), min(a), max(a) FROM t5
   GROUP BY 3 ORDER BY 1;

CREATE VIEW v13(a,b,c,d,e) AS
  SELECT a,b,c,d,e FROM t1
  UNION SELECT a,b,c,d,e FROM t2
  UNION SELECT a,b,c,d,e FROM t3;
CREATE VIEW v23(a,b,c,d,e) AS
  SELECT a,b,c,d,e FROM t1
  EXCEPT SELECT a,b,c,d,e FROM t1 WHERE b<25;

CREATE VIEW v60(a,b,c,d,e) AS
  SELECT t1.a,t2.b,t1.c,t2.d,t1.e
    FROM t1 LEFT JOIN t2 ON (t1.a=t2.b);
CREATE VIEW v61(a,b,c,d,e) AS
  SELECT t2.a,t3.b,t2.c,t3.d,t2.e
    FROM t2 LEFT JOIN t3 ON (t2.a=t3.a);
CREATE VIEW v62(a,b,c,d,e) AS
  SELECT t1.a,t2.b,t3.c,t4.d,t5.b
    FROM t1 JOIN t2 ON (t1.a=t2.b)
            JOIN t3 ON (t1.a=t3.a)
            JOIN t4 ON (t4.b=t3.b)
            LEFT JOIN t5 ON (t5.a=t1.c);
CREATE VIEW v70(a,b,c,d,e) AS
  WITH RECURSIVE c0(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c0 WHERE x<9)
  SELECT x, b, c, d, e FROM c0 JOIN t1 ON (t1.a=50-c0.x);
COMMIT;
```

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

### Create and Populate Table t2

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Creates a table 't2' with integer columns and a composite primary key, defined without a rowid. It then populates 't2' with data from 't1'.

```sqlite
CREATE TABLE t2(a INT, b INT, c INT,d INT,e INT,PRIMARY KEY(b,a))WITHOUT ROWID;
INSERT INTO t2 SELECT * FROM t1;
```

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

### RegisterFunction (Aggregate / Window Functions)

Source: https://context7.com/modernc-org/sqlite/llms.txt

Register an aggregate or window function using `FunctionImpl` with `MakeAggregate`. The `AggregateFunction` interface requires `Step`, `WindowInverse`, `WindowValue`, and `Final` methods.

```APIDOC
## RegisterFunction (Aggregate / Window Functions)

Register an aggregate or window function using `FunctionImpl` with `MakeAggregate`. The `AggregateFunction` interface requires `Step`, `WindowInverse`, `WindowValue`, and `Final` methods.

### Example Usage:
```go
package main

import (
    "database/sql"
    "database/sql/driver"
    "fmt"
    "log"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

type productAgg struct{ product float64 }

func (a *productAgg) Step(_ *sqlite.FunctionContext, args []driver.Value) error {
    if v, ok := args[0].(float64); ok {
        a.product *= v
    } else if v, ok := args[0].(int64); ok {
        a.product *= float64(v)
    }
    return nil
}
func (a *productAgg) WindowInverse(_ *sqlite.FunctionContext, args []driver.Value) error {
    if v, ok := args[0].(float64); ok && v != 0 {
        a.product /= v
    }
    return nil
}
func (a *productAgg) WindowValue(_ *sqlite.FunctionContext) (driver.Value, error) {
    return a.product, nil
}
func (a *productAgg) Final(_ *sqlite.FunctionContext) {}

func init() {
    err := sqlite.RegisterFunction("product", &sqlite.FunctionImpl{
        NArgs:         1,
        Deterministic: true,
        MakeAggregate: func(ctx sqlite.FunctionContext) (sqlite.AggregateFunction, error) {
            return &productAgg{product: 1.0}, nil
        },
    })
    if err != nil {
        panic(err)
    }
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE nums (n REAL)`)
    db.Exec(`INSERT INTO nums VALUES (2.0), (3.0), (4.0)`)

    var result float64
    db.QueryRow(`SELECT product(n) FROM nums`).Scan(&result)
    fmt.Println("product:", result) // product: 24
}
```
```

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

### Create and Populate Table t4

Source: https://github.com/modernc-org/sqlite/blob/master/testdata/tcl/optfuzz-db01.txt

Creates a table 't4' with unique constraints on columns 'a' and 'b'. It attempts to insert data from 't3', ignoring rows that violate the unique constraints.

```sqlite
CREATE TABLE t4(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d,e);
INSERT OR IGNORE INTO t4 SELECT a,b,c,d,e FROM t3;
```

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

### NewBackup and NewRestore (Online Backup API)

Source: https://context7.com/modernc-org/sqlite/llms.txt

The NewBackup and NewRestore functions facilitate online backups and restores of SQLite databases. They return a *Backup object that allows for incremental copying of database pages, enabling hot backups of live databases without exclusive locks.

```APIDOC
## NewBackup and NewRestore (Online Backup API)

`NewBackup` copies the current database to a destination URI. `NewRestore` copies from a source URI into the current database. Both return a `*Backup` whose `Step(n)` copies `n` pages at a time, enabling hot backups of live databases without exclusive locks.

### Usage

```go
package main

import (
    "context"
    "fmt"
    "log"
    "os"

    "database/sql"
    _ "modernc.org/sqlite"
)

type backupConn interface {
    NewBackup(dstUri string) (interface {
        Step(int32) (bool, error)
        Finish() error
    }, error)
}

func main() {
    src, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer src.Close()

    src.Exec(`CREATE TABLE data (id INTEGER PRIMARY KEY, payload TEXT)`)
    for i := 0; i < 1000; i++ {
        src.Exec(`INSERT INTO data(payload) VALUES(?)`, fmt.Sprintf("row-%d", i)))
    }

    dstPath := "/tmp/backup.db"
    defer os.Remove(dstPath)

    ctx := context.Background()
    conn, _ := src.Conn(ctx)
    defer conn.Close()

    conn.Raw(func(dc any) error {
        type backuper interface {
            NewBackup(string) (interface {
                Step(int32) (bool, error)
                Finish() error
            }, error)
        }
        bc, ok := dc.(backuper)
        if !ok {
            return fmt.Errorf("no backup support")
        }
        bk, err := bc.NewBackup(dstPath)
        if err != nil {
            return err
        }
        // Copy 5 pages at a time (non-blocking incremental backup)
        for {
            more, err := bk.Step(5)
            if err != nil {
                bk.Finish()
                return err
            }
            if !more {
                break
            }
        }
        return bk.Finish()
    })

    // Verify backup
    bdb, _ := sql.Open("sqlite", dstPath)
    defer bdb.Close()
    var count int
    bdb.QueryRow("SELECT COUNT(*) FROM data").Scan(&count)
    fmt.Println("backed up rows:", count) // backed up rows: 1000
}
```
```

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

### Register Custom UTF-8 Collation (NOCASE_UTF8)

Source: https://context7.com/modernc-org/sqlite/llms.txt

Registers a case-insensitive UTF-8 collation sequence named 'NOCASE_UTF8'. The implementation function compares two strings after converting them to lowercase. This collation is available to all connections opened after registration.

```go
package main

import (
    "database/sql"
    "fmt"
    "log"
    "strings"

    "modernc.org/sqlite"
    _ "modernc.org/sqlite"
)

func init() {
    // case-insensitive collation
    sqlite.MustRegisterCollationUtf8("NOCASE_UTF8", func(left, right string) int {
        l, r := strings.ToLower(left), strings.ToLower(right)
        if l < r {
            return -1
        }
        if l > r {
            return 1
        }
        return 0
    })
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.Exec(`CREATE TABLE words (w TEXT COLLATE NOCASE_UTF8)`)
    db.Exec(`INSERT INTO words VALUES ('banana'), ('Apple'), ('cherry'), ('avocado')`)

    rows, err := db.Query(`SELECT w FROM words ORDER BY w COLLATE NOCASE_UTF8`)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        var w string
        rows.Scan(&w)
        fmt.Println(w)
    }
    // Output (case-insensitive alphabetical):
    // Apple
    // avocado
    // banana
    // cherry
}

```