ksql

package module
v1.12.3 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 6, 2024 License: MIT Imports: 19 Imported by: 28

README

CI codecov Go Reference Go Report Card

KSQL the Keep it Simple SQL library

KSQL was created to offer an actually simple and satisfactory tool for interacting with SQL Databases in Golang.

The core goal of KSQL is not to offer new features that are unavailable on other libraries (although we do have some), but to offer a well-thought and well-planned API so that users have an easier time, learning, debugging, and avoiding common pitfalls.

KSQL is also decoupled from its backend so that the actual communication with the database is performed by well-known and trusted technologies, namely: pgx and database/sql. You can even create your own backend adapter for KSQL which is useful in some situations.

In this README you will find examples for "Getting Started" with the library, for more advanced use-cases please read our Wiki.

Outstanding Features

  • Every operation returns errors a single time, so its easier to handle them
  • Helper functions for everyday operations, namely: Insert, Patch and Delete
  • Generic and powerful functions for Querying and Scanning data into structs
  • Works on top of existing battle-tested libraries such as database/sql and pgx
  • Supports sql.Scanner and sql.Valuer and also all pgx special types (when using kpgx)
  • And many other features designed to make your life easier

Let's start with some Code:

This short example below is a TLDR version to illustrate how easy it is to use KSQL.

You will find more complete examples in the sections below.

This example is available on ./examples/overview/main.go if you want to run it

package main

import (
	"context"
	"errors"
	"fmt"
	"log"

	"github.com/vingarcia/ksql"
	"github.com/vingarcia/ksql/adapters/kpgx"
)

var UsersTable = ksql.NewTable("users", "user_id")

type User struct {
	ID    int    `ksql:"user_id"`
	Name  string `ksql:"name"`
	Type  string `ksql:"type"`
	Posts []Post
}

// Post have a many to one relationship with User
var PostsTable = ksql.NewTable("posts", "post_id")

type Post struct {
	ID     int    `ksql:"post_id"`
	UserID int    `ksql:"user_id"`
	Title  string `ksql:"title"`
	Text   string `ksql:"text"`
}

// Address have a one to one relationship with User
var AddressesTable = ksql.NewTable("addresses", "id")

type Address struct {
	ID       int    `ksql:"id"`
	UserID   int    `ksql:"user_id"`
	FullAddr string `ksql:"full_addr"`
}

func main() {
	ctx := context.Background()
	dbURL, closeDB := startExampleDB(ctx)
	defer closeDB()

	db, err := kpgx.New(ctx, dbURL, ksql.Config{})
	if err != nil {
		log.Fatalf("unable connect to database: %s", err)
	}
	defer db.Close()

	// For querying only some attributes you can
	// create a custom struct like this:
	var count []struct {
		Count int    `ksql:"count"`
		Type  string `ksql:"type"`
	}
	err = db.Query(ctx, &count, "SELECT type, count(*) as count FROM users GROUP BY type")
	if err != nil {
		log.Fatalf("unable to query users: %s", err)
	}

	fmt.Println("number of users by type:", count)

	// For loading entities from the database KSQL can build
	// the SELECT part of the query for you if you omit it like this:
	var adminUsers []User
	err = db.Query(ctx, &adminUsers, "FROM users WHERE type = $1", "admin")
	if err != nil {
		log.Fatalf("unable to query admin users: %s", err)
	}

	fmt.Println("admin users:", adminUsers)

	// A nice way of loading the posts of a user might be like this:
	var user User
	err = errors.Join(
		db.QueryOne(ctx, &user, "FROM users WHERE user_id = $1", 42),
		db.Query(ctx, &user.Posts, "FROM posts WHERE user_id = $1", user.ID),
	)
	if err != nil {
		log.Fatalf("unable to query users: %s", err)
	}

	fmt.Println("user with posts:", user)

	// You can retrieve data from joined tables like this
	// (notice you can either use the name of the table or the alias you choose for it in the query):
	var rows []struct {
		OneUser    User    `tablename:"users"`
		OneAddress Address `tablename:"addr"`
	}
	err = db.Query(ctx, &rows,
		`FROM users
		JOIN addresses addr
		  ON users.user_id = addr.user_id`,
	)
	if err != nil {
		log.Fatalf("unable to query users: %s", err)
	}

	fmt.Println("rows of joined tables:", rows)
}

Note: In the example above we are using the $1, $2 and $3 as placeholders on the query because this example is meant to run on top of Postgres.

If you are running on top of MySQL or SQLite use ? instead, and if you are running on top of SQLServer use @p1, @p2 and @p3 instead.

Supported Adapters:

