goSqlite

README

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

go-sqlite

High-performance SQLite query builder with read-write separation connection pool architecture, optimized for high-concurrency scenarios.

Table of Contents

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

Features

• Read-Write Separation: Independent read and write connection pools for maximum concurrent performance
• WAL Mode: Write-Ahead Logging enabled by default for improved write performance and reduced lock contention
• Fluent Query Builder: Chainable API design supporting SELECT, INSERT, UPDATE, DELETE operations
• Automatic Struct Binding: Map query results to struct or slice via Bind() method
• SQL Injection Protection: Built-in identifier validation and parameterized queries
• Context Support: All operations support context.Context for timeout and cancellation control
• Conflict Handling: Supports IGNORE, REPLACE, ABORT, FAIL, ROLLBACK strategies
• Automatic WAL Checkpoint: Background goroutine performs periodic checkpoints

Installation

go get github.com/pardnchiu/go-sqlite

Usage

Initialize Connection

package main

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

func main() {
    conn, err := goSqlite.New(core.Config{
        Path:         "data.db",
        MaxOpenConns: 50,  // Read pool size
        MaxIdleConns: 25,  // Idle connections
        Lifetime:     120, // Connection lifetime in seconds
    })
    if err != nil {
        panic(err)
    }
    defer conn.Close()
}

Create Table

err := conn.Write.Table("users").Create(
    core.Column{Name: "id", Type: "INTEGER", IsPrimary: true, AutoIncrease: true},
    core.Column{Name: "name", Type: "TEXT", IsNullable: false},
    core.Column{Name: "email", Type: "TEXT", IsUnique: true},
    core.Column{Name: "created_at", Type: "INTEGER", Default: 0},
)

Insert Data

// Single insert
id, err := conn.Write.Table("users").Insert(map[string]any{
    "name":  "Alice",
    "email": "alice@example.com",
})

// Batch insert
affected, err := conn.Write.Table("users").InsertBatch([]map[string]any{
    {"name": "Bob", "email": "bob@example.com"},
    {"name": "Carol", "email": "carol@example.com"},
})

// Conflict handling
id, err := conn.Write.Table("users").
    Conflict(core.Replace).
    Insert(map[string]any{"name": "Alice", "email": "alice@example.com"})

Query Data

// Basic query
rows, err := conn.Read.Table("users").
    Select("id", "name", "email").
    WhereGt("id", 10).
    OrderBy("created_at", core.Desc).
    Limit(20).
    Get()

// Bind to struct
type User struct {
    ID    int64  `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

var user User
_, err := conn.Read.Table("users").
    WhereEq("id", 1).
    Bind(&user).
    First()

// Bind to slice
var users []User
_, err := conn.Read.Table("users").
    WhereLt("id", 100).
    Bind(&users).
    Get()

// Pagination with total count
rows, err := conn.Read.Table("users").
    Total().
    Limit(10).
    Offset(20).
    Get()

Update Data

// Standard update
affected, err := conn.Write.Table("users").
    WhereEq("id", 1).
    Update(map[string]any{"name": "Alice Updated"})

// Increment/decrement values
affected, err := conn.Write.Table("users").
    WhereEq("id", 1).
    Increase("login_count", 1).
    Update()

// Toggle boolean
affected, err := conn.Write.Table("users").
    WhereEq("id", 1).
    Toggle("is_active").
    Update()

Delete Data

// Conditional delete
affected, err := conn.Write.Table("users").
    WhereEq("id", 1).
    Delete()

// Force delete all
affected, err := conn.Write.Table("users").Delete(true)

JOIN Queries

rows, err := conn.Read.Table("orders").
    Select("orders.id", "users.name", "orders.amount").
    LeftJoin("users", "orders.user_id = users.id").
    WhereGt("orders.amount", 100).
    Get()

Aggregate Queries

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

// GROUP BY + HAVING
rows, err := conn.Read.Table("orders").
    Select("user_id", "SUM(amount) as total").
    GroupBy("user_id").
    HavingGt("total", 1000).
    Get()

API Reference

Configuration

Field	Type	Description
Path	string	Database file path
MaxOpenConns	int	Maximum read pool connections (default 50)
MaxIdleConns	int	Idle connections (default 25)
Lifetime	int	Connection lifetime in seconds (default 120)

Builder Methods

Table Operations

Method	Description
Table(name)	Specify target table
Create(columns...)	Create table

Query Building

Method	Description
Select(columns...)	Specify columns to select
Join(table, on)	INNER JOIN
LeftJoin(table, on)	LEFT JOIN
OrderBy(column, direction)	Order by (core.Asc / core.Desc)
GroupBy(columns...)	Group by
Limit(n) / Limit(offset, n)	Limit rows
Offset(n)	Offset
Total()	Include total count in query
Context(ctx)	Set context
Bind(target)	Bind result to struct/slice

WHERE Conditions

Method	SQL
Where(condition, args...)	Custom condition
WhereEq(col, val)	col = ?
WhereNotEq(col, val)	col != ?
WhereGt(col, val)	col > ?
WhereLt(col, val)	col < ?
WhereGe(col, val)	col >= ?
WhereLe(col, val)	col <= ?
WhereIn(col, vals)	col IN (?, ...)
WhereNotIn(col, vals)	col NOT IN (?, ...)
WhereNull(col)	col IS NULL
WhereNotNull(col)	col IS NOT NULL
WhereBetween(col, start, end)	col BETWEEN ? AND ?
OrWhere*(...)	OR variants

HAVING Conditions

All WHERE methods have corresponding Having* variants.

Execution Methods

Method	Returns	Description
Get()	(*sql.Rows, error)	Execute query
First()	(*sql.Row, error)	Get first row (reverse order)
Last()	(*sql.Row, error)	Get last row
Count()	(int64, error)	Count rows
Insert(data, [conflict])	(int64, error)	Insert and return ID
InsertBatch(data)	(int64, error)	Batch insert
Update([data])	(int64, error)	Update and return affected rows
Delete([force])	(int64, error)	Delete and return affected rows

Update Helpers

Method	Description
Increase(col, [n])	Increment value (default +1)
Decrease(col, [n])	Decrement value (default -1)
Toggle(col)	Toggle boolean
Conflict(mode)	Set conflict handling strategy

Conflict Modes

Constant	Description
core.Ignore	Ignore conflict
core.Replace	Replace existing data
core.Abort	Abort transaction
core.Fail	Fail but keep previous changes
core.Rollback	Rollback entire transaction

Connector Methods

Method	Description
Query(key, query, args...)	Execute raw read query
QueryContext(ctx, key, query, args...)	Raw read query with context
Exec(key, query, args...)	Execute raw write operation
ExecContext(ctx, key, query, args...)	Raw write operation with context
Close()	Close all connections

License

MIT License

Author

邱敬幃 Pardn Chiu

Stars

---

©️ 2026 邱敬幃 Pardn Chiu

Documentation

Index

• func New(c core.Config) (*core.Connector, error)

Functions

func New

func New(c core.Config) (*core.Connector, error)