gopsql

gopsql is a collection of Go packages for building database-driven applications, with a focus on simplicity and flexibility. The core library provides a Rails-inspired approach to database queries, supporting PostgreSQL, MySQL, and SQLite with multiple driver options.

Core Packages

psql

The main query builder library. Build SELECT, INSERT, UPDATE, DELETE statements with a clean, chainable API. Features include:

• Model-based queries inferred from Go structs
• JSONB support with store_accessor-like functionality
• Safe parameter filtering like Rails' permit
• Automatic schema generation
• Support for complex queries with joins, grouping, and aggregations

// Define a model
type Post struct {
    Id        int
    Title     string
    Picture   string `jsonb:"Meta"`
    CreatedAt time.Time
}

Posts := psql.NewModel(Post{}, conn, logger.StandardLogger)

// Insert
Posts.Insert("Title", "Hello", "Picture", "world.jpg").Returning("Id").MustQueryRow(&id)

// Find
var posts []Post
Posts.Find().Where("Id > $?", 10).Limit(5).MustQuery(&posts)

// Update
Posts.Update("Title", "Updated").Where("Id = $?", 1).MustExecute()

// Delete
Posts.Delete().Where("Id = $?", 1).MustExecute()

db

Database interface that abstracts different PostgreSQL, MySQL, and SQLite drivers. This allows you to switch drivers at runtime without changing your application code.

Supported implementations:

Database	Package	Driver
PostgreSQL	standard	database/sql + lib/pq
PostgreSQL	pq	github.com/lib/pq
PostgreSQL	pgx	github.com/jackc/pgx/v5
PostgreSQL	gopg	github.com/go-pg/pg/v10
MySQL/MariaDB	mysql	github.com/go-sql-driver/mysql
SQLite	sqlite	modernc.org/sqlite (CGo-free)

// Switch drivers at runtime
var conn db.DB
switch driver {
case "pgx":
    conn = pgx.MustOpen(connStr)
case "gopg":
    conn = gopg.MustOpen(connStr)
case "mysql":
    conn = mysql.MustOpen(connStr)
case "sqlite":
    conn = sqlite.MustOpen("mydb.sqlite3")
default:
    conn = pq.MustOpen(connStr)
}

migrator

Database migration tool that manages schema changes with up/down migrations. Automatically generates migration files based on differences between your models and the database schema.

m := migrator.NewMigrator("myapp")
m.SetConnection(conn)
m.SetLogger(logger.StandardLogger)
m.SetMigrations(migrations.Migrations)
m.Migrate()   // Run pending migrations
m.Rollback()  // Rollback last migration

Web & API Packages

backend

Integrate admin functionality into your Fiber application with session management, authentication, and CRUD controllers.

conn := pq.MustOpen("postgres://localhost:5432/mydb?sslmode=disable")
backend.Default.SetConnection(conn)
backend.Default.JWTSession = configs.AdminSession

// Set up routes
sc := backend.Default.NewFiberSessionsCtrl()
g.Post("/sign-in", convert(sc.SignIn))
g.Get("/me", convert(sc.Me))
g.Use(convert(sc.Authenticate))

jwt

JWT authentication helpers for parsing and generating Bearer tokens with RSA signing.

session := jwt.NewSession(nil)

// Generate token
auth, _ := session.GenerateAuthorization(userId, sessionId)
// "Bearer eyJhbGciOiJSUzI1NiIs..."

// Parse token
userId, sessionId, ok := session.ParseAuthorization(auth)

pagination

Convert pagination, query, and sort parameters to SQL expressions. Works with Echo and Fiber.

q := pagination.PaginationQuerySort{
    Pagination: pagination.Pagination{MaxPer: 50, DefaultPer: 20},
    Query:      pagination.Query{},
    Sort:       pagination.Sort{AllowedSorts: []string{"name", "created_at"}},
}
c.Bind(&q)

// ?query=admin&page=3&per=20&sort=created_at&order=desc
fmt.Println(q.OrderByLimitOffset())
// ORDER BY created_at DESC LIMIT 20 OFFSET 40

bcrypt

Password hashing utilities using bcrypt, with support for database scanning and JSON marshaling.

var p bcrypt.Password
p.Update("mysecretpassword")
fmt.Println(p.Equal("mysecretpassword")) // true

Utility Packages

logger

Simple logger interface used across all gopsql packages. Includes a standard logger implementation with colored output.

type Logger interface {
    Debug(args ...interface{})
    Info(args ...interface{})
    Notice(args ...interface{})
    Warning(args ...interface{})
    Error(args ...interface{})
    Critical(args ...interface{})
    Fatal(args ...interface{})
}

goconf

Use Go files as configuration files. Marshal and unmarshal Go structs to/from Go source code.

type Config struct {
    APIKey    string
    DebugMode bool
}

config := Config{"secret123", true}
content, _ := goconf.Marshal(config)
// Output:
// package config
// const (
//     APIKey = "secret123"
//     DebugMode = true
// )

watch

Watch .go and .mod files for changes and trigger rebuilds or test runs.

logger := logger.StandardLogger
watch.NewWatch().WithLogger(logger).Do()

gow

Command-line tool for watching Go files and automatically rebuilding or running tests.

# Install
go install -v github.com/gopsql/gow@latest

# Watch and rebuild
gow

# Watch and run tests
gow -cd tests -test -clean -- -v ./...

# With custom build flags
gow -- -v -race -o myapp -- --custom-flag

Quick Start

package main

import (
    "fmt"
    "github.com/gopsql/logger"
    "github.com/gopsql/pq"
    "github.com/gopsql/psql"
)

type User struct {
    Id        int
    Name      string
    Email     string
    CreatedAt time.Time
}

func main() {
    // Connect to database
    conn := pq.MustOpen("postgres://localhost:5432/mydb?sslmode=disable")
    defer conn.Close()

    // Create model
    Users := psql.NewModel(User{}, conn, logger.StandardLogger)

    // Create table
    Users.NewSQL(Users.Schema()).MustExecute()

    // Insert user
    var id int
    Users.Insert("Name", "John", "Email", "john@example.com").
        Returning("Id").MustQueryRow(&id)

    // Find users
    var users []User
    Users.Find().Where("Name LIKE $?", "%John%").MustQuery(&users)
    fmt.Println(users)
}

License

All packages are released under the MIT License.