We support a few different adapters, one of them is illustrated above (kpgx), the other ones have the exact same signature but work on different databases or driver versions, they are:

  • kpgx.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for Postgres, it works on top of pgxpool and pgx version 4, download it with:

    go get github.com/vingarcia/ksql/adapters/kpgx

  • kpgx5.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for Postgres, it works on top of pgxpool and pgx version 5, download it with:

    go get github.com/vingarcia/ksql/adapters/kpgx5

  • kmysql.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for MySQL, it works on top of database/sql, download it with:

    go get github.com/vingarcia/ksql/adapters/kmysql

  • ksqlserver.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for SQLServer, it works on top of database/sql, download it with:

    go get github.com/vingarcia/ksql/adapters/ksqlserver

  • ksqlite3.New(ctx, os.Getenv("DATBAASE_PATH"), ksql.Config{}) for SQLite3, it works on top of database/sql and mattn/go-sqlite3 which relies on CGO, download it with:

    go get github.com/vingarcia/ksql/adapters/ksqlite3

  • ksqlite.New(ctx, os.Getenv("DATABASE_PATH"), ksql.Config{}) for SQLite, it works on top of database/sql and modernc.org/sqlite which does not require CGO, download it with:

    go get github.com/vingarcia/ksql/adapters/modernc-ksqlite

For more detailed examples see:

  • ./examples/all_adapters/all_adapters.go

The KSQL Interface

The current interface contains the methods the users are expected to use, and it is also used for making it easy to mock the whole library if needed.

This interface is declared in the project as ksql.Provider and is displayed below.

We plan on keeping it very simple with a small number of well-thought functions that cover all use cases, so don't expect many additions:

// Provider describes the KSQL public behavior
//
// The Insert, Patch, Delete and QueryOne functions return `ksql.ErrRecordNotFound`
// if no record was found or no rows were changed during the operation.
type Provider interface {
	Insert(ctx context.Context, table Table, record interface{}) error
	Patch(ctx context.Context, table Table, record interface{}) error
	Delete(ctx context.Context, table Table, idOrRecord interface{}) error

	Query(ctx context.Context, records interface{}, query string, params ...interface{}) error
	QueryOne(ctx context.Context, record interface{}, query string, params ...interface{}) error
	QueryChunks(ctx context.Context, parser ChunkParser) error

	Exec(ctx context.Context, query string, params ...interface{}) (Result, error)
	Transaction(ctx context.Context, fn func(Provider) error) error
}

Using KSQL

In the example below we'll cover all the most common use cases such as:

  1. Inserting records
  2. Updating records
  3. Deleting records
  4. Querying one or many records
  5. Making transactions

More advanced use cases are illustrated on their own pages on our Wiki:

For the more common use cases please read the example below, which is also available here if you want to compile it yourself.

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/vingarcia/ksql"
	"github.com/vingarcia/ksql/adapters/ksqlite3"
	"github.com/vingarcia/ksql/nullable"
)

type User struct {
	ID   int    `ksql:"id"`
	Name string `ksql:"name"`
	Age  int    `ksql:"age"`

	// The following attributes are making use of the KSQL Modifiers,
	// you can find more about them on our Wiki:
	//
	// - https://github.com/VinGarcia/ksql/wiki/Modifiers
	//

	// The `json` modifier will save the address as JSON in the database
	Address Address `ksql:"address,json"`

	// The timeNowUTC modifier will set this field to `time.Now().UTC()` before saving it:
	UpdatedAt time.Time `ksql:"updated_at,timeNowUTC"`

	// The timeNowUTC/skipUpdates modifier will set this field to `time.Now().UTC()` only
	// when first creating it and ignore it during updates.
	CreatedAt time.Time `ksql:"created_at,timeNowUTC/skipUpdates"`
}

type PartialUpdateUser struct {
	ID      int      `ksql:"id"`
	Name    *string  `ksql:"name"`
	Age     *int     `ksql:"age"`
	Address *Address `ksql:"address,json"`
}

type Address struct {
	State string `json:"state"`
	City  string `json:"city"`
}

// UsersTable informs KSQL the name of the table and that it can
// use the default value for the primary key column name: "id"
var UsersTable = ksql.NewTable("users")

