goSqlite

[!NOTE] This README was generated by Claude Code, get the ZH version from here.

go-sqlite

A lightweight Go SQLite query builder with fluent chainable API for simplified CRUD operations, supporting Context propagation and conflict handling strategies.

Table of Contents

• Features
• Installation
• Usage
• API Reference
• License
• Author
• Stars

Features

• Chainable API: Fluent query building syntax with Table().Where().Get() chaining
• Multi-database Connection Management: Singleton Connector manages multiple SQLite databases
• Full CRUD Support: Complete Create, Insert, Select, Update operations
• Context Propagation: Timeout and cancellation support via Context(ctx) chaining
• Conflict Handling Strategies: Supports Ignore, Replace, Abort, Fail, Rollback
• Rich WHERE Conditions: Eq, NotEq, Gt, Lt, Ge, Le, In, NotIn, Null, NotNull, Between
• Column Validation: Automatic SQL reserved word and identifier format checking
• WAL Mode: Enabled by default for improved concurrency performance

Installation

go get github.com/pardnchiu/go-sqlite

Usage

Initialize Connection

package main

import (
    "log"
    goSqlite "github.com/pardnchiu/go-sqlite"
)

func main() {
    // Create connection (singleton pattern, can register multiple databases)
    conn, err := goSqlite.New(goSqlite.Config{
        Key:      "main",           // Optional, defaults to filename
        Path:     "./data.db",
        Lifetime: 3600,             // Optional, connection lifetime in seconds
    })
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()
}

Create Table

builder, _ := conn.DB("main")
err := builder.Table("users").Create(
    goSqlite.Column{Name: "id", Type: "INTEGER", IsPrimary: true, AutoIncrease: true},
    goSqlite.Column{Name: "name", Type: "TEXT", IsNullable: false},
    goSqlite.Column{Name: "email", Type: "TEXT", IsUnique: true},
    goSqlite.Column{Name: "age", Type: "INTEGER", Default: 0},
)

Insert Data

builder, _ := conn.DB("main")

// Basic insert, returns LastInsertId
id, err := builder.Table("users").Insert(map[string]any{
    "name":  "Alice",
    "email": "alice@example.com",
    "age":   25,
})

// With Context
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

id, err = builder.Table("users").
    Context(ctx).
    Insert(map[string]any{
        "name": "Bob",
    })

// Conflict handling
id, err = builder.Table("users").
    Conflict(goSqlite.Ignore).
    Insert(map[string]any{"email": "alice@example.com"})

Query Data

builder, _ := conn.DB("main")

// Query multiple rows
rows, err := builder.Table("users").
    Select("id", "name", "email").
    WhereGt("age", 18).
    OrderBy("name", goSqlite.Asc).
    Limit(10).
    Offset(0).
    Get()

// Query single row
row, err := builder.Table("users").
    WhereEq("id", 1).
    First()

// Count
count, err := builder.Table("users").
    WhereNotNull("email").
    Count()

// Query with total count (for pagination)
rows, err := builder.Table("users").
    Total().
    Limit(10).
    Get()

Update Data

builder, _ := conn.DB("main")

// Basic update, returns RowsAffected
affected, err := builder.Table("users").
    WhereEq("id", 1).
    Update(map[string]any{"name": "Alice Updated"})

// Increment numeric value
_, err = builder.Table("users").
    WhereEq("id", 1).
    Increase("login_count", 1).
    Update()

// Toggle boolean
_, err = builder.Table("users").
    WhereEq("id", 1).
    Toggle("is_active").
    Update()

Compound Conditions

builder, _ := conn.DB("main")

// AND conditions
rows, _ := builder.Table("users").
    WhereGe("age", 18).
    WhereLe("age", 30).
    WhereNotNull("email").
    Get()

// OR conditions
rows, _ := builder.Table("users").
    WhereEq("status", "active").
    OrWhereEq("role", "admin").
    Get()

// IN condition
rows, _ := builder.Table("users").
    WhereIn("id", []any{1, 2, 3}).
    Get()

// BETWEEN condition
rows, _ := builder.Table("users").
    WhereBetween("age", 20, 30).
    Get()

JOIN Queries

builder, _ := conn.DB("main")

rows, _ := builder.Table("orders").
    Select("orders.id", "users.name", "orders.total").
    LeftJoin("users", "users.id = orders.user_id").
    WhereGt("orders.total", 100).
    Get()

Raw SQL

// Execute via Connector
rows, err := conn.Query("main", "SELECT * FROM users WHERE age > ?", 18)

// Or get *sql.DB directly
builder, _ := conn.DB("main")
db := builder.Raw()
rows, err := db.Query("SELECT * FROM users")

API Reference

Config

Connector Methods

Builder Methods

Conflict Strategies

Column Structure

License

This project is licensed under the MIT License - see the LICENSE file for details.

Author

邱敬幃 Pardn Chiu

Stars

©️ 2026 邱敬幃 Pardn Chiu