easysqlite

README

Easy SQLite

This is the library for you if

• You want to reduce boilerplate of SQLite init in your pet project
• You want automatic migrations for your app

How to use

//go:embed migrations/*.sql
var embedMigrations embed.FS

func main() {
    // Creating db connection
    db, err := easysqlite.New("db.sqlite", embedMigrations, "migrations")
    if err != nil {
    	panic(err)
    }

    // Inserting records
    ctx := context.Background()
    _, err = db.ExecContext(ctx, `INSERT INTO users (name,age) VALUES(?,?)`, "John", 23)
    if err != nil {
    	panic(err)
    }

    // User represents one row of the table "users"
    type User struct {
    	ID   int64  `db:"id"`
    	Name string `db:"name"`
    	Age  int    `db:"age"`
    }

    // Getting one row
    var user User
    err = db.GetContext(ctx, &user, `SELECT id,name,age FROM users WHERE id=?`, 1)
    if err != nil {
    	panic(err)
    }

    // Selecting many rows
    var users []User
    err = db.SelectContext(ctx, &users, `SELECT id,name,age FROM users`)
    if err != nil {
    	panic(err)
    }
}

You need to store mirations inside *.sql files in a folder relative to the source file which contains var embedMigrations embed.FS. Each SQL file must have a number prefix followed by an underscore. Because migrations will be applied in the order of these number prefixes.

$ tree migrations/
migrations/
├── 001_initial.sql
└── 002_added_age.sql

1 directory, 2 files

The formatting convention used for the migrations are taken from the goose library, since it's being used under the hood. Check out the main.go file and the migrations folder which are within this repository as a reference example

The contents of a simple migration file:

-- +goose Up
CREATE TABLE users (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name STRING NOT NULL
);

-- +goose Down
DROP TABLE users;

The Down clause is needed if you want to rollback your migrations in the future. Note: Rollbacks aren't currently supported, for the meantime if needed you can use the goose CLI-tool.

Should I keep migration sql files near the app binary?

No, after compiling migrations get embedded into the executable binary (with the help of go's embed package). So you don't need to include migration files into the deploy container along with your app. They're needed only for the build.

When do migrations get applied?

When you call easysqlite.New(...) goose checks if there are any migrations that weren't applied yet. If there are, goose applies them. If there is no database file, it will be created and all migrations will be applied to it one-by-one.

Personally I think this a good way for small apps and pet projects to apply migrations on startup.

Transactions

err = db.DoInTx(ctx, func(ctx context.Context) error {
    transferAmount := 200
    userFrom := 100
    userTo := 101

    var currentBalance int
    err := db.GetContext(ctx, &currentBalance,
    	`SELECT balance FROM users WHERE id=?`,
    	userFrom)
    if err != nil {
    	return err
    }

    if currentBalance < transferAmount {
    	return errors.New("insufficient funds")
    }

    _, err = db.ExecContext(ctx,
    	`UPDATE users SET balance=balance-? WHERE id=?`,
    	transferAmount, userFrom)
    if err != nil {
    	return err
    }

    _, err = db.ExecContext(ctx,
    	`UPDATE users SET balance=balance+? WHERE id=?`,
    	transferAmount, userTo)
    if err != nil {
    	return err
    }

    return nil
})

DoInTx initiates a transaction with LevelSerializable and commits if the provided callback function returns nil. If it returns any error, the transaction is rolled back.

It is unnecessary to pass the tx object to query methods; they will retrieve the tx object from the context. Ensure that the context obtained in the callback function is passed to all query methods and that only the query methods exported by easysqlite are utilized.

Utilizing transactions in this manner is advantageous because, for instance, when employing clean architecture, it is challenging to maintain the domain layer free from database-specific implementations when transactions are required. When the transaction is automatically obtained from the context, it liberates the domain layer from implementation-specific imports. You can simply encapsulate DoInTx using an interface and invoke your repository methods.

Even if clean architecture is not employed, this approach remains beneficial as it eliminates the need to manually manage transaction beginnings and rollbacks, ensuring that you never forget to pass a tx object.

Documentation

Index

• type EasySqlite
  • func New(path string, migrations fs.FS, dirName string) (*EasySqlite, error)
  • func (s *EasySqlite) DoInTx(ctx context.Context, callback func(ctx context.Context) error) error
  • func (s *EasySqlite) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
  • func (s *EasySqlite) GetContext(ctx context.Context, dest any, query string, args ...any) error
  • func (s *EasySqlite) MustExecContext(ctx context.Context, query string, args ...any) sql.Result
  • func (s *EasySqlite) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
  • func (s *EasySqlite) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
  • func (s *EasySqlite) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
  • func (s *EasySqlite) SelectContext(ctx context.Context, dest any, query string, args ...any) error

Examples

• New

Types

type EasySqlite

type EasySqlite struct {
	// contains filtered or unexported fields
}

func New

func New(path string, migrations fs.FS, dirName string) (*EasySqlite, error)

New opens the db file or creates it if not exists Migrations should be embedded by the go "embed" package Check out the example of migrations in the cmd/example folder The migration tool used here is https://github.com/pressly/goose

Example

//nolint:testableexamples
package main

import (
	"context"
	"embed"

	easysqlite "github.com/pav5000/easy-sqlite"
)

// embeding migrations folder into executable binary
// the path should be relative to your source file
//
//go:embed migrations/*.sql
var embedMigrations embed.FS

func main() {
	// Creating conn connection
	conn, err := easysqlite.New("db.sqlite", embedMigrations, "migrations")
	if err != nil {
		panic(err)
	}

	// Inserting records
	ctx := context.Background()
	_, err = conn.ExecContext(ctx, `INSERT INTO users (name,age) VALUES(?,?)`, "John", 23)
	if err != nil {
		panic(err)
	}

	// User represents one row of the table "users"
	type User struct {
		ID   int64  `db:"id"`
		Name string `db:"name"`
		Age  int    `db:"age"`
	}

	// Getting one row
	var user User
	err = conn.GetContext(ctx, &user, `SELECT id,name,age FROM users WHERE id=?`, 1)
	if err != nil {
		panic(err)
	}

	// Selecting many rows
	var users []User
	err = conn.SelectContext(ctx, &users, `SELECT id,name,age FROM users`)
	if err != nil {
		panic(err)
	}
}

func (*EasySqlite) DoInTx

func (s *EasySqlite) DoInTx(ctx context.Context, callback func(ctx context.Context) error) error

DoInTx starts transaction and guarantees that all queries in easysqlite methods will use it as long as you use the context provided into the callback function.

func (*EasySqlite) ExecContext

func (s *EasySqlite) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

func (*EasySqlite) GetContext

func (s *EasySqlite) GetContext(ctx context.Context, dest any, query string, args ...any) error

func (*EasySqlite) MustExecContext

func (s *EasySqlite) MustExecContext(ctx context.Context, query string, args ...any) sql.Result

func (*EasySqlite) PrepareContext

func (s *EasySqlite) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

func (*EasySqlite) QueryContext

func (s *EasySqlite) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

func (*EasySqlite) QueryRowContext

func (s *EasySqlite) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row

func (*EasySqlite) SelectContext

func (s *EasySqlite) SelectContext(ctx context.Context, dest any, query string, args ...any) error