func main() {
	ctx := context.Background()

	// In this example we'll use sqlite3:
	db, err := ksqlite3.New(ctx, "/tmp/hello.sqlite", ksql.Config{
		MaxOpenConns: 1,
	})
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	// In the definition below, please note that BLOB is
	// the only type we can use in sqlite for storing JSON.
	_, err = db.Exec(ctx, `CREATE TABLE IF NOT EXISTS users (
	  id INTEGER PRIMARY KEY,
		age INTEGER,
		name TEXT,
		address BLOB,
		created_at DATETIME,
		updated_at DATETIME
	)`)
	if err != nil {
		panic(err.Error())
	}

	var alison = User{
		Name: "Alison",
		Age:  22,
		Address: Address{
			State: "MG",
		},
	}
	err = db.Insert(ctx, UsersTable, &alison)
	if err != nil {
		panic(err.Error())
	}
	fmt.Println("Alison ID:", alison.ID)

	// Inserting inline:
	err = db.Insert(ctx, UsersTable, &User{
		Name: "Cristina",
		Age:  27,
		Address: Address{
			State: "SP",
		},
	})
	if err != nil {
		panic(err.Error())
	}

	// Deleting Alison:
	err = db.Delete(ctx, UsersTable, alison.ID)
	if err != nil {
		panic(err.Error())
	}

	// Retrieving Cristina, note that if you omit the SELECT part of the query
	// KSQL will build it for you (efficiently) based on the fields from the struct:
	var cris User
	err = db.QueryOne(ctx, &cris, "FROM users WHERE name = ? ORDER BY id", "Cristina")
	if err != nil {
		panic(err.Error())
	}
	fmt.Printf("Cristina: %#v\n", cris)

	// Updating all fields from Cristina:
	cris.Name = "Cris"
	err = db.Patch(ctx, UsersTable, cris)

	// Changing the age of Cristina but not touching any other fields:

	// Partial update technique 1:
	err = db.Patch(ctx, UsersTable, struct {
		ID  int `ksql:"id"`
		Age int `ksql:"age"`
	}{ID: cris.ID, Age: 28})
	if err != nil {
		panic(err.Error())
	}

	// Partial update technique 2:
	err = db.Patch(ctx, UsersTable, PartialUpdateUser{
		ID:  cris.ID,
		Age: nullable.Int(28), // (just a pointer to an int, if null it won't be updated)
	})
	if err != nil {
		panic(err.Error())
	}

	// Listing first 10 users from the database
	// (each time you run this example a new Cristina is created)
	//
	// Note: Using this function it is recommended to set a LIMIT, since
	// not doing so can load too many users on your computer's memory or
	// cause an Out Of Memory Kill.
	//
	// If you need to query very big numbers of users we recommend using
	// the `QueryChunks` function.
	var users []User
	err = db.Query(ctx, &users, "FROM users LIMIT 10")
	if err != nil {
		panic(err.Error())
	}

	fmt.Printf("Users: %#v\n", users)

	// Making transactions:
	err = db.Transaction(ctx, func(db ksql.Provider) error {
		var cris2 User
		err = db.QueryOne(ctx, &cris2, "FROM users WHERE id = ?", cris.ID)
		if err != nil {
			// This will cause an automatic rollback:
			return err
		}

		err = db.Patch(ctx, UsersTable, PartialUpdateUser{
			ID:  cris2.ID,
			Age: nullable.Int(29),
		})
		if err != nil {
			// This will also cause an automatic rollback and then panic again
			// so that we don't hide the panic inside the KSQL library
			panic(err.Error())
		}

		// Commits the transaction
		return nil
	})
	if err != nil {
		panic(err.Error())
	}
}

Benchmark Comparison

The results of the benchmark are good for KSQL, but not flawless.

The next section summarizes the results so its more comprehensible, but if you prefer to read the raw benchmark data just scroll down to the Benchmark Results section.

Summary

For transparency purposes this summary will focus at the benchmark showing the worst results for KSQL which is querying multiple lines, this is the summary:

Comparing KSQL running on top of database/sql with sqlx, sqlx is 5% faster than KSQL, which is in practical terms an insignificant difference. And if KSQL is running on top of pgx then KSQL becomes 42% faster because pgx is significantly faster than sqlx. Finally if you are using sqlx with prepared statements everytime then sqlx is 7.5% faster than KSQL on top of pgx.

So between KSQL vs sqlx the performance difference is very small, and if you are using Postgres odds are KSQL will be much faster.

Comparing KSQL running on top of pgx with pgx itself, KSQL is 13.66% slower (on average), which is not insignificant but isn't much either.

Comparing KSQL running on top pgx with gorm, KSQL is 11.87% faster than gorm or inversely gorm is 13.4% slower.

It is worth noting that KSQL is only caching of prepared statements when using postgres, because this is performed by pgx, and this means that when using MySQL, SQLServer or SQLite, if you plan on also using prepared statements other libaries such as sqlx will be significantly faster than KSQL.

We are working on adding support for cached prepared statements for these other databases in the future.

Benchmark Results

To understand the benchmark below you must know that all tests are performed using Postgres 12.1 and that we are comparing the following tools:

  • KSQL using the adapter that wraps database/sql
  • KSQL using the adapter that wraps pgx
  • database/sql
  • sqlx
  • pgx (with pgxpool)
  • gorm
  • sqlc
  • sqlboiler

For each of these tools, we are running 3 different queries:

The insert-one query looks like this:

INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id

The single-row query looks like this:

SELECT id, name, age FROM users OFFSET $1 LIMIT 1

The multiple-rows query looks like this:

SELECT id, name, age FROM users OFFSET $1 LIMIT 10

Keep in mind that some of the tools tested (like GORM) actually build the queries internally so the actual code used for the benchmark might differ a little bit from the example ones above.

Without further ado, here are the results:

