Go library for accessing multi-host SQL database installations

Related tags

go-hasql
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.

Issues
  • 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
  • Fix small typo

    Fix small typo

    opened by ravener 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
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 2.9k Oct 15, 2021
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 126 Oct 11, 2021
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 147 Sep 24, 2021
SQL builder and query library for golang

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

Doug Martin 1.3k Oct 22, 2021
igor is an abstraction layer for PostgreSQL with a gorm like syntax.

igor igor is an abstraction layer for PostgreSQL, written in Go. Igor syntax is (almost) compatible with GORM. When to use igor You should use igor wh

Paolo Galeone 84 Aug 2, 2021
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 774 Oct 13, 2021
Go library for accessing multi-host SQL database installations

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

Yandex 84 Sep 7, 2021
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 750 Oct 12, 2021
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 231 Sep 26, 2021
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 27 Oct 13, 2021
Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. 🔍 📊

askgit askgit is a command-line tool for running SQL queries on git repositories. It's meant for ad-hoc querying of git repositories on disk through a

Augmentable 2.6k Oct 20, 2021
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 148 Sep 22, 2021
Fluent SQL generation for golang

Squirrel is "complete". Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork

null 4.3k Oct 24, 2021
Golang Sequel ORM that support Enum, JSON, Spatial and many more

sqlike A golang SQL ORM which anti toxic query and focus on latest features. Installation go get github.com/si3nloong/sqlike Fully compatible with nat

SianLoong 15 Oct 18, 2021
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 19 Sep 30, 2021
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 128 Aug 8, 2021
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 601 Oct 15, 2021
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 428 Sep 17, 2021
gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

Sang-gon Lee 40 Oct 9, 2021