Go library for accessing multi-host SQL database installations

Overview

hasql

PkgGoDev GoDoc tests lint Go Report Card codecov

hasql provides simple and reliable way to access high-availability database setups with multiple hosts.

Status

hasql is production-ready and is actively used inside Yandex' production environment.

Prerequisites

Installation

With Go module support, simply add the following import

import "golang.yandex/hasql"

to your code, and then go [build|run|test] will automatically fetch the necessary dependencies.

Otherwise, to install the hasql package, run the following command:

$ go get -u golang.yandex/hasql

How does it work

hasql operates using standard database/sql connection pool objects. User creates *sql.DB objects for each node of database cluster and passes them to constructor. Library keeps up to date information on state of each node by 'pinging' them periodically. User is provided with a set of interfaces to retrieve *sql.DB object suitable for required operation.

dbFoo, _ := sql.Open("pgx", "host=foo")
dbBar, _ := sql.Open("pgx", "host=bar")
cl, err := hasql.NewCluster(
    []hasql.Node{hasql.NewNode("foo", dbFoo), hasql.NewNode("bar", dbBar) },
    checkers.PostgreSQL,
)
if err != nil { ... }

node := cl.Primary()
if node == nil { ... }

// Do anything you like
fmt.Println("Node address", node.Addr)

ctx, cancel := context.WithTimeout(context.Background(), time.Second)
defer cancel()
if err = node.DB().PingContext(ctx); err != nil { ... }

hasql does not configure provided connection pools in any way. It is user's job to set them up properly. Library does handle their lifetime though - pools are closed when Cluster object is closed.

Supported criteria

Alive primary|Alive standby|Any alive node, or none otherwise

node := c.Primary()
if node == nil { ... }

Alive primary|Alive standby, or any alive node, or none otherwise

node := c.PreferPrimary()
if node == nil { ... }

Ways of accessing nodes

Any of currently alive nodes satisfying criteria, or none otherwise

node := c.Primary()
if node == nil { ... }

Any of currently alive nodes satisfying criteria, or wait for one to become alive

ctx, cancel := context.WithTimeout(context.Background(), time.Second)
defer cancel()
node, err := c.WaitForPrimary(ctx)
if err == nil { ... }

Node pickers

When user asks Cluster object for a node a random one from a list of suitable nodes is returned. User can override this behavior by providing a custom node picker.

Library provides a couple of predefined pickers. For example if user wants 'closest' node (with lowest latency) PickNodeClosest picker should be used.

cl, err := hasql.NewCluster(
    []hasql.Node{hasql.NewNode("foo", dbFoo), hasql.NewNode("bar", dbBar) },
    checkers.PostgreSQL,
    hasql.WithNodePicker(hasql.PickNodeClosest()),
)
if err != nil { ... }

Supported databases

Since library works over standard database/sql it supports any database that has a database/sql driver. All it requires is a database-specific checker function that can tell if node is primary or standby.

Check out golang.yandex/hasql/checkers package for more information.

Caveats

Node's state is transient at any given time. If Primary() returns a node it does not mean that node is still primary when you execute statement on it. All it means is that it was primary when it was last checked. Nodes can change their state at a whim or even go offline and hasql can't control it in any manner.

This is one of the reasons why nodes do not expose their perceived state to user.

Extensions

Instrumentation

You can add instrumentation via Tracer object similar to httptrace in standard library.

sqlx

hasql can operate over database/sql pools wrapped with sqlx. It works the same as with standard library but requires user to import golang.yandex/hasql/sqlx instead.

Refer to golang.yandex/hasql/sqlx package for more information.