$ make bench TIME=5s
sqlc generate
go test -bench=. -benchtime=5s
goos: linux
goarch: amd64
pkg: github.com/vingarcia/ksql/benchmarks
cpu: Intel(R) Core(TM) i7-10750H CPU @ 2.60GHz
BenchmarkInsert/ksql/sql-adapter/insert-one-12         	    9711	    618727 ns/op
BenchmarkInsert/ksql/pgx-adapter/insert-one-12         	   10000	    555967 ns/op
BenchmarkInsert/sql/insert-one-12                      	    9450	    624334 ns/op
BenchmarkInsert/sql/prep-stmt/insert-one-12            	   10000	    555119 ns/op
BenchmarkInsert/sqlx/insert-one-12                     	    9552	    632986 ns/op
BenchmarkInsert/sqlx/prep-stmt/insert-one-12           	   10000	    560244 ns/op
BenchmarkInsert/pgxpool/insert-one-12                  	   10000	    553535 ns/op
BenchmarkInsert/gorm/insert-one-12                     	    9231	    668423 ns/op
BenchmarkInsert/sqlc/insert-one-12                     	    9589	    632277 ns/op
BenchmarkInsert/sqlc/prep-stmt/insert-one-12           	   10803	    560301 ns/op
BenchmarkInsert/sqlboiler/insert-one-12                	    9790	    631464 ns/op
BenchmarkQuery/ksql/sql-adapter/single-row-12          	   44436	    131191 ns/op
BenchmarkQuery/ksql/sql-adapter/multiple-rows-12       	   42087	    143795 ns/op
BenchmarkQuery/ksql/pgx-adapter/single-row-12          	   86192	     65447 ns/op
BenchmarkQuery/ksql/pgx-adapter/multiple-rows-12       	   74106	     79004 ns/op
BenchmarkQuery/sql/single-row-12                       	   44719	    134491 ns/op
BenchmarkQuery/sql/multiple-rows-12                    	   43218	    138309 ns/op
BenchmarkQuery/sql/prep-stmt/single-row-12             	   89328	     64162 ns/op
BenchmarkQuery/sql/prep-stmt/multiple-rows-12          	   84282	     71454 ns/op
BenchmarkQuery/sqlx/single-row-12                      	   44118	    132928 ns/op
BenchmarkQuery/sqlx/multiple-rows-12                   	   43824	    137235 ns/op
BenchmarkQuery/sqlx/prep-stmt/single-row-12            	   87570	     66610 ns/op
BenchmarkQuery/sqlx/prep-stmt/multiple-rows-12         	   82202	     72660 ns/op
BenchmarkQuery/pgxpool/single-row-12                   	   94034	     63373 ns/op
BenchmarkQuery/pgxpool/multiple-rows-12                	   86275	     70275 ns/op
BenchmarkQuery/gorm/single-row-12                      	   83052	     71539 ns/op
BenchmarkQuery/gorm/multiple-rows-12                   	   62636	     89652 ns/op
BenchmarkQuery/sqlc/single-row-12                      	   44329	    132659 ns/op
BenchmarkQuery/sqlc/multiple-rows-12                   	   44440	    139026 ns/op
BenchmarkQuery/sqlc/prep-stmt/single-row-12            	   91486	     66679 ns/op
BenchmarkQuery/sqlc/prep-stmt/multiple-rows-12         	   78583	     72583 ns/op
BenchmarkQuery/sqlboiler/single-row-12                 	   70030	     87089 ns/op
BenchmarkQuery/sqlboiler/multiple-rows-12              	   69961	     84376 ns/op
PASS
ok  	github.com/vingarcia/ksql/benchmarks	221.596s
Benchmark executed at: 2023-10-22
Benchmark executed on commit: 35b6882317e82de7773fb3908332e8ac3d127010

Running the KSQL tests (for contributors)

The tests use docker-test for setting up all the supported databases, which means that:

  • You need to have docker installed

  • You must be able to run docker without sudo, i.e. if you are not root you should add yourself to the docker group, e.g.:

    $ sudo usermod <your_username> -aG docker

    And then restart your login session (or just reboot)

  • Finally run make pre-download-all-images only once so your tests don't timeout downloading the database images.

After that, you can just run the tests by using:

make test

TODO List

  • Add an Upsert helper method
  • Try to implement an automatic prepared statements cache like pgx does.
  • Update ksqltest.FillStructWith to work with ksql:"..,json" tagged attributes
  • Improve error messages (ongoing)
  • Finish the kbuilder package

Optimization Opportunities

  • Test if using a pointer on the field info is faster or not
  • Consider passing the cached structInfo as an argument for all the functions that use it, so that we don't need to get it more than once in the same call.
  • Use a cache to store often-used queries (like pgx)
  • Preload the insert method for all dialects inside ksql.NewTable()
  • Use prepared statements for the helper functions, Update, Insert and Delete.

