Collects small inserts and sends big requests to ClickHouse servers

# ClickHouse-Bulk

[![build](https://github.com/nikepan/clickhouse-bulk/actions/workflows/test.yml/badge.svg)](https://github.com/nikepan/clickhouse-bulk/actions/workflows/test.yml)
[![Codacy Badge](https://app.codacy.com/project/badge/Coverage/e25b965fe6094daa88cfb23968adefa4)](https://app.codacy.com/gh/nikepan/clickhouse-bulk/dashboard?utm_source=gh&utm_medium=referral&utm_content=&utm_campaign=Badge_coverage)
[![download binaries](https://img.shields.io/badge/binaries-download-blue.svg)](https://github.com/nikepan/clickhouse-bulk/releases)
[![Go Report Card](https://goreportcard.com/badge/github.com/nikepan/clickhouse-bulk)](https://goreportcard.com/report/github.com/nikepan/clickhouse-bulk)
[![godoc](http://img.shields.io/badge/godoc-reference-blue.svg?style=flat)](https://godoc.org/github.com/nikepan/clickhouse-bulk)

Simple [Yandex ClickHouse](https://clickhouse.yandex/) insert collector. It collect requests and send to ClickHouse servers.


### Installation

[Download binary](https://github.com/nikepan/clickhouse-bulk/releases) for you platorm

or

[Use docker image](https://hub.docker.com/r/nikepan/clickhouse-bulk/)


or from sources (Go 1.23+):

```text
git clone https://github.com/nikepan/clickhouse-bulk
cd clickhouse-bulk
go build
```


### Features
- Group n requests and send to any of ClickHouse server
- Sending collected data by interval
- Tested with VALUES, TabSeparated formats
- Supports many servers to send
- Supports query in query parameters and in body
- Supports other query parameters like username, password, database
- Supports basic authentication


For example:
```sql
INSERT INTO table3 (c1, c2, c3) VALUES ('v1', 'v2', 'v3')
INSERT INTO table3 (c1, c2, c3) VALUES ('v4', 'v5', 'v6')
```
sends as
```sql
INSERT INTO table3 (c1, c2, c3) VALUES ('v1', 'v2', 'v3')('v4', 'v5', 'v6')
```


### Options
- -config - config file (json); default _config.json_


### Configuration file
```json lines
{
  "listen": ":8124",
  "flush_count": 10000, // check by \n char
  "flush_interval": 1000, // milliseconds
  "clean_interval": 0, // how often cleanup internal tables - e.g. inserts to different temporary tables, or as workaround for query_id etc. milliseconds
  "remove_query_id": true, // some drivers sends query_id which prevents inserts to be batched
  "dump_check_interval": 300, // interval for try to send dumps (seconds); -1 to disable
  "debug": false, // log incoming requests
  "dump_dir": "dumps", // directory for dump unsended data (if clickhouse errors)
  "clickhouse": {
    "down_timeout": 60, // wait if server in down (seconds)
    "connect_timeout": 10, // wait for server connect (seconds)
    "tls_server_name": "", // override TLS serverName for certificate verification (e.g. in cases you share same "cluster" certificate across multiple nodes)
    "insecure_tls_skip_verify": false, // INSECURE - skip certificate verification at all
    "servers": [
      "http://127.0.0.1:8123"
    ]
  },
  "metrics_prefix": "prefix"
}
```

### Environment variables (used for docker image)

* `CLICKHOUSE_BULK_DEBUG` - enable debug logging
* `CLICKHOUSE_SERVERS` - comma separated list of servers
* `CLICKHOUSE_FLUSH_COUNT` - count of rows for insert
* `CLICKHOUSE_FLUSH_INTERVAL` - insert interval
* `CLICKHOUSE_CLEAN_INTERVAL` - internal tables clean interval
* `DUMP_CHECK_INTERVAL` - interval of resend dumps
* `CLICKHOUSE_DOWN_TIMEOUT` - wait time if server is down
* `CLICKHOUSE_CONNECT_TIMEOUT` - clickhouse server connect timeout
* `CLICKHOUSE_TLS_SERVER_NAME` - server name for TLS certificate verification
* `CLICKHOUSE_INSECURE_TLS_SKIP_VERIFY` - skip certificate verification at all
* `METRICS_PREFIX` - prefix for prometheus metrics

### Quickstart

`./clickhouse-bulk`
and send queries to :8124

### Metrics
manual check main metrics
`curl -s http://127.0.0.1:8124/metrics | grep "^ch_"`
* `ch_bad_servers 0` - actual count of bad servers
* `ch_dump_count 0` - dumps saved from launch
* `ch_queued_dumps 0` - actual dump files id directory
* `ch_good_servers 1` - actual good servers count
* `ch_received_count 40` - received requests count from launch
* `ch_sent_count 1` - sent request count from launch


### Tips

For better performance words FORMAT and VALUES must be uppercase.


clickhouse-bulk

Database benchmarking tool with support for several databases and scripts

# dbbench

![Action](https://github.com/sj14/dbbench/workflows/Go/badge.svg)
[![Go Report Card](https://goreportcard.com/badge/github.com/sj14/dbbench)](https://goreportcard.com/report/github.com/sj14/dbbench)
[![Coverage Status](https://coveralls.io/repos/github/sj14/dbbench/badge.svg?branch=master)](https://coveralls.io/github/sj14/dbbench?branch=master)

## Table of Contents

- [Description](#description)
- [Example](#example)
- [Installation](#installation)
- [Supported Databases](#supported-Databases-/-Driver)
- [Usage](#usage)
- [Custom Scripts](#custom-scripts)
- [Troubeshooting](#troubleshooting)
- [Development](#development)
- [Acknowledgements](#acknowledgements)

## Description

`dbbench` is a simple tool to benchmark or stress test databases. You can use the simple built-in benchmarks or run your own queries.  

**Attention**: This tool comes with no warranty. Don't run it on production databases.

## Example

``` text
$ dbbench postgres --user postgres --pass example --iter 100000
inserts 6.199670776s    61996   ns/op
updates 7.74049898s     77404   ns/op
selects 2.911541197s    29115   ns/op
deletes 5.999572479s    59995   ns/op
total: 22.85141994s
```

## Installation

### Precompiled Binaries

Binaries are available for all major platforms. See the [releases](https://github.com/sj14/dbbench/releases) page.

### Homebrew

Using the [Homebrew](https://brew.sh/) package manager for macOS:

```console
brew install sj14/tap/dbbench
```

### Manually

It's also possible to install the current development snapshot with `go install` (not recommended):

```console
go install github.com/sj14/dbbench/cmd/dbbench
```

### Docker

```console
docker run ghcr.io/sj14/dbbench:latest
```

Check [here](https://github.com/sj14/dbbench/pkgs/container/dbbench) for available images.

## Supported Databases / Driver

Databases | Driver
----------|-----------
Cassandra and compatible databases (e.g. ScyllaDB) | github.com/gocql/gocql
MS SQL and compatible databases (no built-in benchmarks yet) | github.com/denisenkom/go-mssqldb
MySQL and compatible databases (e.g. MariaDB and TiDB) | github.com/go-sql-driver/mysql
PostgreSQL and compatible databases (e.g. CockroachDB) | github.com/lib/pq
SQLite3 and compatible databases | modernc.org/sqlite

## Usage

``` text
Available subcommands:
        cassandra|cockroach|mssql|mysql|postgres|sqlite
        Use 'subcommand --help' for all flags of the specified command.
Generic flags for all subcommands:
      --clean            only cleanup benchmark data, e.g. after a crash
      --iter int         how many iterations should be run (default 1000)
      --noclean          keep benchmark data
      --noinit           do not initialize database and tables, e.g. when only running own script
      --run string       only run the specified benchmarks, e.g. "inserts deletes" (default "all")
      --script string    custom sql file to execute
      --sleep duration   how long to pause after each single benchmark (valid units: ns, us, ms, s, m, h)
      --threads int      max. number of green threads (iter >= threads > 0) (default 25)
      --version          print version information
```

## Custom Scripts

You can run your own SQL statements with the `--script` flag. You can use the auto-generate tables. Beware the file size as it will be completely loaded into memory.

The script must contain valid SQL statements for your database.

There are some built-in variables and functions which can be used in the script. It's using the golang [template engine](https://golang.org/pkg/text/template/) which uses the delimiters `{{` and `}}`. Functions are executed with the `call` command and arguments are passed after the function name.

### Benchmark Settings

A new benchmark is created with the `\benchmark` keyword, followed by either `once` or `loop`. Optional parameters can be added afterwards in the same line.

The the usage description and the example subsection for more information.

Usage                     | Description                                   |
--------------------------|-----------------------------------------------|
`\benchmark once`                | Execute the following statements (lines) only once (e.g. to create and delete tables).
`\benchmark loop`                | Default mode. Execute the following statements (lines) in a loop. Executes them one after another and then starts a new iteration. Add another `\benchmark loop` to start another benchmark of statements.
`\name insert`              | Set a custom name for the DB statement(s), which will be output instead the line numbers (`insert` is an examplay name).

### Statement Substitutions

Usage                     | Description                                   |
--------------------------|-----------------------------------------------|
`{{.Iter}}`                 | The iteration counter. Will return `1` when `\benchmark once`.
`{{call .RandInt64}}`       | [godoc](https://pkg.go.dev/math/rand/v2#Int64)
`{{call .RandInt64N 9999}}` | [godoc](https://pkg.go.dev/math/rand/v2#Int64N) (`9999` is an examplary upper limit)
`{{call .RandUint64}}`       | [godoc](https://pkg.go.dev/math/rand/v2#Uint64)
`{{call .RandUint64N 9999}}` | [godoc](https://pkg.go.dev/math/rand/v2#Uint64N) (`9999` is an examplary upper limit)
`{{call .RandFloat32}}`     | [godoc](https://pkg.go.dev/math/rand/v2#Float32)  
`{{call .RandFloat64}}`     | [godoc](https://pkg.go.dev/math/rand/v2#Float64)
`{{call .RandExpFloat64}}`  | [godoc](https://pkg.go.dev/math/rand/v2#ExpFloat64)
`{{call .RandNormFloat64}}` | [godoc](https://pkg.go.dev/math/rand/v2#NormFloat64)

### Example

Exemplary `sqlite_bench.sql` file:

``` sql
-- Create table
\benchmark once \name init
CREATE TABLE dbbench_simple (id INT PRIMARY KEY, balance DECIMAL);

-- How long takes an insert and delete?
\benchmark loop \name single
INSERT INTO dbbench_simple (id, balance) VALUES({{.Iter}}, {{call .RandInt64}});
DELETE FROM dbbench_simple WHERE id = {{.Iter}}; 

-- How long takes it in a single transaction?
\benchmark loop \name batch
BEGIN TRANSACTION;
INSERT INTO dbbench_simple (id, balance) VALUES({{.Iter}}, {{call .RandInt64}});
DELETE FROM dbbench_simple WHERE id = {{.Iter}}; 
COMMIT;

-- Delete table
\benchmark once \name clean
DROP TABLE dbbench_simple;
```

In this script, we create and delete the table manually, thus we will pass the `--noinit` and `--noclean` flag, which would otherwise create this default table for us:

``` text
dbbench sqlite --script scripts/sqlite_bench.sql --iter 5000 --noinit --noclean
```

output:

``` text
(once) init:    3.404784ms      3404784 ns/op
(loop) single:  10.568390874s   2113678 ns/op
(loop) batch:   5.739021596s    1147804 ns/op
(once) clean:   1.065703ms      1065703 ns/op
total: 16.312319959s
```

## Troubleshooting

**Error message**

``` text
failed to insert: UNIQUE constraint failed: dbbench_simple.id
```

**Description**
The previous data wasn't removed (e.g. because the benchmark was canceled). Try to run the same command again, but with the `--clean` flag attached, which will remove the old data. Then run the original command again.

## Development

Below are some examples how to run different databases and the equivalent call of `dbbench` for testing/developing.

### Cassandra

``` text
docker run --name dbbench-cassandra -p 9042:9042 -d cassandra:latest
```

``` text
dbbench cassandra
```

### CockroachDB

``` text
# port 8080 is the webinterface (optional)
docker run --name dbbench-cockroach -d -p 26257:26257 -p 8080:8080 cockroachdb/cockroach:latest start --insecure
```

``` text
dbbench cockroach
```

### Microsoft SQL Server

``` text
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d microsoft/mssql-server-linux
```

``` text
dbbench mssql -user sa -pass 'yourStrong(!)Password'
```

### MariaDB

``` text
docker run --name dbbench-mariadb -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=root mariadb
```

``` text
dbbench mariadb
```

### MySQL

``` text
docker run --name dbbench-mysql -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=root mysql
```

``` text
dbbench mysql
```

### PostgreSQL

``` text
docker run --name dbbench-postgres -p 5432:5432 -d postgres
```

``` text
dbbench postgres --user postgres --pass example
```

### ScyllaDB

``` text
docker run --name dbbench-scylla -p 9042:9042 -d scylladb/scylla
```

``` text
dbbench scylla
```

### SQLite

``` text
dbbench sqlite
```

### TiDB

``` text
git clone https://github.com/pingcap/tidb-docker-compose.git
cd tidb-docker-compose && docker-compose pull
docker-compose up -d
```

``` text
dbbench tidb --pass '' --port 4000
```

## Acknowledgements

Thanks to the authors of Go and those of the directly and indirectly used libraries, especially the driver developers. It wouldn't be possible without all your work.

This tool was highly inspired by the snippet from user [Fale](https://github.com/cockroachdb/cockroach/issues/23061#issue-300012178) and the tool [pgbench](https://www.postgresql.org/docs/current/pgbench.html). Later, also inspired by [MemSQL's dbbench](https://github.com/memsql/dbbench) which had the name and a similar idea before.


dbbench

A fast data generator that produces CSV files from generated relational data

Cloud-native database gateway and framework for building data-driven applications. Like API gateways, for databases

<p align="center">
    <a href="https://gatewayd.io/">
        <picture>
            <source media="(prefers-color-scheme: dark)" srcset="https://github.com/gatewayd-io/gatewayd/blob/main/assets/gatewayd-logotype-dark.png">
            <img alt="GatewayD logotype" src="https://github.com/gatewayd-io/gatewayd/blob/main/assets/gatewayd-logotype-light.png">
        </picture>
    </a>
    <h3 align="center">Like API gateways, for databases</h3>
    <p align="center">Cloud-native database gateway and framework for building data-driven applications.</p>
</p>

<p align="center">
    <a href="https://github.com/gatewayd-io/gatewayd/releases"><img src="https://img.shields.io/github/v/release/gatewayd-io/gatewayd" alt="Downloads" /></a>
    <a href="https://github.com/gatewayd-io/gatewayd/actions/workflows/release.yaml"><img src="https://img.shields.io/github/actions/workflow/status/gatewayd-io/gatewayd/release.yaml" alt="Release Worflow Status" /></a>
    <a href="https://goreportcard.com/report/github.com/gatewayd-io/gatewayd"><img src="https://goreportcard.com/badge/github.com/gatewayd-io/gatewayd" alt="Go Report Card" /></a>
    <a href="https://docs.gatewayd.io/"><img src="https://img.shields.io/badge/read-docs-brightgreen" alt="Documentation"></a>
    <a href="https://pkg.go.dev/github.com/gatewayd-io/gatewayd"><img src="https://pkg.go.dev/badge/github.com/gatewayd-io/gatewayd.svg" alt="Go Reference"></a>
    <a href="https://coveralls.io/github/gatewayd-io/gatewayd?branch=main"><img src="https://coveralls.io/repos/github/gatewayd-io/gatewayd/badge.svg?branch=main" alt="Coverage Status" /></a>
    <a href="https://awesome-go.com/#database-tools"><img src="https://awesome.re/badge.svg" alt="Mentioned in Awesome Go" /></a>
</p>

**GatewayD** is a free and open-source cloud-native database gateway and framework for building data-driven applications. It is a middleware that sits between your database servers and clients and proxies all their communication. It is like API gateways in system design, but instead is used for proxying database traffic, rather than HTTP traffic.

GatewayD is an [L4](https://en.wikipedia.org/wiki/Transport_layer) proxy for SQL, and eventually NoSQL, databases and clients. The core is database-protocol-agnotic, and the plugins encode, decode and add value to the database traffic flow, hence it can technically support all databases. You can either write your own plugins or make use of our built-in, community and enterprise plugins.

Using GatewayD, you can see through the queries and the data passing between your database server and clients, and take action. For example, you can cache the result of SQL SELECT queries or detect and prevent SQL injection attacks.

GatewayD is developed by [GatewayD Labs](https://gatewayd.io/) and the community.

## Features

- **Cloud-native**:

    Built with cloud-native principles in mind: containerized, stateless,  observable and extensible, while being secure and reliable.

- **Connection pooling and proxying**:

    Pools connections to database servers and clients and proxies them together.

- **Database-agnostic**:

    GatewayD proxies connections, while plugins enable database support.

- **Plugin-based & extensible**:

    Plugins extend functionality. You can write your own plugins or use our built-in, community and enterprise plugins.

- **Multi-tenancy**:

    Manage multiple databases and clients within a single GatewayD instance.

- **Full observability**:

    Integrated logging, metrics, and tracing for comprehensive monitoring and observability.

## Plugins & SDK

The plugins are the lifeblood of GatewayD. They are loaded on startup to add tons of functionality, for example:

- **Query parsing and processing**
- **Authentication, authorization and access management**
- **Caching**
- **Injection detection and prevention**
- **Firewalling**
- **Query, schema and data management and transformation**
- **Change data capture**
- **Distributed query processing**
- Many other possibilities

The plugins are *usually* written in Go and are compiled into stand-alone executables. They are loaded on startup and are ready to be used by the users. In turn, the developers can write their own plugins and use them in their applications, which is made possible by using the [SDK](https://github.com/gatewayd-io/gatewayd-plugin-sdk).

## Documentation

The docs cover all aspects of using GatewayD. Some highlights include:

- [Getting Started](https://docs.gatewayd.io): install, run an instance of GatewayD and test it in action.
- [Configuration](https://docs.gatewayd.io/using-gatewayd/configuration): learn how you can configure GatewayD to behave as you like.
  - [Global Configuration](https://docs.gatewayd.io/using-gatewayd/configuration#global-configuration): control GatewayD's behavior.
  - [Plugins Configuration](https://docs.gatewayd.io/using-gatewayd/configuration#plugins-configuration): control GatewayD's plugin registry and the plugins' behavior.
- [Observability](https://docs.gatewayd.io/using-gatewayd/observability): logs, metrics, and traces are GatewayD's first-class citizens.
- [Using Plugins](https://docs.gatewayd.io/using-plugins/plugins): learn how to use plugins.
- [Developing Plugins](https://docs.gatewayd.io/developing-plugins/plugin-developers-guide): learn how to develop your own plugins.
- [gatewayd-plugin-cache](https://docs.gatewayd.io/plugins/gatewayd-plugin-cache): learn how to use the `gatewayd-plugin-cache` plugin for caching queries and their results.
- [gatewayd-plugin-js](https://docs.gatewayd.io/plugins/gatewayd-plugin-js): learn how to use the experimental `gatewayd-plugin-js` plugin for executing JavaScript code in GatewayD.
- [gatewayd-plugin-sql-ids-ips](https://docs.gatewayd.io/plugins/gatewayd-plugin-sql-ids-ips): learn how to use the `gatewayd-plugin-sql-ids-ips` plugin for detecting and preventing SQL injection attacks by using the [DeepSQLi](https://github.com/gatewayd-io/DeepSQLi) deep learning model.
- [gatewayd-plugin-auth](https://docs.gatewayd.io/plugins/gatewayd-plugin-auth): learn how to use the `gatewayd-plugin-auth` plugin for authentication, authorization and access management.

## Contributing

We welcome contributions from everyone. Just open an [issue](https://github.com/gatewayd-io/gatewayd/issues) or send us a [pull request](https://github.com/gatewayd-io/gatewayd/pulls).

## License

GatewayD is licensed under the [Affero General Public License v3.0](https://github.com/gatewayd-io/gatewayd/blob/main/LICENSE).

gatewayd

Go toolset to handle MySQL protocol and replication

<img src="go-mysql-logo.png" width="150px">

# go-mysql

A pure Go library to handle MySQL network protocol and replication as used by MySQL and MariaDB.

![semver](https://img.shields.io/github/v/tag/go-mysql-org/go-mysql)
![example workflow](https://github.com/go-mysql-org/go-mysql/actions/workflows/ci.yml/badge.svg)
![gomod version](https://img.shields.io/github/go-mod/go-version/go-mysql-org/go-mysql/master)
[![Go Reference](https://pkg.go.dev/badge/github.com/go-mysql-org/go-mysql.svg)](https://pkg.go.dev/github.com/go-mysql-org/go-mysql)

## Platform Support

As a pure Go library, this project follows [Go's minimum requirements](https://go.dev/wiki/MinimumRequirements).

This library has been tested or deployed on the following operating systems and architectures:

| Operating System | Architecture | Runtime Supported | CI | Notes                                                                                                                          |
|------------------|--------------|-------------------|----|--------------------------------------------------------------------------------------------------------------------------------|
| Linux            | amd64        | ✅                | ✅ | Check GitHub Actions of this project.                                                                                          |
| Linux            | s390x        | ✅                | ✅ | A daily CI runs on an s390x VM, supported by the [IBM Z and LinuxONE Community](https://www.ibm.com/community/z/open-source/). |
| Linux            | arm64        | ✅                | ✅ | Deployed in a production environment of a user.                                                                                |
| Linux            | arm          | ✅                | ❌ | A test in CI to make sure builds for 32-bits platforms work.                                                                   |
| FreeBSD          | amd64        | ✅                | ❌ | Sporadically tested by developers.                                                                                             |

Other platforms supported by Go may also work, but they have not been verified. Feel free to report your test results.

This library is not compatible with [TinyGo](https://tinygo.org/).

## Changelog

This library uses [Changelog](CHANGELOG.md).

---
# Content
* [Replication](#replication) - Process events from a binlog stream.
* [Incremental dumping](#canal) - Sync from MySQL to Redis, Elasticsearch, etc.
* [Client](#client) - Simple MySQL client.
* [Fake server](#server) - server side of the MySQL protocol, as library.
* [database/sql like driver](#driver) - An alternative `database/sql` driver for MySQL.
* [Logging](#logging) - Custom logging options.
* [Migration](#how-to-migrate-to-this-repo) - Information for how to migrate if you used the old location of this project.

## Examples

The `cmd` directory contains example applications that can be build by running `make build` in the root of the project. The resulting binaries will be places in `bin/`.

- `go-binlogparser`: parses a binlog file at a given offset
- `go-canal`: streams binlog events from a server to canal
- `go-mysqlbinlog`: streams binlog events
- `go-mysqldump`: like `mysqldump`, but in Go
- `go-mysqlserver`: fake MySQL server

## Replication

Replication package handles MySQL replication protocol like [python-mysql-replication](https://github.com/noplay/python-mysql-replication).

You can use it as a MySQL replica to sync binlog from master then do something, like updating cache, etc...

### Example

```go
import (
	"github.com/go-mysql-org/go-mysql/replication"
	"os"
)
// Create a binlog syncer with a unique server id, the server id must be different from other MySQL's.
// flavor is mysql or mariadb
cfg := replication.BinlogSyncerConfig {
	ServerID: 100,
	Flavor:   "mysql",
	Host:     "127.0.0.1",
	Port:     3306,
	User:     "root",
	Password: "",
}
syncer := replication.NewBinlogSyncer(cfg)

// Start sync with specified binlog file and position
streamer, _ := syncer.StartSync(mysql.Position{binlogFile, binlogPos})

// or you can start a gtid replication like
// gtidSet, _ := mysql.ParseGTIDSet(mysql.MySQLFlavor, "de278ad0-2106-11e4-9f8e-6edd0ca20947:1-2")
// streamer, _ := syncer.StartSyncGTID(gtidSet)
// the mysql GTID set is like this "de278ad0-2106-11e4-9f8e-6edd0ca20947:1-2" and uses mysql.MySQLFlavor
// the mariadb GTID set is like this "0-1-100" and uses mysql.MariaDBFlavor

for {
	ev, _ := streamer.GetEvent(context.Background())
	// Dump event
	ev.Dump(os.Stdout)
}

// or we can use a timeout context
for {
	ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
	ev, err := streamer.GetEvent(ctx)
	cancel()

	if err == context.DeadlineExceeded {
		// meet timeout
		continue
	}

	ev.Dump(os.Stdout)
}
```

The output looks:

```
=== RotateEvent ===
Date: 1970-01-01 08:00:00
Log position: 0
Event size: 43
Position: 4
Next log name: mysql.000002

=== FormatDescriptionEvent ===
Date: 2014-12-18 16:36:09
Log position: 120
Event size: 116
Version: 4
Server version: 5.6.19-log
Create date: 2014-12-18 16:36:09

=== QueryEvent ===
Date: 2014-12-18 16:38:24
Log position: 259
Event size: 139
Salve proxy ID: 1
Execution time: 0
Error code: 0
Schema: test
Query: DROP TABLE IF EXISTS `test_replication` /* generated by server */
```

### MariaDB 11.4+ compatibility

MariaDB 11.4+ introduced an optimization where events written through transaction or statement cache have `LogPos=0` so they can be copied directly to the binlog without computing the real end position. This optimization improves performance but makes position tracking unreliable for replication clients that need to track LogPos of events inside transactions.

To address this, a `FillZeroLogPos` configuration option is available:

```go
cfg := replication.BinlogSyncerConfig {
	ServerID: 100,
	Flavor:   "mariadb",
	Host:     "127.0.0.1",
	Port:     3306,
	User:     "root",
	Password: "",
	// Enable dynamic LogPos calculation for MariaDB 11.4+
	FillZeroLogPos: true,
}
```

**Behavior:**
- When `FillZeroLogPos` is `true` and flavor is `mariadb`, the library automatically:
  - Adds `BINLOG_SEND_ANNOTATE_ROWS_EVENT` flag to binlog dump commands. This ensures correct position tracking by making the server send `ANNOTATE_ROWS_EVENT` events which are needed for accurate position calculation.
  - Calculates LogPos dynamically for events with `LogPos=0` that are not artificial.
- Only works with MariaDB flavor; has no effect with MySQL.
- Should be set to `true` if tracking of LogPos inside transactions is required.

## Canal

Canal is a package that can sync your MySQL into everywhere, like Redis, Elasticsearch.

First, canal will dump your MySQL data then sync changed data using binlog incrementally.

You must use ROW format for binlog, full binlog row image is preferred, because we may meet some errors when primary key changed in update for minimal or noblob row image.

A simple example:

```go
package main

import (
	"github.com/go-mysql-org/go-mysql/canal"
)

type MyEventHandler struct {
	canal.DummyEventHandler
}

func (h *MyEventHandler) OnRow(e *canal.RowsEvent) error {
	log.Infof("%s %v\n", e.Action, e.Rows)
	return nil
}

func (h *MyEventHandler) String() string {
	return "MyEventHandler"
}

func main() {
	cfg := canal.NewDefaultConfig()
	cfg.Addr = "127.0.0.1:3306"
	cfg.User = "root"
	// We only care table canal_test in test db
	cfg.Dump.TableDB = "test"
	cfg.Dump.Tables = []string{"canal_test"}

	c, err := canal.NewCanal(cfg)
	if err != nil {
		log.Fatal(err)
	}

	// Register a handler to handle RowsEvent
	c.SetEventHandler(&MyEventHandler{})

	// Start canal
	c.Run()
}
```

You can see [go-mysql-elasticsearch](https://github.com/go-mysql-org/go-mysql-elasticsearch) for how to sync MySQL data into Elasticsearch.

## Client

Client package supports a simple MySQL connection driver which you can use it to communicate with MySQL server.

For an example see [`example_client_test.go`](client/example_client_test.go). You can run this testable example with
`go test -v ./client -run Example`.

Tested MySQL versions for the client include:
- 5.5.x
- 5.6.x
- 5.7.x
- 8.0.x

### Example for SELECT streaming (v1.1.1)
You can use also streaming for large SELECT responses.
The callback function will be called for every result row without storing the whole resultset in memory.
`result.Fields` will be filled before the first callback call.

```go
// ...
var result mysql.Result
err := conn.ExecuteSelectStreaming(`select id, name from table LIMIT 100500`, &result, func(row []mysql.FieldValue) error {
    for idx, val := range row {
    	field := result.Fields[idx]
    	// You must not save FieldValue.AsString() value after this callback is done.
    	// Copy it if you need.
    	// ...
    }
    return nil
}, nil)

// ...
```

### Example for connection pool (v1.3.0)

```go
import (
    "github.com/go-mysql-org/go-mysql/client"
)

pool := client.NewPool(log.Debugf, 100, 400, 5, "127.0.0.1:3306", `root`, ``, `test`)
// ...
conn, _ := pool.GetConn(ctx)
defer pool.PutConn(conn)

conn.Execute() / conn.Begin() / etc...
```

## Server

Server package supplies a framework to implement a simple MySQL server which can handle the packets from the MySQL client.
You can use it to build your own MySQL proxy. The server connection is compatible with MySQL 5.5, 5.6, 5.7, and 8.0 versions,
so that most MySQL clients should be able to connect to the Server without modifications.

### Example

Minimalistic MySQL server implementation:

```go
package main

import (
	"log"
	"net"

	"github.com/go-mysql-org/go-mysql/server"
)

func main() {
	// Listen for connections on localhost port 4000
	l, err := net.Listen("tcp", "127.0.0.1:4000")
	if err != nil {
		log.Fatal(err)
	}

	// Accept a new connection once
	c, err := l.Accept()
	if err != nil {
		log.Fatal(err)
	}

	// Create a connection with user root and an empty password.
	// You can use your own handler to handle command here.
	conn, err := server.NewConn(c, "root", "", server.EmptyHandler{})
	if err != nil {
		log.Fatal(err)
	}

	// as long as the client keeps sending commands, keep handling them
	for {
		if err := conn.HandleCommand(); err != nil {
			log.Fatal(err)
		}
	}
}
```

Another shell

```
$ mysql -h127.0.0.1 -P4000 -uroot
Your MySQL connection id is 10001
Server version: 5.7.0

MySQL [(none)]>
// Since EmptyHandler implements no commands, it will throw an error on any query that you will send
```

> ```NewConn()``` will use default server configurations:
> 1. automatically generate default server certificates and enable TLS/SSL support.
> 2. support three mainstream authentication methods **'mysql_native_password'**, **'caching_sha2_password'**, and **'sha256_password'**
>    and use **'mysql_native_password'** as default.
> 3. use an in-memory user credential provider to store user and password.
>
> To customize server configurations, use ```NewServer()``` and create connection via ```NewCustomizedConn()```.

## Driver

Driver is the package that you can use go-mysql with go database/sql like other drivers. A simple example:

```go
package main

import (
	"database/sql"

	_ "github.com/go-mysql-org/go-mysql/driver"
)

func main() {
	// dsn format: "user:password@addr?dbname"
	dsn := "root@127.0.0.1:3306?test"
	db, _ := sql.Open("mysql", dsn)
	db.Close()
}
```

### Structured Connector

If you prefer a structured configuration over a DSN string, you can use
`database/sql.OpenDB` with `driver.Connector`:

```go
package main

import (
	"database/sql"
	"net/url"

	"github.com/go-mysql-org/go-mysql/driver"
)

func main() {
	connector := driver.Connector{
		Addr: "127.0.0.1:3306",
		User: "root",
		DB:   "test",
		Params: url.Values{
			// same option keys as the standard DSN form
			"timeout": []string{"10s"},
		},
	}

	db := sql.OpenDB(connector)
	db.Close()
}
```

### Driver Options

Configuration options can be provided by the standard DSN (Data Source Name).

```
[user[:password]@]addr[/db[?param=X]]
```

#### `collation`

Set a collation during the Auth handshake.

| Type      | Default         | Example                                               |
| --------- | --------------- | ----------------------------------------------------- |
| string    | utf8_general_ci | user:pass@localhost/mydb?collation=latin1_general_ci  |

#### `compress`

Enable compression between the client and the server. Valid values are 'zstd','zlib','uncompressed'.

| Type      | Default       | Example                                 |
| --------- | ------------- | --------------------------------------- |
| string    | uncompressed  | user:pass@localhost/mydb?compress=zlib  |

#### `readTimeout`

I/O read timeout. The time unit is specified in the argument value using
golang's [ParseDuration](https://pkg.go.dev/time#ParseDuration) format.

0 means no timeout.

| Type      | Default   | Example                                     |
| --------- | --------- | ------------------------------------------- |
| duration  | 0         | user:pass@localhost/mydb?readTimeout=10s    |

#### `ssl` or `tls`

Enable TLS between client and server. Valid values are `true`,`false`,`skip-verify` or `custom`. When using `custom`,
the connection will use the TLS configuration set by `SetCustomTLSConfig` matching the host.

| Type      | Default   | Example                                     |
| --------- | --------- | ------------------------------------------- |
| string    |           | user:pass@localhost/mydb?ssl=true           |

#### `timeout`

Timeout is the maximum amount of time a dial will wait for a connect to complete.
The time unit is specified in the argument value using golang's [ParseDuration](https://pkg.go.dev/time#ParseDuration) format.

0 means no timeout.

| Type      | Default   | Example                                     |
| --------- | --------- | ------------------------------------------- |
| duration  | 0         | user:pass@localhost/mydb?timeout=1m         |

#### `writeTimeout`

I/O write timeout. The time unit is specified in the argument value using
golang's [ParseDuration](https://pkg.go.dev/time#ParseDuration) format.

0 means no timeout.

| Type      | Default   | Example                                         |
| --------- | --------- | ----------------------------------------------- |
| duration  | 0         | user:pass@localhost/mydb?writeTimeout=1m30s     |

#### `retries`

Allows disabling the golang `database/sql` default behavior to retry errors
when `ErrBadConn` is returned by the driver. When retries are disabled
this driver will not return `ErrBadConn` from the `database/sql` package.

Valid values are `on` (default) and `off`.

| Type      | Default   | Example                                         |
| --------- | --------- | ----------------------------------------------- |
| string    | on        | user:pass@localhost/mydb?retries=off            |

### Custom Driver Options

The driver package exposes the function `SetDSNOptions`, allowing for modification of the
connection by adding custom driver options.
It requires a full import of the driver (not by side-effects only).

Example of defining a custom option:

```golang
import (
 "database/sql"

 "github.com/go-mysql-org/go-mysql/driver"
)

func main() {
 driver.SetDSNOptions(map[string]DriverOption{
  "no_metadata": func(c *client.Conn, value string) error {
   c.SetCapability(mysql.CLIENT_OPTIONAL_RESULTSET_METADATA)
   return nil
  },
 })

 // dsn format: "user:password@addr/dbname?"
 dsn := "root@127.0.0.1:3306/test?no_metadata=true"
 db, _ := sql.Open(dsn)
 db.Close()
}
```

### Custom Driver Name

A custom driver name can be set via build options: `-ldflags '-X "github.com/go-mysql-org/go-mysql/driver.driverName=gomysql"'`.

This can be useful when using [GORM](https://gorm.io/docs/connecting_to_the_database.html#Customize-Driver):

```go
import (
  _ "github.com/go-mysql-org/go-mysql/driver"
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
)

db, err := gorm.Open(mysql.New(mysql.Config{
  DriverName: "gomysql",
  DSN: "gorm:gorm@127.0.0.1:3306/test",
}))
```

### Custom NamedValueChecker

Golang allows for custom handling of query arguments before they are passed to the driver
with the implementation of a [NamedValueChecker](https://pkg.go.dev/database/sql/driver#NamedValueChecker). By doing a full import of the driver (not by side-effects only),
a custom NamedValueChecker can be implemented.

```golang
import (
 "database/sql"

 "github.com/go-mysql-org/go-mysql/driver"
)

func main() {
 driver.AddNamedValueChecker(func(nv *sqlDriver.NamedValue) error {
  rv := reflect.ValueOf(nv.Value)
  if rv.Kind() != reflect.Uint64 {
   // fallback to the default value converter when the value is not a uint64
   return sqlDriver.ErrSkip
  }

  return nil
 })

 conn, err := sql.Open("mysql", "root@127.0.0.1:3306/test")
 defer conn.Close()

 stmt, err := conn.Prepare("select * from table where id = ?")
 defer stmt.Close()
 var val uint64 = math.MaxUint64
 // without the NamedValueChecker this query would fail
 result, err := stmt.Query(val)
}
```


We pass all tests in https://github.com/bradfitz/go-sql-test using go-mysql driver. :-)

## Logging

Logging uses [log/slog](https://pkg.go.dev/log/slog) and by default is sent to standard out.

For the old logging package `github.com/siddontang/go-log/log`, a converting package
`https://github.com/serprex/slog-siddontang` is available.
## How to migrate to this repo
To change the used package in your repo it's enough to add this `replace` directive to your `go.mod`:
```
replace github.com/siddontang/go-mysql => github.com/go-mysql-org/go-mysql v1.13.0
```

This can be done by running this command:
```
go mod edit -replace=github.com/siddontang/go-mysql=github.com/go-mysql-org/go-mysql@v1.13.0
```

v1.13.0 - is the last tag in repo, feel free to choose what you want.

## Credits

go-mysql was started by @siddontang and has many [contributors](https://github.com/go-mysql-org/go-mysql/graphs/contributors)


go-mysql

Multi-tenancy support for GORM managed databases

# gorm-multitenancy

[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)
[![Go Reference](https://pkg.go.dev/badge/github.com/bartventer/gorm-multitenancy.svg)](https://pkg.go.dev/github.com/bartventer/gorm-multitenancy/v8)
[![Release](https://img.shields.io/github/release/bartventer/gorm-multitenancy.svg)](https://github.com/bartventer/gorm-multitenancy/releases/latest)
[![Go Report Card](https://goreportcard.com/badge/github.com/bartventer/gorm-multitenancy/v8)](https://goreportcard.com/report/github.com/bartventer/gorm-multitenancy/v8)
[![codecov](https://codecov.io/gh/bartventer/gorm-multitenancy/graph/badge.svg?token=6i0Pr1GFek)](https://codecov.io/gh/bartventer/gorm-multitenancy)
[![Tests](https://github.com/bartventer/gorm-multitenancy/actions/workflows/default.yml/badge.svg)](https://github.com/bartventer/gorm-multitenancy/actions/workflows/default.yml)
![GitHub issues](https://img.shields.io/github/issues/bartventer/gorm-multitenancy)
[![License](https://img.shields.io/github/license/bartventer/gorm-multitenancy.svg)](LICENSE)

<p align="center">
  <img src="https://i.imgur.com/bOZB8St.png" title="GORM Multitenancy" alt="GORM Multitenancy">
</p>
<p align="center">
  <sub><small>Photo by <a href="https://github.com/ashleymcnamara">Ashley McNamara</a>, via <a href="https://github.com/ashleymcnamara/gophers">ashleymcnamara/gophers</a> (CC BY-NC-SA 4.0)</small></sub>
</p>

## Overview

Gorm-multitenancy provides a Go framework for building multi-tenant applications, streamlining
tenant management and model migrations. It abstracts multitenancy complexities through a unified,
database-agnostic API compatible with GORM.

## Multitenancy Approaches

There are three common approaches to multitenancy in a database:

- Shared database, shared schema
- Shared database, separate schemas
- Separate databases

Depending on the database in use, this package utilizes either the _"shared database, separate schemas"_ or _"separate databases"_ strategy, ensuring a smooth integration with your existing database configuration through the provision of tailored drivers.

## Features

- **GORM Integration**: Simplifies [GORM](https://gorm.io/) usage in multi-tenant environments, offering a unified API alongside direct access to driver-specific APIs for flexibility.
- **Custom Database Drivers**: Enhances existing drivers for easy multitenancy setup without altering initialization.
- **HTTP Middleware**: Provides middleware for easy tenant context management in web applications.

## Supported Databases

| Database | Approach |
|----------|----------|
| PostgreSQL | Shared database, separate schemas |
| MySQL | Separate databases |

## Router Integration

- Echo - [Guide](https://pkg.go.dev/github.com/bartventer/gorm-multitenancy/middleware/echo/v8)
- Gin - [Guide](https://pkg.go.dev/github.com/bartventer/gorm-multitenancy/middleware/gin/v8)
- Iris - [Guide](https://pkg.go.dev/github.com/bartventer/gorm-multitenancy/middleware/iris/v8)
- Net/HTTP - [Guide](https://pkg.go.dev/github.com/bartventer/gorm-multitenancy/middleware/nethttp/v8)

## Installation

Install the core package:

```bash
go get -u github.com/bartventer/gorm-multitenancy/v8
```

Install the database-specific driver:

```bash
# PostgreSQL
go get -u github.com/bartventer/gorm-multitenancy/postgres/v8

# MySQL
go get -u github.com/bartventer/gorm-multitenancy/mysql/v8
```

Optionally, install the router-specific middleware:

```bash
# Echo
go get -u github.com/bartventer/gorm-multitenancy/middleware/echo/v8

# Gin
go get -u github.com/bartventer/gorm-multitenancy/middleware/gin/v8

# Iris
go get -u github.com/bartventer/gorm-multitenancy/middleware/iris/v8

# Net/HTTP
go get -u github.com/bartventer/gorm-multitenancy/middleware/nethttp/v8
```

## Getting Started

Check out the [pkg.go.dev](https://pkg.go.dev/github.com/bartventer/gorm-multitenancy/v8) documentation for comprehensive guides and API references.

### Running the Example Application

For a practical demonstration, you can run [the example application](./_examples/README.md). It showcases various configurations and usage scenarios.

## Contributing

All contributions are welcome! See the [Contributing Guide](CONTRIBUTING.md) for more details.

## License

This project is licensed under the Apache License 2.0 - see the [LICENSE](LICENSE) file for details.

gorm-multitenancy

Go package for sharding databases ( Supports every ORM or raw SQL )

# Octillery [![GoDoc](https://godoc.org/go.knocknote.io/octillery?status.svg)](https://godoc.org/go.knocknote.io/octillery) [![CircleCI](https://circleci.com/gh/blastrain/octillery.svg?style=shield)](https://circleci.com/gh/blastrain/octillery)  [![codecov](https://codecov.io/gh/blastrain/octillery/branch/master/graph/badge.svg?token=hRKqugQMsg)](https://codecov.io/gh/blastrain/octillery) [![Go Report Card](https://goreportcard.com/badge/go.knocknote.io/octillery)](https://goreportcard.com/report/go.knocknote.io/octillery)


<img width="300px" height="238px" src="https://user-images.githubusercontent.com/209884/29391665-d1d6e1d0-8333-11e7-9a33-1db3dc9d2f72.png"></img>

`Octillery` is a Go package for sharding databases.
It can use with every OR Mapping library ( `xorm` , `gorp` , `gorm` , `dbr` ...) implementing `database/sql` interface, or raw SQL.

Currently supports `MySQL` (for product) and `SQLite3` (for testing) .

# Motivation

We need database sharding library in Go. Of course, we know some libraries like ( https://github.com/evalphobia/wizard , https://github.com/go-pg/sharding ). But OR Mapping library they support are restricted and we want to write sharding configuration declaratively, also expect to pluggable for sharding algorithm or database adapter, and expect to configurable sharding key or whether use sequencer or not.

# Features

- Supports every OR Mapping library implementing `database/sql` interface ( `xorm` , `gorp` , `gorm` , `dbr` , ... )
- Supports using `database/sql` ( raw SQL ) directly
- Pluggable sharding algorithm ( preinstalled algorithms are `modulo` and `hashmap` )
- Pluggable database adapter ( preinstalled adapters are `mysql` and `sqlite3` )
- Declarative describing for sharding configuration in `YAML`
- Configurable sharding algorithm, database adapter, sharding key, whether use sequencer or not.
- Supports capture read/write queries just before passing to database driver
- Supports database migration by CLI ( powered by `schemalex` )
- Supports import seeds from CSV

# Install

## Install as a CLI tool

```shell
go get go.knocknote.io/octillery/cmd/octillery
```

## Install as a library

```shell
go get go.knocknote.io/octillery
```

# How It Works

## 1. How database sharding works

We explain by using `posts` table.

`posts` table schema is

```sql
CREATE TABLE `posts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_posts_01` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

And we want to shard this table to four databases for load distribution.

In this case, we can try to two approach according to requirements.

### 1. Using Sequencer

If you want `id` to be unique in all databases, you should use this approach.  
Architecture of this approach would be the following.

![architecture](https://user-images.githubusercontent.com/209884/48552381-c8612a80-e91b-11e8-8625-af0043b2536c.png)

Application create SQL ( like `insert into posts (id, user_id, ...) values (null, 1, ...)` ), in this point, id value is null because still not decide. In accordance with the above graph, insert this query to one of the databases.

1. Application requests id value to sequencer
2. Sequencer generates next unique id in all shards
3. Sequencer returns id value to application ( ex. `id = 1` )
4. Replace `id` value from `null` to `1` (ex. `insert into posts (id, user_id, ...) values (1, 1, ...)` )
5. Decide target based of `id` value by sharding algorithm ( default `modulo` ) and insert record to selected database.

By using sequencer approach, you can get **unique** `id` value in all databases.
Therefore, if you insert multiple records, database records should looks like the following.

<table >
<tr>
  <th colspan=2>posts_shard_1</th>
  <th colspan=2>posts_shard_2</th>
  <th colspan=2>posts_shard_3</th>
  <th colspan=2>posts_shard_4</th>
</tr>
<tr align="center">
  <td>id</td>
  <td>user_id</td>
  <td>id</td>
  <td>user_id</td>
  <td>id</td>
  <td>user_id</td>
  <td>id</td>
  <td>user_id</td>
</tr>
<tr align="center">
  <td>1</td>
  <td>1</td>
  <td>2</td>
  <td>2</td>
  <td>3</td>
  <td>3</td>
  <td>4</td>
  <td>4</td>
</tr>
<tr align="center">
  <td>5</td>
  <td>5</td>
  <td>6</td>
  <td>6</td>
  <td>7</td>
  <td>7</td>
  <td>8</td>
  <td>8</td>
</tr>
</table>

### 2. Using Sharding Key ( without Sequencer )
If you don't care about uniqueness of `id`, you can use sharding key approach.  
Architecture of this appraoch would be the following.

![architecture2](https://user-images.githubusercontent.com/209884/48595164-e455e200-e996-11e8-8207-f9a432812cc6.png)

1. Decide target based of `user_id` value by sharding algorithm ( default `modulo` ) and insert record to selected database.

By using sharding key approach, same id value will appear in multiple databases.
Therefore, if you insert multiple records, database record should looks like the following.

<table >
<tr>
  <th colspan=2>posts_shard_1</th>
  <th colspan=2>posts_shard_2</th>
  <th colspan=2>posts_shard_3</th>
  <th colspan=2>posts_shard_4</th>
</tr>
<tr align="center">
  <td>id</td>
  <td>user_id</td>
  <td>id</td>
  <td>user_id</td>
  <td>id</td>
  <td>user_id</td>
  <td>id</td>
  <td>user_id</td>
</tr>
<tr align="center">
  <td>1</td>
  <td>1</td>
  <td>1</td>
  <td>2</td>
  <td>1</td>
  <td>3</td>
  <td>1</td>
  <td>4</td>
</tr>
<tr align="center">
  <td>2</td>
  <td>5</td>
  <td>2</td>
  <td>6</td>
  <td>2</td>
  <td>7</td>
  <td>2</td>
  <td>8</td>
</tr>
</table>

## 2. Requirements of database sharding library

We explained how to sharding database at section 1.
From this we define requirements of database sharding library.

- Know about database sharding configuration
- Capture query just before passing to database driver
- Parse query and find sharding key
- If use sequencer, requests `id` value to sequencer and replace value of `id` column by it
- Select sharding target based of sharding key by sharding algorithm

## 3. How Octillery works

### How To Capture Query

`Octillery` CLI tool supports `transpose` command.
It replace import statement of `database/sql` to `go.knocknote.io/octillery/database/sql`.

`go.knocknote.io/octillery/database/sql` package has compatible interface of `database/sql`.

Therefore, OR Mapping library call `Octillery`'s interface. and it can capture all queries.

### How To Parse SQL

`Octillery` use [github.com/blastrain/vitess-sqlparser](https://github.com/blastrain/vitess-sqlparser) as SQL parser. It implements powered by `vitess` and `tidb` .

### How To Use New Database Adapter

`Octillery` supports `mysql` and `sqlite3` adapter by default.  
If you want to use new database adapter, need to the following two steps.

1. Write `DBAdapter` interface. ( see https://godoc.org/go.knocknote.io/octillery/connection/adapter )
2. Put new adapter file to `go.knocknote.io/octillery/plugin` directory

### How To Use New Database Sharding Algorithm

`Octillery` supports `modulo` and `hashmap` algorithm by default.  
If you want to use new algorithm, need to the following two steps.

1. Write `ShardingAlgorithm` interface. ( see https://godoc.org/go.knocknote.io/octillery/algorithm )
2. Put new algorithm file to `go.knocknote.io/octillery/algorithm` directory

# Usage

## 1. Install CLI tool

```shell
$ go get go.knocknote.io/octillery/cmd/octillery
```

## 2. Install library

```shell
$ go get go.knocknote.io/octillery
```


## 3. Replace already imported `database/sql` statement

```shell
$ octillery transpose
```
※ `--dry-run` option confirms without overwriting

## 4. Install database adapter

```shell
$ octillery install --mysql
```

## 5. Describe database cofiguration in YAML

`databases.yml`

```yaml
default: &default
  adapter: mysql
  encoding: utf8mb4
  username: root
  master:
    - localhost:3306

tables:
  posts:
    shard: true
    shard_key: user_id
    shards:
      - post_shard_1:
          <<: *default
          database: posts_shard_1
      - post_shard_2:
          <<: *default
          database: posts_shard_2
```

## 6. Migrate database

```shell
$ octillery migrate --config databases.yml /path/to/schema
```

※ `--dry-run` option confirms migration plan

## 7. Load configuration file

```go
package main

import (
	"go.knocknote.io/octillery"
	"go.knocknote.io/octillery/database/sql"
)

func main() {
	if err := octillery.LoadConfig("databases.yml"); err != nil {
		panic(err)
	}
	db, _ := sql.Open("mysql", "")
	db.QueryRow("...")
}
```

# Document

See [GoDoc](https://godoc.org/go.knocknote.io/octillery)

# Development

## Install dependencies

```shell
$ make deps
```

If update dependencies, the following

1. Modify `glide.yaml`
2. Run `make update-deps`
3. Commit `glide.yaml` and `glide.lock`

## Run tests

```shell
$ make test
```

# See also

- `sqlparser` : https://github.com/blastrain/vitess-sqlparser
- `schemalex` : https://github.com/schemalex/schemalex

# Committers

Masaaki Goshima ([@goccy](https://github.com/goccy))

# LICENSE

MIT


octillery

Database backup from different drivers to different destinations with one command and configuration

<p align="center">
<img src="docs/logo.png" alt="Onedump" title="Onedump" />
</p>

[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)
[![GoDoc](https://godoc.org/github.com/liweiyi88/onedump?status.svg)](https://godoc.org/github.com/liweiyi88/onedump)
![tests](https://github.com/liweiyi88/onedump/actions/workflows/tests.yaml/badge.svg)
[![codecov](https://codecov.io/gh/liweiyi88/onedump/branch/main/graph/badge.svg?token=ROIDLHX41V)](https://codecov.io/gh/liweiyi88/onedump)
[![Go Report Card](https://goreportcard.com/badge/github.com/liweiyi88/onedump)](https://goreportcard.com/report/github.com/liweiyi88/onedump)
[![License](https://img.shields.io/badge/license-MIT-blue.svg)](https://github.com/liweiyi88/onedump/blob/main/LICENSE.md)

Onedump is a database administration tool that streamlines backup and restore tasks across multiple databases and storage destinations.

## Features
* Database backup from different sources to different destinations.
* MySQL dump with zero dependencies (with built-in mysql native dumper). 
* Supports dumpers with dependencies (`mysqldump` and `pg_dump`).
* MySQL binlog backup to AWS S3.
* MySQL restore from binlogs.
* MySQL slow log parser.
* Resumable and concurrent SFTP file transfers.
* Loads configuration from S3 bucket.
* Slack notification.
* Maintained docker image that contains all dependencies.

## Table of Contents

* [Installation](#installation)
  - [Binary](#binary)
  - [Docker image](#docker-image)
* [Prerequisites](#prerequisites)
* [Run Onedump](#run-onedump)
* [How it works](#how-it-works)
* [The native MySQL dumper](#the-native-mysql-dumper) 
* [The slow log parser](#the-slow-log-parser)
* [MySQL binlog backup to AWS S3](#mysql-binlog-backup-to-aws-s3)
* [MySQL binlog restore](#mysql-binlog-restore)
* [Resumable and concurrent SFTP file transfers](#resumable-and-concurrent-sftp-file-transfers)
* [Contribution](#contribution)

### Supported source databases
- MySQL
- PostgreSQL

### Supported storage destinations

- Local file system
- AWS S3
- Google Drive
- Dropbox
- SFTP


## Installation
`onedump` provides both binaries and Docker images via the release process.

### Binary
`onedump` binaries are available at https://github.com/liweiyi88/onedump/releases. Use the latest version of the binary that is suitable for your OS.
After downloading the binary and moving it to a folder that is in your `$PATH` environment variable (e.g., `/usr/local/bin/onedump`), give it executable permissions (e.g., `sudo chmod +x /usr/local/bin/onedump`). Then you should be able to run it:
```
$ onedump
```

### Docker image

Docker images are also available in [Docker Hub](https://hub.docker.com/r/julianli/onedump/tags).

#### When to use the Docker image
1. You want run `onedump` in Kubernetes, ECS, or any other container environment.

2. Currently, `onedump` does not provide a native PostgreSQL dumper out of the box. Therefore, for PostgreSQL, you need to have `pg_dump` installed on the same machine. The Docker image includes `pg_dump`, making it easier to run `onedump` without additional manual installation.

3. When the current native MySQL dumper doesn't fit your needs and you need `mysqldump`.

#### Use a specific pg_dump version
The docker image contains `mysql` client, `postgresql15-client` and `postgresql16-client`. By default it uses `postgresql16-client`. However, you can pass an environemnt `PG_VERSION` to swtich postgresql client version between `15` and `16`. For example, `docker run -e PG_VERSION=15 julianli/onedump:v1.5.0-arm64 -f config.yaml`.

> Although we maintain both `ARM64` and `AMD64` Docker images, usually what you need is the `AMD64` image on your production Linux machine. For example: `julianli/onedump:v1.5.0-amd64`*

## Prerequisites

`onedump` provides a native MySQL dumper, allowing you to use the `onedump` binary to dump your MySQL database contents without needing `mysqldump` be installed on your machine.

If you need more advanced MySQL dump features or for PostgreSQL dump, you may want to use `mysqldump` or `pg_dump` dumper, which you need to install the below dependencies on the machine.

MySQL: `mysql-client`  
PostgreSQL: `postgresql-client`

> If you use the Docker image we maintain, these tools are included by default. Alternatively, you can extend or build the Docker image yourself if you need further customization.

## Run onedump

`onedump` has just one simple command to load a config file and dump DB contents based on the configuration. It has two ways of loading the config file.

### Option 1. Load configuration file from local directory
After installing onedump, you should be able to run it as a simple cli command. For example:

```
$ onedump -f /path/to/config.yaml
```

The config.yaml contains all DB backup jobs in yaml format. For all configurable items. see [configuration](./docs/CONFIG_REF.md)

### Option 2. Load configuration from an S3 bucket
Moreover, instead of loading your config file from a local directory, you can also store it in an AWS S3 bucket. Run the cli command to load the config file from an S3 bucket
```
$ onedump -f backup-config/config.yaml --s3-bucket mybucket
```
In this case, you pass the `--s3-bucket` option to indicate onedump that it should load the configuration content from an s3 bucket called `mybucket`. Then  onedump will treat the file path option `backup-config/config.yaml` as the s3 key. By default, onedump will use any AWS environment variables to interact with S3, if environment variables are not found, then it will use the credentials of the default profile in your `~/.aws/credentials` file. To overwirte these default credentials, you can pass `--aws-key`, `--aws-region` and `--aws-secret` options.

### Configuration examples

For all configurable items and instructions. see [configuration](./docs/CONFIG_REF.md)

#### Dump a local DB to 2 local directories
```
jobs:
- name: local-dump
  dbdriver: mysql
  dbdsn: root@tcp(127.0.0.1)/test_local
  gzip: true
  storage:
    local:
      - path: /Users/jack/Desktop/mydb.sql
      - path: /Users/jack/Desktop/mydb2.sql
```

#### Dump a remote DB via SSH and save to a local directory and a S3 bucket
```
jobs:
- name: ssh-dump
  dbdriver: mysql
  dbdsn: user:password@tcp(127.0.0.1:3306)/mydb
  sshhost: mywebsite.com
  sshuser: root
  sshkey: |-
    -----BEGIN OPENSSH PRIVATE KEY-----
    b3BlbnNzaC1rZXktdjEAAAAABG5vbmUAAAAEbm9uZQAAAAAAAAABAAACFwAAAAdzc2gtcn...
    -----END OPENSSH PRIVATE KEY-----
  storage:
    local:
      - path: /Users/jack/Desktop/db.sql
    s3:
      - bucket: mys3bucket
        key: backup/mydb.sql
        region: ap-southeast-2
        access-key-id: awsaccesskey
        secret-access-key: awssecret
        session-token: <session-token> # optional, specify the value if you assume a role.
```

#### Multiple dumps to different storage
```
jobs:
- name: local-dump
  dbdriver: mysql
  dbdsn: root@tcp(127.0.0.1)/test_local
  storage:
    local:
      - path: /Users/jack/Desktop/mydb.sql
- name: ssh-dump
  dbdriver: mysql
  dbdsn: user:password@tcp(127.0.0.1:3306)/mydb
  sshhost: mywebsite.com
  sshuser: root
  sshkey: |-
    -----BEGIN OPENSSH PRIVATE KEY-----
    b3BlbnNzaC1rZXktdjEAAAAABG5vbmUAAAAEbm9uZQAAAAAAAAABAAACFwAAAAdzc2gtcn...
    -----END OPENSSH PRIVATE KEY-----
  storage:
    s3:
      - bucket: mys3bucket
        key: backup/mydb.sql
        region: ap-southeast-2
        access-key-id: awsaccesskey
        secret-access-key: awssecret
        session-token: <session-token> # optional, specify the value if you assume a role.
```
#### Controlling the maximum number of concurrent jobs

You can control how many jobs run concurrently by setting the `maxjobs` option (10 jobs by default) in your configuration file. For example:

```
maxjobs: 20
jobs:
- name: local-dump
  dbdriver: mysql
  ...
```

#### Slack notification

```
notifier:
    slack:
      - incomingwebhook: https://hooks.slack.com/services/A0B8A11N4N/...
jobs:
- name: local-dump
  dbdriver: mysql
  dbdsn: root@tcp(127.0.0.1)/test_local
  gzip: true
  storage:
    local:
      - path: /Users/jack/Desktop/mydb.sql
```

### Recommendation

Loading the configuration from a local directory is handy when you have control of a machine and want to run `onedump` as a normal cli command. However, you are responsible to make sure the config file is stored securely in that machine or maybe you are responsible for encryption at rest yourself.

On the other hand, loading the configuration from an S3 bucket is better when security is your concern (encryption, versioning and fine-grain permission control etc) and when it is not convenient to have a persistent volume to store your config file (e.g. run it via a docker container). Instead of downloading the config file from S3 to a local directory. `onedump` will load the config directly to memory from S3 via AWS API.

## How it works
The primary use case of `onedump` is to run one command with a configuration file. It dumps database from different drivers to different destinations.

### Connect to the database
`onedump` connects to your database in two ways: direct network access or SSH.

#### Connect the database via network access
No matter if it is to dump from your local DB or a DB host that the machine can dial directly. You can create a `job` configurable item in the config file

```
jobs:
- name: exec-dump
  dbdriver: mysql
  dbdsn: user:password@tcp(10.10.10.1)/dbname
  # the rest of config...
```

`dbdriver` and `dbdsn` are the required fields to be able to connect to your database. For this case, the DB host is in a private network that has an IP address `10.10.10.1`. It is possible to connect to the DB when the machine that runs `onedump` is in the same private network.

#### Connect the database via SSH
You can also connect to a remote database when SSH is enabled. Create a `job` configurable item in the config file
```
jobs:
- name: ssh-dump
  dbdriver: mysql
  dbdsn: user:password@tcp(127.0.0.1:3306)/dbname
  sshhost: mywebsite.com
  sshuser: root
  sshkey: |-
    -----BEGIN OPENSSH PRIVATE KEY-----
    b3BlbnNzaC1rZXktdjEAAAAABG5vbmUAAAAEbm9uZQAAAAAAAAABAAACFwAAAAdzc2gtcn...
    -----END OPENSSH PRIVATE KEY-----
  # the rest of config...
```

For this case, you need to pass three extra config options: `sshhost`, `sshuser` and `sshkey` to tell `onedump` to talk to the remote database via ssh.

### Save DB to storage
It is required to config at least one storage for the dump job. For example, we want it to dump a local DB and save the contents in a local directory as well as an S3 bucket.

```
jobs:
- name: local-dump
  dbdriver: mysql
  dbdsn: root@tcp(127.0.0.1)/db
  storage:
    local:
      - path: /Users/jack/Desktop/db.sql
    s3:
      - bucket: mybucket
        key: db-backup/mydb.sql
        region: ap-southeast-2
        access-key-id: MYKEY...
        secret-access-key: AWSSECRET..
        session-token: <session-token> # optional, specify the value if you assume a role.
```

### Setting cron job
Run onedump with cron mode by passing cron experssions.

```
$ onedump -f /path/to/config.yaml -c 21h
```

## The native MySQL dumper

The MySQL native dumper provides a user experience similar to `mysqldump`. However, it doesn't implement all the features of `mysqldump`. It is suitable for most basic use cases but has some limitations:

1. It doesn't support MySQL Spatial Data Types. For example If you use spatial data types like `GEOMETRY`, `POINT` or `POLYGON`, use `mysqldump` as the dumper.

1. It doesn't support all `mysqldump` options. Currently it supports `--skip-add-drop-table` and `--skip-add-locks`

If the native MySQL dumper doesn't meet your needs, switching to mysqldump is easy. Just update `dbdriver` in the configuration file to `mysqldump`:
```
jobs:
- dbdriver: mysqldump
  ...
```

## The slow log parser
`onedump` also has a built-in feature (run with `onedump slow [flags]`) to parse MySQL slow logs and print JSON in the following structure.

```
{ok: bool, error: string, results: []SlowResult}
```

For example
```json
{"ok":true,"error":"","results":[{"time":"2023-10-15T12:36:05.987654Z","user":"admin[admin]","host_ip":"[192.168.1.101]","query_time":12.890323,"lock_time":0.001456,"rows_sent":100,"rows_examined":100000,"thread_id":0,"errno":0,"killed":0,"bytes_received":0,"bytes_sent":0,"read_first":0,"read_last":0,"read_key":0,"read_next":0,"read_prev":0,"read_rnd":0,"read_rnd_next":0,"sort_merge_passes":0,"sort_range_count":0,"sort_rows":0,"sort_scan_count":0,"created_tmp_disk_tables":0,"created_tmp_tables":0,"count_hit_tmp_table_size":0,"start":"","end":"","query":"SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id HAVING order_count > ?"}]}
```


### Usage
```bash
// parse a single file
$onedump slow -f /path/to/file/slow.log

// parse a folder
$onedump slow -f /path/to/folder 

// Parse a folder or file by searching for filenames that match the pattern
$onedump slow -f /path/to/folder-or-file -p="*slow.log"

// Mask query values with ?
$onedump slow -f /path/to/file -m="true"
```
## MySQL binlog backup to AWS S3

The `binlog sync-s3` command enables you to back up MySQL binary log (binlog) files to an AWS S3 bucket. This is particularly useful for achieving point-in-time recovery.

Refer to the [documentation](./docs/binlog/sync-s3.md) for detailed usage.

## MySQL binlog restore
The `binlog restore` command can be used as part of the point-in-time recovery process. It replays events from the binlogs and restores the data to a specified point in time.

Refer to the [documentation](./docs/binlog/restore.md) for detailed usage.


## Resumable and concurrent SFTP file transfers

The `sync sftp` command provides an efficient way to transfer files from a source to a remote destination using the SFTP protocol. It supports various options to accommodate different use cases.

Refer to the [documentation](./docs/sync/sftp.md) for detailed usage.

## Contribution
For development guidelines, refer to the [Development Guides](./docs/development.md).

onedump

[![Documentation](https://img.shields.io/badge/Documentation-%F0%9F%93%9C-666?logo=github)](https://cybertec-postgresql.github.io/pg_timetable/)
[![License: MIT](https://img.shields.io/badge/License-MIT-green.svg)](https://opensource.org/licenses/MIT)
[![Build and Test](https://github.com/cybertec-postgresql/pg_timetable/actions/workflows/build.yml/badge.svg)](https://github.com/cybertec-postgresql/pg_timetable/actions/workflows/build.yml)
[![Coverage Status](https://img.shields.io/coverallsCoverage/github/cybertec-postgresql/pg_timetable?branch=master&label=Coverage&color=green)](https://coveralls.io/github/cybertec-postgresql/pg_timetable?branch=master)
[![Github All Releases](https://img.shields.io/github/downloads/cybertec-postgresql/pg_timetable/total?label=Downloads)](https://github.com/cybertec-postgresql/pg_timetable/releases)
[![Docker Pulls](https://img.shields.io/docker/pulls/cybertecpostgresql/pg_timetable?label=Docker%20Pulls)](https://hub.docker.com/r/cybertecpostgresql/pg_timetable)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)

# pg_timetable: Advanced scheduling for PostgreSQL

**pg_timetable** is an advanced standalone job scheduler for PostgreSQL, offering many advantages over traditional schedulers such as **cron** and others.
It is completely database driven and provides a couple of advanced concepts. It allows you to schedule PostgreSQL commands, system programs and built-in operations:

```sql
-- Run public.my_func() at 00:05 every day in August:
SELECT timetable.add_job('execute-func', '5 0 * 8 *', 'SELECT public.my_func()');

-- Run VACUUM at minute 23 past every 2nd hour from 0 through 20 every day:
SELECT timetable.add_job('run-vacuum', '23 0-20/2 * * *', 'VACUUM');

-- Refresh materialized view every 2 hours:
SELECT timetable.add_job('refresh-matview', '@every 2 hours', 
  'REFRESH MATERIALIZED VIEW public.mat_view');

-- Clear log table after pg_timetable restart:
SELECT timetable.add_job('clear-log', '@reboot', 'TRUNCATE public.log');

-- Reindex at midnight on Sundays with reindexdb utility:

--  using default database under default user (no command line arguments)
SELECT timetable.add_job('reindex-job', '0 0 * * 7', 'reindexdb', job_kind := 'PROGRAM');

--  specifying target database and tables, and be verbose
SELECT timetable.add_job('reindex-job', '0 0 * * 7', 'reindexdb',
          '["--table=foo", "--dbname=postgres", "--verbose"]'::jsonb, 'PROGRAM');

--  passing password using environment variable through bash shell
SELECT timetable.add_job('reindex-job', '0 0 * * 7', 'bash',
    '["-c", "PGPASSWORD=5m3R7K4754p4m reindexdb -U postgres -h 192.168.0.221 -v"]'::jsonb,
    'PROGRAM');    
```

## Documentation

<https://cybertec-postgresql.github.io/pg_timetable/>

## Main features

- Tasks can be arranged in chains
- Each task executes SQL, built-in or executable command
- Parameters can be passed to tasks
- Missed chains (possibly due to downtime) can be retried automatically
- Support for configurable repetitions
- Builtin tasks such as sending emails, downloading, importing files, etc.
- Fully database driven configuration
- Full support for database driven logging
- Enhanced cron-style scheduling
- Optional concurrency protection
- **NEW**: YAML-based chain definitions for easy configuration

## YAML Configuration

You can now define chains using YAML files instead of SQL inserts, making configuration more readable and maintainable:

```yaml
chains:
  - name: "Daily ETL Pipeline"
    schedule: "0 2 * * *"  # 2 AM daily
    live: true
    max_instances: 1
    timeout: 3600000  # 1 hour
    
    tasks:
      - name: "Extract data"
        command: "SELECT extract_sales_data($1)"
        parameters: ["yesterday"]
        
      - name: "Transform data"
        command: "CALL transform_sales_data()"
        autonomous: true
        
      - name: "Load to warehouse"
        command: "CALL load_to_warehouse()"
```

Load YAML chains with:

```bash
pg_timetable --file chains.yaml postgresql://user:pass@host/db
```

See [`samples/yaml/`](samples/yaml/) for more examples and [YAML Schema](https://cybertec-postgresql.github.io/pg_timetable/latest/yaml-format/) for complete format specification.

## Installation

Complete installation guide can be found in the [documentation](https://cybertec-postgresql.github.io/pg_timetable/latest/installation/).

Possible choices are:

- official [release packages](https://github.com/cybertec-postgresql/pg_timetable/releases);
- [Docker images](https://hub.docker.com/r/cybertecpostgresql/pg_timetable);
- [build from sources](https://cybertec-postgresql.github.io/pg_timetable/latest/installation/#build-from-sources).

## Quick Start

Complete usage guide can be found in the [documentation](https://cybertec-postgresql.github.io/pg_timetable/latest/basic_jobs/).

1. Download **pg_timetable** executable

1. Make sure your **PostgreSQL** server is up and running and has a role with `CREATE` privilege for a target database, e.g.

    ```sql
    my_database=> CREATE ROLE scheduler PASSWORD 'somestrong';
    my_database=> GRANT CREATE ON DATABASE my_database TO scheduler;
    ```

1. Create a new job, e.g. run `VACUUM` each night at 00:30

    ```sql
    my_database=> SELECT timetable.add_job('frequent-vacuum', '30 0 * * *', 'VACUUM');
    add_job
    ---------
        3
    (1 row)
    ```

1. Run the pg_timetable

    ```terminal
    pg_timetable postgresql://scheduler:somestrong@localhost/my_database --clientname=vacuumer
    ```

1. PROFIT!

## Supported Environments  

| Cloud Service    | Supported | PostgreSQL Version  | Supported | OS | Supported |
| ---------------- |:---------:| ------------------- |:---------:| -- |:---------:|
| [Alibaba Cloud]  | ✅       | [19 (devel)]         | ✅       | Linux     | ✅ |
| [Amazon RDS]     | ✅       | [18 (current)]       | ✅       | Darwin    | ✅ |
| [Amazon Aurora]  | ✅       | [17]                 | ✅       | Windows   | ✅ |
| [Azure]          | ✅       | [16]                 | ✅       | FreeBSD\* | ✅ |
| [Citus Cloud]    | ✅       | [15]                 | ✅       | NetBSD\*  | ✅ |
| [Crunchy Bridge] | ✅       | [14]                 | ✅       | OpenBSD\* | ✅ |
| [DigitalOcean]   | ✅       | [13]                 | ✅       | Solaris\* | ✅ |
| [Google Cloud]   | ✅       | [12]                 | ✅       |           |     |
| [Heroku]         | ✅       | [11]                 | ✅       |           |     |
| [Supabase]       | ✅       | [10]                 | ✅       |           |     |

\* - there are no official release binaries made for these OSes. One would need to build them from sources.

\** - previous PostgreSQL versions may and should work smoothly. Only [officially supported PostgreSQL versions](https://www.postgresql.org/support/versioning/) are listed in this table.

[Alibaba Cloud]: https://www.alibabacloud.com/help/doc-detail/96715.htm
[Amazon RDS]: https://aws.amazon.com/rds/postgresql/
[Amazon Aurora]: https://aws.amazon.com/rds/aurora/
[Azure]: https://azure.microsoft.com/en-us/services/postgresql/
[Citus Cloud]: https://www.citusdata.com/product/cloud
[Crunchy Bridge]: https://www.crunchydata.com/products/crunchy-bridge/
[DigitalOcean]: https://www.digitalocean.com/products/managed-databases/
[Google Cloud]: https://cloud.google.com/sql/docs/postgres/
[Heroku]: https://elements.heroku.com/addons/heroku-postgresql
[Supabase]: https://supabase.io/docs/guides/database
[19 (devel)]: https://www.postgresql.org/docs/devel/index.html
[18 (current)]: https://www.postgresql.org/docs/18/index.html
[17]: https://www.postgresql.org/docs/17/index.html
[16]: https://www.postgresql.org/docs/16/index.html
[15]: https://www.postgresql.org/docs/15/index.html
[14]: https://www.postgresql.org/docs/14/index.html
[13]: https://www.postgresql.org/docs/13/index.html
[12]: https://www.postgresql.org/docs/12/index.html
[11]: https://www.postgresql.org/docs/11/index.html
[10]: https://www.postgresql.org/docs/10/index.html

## Contributing

If you want to contribute to **pg_timetable** and help make it better, feel free to open an [issue][issue] or even consider submitting a [pull request][PR].

[issue]: https://github.com/cybertec-postgresql/pg_timetable/issues
[PR]: https://github.com/cybertec-postgresql/pg_timetable/pulls

## Support

For professional support, please contact [Cybertec](https://www.cybertec-postgresql.com/).

## Authors

- Implementation: [Pavlo Golub](https://github.com/pashagolub)
- Initial idea and draft design: [Hans-Jürgen Schönig](https://github.com/postgresql007)

## Star History

[![Star History Chart](https://api.star-history.com/svg?repos=cybertec-postgresql/pg_timetable&type=Date)](https://star-history.com/#cybertec-postgresql/pg_timetable&Date)


pg_timetable

# pgweb

Simple web-based and cross platform PostgreSQL database explorer.

[![Release](https://img.shields.io/github/release/sosedoff/pgweb.svg?label=Release)](https://github.com/sosedoff/pgweb/releases)
[![Linux Build](https://github.com/sosedoff/pgweb/actions/workflows/checks.yml/badge.svg)](https://github.com/sosedoff/pgweb/actions?query=branch%3Amain)
[![Go Report Card](https://goreportcard.com/badge/github.com/sosedoff/pgweb)](https://goreportcard.com/report/github.com/sosedoff/pgweb)
[![GoDoc](https://godoc.org/github.com/sosedoff/pgweb?status.svg)](https://godoc.org/github.com/sosedoff/pgweb)
[![Docker Pulls](https://img.shields.io/docker/pulls/sosedoff/pgweb.svg)](https://hub.docker.com/r/sosedoff/pgweb/)

## Overview

Pgweb is a web-based database explorer for PostgreSQL, written in Go, and works
on Mac, Linux and Windows machines. Distributed as a simple binary with zero dependencies.
Very easy to use and packs just the right amount of features.

[See application screenshots](SCREENS.md)

## Features

- Cross-platform: Mac/Linux/Windows (64bit).
- Simple installation (distributed as a single binary).
- Zero dependencies.
- Works with PostgreSQL 9.1+.
- Supports native SSH tunnels.
- Multiple database sessions.
- Execute and analyze custom SQL queries.
- Table and query data export to CSV/JSON/XML.
- Query history.
- Server bookmarks.

Visit [WIKI](https://github.com/sosedoff/pgweb/wiki) for more details.

## Demo

Visit https://pgweb-demo.fly.dev/ to see Pgweb in action.

## Installation

- [Precompiled binaries](https://github.com/sosedoff/pgweb/releases) for supported operating systems are available.
- [More installation options](https://github.com/sosedoff/pgweb/wiki/Installation)

## Usage

Start server:

```
pgweb
```

You can also provide connection flags:

```
pgweb --host localhost --user myuser --db mydb
```

Connection URL scheme is also supported:

```
pgweb --url postgres://user:password@host:port/database?sslmode=[mode]
pgweb --url "postgres:///database?host=/absolute/path/to/unix/socket/dir"
```

### Multiple database sessions

To enable multiple database sessions in pgweb, start the server with:

```
pgweb --sessions
```

Or set environment variable:

```
PGWEB_SESSIONS=1 pgweb
```

## Testing

Before running tests, make sure you have PostgreSQL server running on `localhost:5432`
interface. Also, you must have `postgres` user that could create new databases
in your local environment. Pgweb server should not be running at the same time.

Execute test suite:

```
make test
```

If you're using Docker locally, you might also run pgweb test suite against
all supported PostgreSQL version with a single command:

```
make test-all
```

## Contribute

- Fork this repository
- Create a new feature branch for a new functionality or bugfix
- Commit your changes
- Execute test suite
- Push your code and open a new pull request
- Use [issues](https://github.com/sosedoff/pgweb/issues) for any questions
- Check [wiki](https://github.com/sosedoff/pgweb/wiki) for extra documentation

## License

The MIT License (MIT). See [LICENSE](LICENSE) file for more details.


pgweb

Simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

# pRESTd

[![Tests](https://github.com/prest/prest/actions/workflows/test.yml/badge.svg)](https://github.com/prest/prest/actions/workflows/test.yml)
[![GoDoc](https://godoc.org/github.com/prest/prest?status.png)](https://godoc.org/github.com/prest/prest)
[![Go Report Card](https://goreportcard.com/badge/github.com/prest/prest)](https://goreportcard.com/report/github.com/prest/prest)
[![codecov](https://codecov.io/gh/prest/prest/branch/main/graph/badge.svg?token=eVD9urwIEv)](https://codecov.io/gh/prest/prest)
[![Homebrew](https://img.shields.io/badge/dynamic/json.svg?url=https://formulae.brew.sh/api/formula/prestd.json&query=$.versions.stable&label=homebrew)](https://formulae.brew.sh/formula/prestd)
[![Discord](https://img.shields.io/badge/discord-prestd-blue?logo=discord)](https://discord.gg/JnRjvu39w8)

_p_**REST** (**P**_ostgreSQL_ **REST**), is a simple production-ready API, that delivers an instant, realtime, and high-performance application on top of your **existing or new Postgres** database.

> PostgreSQL version 9.5 or higher

Contributor License Agreement - [![CLA assistant](https://cla-assistant.io/readme/badge/prest/prest)](https://cla-assistant.io/prest/prest)

<a href="https://www.producthunt.com/posts/prest?utm_source=badge-featured&utm_medium=badge&utm_souce=badge-prest" target="_blank"><img src="https://api.producthunt.com/widgets/embed-image/v1/featured.svg?post_id=303506&theme=light" alt="pREST - instant, realtime, high-performance on PostgreSQL | Product Hunt" style="width: 250px; height: 54px;" width="250" height="54" /></a>

## Problems we solve

The pREST project is the API that addresses the need for fast and efficient solution in building RESTful APIs on PostgreSQL databases. It simplifies API development by offering:

1. A **lightweight server** with easy configuration;
2. Direct **SQL queries with templating** in customizable URLs;
3. Optimizations for **high performance**;
4. **Enhanced** developer **productivity**;
5. **Authentication and authorization** features;
6. **Pluggable** custom routes and middlewares.

Overall, pREST simplifies the process of creating secure and performant RESTful APIs on top of your new or old PostgreSQL database.

[Read more](https://github.com/prest/prest/issues/41).

## Why we built pREST

When we built pREST, we originally intended to contribute and build with the PostgREST project, although it took a lot of work as the project is in Haskell. At the time, we did not have anything similar or intended to keep working with that tech stack. We've been building production-ready Go applications for a long time, so building a similar project with Golang as its core was natural.

Additionally, as Go has taken a huge role in many other vital projects such as Kubernetes and Docker, and we've been able to use the pREST project in many different companies with success over the years, it has shown to be an excellent decision.

## 1-Click Deploy

### Heroku

Deploy to Heroku and instantly get a realtime RESTFul API backed by Heroku Postgres:

[![Deploy to Heroku](https://www.herokucdn.com/deploy/button.svg)](https://heroku.com/deploy?template=https://github.com/prest/prest-heroku)

## Documentation

Visit <https://docs.prestd.com/>

## Testing

Run the test suite inside Docker (no local Postgres required):

```bash
make test
```

Or directly with Docker Compose:

```bash
docker compose -f docker-compose-test.yml up --abort-on-container-exit --exit-code-from tests
docker compose -f docker-compose-test.yml down -v --remove-orphans
```

The `tests` service runs `./testdata/runtest.sh`, provisioning databases and executing Go tests.

## Example: Docker Build

You can build the Docker image locally for development (this compiles the code from source):

```bash
docker build -t prest/prest:latest .
```

For release builds, GoReleaser uses the same `Dockerfile` but injects version information via build arguments:

```bash
docker build \
  --build-arg VERSION=v1.0.0 \
  --build-arg COMMIT=hash \
  --build-arg DATE=2026-02-11 \
  -t prest/prest:latest .
```



pREST

Redis RDB file parser for secondary development and memory analysis

![license](https://img.shields.io/github/license/HDT3213/rdb)
![download](https://img.shields.io/github/downloads/hdt3213/rdb/total)
[![Go Reference](https://pkg.go.dev/badge/github.com/hdt3213/rdb.svg)](https://pkg.go.dev/github.com/hdt3213/rdb)
<br>
[![Build Status](https://github.com/hdt3213/rdb/actions/workflows/main.yml/badge.svg)](https://github.com/HDT3213/rdb/actions?query=branch%3Amaster)
[![Coverage Status](https://coveralls.io/repos/github/HDT3213/rdb/badge.svg?branch=master)](https://coveralls.io/github/HDT3213/rdb?branch=master)
[![Go Report Card](https://goreportcard.com/badge/github.com/HDT3213/rdb)](https://goreportcard.com/report/github.com/HDT3213/rdb)
<br>
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge-flat.svg)](https://github.com/avelino/awesome-go)

[中文版](https://github.com/HDT3213/rdb/blob/master/README_CN.md)

This is a golang implemented Redis RDB parser for secondary development and memory analysis.

It provides abilities to:

- Generate memory report for rdb file
- Convert RDB files to JSON
- Convert RDB files to Redis Serialization Protocol (or AOF file)
- Find the biggest N keys in RDB files
- Draw FlameGraph to analysis which kind of keys occupied most memory
- Customize data usage
- Generate RDB file

Support RDB version: 1 <= version <= 12(Redis 7.2)

If you read Chinese, you could find a thorough introduction to the RDB file format here: [Golang 实现 Redis(11): RDB 文件格式](https://www.cnblogs.com/Finley/p/16251360.html)

Thanks sripathikrishnan for his [redis-rdb-tools](https://github.com/sripathikrishnan/redis-rdb-tools)

# Install

If you have installed `go` on your compute, just simply use:

```
go install github.com/hdt3213/rdb@latest
```

### Package Managers

If you're a [Homebrew](https://brew.sh/) user, you can install [rdb](https://formulae.brew.sh/formula/rdb) via:

```sh
$ brew install rdb
```

Or, you can download executable binary file from [releases](https://github.com/HDT3213/rdb/releases) and put its path to
PATH environment.

use `rdb` command in terminal, you can see it's manual

```
$ rdb
This is a tool to parse Redis' RDB files
Options:
  -c command, including: json/memory/aof/bigkey/prefix/flamegraph
  -o output file path
  -n number of result, using in command: bigkey/prefix
  -port listen port for flame graph web service
  -sep separator for flamegraph, rdb will separate key by it, default value is ":". 
    supporting multi separators: -sep sep1 -sep sep2 
  -regex using regex expression filter keys
  -expire filter keys by its expiration time
    1. '1751731200~1751817600' get keys with expiration time in range [1751731200, 1751817600]
    2. '1751731200~now' 'now~1751731200' magic variable 'now' represents the current timestamp
    3. '1751731200~inf' 'now~inf' magic variable 'inf' represents the Infinity
    4. 'noexpire' get keys without expiration time
    5. 'anyexpire' get all keys with expiration time
  -size filter keys by size, supports B/KB/MB/GB/TB/PB/EB
    1. '1KB~1MB' get keys with size in range [1KB, 1MB]
    2. '10MB~inf' magic variable 'inf' represents the Infinity
    3. '1024~10KB' get keys with size in range [0Bytes, 10KB]
  -concurrent The number of concurrent json converters. 4 by default.
  -show-global-meta Show global meta likes redis-verion/ctime/functions
  -no-expired filter expired keys(deprecated, please use 'expire' option)

Examples:
parameters between '[' and ']' is optional
1. convert rdb to json
  rdb -c json -o dump.json dump.rdb
2. generate memory report
  rdb -c memory -o memory.csv dump.rdb
3. convert to aof file
  rdb -c aof -o dump.aof dump.rdb
4. get largest keys
  rdb -c bigkey [-o dump.aof] [-n 10] dump.rdb
5. get number and memory size by prefix
  rdb -c prefix [-n 10] [-max-depth 3] [-o prefix-report.csv] dump.rdb
6. draw flamegraph
  rdb -c flamegraph [-port 16379] [-sep :] dump.rdb
```

# Convert to Json

Usage:

```
rdb -c json -o <output_path> <source_path>
```

example:

```
rdb -c json -o intset_16.json cases/intset_16.rdb
```

You can get some rdb examples in [cases](https://github.com/HDT3213/rdb/tree/master/cases)

The examples for json result:

```json
[
    {"db":0,"key":"hash","size":64,"type":"hash","hash":{"ca32mbn2k3tp41iu":"ca32mbn2k3tp41iu","mddbhxnzsbklyp8c":"mddbhxnzsbklyp8c"}},
    {"db":0,"key":"string","size":10,"type":"string","value":"aaaaaaa"},
    {"db":0,"key":"expiration","expiration":"2022-02-18T06:15:29.18+08:00","size":8,"type":"string","value":"zxcvb"},
    {"db":0,"key":"list","expiration":"2022-02-18T06:15:29.18+08:00","size":66,"type":"list","values":["7fbn7xhcnu","lmproj6c2e","e5lom29act","yy3ux925do"]},
    {"db":0,"key":"zset","expiration":"2022-02-18T06:15:29.18+08:00","size":57,"type":"zset","entries":[{"member":"zn4ejjo4ths63irg","score":1},{"member":"1ik4jifkg6olxf5n","score":2}]},
    {"db":0,"key":"set","expiration":"2022-02-18T06:15:29.18+08:00","size":39,"type":"set","members":["2hzm5rnmkmwb3zqd","tdje6bk22c6ddlrw"]}
]
```

You can use `-concurrent` to change the number of concurrent convertes. The default value is 4.

```
rdb -c json -o intset_16.json -concurrent 8 cases/intset_16.rdb
```

You can use `-show-global-meta` to get metadata (redis-ver,ctime,used-mem, etc.) and functions in rdb file.

```bash
rdb -c json -o function.json -show-global-meta cases/function.rdb
```

Examples:

```json
[
{"db":0,"key":"redis-ver","size":0,"type":"aux","encoding":"","value":"7.2.5"},
{"db":0,"key":"redis-bits","size":0,"type":"aux","encoding":"","value":"64"},
{"db":0,"key":"ctime","size":0,"type":"aux","encoding":"","value":"1767107423"},
{"db":0,"key":"used-mem","size":0,"type":"aux","encoding":"","value":"1269264"},
{"db":0,"key":"aof-base","size":0,"type":"aux","encoding":"","value":"0"},
{"db":0,"key":"functions","size":0,"type":"functions","encoding":"functions","functionsLua":"#!lua name=mylib\nredis.register_function('myfunc', function(keys, args) return 'hello' end)"}
]
```

<details>
<summary>Json Fromat Detail</summary>
  
## string 

```json
{
    "db": 0,
    "key": "string",
    "size": 10, // estimated memory size
    "type": "string",
	"expiration":"2022-02-18T06:15:29.18+08:00",
    "value": "aaaaaaa"
}
```

## list

```json
{
    "db": 0,
    "key": "list",
    "expiration": "2022-02-18T06:15:29.18+08:00",
    "size": 66,
    "type": "list",
    "values": [
        "7fbn7xhcnu",
        "lmproj6c2e",
        "e5lom29act",
        "yy3ux925do"
    ]
}
```

## set

```json
{
    "db": 0,
    "key": "set",
    "expiration": "2022-02-18T06:15:29.18+08:00",
    "size": 39,
    "type": "set",
    "members": [
        "2hzm5rnmkmwb3zqd",
        "tdje6bk22c6ddlrw"
    ]
}
```

## hash

```json
{
    "db": 0,
    "key": "hash",
    "size": 64,
    "type": "hash",
	"expiration": "2022-02-18T06:15:29.18+08:00",
    "hash": {
        "ca32mbn2k3tp41iu": "ca32mbn2k3tp41iu",
        "mddbhxnzsbklyp8c": "mddbhxnzsbklyp8c"
    }
}
```

## zset

```json
{
    "db": 0,
    "key": "zset",
    "expiration": "2022-02-18T06:15:29.18+08:00",
    "size": 57,
    "type": "zset",
    "entries": [
        {
            "member": "zn4ejjo4ths63irg",
            "score": 1
        },
        {
            "member": "1ik4jifkg6olxf5n",
            "score": 2
        }
    ]
}
```

## stream

```json
{
    "db": 0,
    "key": "mystream",
    "size": 1776,
    "type": "stream",
    "encoding": "",
    "version": 3, // Version 2 means is RDB_TYPE_STREAM_LISTPACKS_2, 3 means is RDB_TYPE_STREAM_LISTPACKS_3
	// StreamEntry is a node in the underlying radix tree of redis stream, of type listpacks, which contains several messages. There is no need to care about which entry the message belongs to when using it.
    "entries": [ 
        {
            "firstMsgId": "1704557973866-0", // ID of the master entry at listpack head 
            "fields": [ // master fields, used for compressing size
                "name",
                "surname"
            ],
            "msgs": [ // messages in entry
                {
                    "id": "1704557973866-0",
                    "fields": {
                        "name": "Sara",
                        "surname": "OConnor"
                    },
                    "deleted": false
                }
            ]
        }
    ],
    "groups": [ // consumer groups
        {
            "name": "consumer-group-name",
            "lastId": "1704557973866-0",
            "pending": [ // pending messages
                {
                    "id": "1704557973866-0",
                    "deliveryTime": 1704557998397,
                    "deliveryCount": 1
                }
            ],
            "consumers": [ // consumers in the group
                {
                    "name": "consumer-name",
                    "seenTime": 1704557998397,
                    "pending": [
                        "1704557973866-0"
                    ],
                    "activeTime": 1704557998397
                }
            ],
            "entriesRead": 1
        }
    ],
    "len": 1, // current number of messages inside this stream
    "lastId": "1704557973866-0",
    "firstId": "1704557973866-0",
    "maxDeletedId": "0-0",
    "addedEntriesCount": 1
}
```

## aux

```json
[
    {
        "db": 0,
        "key": "redis-ver",
        "size": 0,
        "type": "aux",
        "encoding": "",
        "value": "7.2.5"
    },
    {
        "db": 0,
        "key": "redis-bits",
        "size": 0,
        "type": "aux",
        "encoding": "",
        "value": "64"
    },
    {
        "db": 0,
        "key": "ctime",
        "size": 0,
        "type": "aux",
        "encoding": "",
        "value": "1767107423"
    },
    {
        "db": 0,
        "key": "used-mem",
        "size": 0,
        "type": "aux",
        "encoding": "",
        "value": "1269264"
    },
    {
        "db": 0,
        "key": "aof-base",
        "size": 0,
        "type": "aux",
        "encoding": "",
        "value": "0"
    }
]
```

## functions

```json
{
    "db": 0,
    "key": "functions",
    "size": 0,
    "type": "functions",
    "encoding": "functions",
    "functionsLua": "#!lua name=mylib\nredis.register_function('myfunc', function(keys, args) return 'hello' end)"
}
```

</details>

# Generate Memory Report

RDB uses rdb encoded size to estimate redis memory usage.

```bash
rdb -c memory -o <output_path> <source_path>
```

Example:

```bash
rdb -c memory -o mem.csv cases/memory.rdb
```

The examples for csv result:

```csv
database,key,type,size,size_readable,element_count
0,hash,hash,64,64B,2
0,s,string,10,10B,0
0,e,string,8,8B,0
0,list,list,66,66B,4
0,zset,zset,57,57B,2
0,large,string,2056,2K,0
0,set,set,39,39B,2
```

# Analyze By Prefix

If you can distinguish modules based on the prefix of the key, for example, the key of user data is `User:<uid>`, the key of Post is `Post:<postid>`, the user statistics is `Stat:User:???`, and the statistics of Post is `Stat:Post:???`.Then we can get the status of each module through prefix analysis:

```csv
database,prefix,size,size_readable,key_count
0,Post:,1170456184,1.1G,701821
0,Stat:,405483812,386.7M,3759832
0,Stat:Post:,291081520,277.6M,2775043
0,User:,241572272,230.4M,265810
0,Topic:,171146778,163.2M,694498
0,Topic:Post:,163635096,156.1M,693758
0,Stat:Post:View,133201208,127M,1387516
0,Stat:User:,114395916,109.1M,984724
0,Stat:Post:Comment:,80178504,76.5M,693758
0,Stat:Post:Like:,77701688,74.1M,693768
```

Format:

```bash
rdb -c prefix [-n <top-n>] [-max-depth <max-depth>] -o <output_path> <source_path>
```

- The prefix analysis results are arranged in descending order of memory space. The `-n` option can specify the number of outputs. All are output by default.

- `-max-depth` can limit the maximum depth of the prefix tree. In the above example, the depth of `Stat:` is 1, and the depth of `Stat:User:` and `Stat:Post:` is 2.

Example:

```bash
rdb -c prefix -n 10 -max-depth 2 -o prefix.csv cases/memory.rdb
```

# Flame Graph

In many cases there is not a few very large key but lots of small keys that occupied most memory.

RDB tool could separate keys by the given delimeters, then aggregate keys with same prefix.

Finally RDB tool presents the result as flame graph, with which you could find out which kind of keys consumed most
memory.

![截屏2022-10-30 12.06.00.png](https://s2.loli.net/2022/11/08/HW9ZxGfeEzArUhM.png)

In this example, the keys of pattern `Comment:*` use 8.463% memory.

Usage:

```
rdb -c flamegraph [-port <port>] [-sep <separator1>] [-sep <separator2>] <source_path>
```

Example:

```
rdb -c flamegraph -port 16379 -sep : dump.rdb
```

# Find The Biggest Keys

RDB can find biggest N keys in file

```
rdb -c bigkey -n <result_number> <source_path>
```

Example:

```
rdb -c bigkey -n 5 cases/memory.rdb
```

The examples for csv result:

```csv
database,key,type,size,size_readable,element_count
0,large,string,2056,2K,0
0,list,list,66,66B,4
0,hash,hash,64,64B,2
0,zset,zset,57,57B,2
0,set,set,39,39B,2
```

# Convert to AOF

Usage:

```
rdb -c aof -o <output_path> <source_path>
```

Example:

```
rdb -c aof -o mem.aof cases/memory.rdb
```

The examples for aof result:

```
*3
$3
SET
$1
s
$7
aaaaaaa
```

# Regex Filter

RDB tool supports using regex expression to filter keys.

Example:
```rdb
rdb -c json -o regex.json -regex '^l.*' cases/memory.rdb
```

# Size Filter

The `-size` parameter can be configured to filter based on the object size in bytes.

- Units supported: KB/MB/GB/TB/PB/EB. Binary and SI prefixes both mean base-2 units:
  - KB = K = KiB = 1024
  - MB = M = MiB = 1024 × KB
  - GB = G = GiB = 1024 × MB
  - TB = T = TiB = 1024 × GB
  - PB = P = PiB = 1024 × TB
  - EB = E = EiB = 1024 × PB
- Use range format `min~max` and the magic variable `inf` for infinity.

Examples:
```bash
# Keys sized between 1KB and 1MB (inclusive)
rdb -c json -o dump.json -size 1KB~1MB cases/memory.rdb

# Keys sized greater than or equal to 10MB
rdb -c memory -o memory.csv -size 10MB~inf cases/memory.rdb
```

# Expiration Filter

The `-expire` parameter can be configured to filter based on the expiration time.

Keys with expiration times between 2025-07-06 00:00:00 and 2025-07-07 00:00:00:
```bash
# toTimestamp(2025-07-06 00:00:00) == 1751731200
# toTimestamp(2025-07-07 00:00:00) == 1751817600
rdb -c json -o dump.json -expire 1751731200~1751817600 cases/expiration.rdb
```

```bash
# Keys with expiration times earlier than 2025-07-07 00:00:00
rdb -c json -o dump.json -expire 0~1751817600 cases/expiration.rdb
```

The magic variable `inf` represents infinity:
```bash
rdb -c json -o dump.json -expire 1751731200~inf cases/expiration.rdb
```

The magic variable `now` represents the current time:

```bash
# Keys with expiration times earlier than now
rdb -c json -o dump.json -expire 0~now cases/expiration.rdb
```

```bash
# Keys with expiration times later than now
rdb -c json -o dump.json -expire now~inf cases/expiration.rdb
```

All keys with expiration times set:
```bash
rdb -c json -o dump.json -expire anyexpire cases/expiration.rdb
```

All keys without expiration times set:
```bash
rdb -c json -o dump.json -expire noexpire cases/expiration.rdb
```

# Customize data usage

```go
package main

import (
	"github.com/hdt3213/rdb/parser"
	"os"
)

func main() {
	rdbFile, err := os.Open("dump.rdb")
	if err != nil {
		panic("open dump.rdb failed")
	}
	defer func() {
		_ = rdbFile.Close()
	}()
	decoder := parser.NewDecoder(rdbFile)
	err = decoder.Parse(func(o parser.RedisObject) bool {
		switch o.GetType() {
		case parser.StringType:
			str := o.(*parser.StringObject)
			println(str.Key, str.Value)
		case parser.ListType:
			list := o.(*parser.ListObject)
			println(list.Key, list.Values)
		case parser.HashType:
			hash := o.(*parser.HashObject)
			println(hash.Key, hash.Hash)
		case parser.ZSetType:
			zset := o.(*parser.ZSetObject)
			println(zset.Key, zset.Entries)
		case parser.StreamType:
			stream := o.(*parser.StreamObject)
			println(stream.Entries, stream.Groups)
		}
		// return true to continue, return false to stop the iteration
		return true
	})
	if err != nil {
		panic(err)
	}
}
```

# Generate RDB file

This library can generate RDB file: 

```go
package main

import (
	"github.com/hdt3213/rdb/encoder"
	"github.com/hdt3213/rdb/model"
	"os"
	"time"
)

func main() {
	rdbFile, err := os.Create("dump.rdb")
	if err != nil {
		panic(err)
	}
	defer rdbFile.Close()
	enc := encoder.NewEncoder(rdbFile)
	err = enc.WriteHeader()
	if err != nil {
		panic(err)
	}
	auxMap := map[string]string{
		"redis-ver":    "4.0.6",
		"redis-bits":   "64",
		"aof-preamble": "0",
	}
	for k, v := range auxMap {
		err = enc.WriteAux(k, v)
		if err != nil {
			panic(err)
		}
	}

	err = enc.WriteDBHeader(0, 5, 1)
	if err != nil {
		panic(err)
	}
	expirationMs := uint64(time.Now().Add(time.Hour*8).Unix() * 1000)
	err = enc.WriteStringObject("hello", []byte("world"), encoder.WithTTL(expirationMs))
	if err != nil {
		panic(err)
	}
	err = enc.WriteListObject("list", [][]byte{
		[]byte("123"),
		[]byte("abc"),
		[]byte("la la la"),
	})
	if err != nil {
		panic(err)
	}
	err = enc.WriteSetObject("set", [][]byte{
		[]byte("123"),
		[]byte("abc"),
		[]byte("la la la"),
	})
	if err != nil {
		panic(err)
	}
	err = enc.WriteHashMapObject("list", map[string][]byte{
		"1":  []byte("123"),
		"a":  []byte("abc"),
		"la": []byte("la la la"),
	})
	if err != nil {
		panic(err)
	}
	err = enc.WriteZSetObject("list", []*model.ZSetEntry{
		{
			Score: 1.234,
			Member: "a",
		},
		{
			Score: 2.71828,
			Member: "b",
		},
	})
	if err != nil {
		panic(err)
	}
	err = enc.WriteEnd()
	if err != nil {
		panic(err)
	}
}
```

# Benchmark

Tested on MacBook Air（M2，2022年）, using  a 1.3 GB RDB file encoded with v9 format from Redis 5.0 in production environment.

|usage|elapsed|speed|
|:-:|:-:|:-:|
|ToJson|25s|53.24MB/s|
|Memory|10s|133.12MB/s|
|AOF|25s|53.24MB/s|
|Top10|6s|221.87MB/s|
|Prefix|25s|53.24MB/s|


WeScale is a database proxy designed to enhance the scalability, performance, security, and resilience of your applications



<div align="center">
    <h1>WeScale</h1>

[![LICENSE](https://img.shields.io/badge/License-Apache%202.0-green.svg)](https://github.com/wesql/wescale/blob/vitess-release-16.0-dev/LICENSE)
[![Language](https://img.shields.io/badge/Language-Go-blue.svg)](https://go.dev/)
<br/>
[![Unit Test (mysql57)](https://github.com/wesql/wescale/actions/workflows/unit_test_mysql57.yml/badge.svg?branch=main)](https://github.com/wesql/wescale/actions/workflows/unit_test_mysql57.yml)
[![Unit Test (mysql80)](https://github.com/wesql/wescale/actions/workflows/unit_test_mysql80.yml/badge.svg?branch=main)](https://github.com/wesql/wescale/actions/workflows/unit_test_mysql80.yml)
[![E2E Test](https://github.com/wesql/wescale/actions/workflows/cluster_endtoend_wesql.yml/badge.svg?branch=main)](https://github.com/wesql/wescale/actions/workflows/cluster_endtoend_wesql.yml)
[![MTR Test](https://github.com/wesql/wescale/actions/workflows/wescale_wesql_performance_docker.yml/badge.svg)](https://github.com/wesql/wescale/actions/workflows/wescale_wesql_performance_docker.yml)

</div>


# Introduction
WeScale is an open-source database proxy built with application developers in mind. It aims to simplify and enhance the 
interaction between your application and the database, making development more efficient and enjoyable.
<br/>
While databases have evolved to include complex and common application requirements like transactions and indexing, 
modern applications have a growing set of shared needs that aren’t suitable or haven’t yet been integrated into the database layer. 
This gap has led to the rise of various database middleware solutions such as 
client-side connection pools, SQL retry libraries, online DDL tools, and more.
<br/>
As a database proxy, WeScale serves as a crucial bridge between the application layer and the database layer.
It leans towards the application side, offering features specifically designed to simplify the developer experience. 

# Getting Started
* To [Get Started On Your Local Machine](doc%2Ftoturial%2F00-Deploy%26Debug.md) with WeScale, simply clone the repository and follow the installation instructions
provided in the documentation.
* To [Deploy WeScale on Kubernetes](doc%2Ftoturial%2F11-Getting-Started-with-Kubernetes.md), 
you can use the powerful Kubeblocks Operator to quickly launch a WeScale cluster in a Kubernetes cluster. We recommend this method for production environments. 

## Getting Started with Docker
To Start WeScale with Docker, you can simply run the following command:
```shell
docker network create wescale-network

docker run -itd --network wescale-network --name mysql-server \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=passwd \
  -e MYSQL_ROOT_HOST=% \
  -e MYSQL_LOG_CONSOLE=true \
  mysql/mysql-server:8.0.32 \
  --bind-address=0.0.0.0 \
  --port=3306 \
  --log-bin=binlog \
  --gtid_mode=ON \
  --enforce_gtid_consistency=ON \
  --log_replica_updates=ON \
  --binlog_format=ROW

docker run -itd --network wescale-network --name wescale \
  -p 15306:15306 \
  -w /vt/examples/wesql-server \
  -e MYSQL_ROOT_USER=root \
  -e MYSQL_ROOT_PASSWORD=passwd \
  -e MYSQL_PORT=3306 \
  -e MYSQL_HOST=mysql-server \
  apecloud/apecloud-mysql-scale:0.3.8 \
  /vt/examples/wesql-server/init_single_node_cluster.sh
```

Now you can connect to WeScale and the original MySQL server:
```shell
# Connect to WeScale
mysql -h127.0.0.1 -uroot -ppasswd -P15306

# You can still connect to the original MySQL server
mysql -h127.0.0.1 -uroot -ppasswd -P3306
```

Try out Declarative DDL:
```sql
SET @@enable_declarative_ddl=true;

CREATE DATABASE if not exists test;
USE test;

CREATE TABLE if not exists test_table (
    id int primary key,
    name varchar(255)
);

SHOW CREATE TABLE test_table;

CREATE TABLE if not exists test_table (
    id int primary key,
    name varchar(255),
    email varchar(255),
    profile varchar(255),
    index (name, email)
);

SHOW CREATE TABLE test_table;
```

Clean up the containers:
```shell
docker rm -f mysql-server wescale
docker network rm wescale-network
```

## Community
Join our [Discord](https://discord.com/channels/1308609231498510427/1308609231498510430) to discuss features, get help, and connect with other users.

# Features
* [Declarative-DDL.md](doc%2Ftoturial%2F14-Declarative-DDL.md)
* [OnlineDDL.md](doc%2Ftoturial%2F07-OnlineDDL.md)
* [Transaction Chopping.md](doc%2Ftoturial%2F09-Transaction-Chopping.md)
* [Filters](doc%2Ftoturial%2F12-Filters.md)
* [Write a Wasm Plugin In WeScale](doc%2Ftoturial%2F13-Write-a-Wasm-Plugin-In-WeScale.md)
* [Branch](doc%2Ftoturial%2F08-Branch.md)
* [Show Tablets Query Plans](doc%2Ftoturial%2F10-Show%20Tablets%20Query%20Plans.md)
* [Transparent Failover.md](doc%2Ftoturial%2F05-Transparent%20Failover.md)
* [Authentication & Authorization.md](doc%2Ftoturial%2F06-Authentication%26Authorization.md)
* [Read-Write-Split & LoadBalancer.md](doc%2Ftoturial%2F03-Read-Write-Split%20%26%20LoadBalancer.md)
* [Read-After-Write-Consistency.md](doc%2Ftoturial%2F04-Read-After-Write-Consistency.md)

# Blogs
* [Architecture.md](doc%2Ftoturial%2F01-Architecture.md)
* [Life of A Query.md](doc%2Ftoturial%2F02-Life%20of%20A%20Query.md)
* [Introduction To WeScale.md](doc%2Fblogs%2FIntroduction%20To%20WeScale.md)
* [Dive into Read-Write-Splitting of WeScale.md](doc%2Fblogs%2FDive%20into%20Read-Write-Splitting%20of%20WeScale.md)
* [Performance Comparison WeScale vs MySQL.md](doc%2Fblogs%2FPerformance%20Comparison%20WeScale%20vs%20MySQL.md)
* [Execution Process of DROP TABLE in OnlineDDL Mode.md](doc%2Fblogs%2FExecution%20Process%20of%20DROP%20TABLE%20in%20OnlineDDL%20Mode.md)
* [Scaling-database-connections.md](doc%2Fblogs%2FScaling-database-connections.md)

# Developer
* [Use FailPoint Injection In WeScale.md](doc%2Fdeveloper%2FUse%20FailPoint%20Injection%20In%20WeScale.md)

# Monitoring
Once you have WeScale up and running, you can monitor the cluster using prometheus and grafana.
```bash
cd ./examples/metrics && ./start_dashboard.sh
```
Open your browser and navigate to `http://localhost:3000/dashboards` to view the dashboard.

![20241217-164819.jpeg](doc%2F20241217-164819.jpeg)

# Contributing
We welcome contributions to WeScale! If you have any ideas, bug reports, or feature requests,
please feel free to open an issue or submit a pull request.

# License
WeScale is released under the Apache 2.0 License.

# Acknowledgements
WeScale is a fork of the Vitess project, which is a database clustering system for horizontal scaling of MySQL.
We would like to thank the Vitess team for their hard work.


wescale

[![Go Report Card](https://goreportcard.com/badge/github.com/ContentSquare/chproxy)](https://goreportcard.com/report/github.com/ContentSquare/chproxy)
[![Go Coverage](https://github.com/ContentSquare/chproxy/wiki/coverage.svg)](https://raw.githack.com/wiki/ContentSquare/chproxy/coverage.html)
# chproxy

Chproxy is an HTTP proxy and load balancer for the [ClickHouse](https://ClickHouse.yandex) database.

It is an open-source community project and not an official ClickHouse project.

Full documentation is available on [the official website](https://www.chproxy.org/).

## Contributing

See our [contributing guide](./CONTRIBUTING.md)


chproxy

Use prepared SQL statements without changing your code

# Prep [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/hexdigest/prep/blob/master/LICENSE) [![Build Status](https://travis-ci.org/hexdigest/prep.svg?branch=master)](https://travis-ci.org/hexdigest/prep) [![Coverage Status](https://coveralls.io/repos/github/hexdigest/prep/badge.svg?branch=master)](https://coveralls.io/github/hexdigest/prep?branch=master) [![Go Report Card](https://goreportcard.com/badge/github.com/hexdigest/prep)](https://goreportcard.com/report/github.com/hexdigest/prep) [![GoDoc](https://godoc.org/github.com/hexdigest/prep?status.svg)](http://godoc.org/github.com/hexdigest/prep)
Prep finds all SQL statements in a Go package and instruments db connection with prepared statements.
It allows you to benefit from the prepared SQL statements almost without any changes to your code.

Prep consists of two parts:
* A command line tool that finds all SQL statements in your code
* A package that instruments your code with prepared SQL statements using the found ones

# Usage

## Generate a list of SQL statements used in your application

$ cat example.go
```go
func main() {
	db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/mysql")
	if err != nil {
		panic(err)
	}
	const query = `SELECT CONCAT("Hello ", ?, "!")`
	var s string
	if err := db.QueryRow(query, "World").Scan(&s); err != nil {
		panic(err)
	}
	fmt.Println(s)
}
```

Let's generate a list of the SQL statements used in your package:

```bash
$ prep -f github.com/hexdigest/prepdemo
$ cat prepared_statements.go
```

```go
//go:generate prep -f github.com/hexdigest/prepdemo

package main

var prepStatements = []string{
	"SELECT CONCAT(\"Hello \", ?, \"!\")",
}
```

# Using prepared statements

```go
func main() {
	sqlDB, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/mysql")
	if err != nil {
		panic(err)
	}

	db, err := prep.NewConnection(sqlDB, prepStatements)
	if err != nil {
		panic(err)
	}

	const query = `SELECT CONCAT("Hello ", ?, "!")`
	var s string
	if err := db.QueryRow(query, "World").Scan(&s); err != nil {
		panic(err)
	}
	fmt.Println(s)
}
```

Take a look at the line:
```go
db, err := prep.NewConnection(sqlDB, prepStatements)
```

It instruments your connection with prepared statements found by the generator.
The generated code already contains //go:generate instruction, so in order to update the statements list you can simply run:

```bash
$ go generate
```

# Some synthetic benchmarks

```bash
$ go test -bench=.
BenchmarkPostgresWithoutPreparedStatements-4   	   20000	     59941 ns/op	    1183 B/op	      32 allocs/op
BenchmarkPostgresWithPreparedStatements-4      	   50000	     41560 ns/op	    1021 B/op	      26 allocs/op
BenchmarkMySQLWithoutPreparedStatements-4      	   50000	     26454 ns/op	     827 B/op	      23 allocs/op
BenchmarkMySQLWithPreparedStatements-4         	  200000	      9509 ns/op	     634 B/op	      19 allocs/op
PASS
ok  	github.com/hexdigest/prep	7.884s
```


prep

rwdb provides read replica capability for multiple database servers setup

# rwdb

[![Build Status](https://travis-ci.org/andizzle/rwdb.svg?branch=master)](https://travis-ci.org/andizzle/rwdb)
[![GoDoc](https://godoc.org/github.com/andizzle/rwdb?status.svg)](https://godoc.org/github.com/andizzle/rwdb)
[![Coverage Status](https://coveralls.io/repos/github/andizzle/rwdb/badge.svg?branch=master)](https://coveralls.io/github/andizzle/rwdb?branch=master)
[![Go Report Card](https://goreportcard.com/badge/github.com/andizzle/rwdb)](https://goreportcard.com/report/github.com/andizzle/rwdb)

Database wrapper that manage read write connections

## Install

```
go get github.com/andizzle/rwdb
```

## Create connections

```go
package main

import "github.com/andizzle/rwdb"

var conns = []string{
        "tcp://user:pass@write/dbname",
        "tcp://user:pass@read1/dbname",
        "tcp://user:pass@read2/dbname",
        "tcp://user:pass@read3/dbname",
}


// unable to open write will cause an error
db, err := rwdb.Open("driver", conns...)
```

## Rotation read and Sticky read

Query a database rotate the use of database connections

### Basic usage

```go
db, err := rwdb.Open("driver", conns...)

// Use the first connection
db.QueryContext(ctx)

// Use the next connection
db.QueryContext(ctx)
```

Execute a statement will cause all subsequent queries to use the write connection (sticky connection). This is to allow the 
immediate reading of records that have been written to the database during the current request cycle. 


```go
db, err := rwdb.Open("driver", conns...)

// Use the next connection
db.QueryContext(ctx)

// Use the write conenction
db.ExecContext(ctx)

// Use the write connection
db.Query()
```

Sticky can be turned off
```go
db.SetSticky(false)
```

### A more practical example

The db is marked as modified if there's a successful `Write` to the databse, which turns on the sticky logic. 
However, the real world usecase would require `modified` value to be reset on each request session.

Here's what we can do:

```go
db, err := rwdb.Open("driver", conns...)

func RecordUserLogin() {
        d := db.New()       // This will make sure the following read are not affected by 
                            // other sessions' write action

        d.Query("SELECT * from `users` where id = ?")
        ...
        d.Exec("UPDATE `users` set last_login_at = now();")
        d.Query(...)        // Connection is set to the writer
}
```

## License

[License](https://github.com/andizzle/rwdb/blob/master/LICENSE)


rwdb

Running SQL in production with ACLs, logs, and shared links

# Database Gateway

[![Go Reference](https://pkg.go.dev/badge/github.com/kazhuravlev/database-gateway.svg)](https://pkg.go.dev/github.com/kazhuravlev/database-gateway)
[![License](https://img.shields.io/github/license/kazhuravlev/database-gateway?color=blue)](https://github.com/kazhuravlev/database-gateway/blob/master/LICENSE)
[![Test Status](https://github.com/kazhuravlev/database-gateway/actions/workflows/test.yml/badge.svg)](https://github.com/kazhuravlev/database-gateway/actions/workflows/test.yml)
[![Release Status](https://github.com/kazhuravlev/database-gateway/actions/workflows/release.yml/badge.svg)](https://github.com/kazhuravlev/database-gateway/actions/workflows/release.yml)
[![Go Report Card](https://goreportcard.com/badge/github.com/kazhuravlev/database-gateway)](https://goreportcard.com/report/github.com/kazhuravlev/database-gateway)
[![codecov](https://codecov.io/gh/kazhuravlev/database-gateway/graph/badge.svg?token=DLOML3FTN1)](https://codecov.io/gh/kazhuravlev/database-gateway)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go#database-tools)

This service provides a unified web interface for secure, controlled access to company databases. It enables employees
to run queries on `production` databases while enforcing Open Policy Agent (`OPA`) policies. For example, team leads may
have permissions to execute both `SELECT` and `INSERT` queries on certain tables, while other team members are
restricted to read-only (`SELECT`) access. This approach ensures that database interactions are managed safely and
that each user's access is tailored to their role and responsibilities.

## TL;DR

- Run approved SQL against multiple PostgreSQL targets from one web UI.
- Authenticate users via OIDC and enforce OPA rules by user, target, operation, and table.
- Store query results (with shareable links and execution metadata) for debugging and auditing.

## Table of Contents

- [TL;DR](#tldr)
- [Architecture Overview](#architecture-overview)
- [Quickstart with example setup](#quickstart-with-example-setup)
- [Features](#features)
- [Advanced Configuration](#advanced-configuration)
- [Performance Optimizations](#performance-optimizations)
- [Security Considerations](#security-considerations)
- [Edge Cases and Troubleshooting](#edge-cases-and-troubleshooting)
- [Interesting projects](#interesting-projects)

## Architecture Overview

This application acts as a secure gateway to multiple PostgreSQL instances, allowing authenticated users to run approved
queries through a unified web interface, with fine-grained OPA policies controlling access.


```
                     ┌───────────────────────────┐
                     │ PROD      ┌─────────────┐ │
                     │       ┌───┤  Postgres1  │ │
  ┌────────┐    ┌────────┐   │   └─────────────┘ │
  │  USER  │────│  DBGW  │───┼                   │
  └────────┘    └────────┘   │   ┌─────────────┐ │
                     │       └───┤  Postgres2  │ │
                     │           └─────────────┘ │
                     └───────────────────────────┘
```

### Components

1. **Local PostgreSQL Database**:
    - Stores query results and user profiles.
    - Acts as a cache for query results, allowing unique links for debugging without re-execution.

2. **Remote PostgreSQL Instances**:
    - Host production data and are accessed only through the app.
    - Queries are run only if authorized by OPA policies, limiting access to specific users, tables, and query types.

3. **OIDC Authentication**:
    - Users authenticate via an external OIDC provider.
    - User roles are mapped to OPA subjects, defining what queries each user can run.

4. **OPA Policies**:
    - Define user permissions at the instance, table, and query type levels.
    - Loaded from `.rego` files on disk and evaluated inside the gateway.

5. **Web Interface**:
    - Provides login, query submission, and result viewing.
    - Shows error feedback for unauthorized or restricted queries.

### Flow of Operations

1. **Authentication**: Users log in via OIDC, and their identity maps to OPA subjects.
2. **Query Submission**: Authorized queries are checked against OPA policies, then run on remote instances.
3. **Result Caching**: Results are stored locally with unique links for easy access and debugging.

This architecture ensures secure, controlled access to production data, balancing usability with data protection.

## Quickstart with example setup

Run commands to get a local dbgw instance with 3 PostgreSQL instances.

```shell
git clone https://github.com/kazhuravlev/database-gateway.git
cd database-gateway/example
docker compose up --pull always --force-recreate -d
open 'http://localhost:8080'
# Authentik and test users are bootstrapped automatically.
```

The example setup uses self-hosted Authentik as the OIDC provider.
OPA policies are loaded from `example/opa/basic/` and configured in [config.json](example/config.json).
Use `localhost` consistently for the example login flow, because the example OIDC redirect URL is
`http://localhost:8080/auth/callback`.

Bootstrap details for local Authentik:

1. OIDC app is created from `example/authentik-blueprint.yaml`.
2. Static users are created with password `password`:
   - `admin@example.com`
   - `user1@example.com`
3. `admin@example.com` belongs to `dbgw-admins` and `dbgw-users`; `user1@example.com` belongs to `dbgw-users`.
4. Authentik admin user:
   - `akadmin@example.com` / `password`

![pic1_instances.png](example/list_instances.png)

Choose `local-1`, run this query `select id, name from clients`, then click `Run`. ![pic2_run.png](example/instance.png)

Admins can inspect recent stored requests and open a detailed result view with execution metadata and exported formats.

![admin-query-list.png](example/admin-query-list.png)

![admin-query-inspect.png](example/admin-query-inspect.png)

## Features

### Security & Access Control

- [x] Integrates with OpenID Connect for user authentication
- [x] Enforces access filtering through OPA
- [x] Fine-grained table-level permissions
- [x] Schema-backed column allowlists
- [x] SQL parsing to enforce query type restrictions (SELECT, INSERT, etc.)
- [x] Query validation and sanitization
- [x] Session management with token expiration
- [x] Secure cookie handling

### Query UX

- [x] Supports any PostgreSQL wire-protocol database
- [x] Interactive web UI with keyboard shortcuts (Shift+Enter to run queries)
- [x] Provides query result output in HTML format
- [x] Provides query result output in JSON format
- [x] Query bookmarks (save, list, run, delete)
- [x] Recent queries feed on the main page (last 50 per user) with quick result access
- [x] Unique links for query results (useful for debugging)

### LRPC API

LRPC endpoint is exposed on the same port as the web facade:

- `GET /api/v1/token` (returns current session access token for frontend app)
- `POST /api/v1/:method`
- `GET /api/v1/schema`
- `GET /api/v1/query-results/export/:token`

Available methods:

- `targets.list.v1` - list user-available targets
- `targets.get.v1` - get a single target by `target_id`
- `bookmarks.list.v1` - list all bookmarks, or filter by optional `target_id`
- `bookmarks.add.v1` - save a bookmark for `target_id`, `title`, and `query`
- `bookmarks.delete.v1` - delete a bookmark by `id`
- `queries.list.v1` - list recent queries, with optional `limit`
- `query.run.v1` - run query for a target and return table data
- `query-results.get.v1` - get stored query result by `query_result_id`; users can read their own results and admins can read any user's result
- `query-results.export-link.v1` - issue a short-lived export link for `json` or `csv`

Download endpoints:

- `/api/v1/query-results/export/:token` - download an exported file using a short-lived signed token

All API requests require an OIDC access token in the header:

```json
Authorization: Bearer <access_token>
```

`params` are method-specific. User identity and role are resolved from the verified token claims.

### Observability & Performance

- [x] Includes query execution stats in results (full round trip, parsing time, network round trip)
- [x] Connection pooling for performance optimization

## Advanced Configuration

### Authentication

The service uses OIDC authentication:

```json
{
  "users": {
    "client_id": "db-gateway",
    "client_secret": "db-gateway-secret",
    "issuer_url": "http://localhost:9000/application/o/db-gateway/",
    "redirect_url": "http://localhost:8080/auth/callback",
    "access_token_audience": "db-gateway",
    "scopes": ["groups", "email", "profile"],
    "role_claim": "groups",
    "role_mapping": {
      "dbgw-admins": "admin",
      "dbgw-users": "user"
    }
  }
}
```

`access_token_audience` is optional. If omitted, `client_id` is used for access-token audience validation.

### Policy Configuration

OPA policy bundles are loaded from disk:

```json
{
  "policy": {
    "path": "./opa/basic"
  }
}
```

Each `.rego` file in the configured directory is compiled into the embedded OPA authorizer. Policies must define:

- `data.gateway.allow_target`
- `data.gateway.allow_query`

`policy.path` is resolved relative to the config file when it is not absolute.

Current OPA input:

```json
{
  "subjects": ["user:alice@example.com", "role:user"],
  "target": "local-1",
  "op": "select",
  "table": "public.clients"
}
```

Notes:

- `subjects` always includes both the concrete user principal and the mapped role principal
- `table` is always sent to OPA in canonical `schema.table` form
- unqualified SQL like `select id from clients` is normalized before policy evaluation
- policies run once for target visibility and once for each parsed query vector

### Database Connection Settings

Configure performance settings for each database connection:

```json
{
  "connection": {
    "host": "postgres1",
    "port": 5432,
    "user": "pg01",
    "password": "pg01",
    "db": "pg01",
    "use_ssl": false,
    "max_pool_size": 4
  }
}
```

For a complete working config, see [example/config.json](example/config.json).

## Performance Optimizations

- **Connection Pooling**: Configurable connection pool sizes for each database target
- **Query Result Caching**: Results are stored in the local database for later reference
- **Efficient Query Execution**: Parsed and validated for optimal performance

## Security Considerations

- **SQL Injection Protection**: All queries are parsed and validated before execution
- **No Direct Database Access**: Remote databases are only accessible through the gateway
- **Column-Level Restrictions**: schema validation limits which fields users can query
- **Query Type Restrictions**: Limit users to specific operations (SELECT, INSERT, etc.)
- **Session Security**: Secure cookie handling with configurable expiration
- **Error Handling**: Error messages are sanitized to prevent information leakage

## Edge Cases and Troubleshooting

- **Multiple Schema Support**: Tables can be specified with schema names (`schema.table`)
- **Complex Query Handling**: Some complex queries might be rejected by the parser
- **Connection Failures**: The service gracefully handles database connection failures
- **Missing Tables/Fields**: Queries referencing unknown tables or fields are rejected
- **Policy Compilation Errors**: invalid `.rego` files fail startup

## Interesting projects

- https://github.com/antlr/grammars-v4/tree/master/sql/postgresql/Go
- https://github.com/auxten/postgresql-parser
- https://github.com/blastrain/vitess-sqlparser
- https://github.com/cockroachdb/cockroach/pkg/sql/parser
- https://github.com/pganalyze/pg_query_go/
- https://github.com/pingcap/tidb/tree/master/pkg/parser
- https://github.com/topics/sql-parser?l=go
- https://github.com/vitessio/vitess
- https://github.com/xwb1989/sqlparser


database-gateway

High-performance SQL parser, formatter, linter, and security scanner with multi-dialect support and WASM playground

# GoSQLX

<div align="center">

<img src="https://raw.githubusercontent.com/ajitpratap0/GoSQLX/main/.github/logo.png" alt="GoSQLX Logo" width="180"/>

### Parse SQL at the speed of Go

[![Go Version](https://img.shields.io/badge/Go-1.26+-00ADD8?style=for-the-badge&logo=go)](https://go.dev)
[![Release](https://img.shields.io/github/v/release/ajitpratap0/GoSQLX?style=for-the-badge&color=orange)](https://github.com/ajitpratap0/GoSQLX/releases)
[![License](https://img.shields.io/badge/License-Apache--2.0-blue.svg?style=for-the-badge)](LICENSE)
[![PRs Welcome](https://img.shields.io/badge/PRs-welcome-brightgreen.svg?style=for-the-badge)](http://makeapullrequest.com)

[![Website](https://img.shields.io/badge/gosqlx.dev-Website-blue?style=for-the-badge&logo=google-chrome)](https://gosqlx.dev)
[![VS Code](https://img.shields.io/visual-studio-marketplace/v/ajitpratap0.gosqlx?style=for-the-badge&logo=visual-studio-code&label=VS%20Code)](https://marketplace.visualstudio.com/items?itemName=ajitpratap0.gosqlx)
[![MCP](https://img.shields.io/badge/MCP-Remote%20Server-blue?style=for-the-badge&logo=cloud)](https://mcp.gosqlx.dev/health)
[![Glama MCP Server](https://glama.ai/mcp/servers/ajitpratap0/GoSQLX/badges/score.svg)](https://glama.ai/mcp/servers/ajitpratap0/GoSQLX)
[![Lint Action](https://img.shields.io/badge/Lint%20Action-GitHub-blue?style=for-the-badge&logo=github)](https://github.com/marketplace/actions/gosqlx-lint-action)

[![Tests](https://img.shields.io/github/actions/workflow/status/ajitpratap0/GoSQLX/test.yml?branch=main&label=Tests&style=flat-square)](https://github.com/ajitpratap0/GoSQLX/actions)
[![Go Report](https://goreportcard.com/badge/github.com/ajitpratap0/GoSQLX?style=flat-square)](https://goreportcard.com/report/github.com/ajitpratap0/GoSQLX)
[![GoDoc](https://pkg.go.dev/badge/github.com/ajitpratap0/GoSQLX?style=flat-square)](https://pkg.go.dev/github.com/ajitpratap0/GoSQLX)
[![Stars](https://img.shields.io/github/stars/ajitpratap0/GoSQLX?style=social)](https://github.com/ajitpratap0/GoSQLX)
[![OpenSSF Scorecard](https://api.securityscorecards.dev/projects/github.com/ajitpratap0/GoSQLX/badge)](https://securityscorecards.dev/viewer/?uri=github.com/ajitpratap0/GoSQLX)

<br/>

**[🌐 Try the Playground](https://gosqlx.dev/playground/)** &nbsp;·&nbsp; **[📖 Read the Docs](https://gosqlx.dev/docs/)** &nbsp;·&nbsp; **[🚀 Get Started](docs/GETTING_STARTED.md)** &nbsp;·&nbsp; **[📊 Benchmarks](https://gosqlx.dev/benchmarks/)**

<br/>

| **1.38M+ ops/sec** | **<1μs latency** | **85% SQL-99** | **8 dialects** | **0 race conditions** |
|:---:|:---:|:---:|:---:|:---:|

</div>

<br/>

## What is GoSQLX?

GoSQLX is a **production-ready SQL parsing SDK** for Go. It tokenizes, parses, and generates ASTs from SQL with zero-copy optimizations and intelligent object pooling - handling **1.38M+ operations per second** with sub-microsecond latency.

```go
ast, _ := gosqlx.Parse("SELECT u.name, COUNT(*) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name")
// → Full AST with statements, columns, joins, grouping - ready for analysis, transformation, or formatting
```

### Why GoSQLX?

- **Not an ORM** - a parser. You get the AST, you decide what to do with it.
- **Not slow** - zero-copy tokenization, sync.Pool recycling, no allocations on hot paths.
- **Not limited** - PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, ClickHouse. CTEs, window functions, MERGE, set operations.
- **Not just a library** - CLI, VS Code extension, GitHub Action, MCP server, WASM playground, Python bindings.

<br/>

## Get Started in 60 Seconds

```bash
go get github.com/ajitpratap0/GoSQLX
```

```go
package main

import (
    "fmt"
    "github.com/ajitpratap0/GoSQLX/pkg/gosqlx"
)

func main() {
    // Parse any SQL dialect
    ast, _ := gosqlx.Parse("SELECT * FROM users WHERE active = true")
    fmt.Printf("%d statement(s)\n", len(ast.Statements))

    // Format messy SQL
    clean, _ := gosqlx.Format("select id,name from users where id=1", gosqlx.DefaultFormatOptions())
    fmt.Println(clean)
    // SELECT
    //   id,
    //   name
    // FROM users
    // WHERE id = 1

    // Catch errors before production
    if err := gosqlx.Validate("SELECT * FROM"); err != nil {
        fmt.Println(err) // → expected table name
    }
}
```

<br/>

## Install Everywhere

<table>
<tr>
<td width="50%">

### 📦 Go Library
```bash
go get github.com/ajitpratap0/GoSQLX
```

### 🖥️ CLI Tool
```bash
go install github.com/ajitpratap0/GoSQLX/cmd/gosqlx@latest
gosqlx validate "SELECT * FROM users"
gosqlx format query.sql
gosqlx lint query.sql
```

</td>
<td width="50%">

### 💻 VS Code Extension
```bash
code --install-extension ajitpratap0.gosqlx
```
Bundles the binary - zero setup. [Learn more →](https://gosqlx.dev/vscode/)

### 🤖 MCP Server (AI Integration)
```bash
claude mcp add --transport http gosqlx \
  https://mcp.gosqlx.dev/mcp
```
7 SQL tools in Claude, Cursor, or any MCP client. [Guide →](https://gosqlx.dev/docs/mcp_guide/)

</td>
</tr>
</table>

<br/>

## Features at a Glance

<table>
<tr>
<td align="center" width="33%"><h3>⚡ Parser</h3>Zero-copy tokenizer<br/>Recursive descent parser<br/>Full AST generation<br/>Multi-dialect engine</td>
<td align="center" width="33%"><h3>🛡️ Analysis</h3>SQL injection scanner<br/>10 lint rules (L001–L010)<br/>Query complexity scoring<br/>Metadata extraction</td>
<td align="center" width="33%"><h3>🔧 Tooling</h3>AST-based formatter<br/>Query transforms API<br/>VS Code extension<br/>GitHub Action</td>
</tr>
<tr>
<td align="center"><h3>🌐 Multi-Dialect</h3>PostgreSQL · MySQL · MariaDB<br/>SQL Server · Oracle<br/>SQLite · Snowflake · ClickHouse</td>
<td align="center"><h3>🤖 AI-Ready</h3>MCP server (7 tools)<br/>Public remote endpoint<br/>Streamable HTTP</td>
<td align="center"><h3>🧪 Battle-Tested</h3>20K+ concurrent ops<br/>Zero race conditions<br/>~85% SQL-99 compliance</td>
</tr>
</table>

<br/>

## Documentation

| | Resource | Description |
|---|---|---|
| 🌐 | **[gosqlx.dev](https://gosqlx.dev)** | Website with interactive playground |
| 🚀 | **[Getting Started](https://gosqlx.dev/docs/getting_started/)** | Parse your first SQL in 5 minutes |
| 📖 | **[Usage Guide](https://gosqlx.dev/docs/usage_guide/)** | Comprehensive patterns and examples |
| 📄 | **[API Reference](https://gosqlx.dev/docs/api_reference/)** | Complete API documentation |
| 🖥️ | **[CLI Guide](https://gosqlx.dev/docs/cli_guide/)** | Command-line tool reference |
| 🌍 | **[SQL Compatibility](https://gosqlx.dev/docs/sql_compatibility/)** | Dialect support matrix |
| 🤖 | **[MCP Guide](https://gosqlx.dev/docs/mcp_guide/)** | AI assistant integration |
| 🏗️ | **[Architecture](https://gosqlx.dev/docs/architecture/)** | System design deep-dive |
| 📊 | **[Benchmarks](https://gosqlx.dev/benchmarks/)** | Performance data and methodology |
| 📝 | **[Release Notes](https://gosqlx.dev/blog/)** | What's new in each version |

<br/>

## Contributing

GoSQLX is built by contributors like you. Whether it's a bug fix, new feature, documentation improvement, or just a typo - every contribution matters.

```bash
git clone https://github.com/ajitpratap0/GoSQLX.git && cd GoSQLX
task check    # fmt → vet → lint → test (with race detection)
```

1. **Fork & branch** from `main`
2. **Write tests** - we use TDD and require race-free code
3. **Run `task check`** - must pass before PR
4. **Open a PR** - we review within 24 hours

📋 [Contributing Guide](CONTRIBUTING.md) · 📜 [Code of Conduct](CODE_OF_CONDUCT.md) · 🏛️ [Governance](GOVERNANCE.md)

<br/>

## Who's Using GoSQLX?

GoSQLX is downloaded and cloned by developers worldwide -- 595 unique cloners in just 14 days. If you're using GoSQLX in your project or organization, we'd love to hear about it!

| Project / Company | Use Case |
|---|---|
| *Your project here* | [Add yourself via PR](https://github.com/ajitpratap0/GoSQLX/edit/main/README.md) or [tell us in Discussions](https://github.com/ajitpratap0/GoSQLX/discussions) |

Using GoSQLX at work? Building something cool with it? Share your story in [GitHub Discussions](https://github.com/ajitpratap0/GoSQLX/discussions) -- it helps the community grow and motivates continued development.

<br/>

## Community

<div align="center">

**Got questions? Ideas? Found a bug?**

<a href="https://github.com/ajitpratap0/GoSQLX/discussions"><img src="https://img.shields.io/badge/💬_Discussions-Ask_&_Share-purple?style=for-the-badge" alt="Discussions"></a>
<a href="https://github.com/ajitpratap0/GoSQLX/issues/new/choose"><img src="https://img.shields.io/badge/🐛_Issues-Report_&_Request-red?style=for-the-badge" alt="Issues"></a>
<a href="https://gosqlx.dev/blog/"><img src="https://img.shields.io/badge/📝_Blog-Release_Notes-green?style=for-the-badge" alt="Blog"></a>

</div>

<br/>

## License

Apache License 2.0 - see [LICENSE](LICENSE) for details.

---

<div align="center">

<sub>Built with ❤️ by the GoSQLX community</sub>

**[gosqlx.dev](https://gosqlx.dev)** · **[Playground](https://gosqlx.dev/playground/)** · **[Docs](https://gosqlx.dev/docs/)** · **[MCP Server](https://mcp.gosqlx.dev/mcp)** · **[VS Code](https://marketplace.visualstudio.com/items?itemName=ajitpratap0.gosqlx)**

<br/>

If GoSQLX helps your project, consider giving it a ⭐

</div>


GoSQLX

vitess provides servers and tools which facilitate scaling of MySQL databases for large scale web services

[![Maven Central](https://maven-badges.sml.io/maven-central/io.vitess/vitess-jdbc/badge.svg)](https://maven-badges.herokuapp.com/maven-central/io.vitess/vitess-jdbc)
[![Coverage Status](https://codecov.io/gh/vitessio/vitess/branch/main/graph/badge.svg)](https://app.codecov.io/gh/vitessio/vitess/tree/main)
[![Go Report Card](https://goreportcard.com/badge/vitess.io/vitess)](https://goreportcard.com/report/vitess.io/vitess)
[![FOSSA Status](https://app.fossa.com/api/projects/custom%2B162%2Fvitess.svg?type=shield&issueType=license)](https://app.fossa.com/projects/custom%2B162%2Fvitess?ref=badge_shield&issueType=license)
[![CII Best Practices](https://bestpractices.coreinfrastructure.org/projects/1724/badge)](https://bestpractices.coreinfrastructure.org/projects/1724)
[![OpenSSF Scorecard](https://api.scorecard.dev/projects/github.com/vitessio/vitess/badge)](https://scorecard.dev/viewer/?uri=github.com/vitessio/vitess)

# Vitess 

Vitess is a cloud-native horizontally-scalable distributed database system that is built around MySQL.
Vitess can achieve unlimited scaling through generalized sharding.

Vitess allows application code and database queries to remain agnostic to the distribution of data onto
multiple database servers. With Vitess, you can even split and merge shards as your needs
grow, with an atomic cutover step that takes only a few seconds.

Vitess was a core component of YouTube's database infrastructure
from 2011, and grew to encompass tens of thousands of MySQL nodes. 
Starting in 2015, Vitess was adopted by many other large companies, including Slack, Square (now Block), and JD.com.

For more about Vitess, please visit [vitess.io](https://vitess.io).

## Community

Vitess has a growing [community](https://github.com/vitessio/vitess/blob/main/ADOPTERS.md).

If you are interested in contributing or participating in our monthly community meetings, please visit the [Community page on our website](https://vitess.io/community/).

We also maintain a [roadmap](https://vitess.io/docs/roadmap/) on our website.

Follow our [blog](https://blog.vitess.io/) for low-frequency updates like new features and releases.

## Reporting a Problem, Issue, or Bug

To report a problem, create a [GitHub issue](https://github.com/vitessio/vitess/issues).

For topics that are better discussed live, please join the [Vitess Slack](https://vitess.io/slack) workspace.
You may post any questions on the #general channel or join some of the special-interest channels.

## Security

### Reporting Security Vulnerabilities

To report a security vulnerability, please email [vitess-maintainers](mailto:cncf-vitess-maintainers@lists.cncf.io).

See [Security](SECURITY.md) for a full outline of the security process.

### Security Audit

A third party security audit was performed by ADA Logics. [Read the full report](doc/VIT-03-report-security-audit.pdf).

## License

Unless otherwise noted, the Vitess source files are distributed
under the Apache Version 2.0 license found in the LICENSE file.

[![FOSSA Status](https://app.fossa.com/api/projects/custom%2B162%2Fvitess.svg?type=large&issueType=license)](https://app.fossa.com/projects/custom%2B162%2Fvitess?ref=badge_large&issueType=license)


The Best Go Libraries For Database - Database Tools (19)