Comments
  • Support for checking node latency:

    Support for checking node latency:

    • all alive nodes are stored in order of their latency (from lowest to greatest)
    • added PickNodeClosest - always returns 'closest' node (with least latency)
    opened by sidh 1
  • Support broken database cluster on startup

    Support broken database cluster on startup

    Error pops up while running newly added test with -race flag

    $ go test -v -race -run=^TestCluster_BrokenAtStartup$ ./...
    You use x86_64 version of selected tool.
    === RUN   TestCluster_BrokenAtStartup
    === RUN   TestCluster_BrokenAtStartup/broken_primary
    ==================
    WARNING: DATA RACE
    Read at 0x00c0001901d8 by goroutine 21:
      github.com/DATA-DOG/go-sqlmock.(*rowSets).Next()
          /Users/gzuykov/go/pkg/mod/github.com/!d!a!t!a-!d!o!g/go-[email protected]/rows.go:45 +0xa8
      database/sql.(*Rows).nextLocked()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:2967 +0x1d0
      database/sql.(*Rows).Next.func1()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:2945 +0x48
      database/sql.withLock()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:3396 +0x74
      database/sql.(*Rows).Next()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:2944 +0x7c
      database/sql.(*Row).Scan()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:3333 +0x108
      golang.yandex/hasql/checkers.Check()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/checkers/check.go:29 +0xd8
      golang.yandex/hasql/checkers.PostgreSQL()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/checkers/postgresql.go:26 +0x54
      golang.yandex/hasql.checkExecutor.func1()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/check_nodes.go:149 +0x80
      golang.yandex/hasql.checkNodes.func1()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/check_nodes.go:109 +0x88
    
    Previous write at 0x00c0001901d8 by goroutine 20:
      github.com/DATA-DOG/go-sqlmock.(*rowSets).Next()
          /Users/gzuykov/go/pkg/mod/github.com/!d!a!t!a-!d!o!g/[email protected]/rows.go:45 +0xc0
      database/sql.(*Rows).nextLocked()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:2967 +0x1d0
      database/sql.(*Rows).Next.func1()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:2945 +0x48
      database/sql.withLock()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:3396 +0x74
      database/sql.(*Rows).Next()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:2944 +0x7c
      database/sql.(*Row).Scan()
          /Users/gzuykov/.ya/tools/v4/1217047391/src/database/sql/sql.go:3333 +0x108
      golang.yandex/hasql/checkers.Check()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/checkers/check.go:29 +0xd8
      golang.yandex/hasql/checkers.PostgreSQL()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/checkers/postgresql.go:26 +0x54
      golang.yandex/hasql.checkExecutor.func1()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/check_nodes.go:149 +0x80
      golang.yandex/hasql.checkNodes.func1()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/check_nodes.go:109 +0x88
    
    Goroutine 21 (running) created at:
      golang.yandex/hasql.checkNodes()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/check_nodes.go:106 +0x450
      golang.yandex/hasql.(*Cluster).updateNodes()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/cluster.go:321 +0x1b4
      golang.yandex/hasql.(*Cluster).backgroundNodesUpdate()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/cluster.go:297 +0x30
    
    diff --git a/cluster_test.go b/cluster_test.go
    index 10c22eb..c816f2f 100644
    --- a/cluster_test.go
    +++ b/cluster_test.go
    @@ -29,6 +29,8 @@ import (
            "github.com/gofrs/uuid"
            "github.com/stretchr/testify/assert"
            "github.com/stretchr/testify/require"
    +
    +       "golang.yandex/hasql/checkers"
     )
    
     func TestNewCluster(t *testing.T) {
    @@ -543,6 +545,89 @@ func TestCluster_WaitForStandbyPreferred(t *testing.T) {
            }
     }
    
    +// TestCluster_BrokenAtStartup checks if broken at startup cluster behaves properly
    +func TestCluster_BrokenAtStartup(t *testing.T) {
    +       isPrimaryRow := sqlmock.NewRows([]string{`NOT pg_is_in_recovery()`}).AddRow(true)
    +       isStandbyRow := sqlmock.NewRows([]string{`NOT pg_is_in_recovery()`}).AddRow(false)
    +
    +       testCases := []struct {
    +               name  string
    +               nodes []Node
    +       }{
    +               {
    +                       name: "broken_primary",
    +                       nodes: func() []Node {
    +                               db1, mock1, _ := sqlmock.New() // primary
    +                               db2, mock2, _ := sqlmock.New() // standby
    +                               db3, mock3, _ := sqlmock.New() // standby
    +
    +                               mock1.ExpectQuery(`SELECT NOT pg_is_in_recovery()`).WillDelayFor(20 * time.Millisecond)
    +                               mock2.ExpectQuery(`SELECT NOT pg_is_in_recovery()`).WillReturnRows(isStandbyRow)
    +                               mock3.ExpectQuery(`SELECT NOT pg_is_in_recovery()`).WillReturnRows(isStandbyRow)
    +
    +                               return []Node{
    +                                       NewNode("primary", db1),
    +                                       NewNode("standby1", db2),
    +                                       NewNode("standby2", db3),
    +                               }
    +                       }(),
    +               },
    +               {
    Goroutine 20 (finished) created at:
      golang.yandex/hasql.checkNodes()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/check_nodes.go:106 +0x450
      golang.yandex/hasql.(*Cluster).updateNodes()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/cluster.go:321 +0x1b4
      golang.yandex/hasql.(*Cluster).backgroundNodesUpdate()
          /Users/gzuykov/dev/github.com/yandex/go-hasql/cluster.go:297 +0x30
    ==================
        testing.go:1152: race detected during execution of test
    === RUN   TestCluster_BrokenAtStartup/broken_standby
    === RUN   TestCluster_BrokenAtStartup/broken_standbys
        cluster_test.go:624:
            	Error Trace:	cluster_test.go:624
            	Error:      	Received unexpected error:
            	            	context deadline exceeded
            	Test:       	TestCluster_BrokenAtStartup/broken_standbys
        cluster_test.go:625:
            	Error Trace:	cluster_test.go:625
            	Error:      	Expected value not to be nil.
            	Test:       	TestCluster_BrokenAtStartup/broken_standbys
        cluster_test.go:626:
            	Error Trace:	cluster_test.go:626
            	Error:      	Should NOT be empty, but was []
            	Test:       	TestCluster_BrokenAtStartup/broken_standbys
    === CONT  TestCluster_BrokenAtStartup
        testing.go:1152: race detected during execution of test
    --- FAIL: TestCluster_BrokenAtStartup (0.01s)
        --- FAIL: TestCluster_BrokenAtStartup/broken_primary (0.00s)
        --- PASS: TestCluster_BrokenAtStartup/broken_standby (0.00s)
        --- FAIL: TestCluster_BrokenAtStartup/broken_standbys (0.01s)
    === CONT
        testing.go:1152: race detected during execution of test
    FAIL
    FAIL	golang.yandex/hasql	0.286s
    ?   	golang.yandex/hasql/checkers	[no test files]
    testing: warning: no tests to run
    PASS
    ok  	golang.yandex/hasql/sqlx	(cached) [no tests to run]
    FAIL
    
    opened by bbrodriges 1
  • Allow creating a new Cluster with alive and down nodes

    Allow creating a new Cluster with alive and down nodes

    Scenario:

    • I call hasql.NewCluster with three nodes. One node is unreachable ("context deadline exceeded").
    • All further calls to Cluster.WaitFor* return an error.
    • Calls to Cluster.Alive, Cluster.Primary* and Cluster.Standby* return nil.

    Expected: Being able to create a cluster which includes unreachable nodes and still be able to query the alive nodes.

    opened by nfo 0
  • [feature] Support replication lag checking

    [feature] Support replication lag checking

    This commit adds ability to filter replica nodes by replication lag. User can provide ReplicationLagChecker function and MaxreplicationLag value as options to cluster constructor to check nodes and remove any replica which is too slow.

    Example:

    // Construct cluster nodes
    nodes := []hasql.Node{...}
    
    // Use options to fine-tune cluster behavior
    opts := []hasql.ClusterOption{
    	hasql.WithUpdateInterval(2 * time.Second),        // set custom update interval
    	hasql.WithNodePicker(hasql.PickNodeRoundRobin()), // set desired nodes selection algorithm
    	hasql.WithReplicationLagChecker(checkers.PostgreSQLReplicationLag), // set appropriate replication lag checker
    	hasql.WithMaxReplicationLag(10 * time.Second), // set desired maximum lag value
    }
    
    // Create cluster handler
    c, err := hasql.NewCluster(nodes, checkers.PostgreSQL, opts...)
    if err != nil {
    	panic(err)
    }
    defer func() { _ = c.Close() }() // close cluster when it is not needed
    
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    
    // Wait for any alive standby
    node, err = c.WaitForStandby(ctx)
    if err != nil {
    	panic(err)
    }
    
    ...
    
    opened by bbrodriges 6
  • Return error with description when node of requested type is unavailable

    Return error with description when node of requested type is unavailable

    Functions like Primary return node and nothing else. If nil is returned it is very hard to understand why exactly it happened - you have to parse logs (if you have those), etc.

    We need another set of functions returning both node and error describing why exactly requested node is unavailable.

    When we have enough API changes to warrant v2 we should think about removing old versions of these functions leaving only one variant.

    opened by sidh 0
Releases(v1.0.0)
  • v1.0.0(Nov 21, 2020)

  • v0.9.1(Sep 16, 2020)

  • v0.9.0(Sep 14, 2020)

    Initial release of hasql library.

    Code is production-ready and was used inside Yandex for a couple of years. Marking as v0.9 only because we want to leave room for possible API adjustments in case of community feedback.

    Source code(tar.gz)
    Source code(zip)
Owner
Yandex
Yandex open source projects and technologies
Yandex
Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Sqlvet Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time. Feature highlights:

QP Hou 455 Sep 22, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 54 Sep 26, 2022
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Ozzo Framework 578 Sep 29, 2022
LBADD: An experimental, distributed SQL database

LBADD Let's build a distributed database. LBADD is an experimental distributed SQL database, written in Go. The goal of this project is to build a dat

Tom Arrell 378 Aug 10, 2022
Mocking your SQL database in Go tests has never been easier.

copyist Mocking your SQL database in Go tests has never been easier. The copyist library automatically records low-level SQL calls made during your te

CockroachDB 817 Sep 21, 2022
Additions to Go's database/sql for super fast performance and convenience. (fork of gocraft/dbr)

dbr (fork of gocraft/dbr) provides additions to Go's database/sql for super fast performance and convenience. Getting Started // create a connection (

Free and open source software developed at Mail.Ru 159 Sep 8, 2022
Document-oriented, embedded SQL database

Genji Document-oriented, embedded, SQL database Table of contents Table of contents Introduction Features Installation Usage Using Genji's API Using d

Genji 1k Sep 27, 2022
A Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Gustavo Chaín 650 Sep 26, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.4k Sep 26, 2022
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.7k Sep 22, 2022
A Go library for collecting sql.DBStats in Prometheus format

sqlstats A Go library for collecting sql.DBStats and exporting them in Prometheus format. A sql.DB object represents a pool of zero or more underlying

Daniel Middlecote 151 Sep 27, 2022
Go database query builder library for PostgreSQL

buildsqlx Go Database query builder library Installation Selects, Ordering, Limit & Offset GroupBy / Having Where, AndWhere, OrWhere clauses WhereIn /

Arthur Kushman 78 Sep 27, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 26 Sep 26, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 795 Sep 26, 2022
Write your SQL queries in raw files with all benefits of modern IDEs, use them in an easy way inside your application with all the profit of compile time constants

About qry is a general purpose library for storing your raw database queries in .sql files with all benefits of modern IDEs, instead of strings and co

Sergey Treinis 23 Sep 26, 2022
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 172 Oct 3, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 85 Sep 26, 2022
💥 A lightweight DSL & ORM which helps you to write SQL in Go.

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Qishuai Liu 266 Sep 26, 2022
Fluent SQL generation for golang

sqrl - fat-free version of squirrel - fluent SQL generator for Go Non thread safe fork of squirrel. The same handy fluffy helper, but with extra lette

Ivan Kirichenko 242 Jul 26, 2022