Features for a possible V2

  • Change the .Transaction(db ksql.Provider) to a .Transaction(ctx context.Context)
  • Make the .Query() method to return a type Query interface { One(); All(); Chunks(); }
  • Have an Update() method that updates without ignoring NULLs as Patch() does
    • Have a new Modifier skipNullUpdates so that the Update function will do the job of the Patch
    • Remove the Patch function.
  • Rename NewTable() to just Table() so it feels right to declare it inline when convenient

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var ErrAbortIteration error = fmt.Errorf("ksql: abort iteration, should only be used inside QueryChunks function")

ErrAbortIteration should be used inside the QueryChunks function to inform QueryChunks it should stop querying, close the connection and return with no errors.

View Source 
var ErrNoValuesToUpdate error = fmt.Errorf("ksql: the input struct contains no values to update")

ErrNoValuesToUpdate informs the error of trying to make an update that would not change any attributes.

This could happen if all the non-ID attributes of the struct are being ignored or if they don't exist.

Since ID attributes are ignored by the Patch() method updating a struct that only have IDs will result in this error. And this error will also occur if the struct does have some non-ID attributes but they are all being ignored.

The reasons that can cause an attribute to be ignored in the Patch() function are: (1) If it is a nil pointer, Patch() will just ignore it. (2) If the attribute is using a modifier that contains the SkipUpdates flag.

View Source 
var ErrRecordMissingIDs error = fmt.Errorf("ksql: missing required ID fields")

ErrRecordMissingIDs is returned by the Update or Delete functions if an input record does not have all of the IDs described on the input table.

View Source 
var ErrRecordNotFound error = fmt.Errorf("ksql: the query returned no results: %w", sql.ErrNoRows)

ErrRecordNotFound informs that a given query failed because the record was not found. This error can be returned by the following methods: Patch(), QueryOne() and Delete().

Functions

func DeleteTest added in v1.4.2 

func DeleteTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

DeleteTest runs all tests for making sure the Delete function is working for a given adapter and dialect.

func ErrorLogger added in v1.9.0 

func ErrorLogger(ctx context.Context, values LogValues)

ErrorLogger is a builtin logger that can be passed to ksql.InjectLogger() to only log when an error occurs.

Note that only errors that happen after KSQL sends the query to the backend adapter will be logged. Any errors that happen before that will not be logged.

func InjectLogger added in v1.9.0 

func InjectLogger(
	ctx context.Context,
	logFn LoggerFn,
) context.Context

InjectLogger is a debugging tool that allows the user to force KSQL to log the query, query params and error response whenever a query is executed.

Example Usage: 

// After injecting a logger into `ctx` all subsequent queries
// that use this context will be logged.
ctx = ksql.InjectLogger(ctx, ksql.Logger)

// All the calls below will cause KSQL to log the queries:
var user User
db.Insert(ctx, usersTable, &user)

user.Name = "NewName"
db.Patch(ctx, usersTable, &user)

var users []User
db.Query(ctx, &users, someQuery, someParams...)
db.QueryOne(ctx, &user, someQuery, someParams...)

db.Delete(ctx, usersTable, user.ID)

func InsertTest added in v1.4.2 

func InsertTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

InsertTest runs all tests for making sure the Insert function is working for a given adapter and dialect.

func Logger added in v1.9.0 

func Logger(ctx context.Context, values LogValues)

Logger is a builtin logger that can be passed to ksql.InjectLogger() to log every query and query errors.

Note that only errors that happen after KSQL sends the query to the backend adapter will be logged. Any errors that happen before that will not be logged.

func ModifiersTest added in v1.4.9 

func ModifiersTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

func PatchTest added in v1.4.8 

func PatchTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

PatchTest runs all tests for making sure the Patch function is working for a given adapter and dialect.

func QueryChunksTest added in v1.4.2 

func QueryChunksTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

QueryChunksTest runs all tests for making sure the QueryChunks function is working for a given adapter and dialect.

func QueryOneTest added in v1.4.2 

func QueryOneTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

QueryOneTest runs all tests for making sure the QueryOne function is working for a given adapter and dialect.

func QueryTest added in v1.4.2 

func QueryTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

QueryTest runs all tests for making sure the Query function is working for a given adapter and dialect.

func RunTestsForAdapter added in v1.4.2 

func RunTestsForAdapter(
	t *testing.T,
	adapterName string,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

RunTestsForAdapter will run all necessary tests for making sure a given adapter is working as expected.

Optionally it is also possible to run each of these tests separatedly, which might be useful during the development of a new adapter.

func ScanRowsTest added in v1.4.2 

func ScanRowsTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

ScanRowsTest runs all tests for making sure the ScanRows feature is working for a given adapter and dialect.

func TransactionTest added in v1.4.2 

func TransactionTest(
	t *testing.T,
	dialect sqldialect.Provider,
	connStr string,
	newDBAdapter func(t *testing.T) (DBAdapter, io.Closer),
)

TransactionTest runs all tests for making sure the Transaction function is working for a given adapter and dialect.

Types

type ChunkParser 

type ChunkParser struct {
	// The Query and Params are used together to build a query with
	// protection from injection, just like when using the Find function.
	Query  string
	Params []interface{}

	ChunkSize int

	// This attribute must be a function with the following signature:
	//
	// `func(chunk []<Record>) error`.
	//
	// Where the actual Record type should be of a struct
	// representing the rows you are expecting to receive.
	ForEachChunk interface{}
}

ChunkParser stores the arguments of the QueryChunks function

type Config 

type Config struct {
	// MaxOpenCons defaults to 1 if not set
	MaxOpenConns int

	// Used by some adapters (such as kpgx) where nil disables TLS
	TLSConfig *tls.Config
}

Config describes the optional arguments accepted by the `ksql.New()` function.

func (*Config) SetDefaultValues added in v1.2.0 

func (c *Config) SetDefaultValues()

SetDefaultValues should be called by all adapters to set the default config values if unset.

type DB 

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

DB represents the KSQL client responsible for interfacing with the "database/sql" package implementing the KSQL interface `ksql.Provider`.

func NewWithAdapter 

func NewWithAdapter(
	adapter DBAdapter,
	dialect sqldialect.Provider,
) (DB, error)

NewWithAdapter allows the user to insert a custom implementation of the DBAdapter interface

func (DB) Close added in v1.4.6 

func (c DB) Close() error

Close implements the io.Closer interface

func (DB) Delete 

func (c DB) Delete(
	ctx context.Context,
	table Table,
	idOrRecord interface{},
) (err error)

Delete deletes one record from the database using the ID or IDs defined on the `ksql.Table` passed as second argument.

For tables with a single ID column you can pass the record to be deleted as a struct, as a map or just pass the ID itself.

For tables with composite keys you must pass the record as a struct or a map so that KSQL can read all the composite keys from it.

The examples below should work for both types of tables: 

err := c.Delete(ctx, UsersTable, user)

err := c.Delete(ctx, UserPostsTable, map[string]interface{}{
    "user_id": user.ID,
    "post_id": post.ID,
})

The example below is shorter but will only work for tables with a single primary key: 

err := c.Delete(ctx, UsersTable, user.ID)

func (DB) Exec 

func (c DB) Exec(ctx context.Context, query string, params ...interface{}) (_ Result, err error)

Exec just runs an SQL command on the database returning no rows.

func (DB) Insert 

func (c DB) Insert(
	ctx context.Context,
	table Table,
	record interface{},
) (err error)

Insert one or more instances on the database

If the original instances have been passed by reference the ID is automatically updated after insertion is completed.

func (DB) Patch added in v1.4.2 

func (c DB) Patch(
	ctx context.Context,
	table Table,
	record interface{},
) (err error)

Patch applies a partial update (explained below) to the given instance on the database by id.

Partial updates will ignore any nil pointer attributes from the struct, updating only the non nil pointers and non pointer attributes.

func (DB) Query 

func (c DB) Query(
	ctx context.Context,
	records interface{},
	query string,
	params ...interface{},
) (err error)

Query queries several rows from the database, the input should be a slice of structs (or *struct) passed by reference and it will be filled with all the results.

Note: it is very important to make sure the query will return a small known number of results, otherwise you risk of overloading the available memory.

func (DB) QueryChunks 

func (c DB) QueryChunks(
	ctx context.Context,
	parser ChunkParser,
) (err error)

QueryChunks is meant to perform queries that returns more results than would normally fit on memory, for others cases the Query and QueryOne functions are indicated.

The ChunkParser argument has 4 attributes: (1) The Query; (2) The query args; (3) The chunk size; (4) A callback function called ForEachChunk, that will be called to process each chunk loaded from the database.

Note that the signature of the ForEachChunk callback can be any function that receives a slice of structs or a slice of pointers to struct as its only argument and that reflection will be used to instantiate this argument and to fill it with the database rows.

func (DB) QueryOne 

func (c DB) QueryOne(
	ctx context.Context,
	record interface{},
	query string,
	params ...interface{},
) (err error)

QueryOne queries one instance from the database, the input struct must be passed by reference and the query should return only one result.

QueryOne returns a ErrRecordNotFound if the query returns no results.

func (DB) Transaction 

func (c DB) Transaction(ctx context.Context, fn func(Provider) error) error

Transaction encapsulates several queries into a single transaction. All these queries should be made inside the input callback `fn` and they should use the input ksql.Provider.

If the callback returns any errors the transaction will be rolled back, otherwise the transaction will me committed.

If it happens that a second transaction is started inside a transaction callback the same transaction will be reused with no errors.

type DBAdapter 

type DBAdapter interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (Rows, error)
}

DBAdapter is minimalistic interface to decouple our implementation from database/sql, i.e. if any struct implements the functions below with the exact same semantic as the sql package it will work with KSQL.

To create a new client using this adapter use `ksql.NewWithAdapter()`

type LogValues added in v1.9.0 

type LogValues struct {
	Query  string
	Params []interface{}
	Err    error
}

LogValues is the argument type of ksql.LoggerFn which contains the data available for logging whenever a query is executed.

func (LogValues) MarshalJSON added in v1.9.0 

func (l LogValues) MarshalJSON() ([]byte, error)

type LoggerFn added in v1.9.0 

type LoggerFn func(ctx context.Context, values LogValues)

LoggerFn is a the type of function received as argument of the ksql.InjectLogger function.

type Mock 

type Mock struct {
	InsertFn func(ctx context.Context, table Table, record interface{}) error
	PatchFn  func(ctx context.Context, table Table, record interface{}) error
	DeleteFn func(ctx context.Context, table Table, idOrRecord interface{}) error

	QueryFn       func(ctx context.Context, records interface{}, query string, params ...interface{}) error
	QueryOneFn    func(ctx context.Context, record interface{}, query string, params ...interface{}) error
	QueryChunksFn func(ctx context.Context, parser ChunkParser) error

	ExecFn        func(ctx context.Context, query string, params ...interface{}) (Result, error)
	TransactionFn func(ctx context.Context, fn func(db Provider) error) error
}

Mock implements the Provider interface in order to allow users to easily mock the behavior of a ksql.Provider.

To mock a particular method, e.g. Insert, you just need to overwrite the corresponding function attribute whose name is InsertFn().

NOTE: This mock should be instantiated inside each unit test not globally.

For capturing input values use a closure as in the example: 

var insertRecord interface{}
dbMock := Mock{
	InsertFn: func(ctx context.Context, table Table, record interface{}) error {
		insertRecord = record
	},
}

NOTE: It is recommended not to make assertions inside the mocked methods, you should only check the captured values afterwards as all tests should have 3 stages: (1) setup, (2) run and finally (3) assert.

For cases where the function will be called several times you might want to capture the number of calls as well as the values passed each time for that use closures and a slice of values, e.g.: 

var insertRecords []interface{}
dbMock := Mock{
	InsertFn: func(ctx context.Context, table Table, record interface{}) error {
		insertRecords = append(insertRecords, record)
	},
}

expectedNumberOfCalls := 2
assert.Equal(t, expectedNumberOfCalls, len(insertRecords))

expectedInsertedRecords := []interface{}{
	user1,
	user2,
}
assert.Equal(t, expectedInsertedRecords, insertRecords)

func (Mock) Delete 

func (m Mock) Delete(ctx context.Context, table Table, idOrRecord interface{}) error

Delete mocks the behavior of the Delete method. If DeleteFn is set it will just call it returning the same return values. If DeleteFn is unset it will panic with an appropriate error message.

func (Mock) Exec 

func (m Mock) Exec(ctx context.Context, query string, params ...interface{}) (Result, error)

Exec mocks the behavior of the Exec method. If ExecFn is set it will just call it returning the same return values. If ExecFn is unset it will panic with an appropriate error message.

func (Mock) Insert 

func (m Mock) Insert(ctx context.Context, table Table, record interface{}) error

Insert mocks the behavior of the Insert method. If InsertFn is set it will just call it returning the same return values. If InsertFn is unset it will panic with an appropriate error message.

func (Mock) Patch added in v1.4.2 

func (m Mock) Patch(ctx context.Context, table Table, record interface{}) error

Patch mocks the behavior of the Patch method. If PatchFn is set it will just call it returning the same return values. If PatchFn is unset it will panic with an appropriate error message.

func (Mock) Query 

func (m Mock) Query(ctx context.Context, records interface{}, query string, params ...interface{}) error

Query mocks the behavior of the Query method. If QueryFn is set it will just call it returning the same return values. If QueryFn is unset it will panic with an appropriate error message.

func (Mock) QueryChunks 

func (m Mock) QueryChunks(ctx context.Context, parser ChunkParser) error

QueryChunks mocks the behavior of the QueryChunks method. If QueryChunksFn is set it will just call it returning the same return values. If QueryChunksFn is unset it will panic with an appropriate error message.

func (Mock) QueryOne 

func (m Mock) QueryOne(ctx context.Context, record interface{}, query string, params ...interface{}) error

QueryOne mocks the behavior of the QueryOne method. If QueryOneFn is set it will just call it returning the same return values. If QueryOneFn is unset it will panic with an appropriate error message.

func (Mock) SetFallbackDatabase 

func (m Mock) SetFallbackDatabase(db Provider) Mock

SetFallbackDatabase will set all the Fn attributes to use the function from the input database.

SetFallbackDatabase is useful when you only want to overwrite some of the operations, e.g. for testing errors or if you want to use the same setup for making unit tests and integration tests, this way instead of creating a new server with a real database and another with a mocked one you can start the server once and run both types of tests.

Example Usage: 

db, err := ksql.New(...)
if err != nil {
	t.Fatal(err.Error())
}

mockdb := ksql.Mock{
	PatchFn: func(_ context.Context, _ ksql.Table, record interface{}) error {
		return ksql.ErrRecordNotFound
	},
}.SetFallbackDatabase(db)

// Passing the address to the service so
// you can change it for each test
myService := myservice.New(..., &mockdb, ...)

func (Mock) Transaction 

func (m Mock) Transaction(ctx context.Context, fn func(db Provider) error) error

Transaction mocks the behavior of the Transaction method. If TransactionFn is set it will just call it returning the same return values. If TransactionFn is unset it will just call the input function passing the Mock itself as the database.

type MockResult added in v1.4.2 

type MockResult struct {
	LastInsertIdFn func() (int64, error)
	RowsAffectedFn func() (int64, error)
}

MockResult implements the Result interface returned by the Exec function

Use the constructor `NewMockResult(42, 42)` for a simpler instantiation of this mock.

But if you want one of the functions to return an error you'll need to specify the desired behavior by overwriting one of the attributes of the struct.

func (MockResult) LastInsertId added in v1.4.2 

func (m MockResult) LastInsertId() (int64, error)

LastInsertId implements the Result interface

func (MockResult) RowsAffected added in v1.4.2 

func (m MockResult) RowsAffected() (int64, error)

RowsAffected implements the Result interface

type Provider 

type Provider interface {
	Insert(ctx context.Context, table Table, record interface{}) error
	Patch(ctx context.Context, table Table, record interface{}) error
	Delete(ctx context.Context, table Table, idOrRecord interface{}) error

	Query(ctx context.Context, records interface{}, query string, params ...interface{}) error
	QueryOne(ctx context.Context, record interface{}, query string, params ...interface{}) error
	QueryChunks(ctx context.Context, parser ChunkParser) error

	Exec(ctx context.Context, query string, params ...interface{}) (Result, error)
	Transaction(ctx context.Context, fn func(Provider) error) error
}

Provider describes the ksql public behavior.

The Insert, Update, Delete and QueryOne functions return ksql.ErrRecordNotFound if no record was found or no rows were changed during the operation.

type Result 

type Result interface {
	LastInsertId() (int64, error)
	RowsAffected() (int64, error)
}

Result stores information about the result of an Exec query

func NewMockResult added in v1.4.2 

func NewMockResult(lastInsertID int64, rowsAffected int64) Result

NewMockResult returns a simple implementation of the Result interface.

type Rows 

type Rows interface {
	Scan(...interface{}) error
	Close() error
	Next() bool
	Err() error
	Columns() ([]string, error)
}

Rows represents the results from a call to Query()

type ScanArgError added in v1.4.10 

type ScanArgError struct {
	ColumnIndex int
	Err         error
}

ScanArgError is a type of error that is expected to be returned from the Scan() method of the Rows interface.

It should be returned when there is an error scanning one of the input values.

This is necessary in order to allow KSQL to produce a better and more readable error message when this type of error occur.

func (ScanArgError) Error added in v1.4.10 

func (s ScanArgError) Error() string

Error implements the error interface.

func (ScanArgError) ErrorWithStructNames added in v1.4.10 

func (s ScanArgError) ErrorWithStructNames(structName string, colName string) error

type Table 

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

Table describes the required information for inserting, updating and deleting entities from the database by ID using the 3 helper functions created for that purpose.

func NewTable 

func NewTable(tableName string, ids ...string) Table

NewTable returns a Table instance that stores the tablename and the names of columns used as ID, if no column name is passed it defaults to using the `"id"` column.

This Table is required only for using the helper methods:

- Insert - Patch - Delete

Passing multiple ID columns will be interpreted as a single composite key, if you want to use the helper functions with different keys you'll need to create multiple Table instances for the same database table, each with a different set of ID columns, but this is usually not necessary.

type Tx 

type Tx interface {
	DBAdapter

	Rollback(ctx context.Context) error
	Commit(ctx context.Context) error
}

Tx represents a transaction and is expected to be returned by the DBAdapter.BeginTx function

type TxBeginner 

type TxBeginner interface {
	BeginTx(ctx context.Context) (Tx, error)
}

TxBeginner needs to be implemented by the DBAdapter in order to make it possible to use the `ksql.Transaction()` function.

Source Files

View all Source files

Directories

Path Synopsis
adapters
kmysql Module
kpgx Module
kpgx5 Module
kpostgres Module
ksqlite3 Module
ksqlserver Module
modernc-ksqlite Module
benchmarks module
internal
kbuilder
modifiers
structs
testtools
This package exposes only the public types and functions for creating new modifiers for KSQL.
 This package exposes only the public types and functions for creating new modifiers for KSQL.
Package kstructs is deprecated: use ksqltest instead.
 Package kstructs is deprecated: use ksqltest instead.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.