sqle

go-mysql-server

go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax) and executes queries on data sources of your choice. A simple in-memory database and table implementation are provided, and you can query any data source you want by implementing a few interfaces.

go-mysql-server also provides a server implementation compatible with the MySQL wire protocol. That means it is compatible with MySQL ODBC, JDBC, or the default MySQL client shell interface.

Scope of this project

These are the goals of go-mysql-server:

• Be a generic extensible SQL engine that performs queries on your data sources.
• Provide a simple database implementation suitable for use in tests.
• Define interfaces you can implement to query your own data sources.
• Provide a runnable server speaking the MySQL wire protocol, connected to data sources of your choice.
• Optimize query plans.
• Allow implementators to add their own analysis steps and optimizations.
• Support indexed lookups and joins on data tables that support them.
• Support external index driver implementations such as pilosa.
• Provide a common index driver implementation.
• With few caveats and using a full database implementation, be a drop-in MySQL database replacement.

Non-goals of go-mysql-server:

• Be an application/server you can use directly.
• Provide any kind of backend implementation (other than the memory one used for testing) such as json, csv, yaml. That's for clients to implement and use.

What's the use case of go-mysql-server?

go-mysql-server has two primary uses case:

1. Stand-in for MySQL in a golang test environment, using the built-in memory database implementation.

2. Providing access to aribtrary data sources with SQL queries by implementing a handful of interfaces. The most complete real-world implementation is Dolt.

Installation

The import path for the package is github.com/fabiuslabs/go-mysql-server.

To install it, run:

go get github.com/fabiuslabs/go-mysql-server

go-mysql-server relies on a fork of vitess, which implements the SQL parsing and SQL server for the engine. To use this fork of vitess, you must include the same replace directive in your project's go.mod file as this package does (see the go.mod file for the exact line to copy and paste):

replace vitess.io/vitess => github.com/liquidata-inc/vitess v0.0.0-20200430040751-192bb76ecd8b

Go Documentation

• go-mysql-server godoc

SQL syntax

The goal of go-mysql-server is to support 100% of the statements that MySQL does. We are continuously adding more functionality to the engine, but not everything is supported yet. To see what is currently included check the SUPPORTED file.

Third-party clients

We support and actively test against certain third-party clients to ensure compatibility between them and go-mysql-server. You can check out the list of supported third party clients in the SUPPORTED_CLIENTS file along with some examples on how to connect to go-mysql-server using them.

Available functions

Configuration

The behaviour of certain parts of go-mysql-server can be configured using either environment variables or session variables.

Session variables are set using the following SQL queries:

SET <variable name> = <value>

Example

go-mysql-server contains a SQL engine and server implementation. So, if you want to start a server, first instantiate the engine and pass your sql.Database implementation.

It will be in charge of handling all the logic to retrieve the data from your source. Here you can see an example using the in-memory database implementation:

func main() {
    driver := sqle.NewDefault()
    driver.AddDatabase(createTestDatabase())

    config := server.Config{
        Protocol: "tcp",
        Address:  "localhost:3306",
        Auth:     auth.NewNativeSingle("user", "pass", auth.AllPermissions),
    }

    s, err := server.NewDefaultServer(config, driver)
    if err != nil {
        panic(err)
    }

    s.Start()
}

func createTestDatabase() *memory.Database {
    const (
        dbName    = "test"
        tableName = "mytable"
    )

    db := memory.NewDatabase(dbName)
    table := memory.NewTable(tableName, sql.Schema{
        {Name: "name", Type: sql.Text, Nullable: false, Source: tableName},
        {Name: "email", Type: sql.Text, Nullable: false, Source: tableName},
        {Name: "phone_numbers", Type: sql.JSON, Nullable: false, Source: tableName},
        {Name: "created_at", Type: sql.Timestamp, Nullable: false, Source: tableName},
    })

    db.AddTable(tableName, table)
    ctx := sql.NewEmptyContext()

    rows := []sql.Row{
        sql.NewRow("John Doe", "john@doe.com", []string{"555-555-555"}, time.Now()),
        sql.NewRow("John Doe", "johnalt@doe.com", []string{}, time.Now()),
        sql.NewRow("Jane Doe", "jane@doe.com", []string{}, time.Now()),
        sql.NewRow("Evil Bob", "evilbob@gmail.com", []string{"555-666-555", "666-666-666"}, time.Now()),
	}

    for _, row := range rows {
        table.Insert(ctx, row)
    }

    return db
}

Then, you can connect to the server with any MySQL client:

> mysql --host=127.0.0.1 --port=3306 -u user -ppass test -e "SELECT * FROM mytable"
+----------+-------------------+-------------------------------+---------------------+
| name     | email             | phone_numbers                 | created_at          |
+----------+-------------------+-------------------------------+---------------------+
| John Doe | john@doe.com      | ["555-555-555"]               | 2018-04-18 10:42:58 |
| John Doe | johnalt@doe.com   | []                            | 2018-04-18 10:42:58 |
| Jane Doe | jane@doe.com      | []                            | 2018-04-18 10:42:58 |
| Evil Bob | evilbob@gmail.com | ["555-666-555","666-666-666"] | 2018-04-18 10:42:58 |
+----------+-------------------+-------------------------------+---------------------+

See the complete example here.

Queries examples

SELECT count(name) FROM mytable
+---------------------+
| COUNT(mytable.name) |
+---------------------+
|                   4 |
+---------------------+

SELECT name,year(created_at) FROM mytable
+----------+--------------------------+
| name     | YEAR(mytable.created_at) |
+----------+--------------------------+
| John Doe |                     2018 |
| John Doe |                     2018 |
| Jane Doe |                     2018 |
| Evil Bob |                     2018 |
+----------+--------------------------+

SELECT email FROM mytable WHERE name = 'Evil Bob'
+-------------------+
| email             |
+-------------------+
| evilbob@gmail.com |
+-------------------+

Custom data source implementation

To create your own data source implementation you need to implement the following interfaces:

• sql.Database interface. This interface will provide tables from your data source. You can also implement other interfaces on your database to unlock additional functionality:

  • sql.TableCreator to support creating new tables
  • sql.TableDropper to support dropping tables
  • sql.TableRenamer to support renaming tables
  • sql.ViewCreator to support creating persisted views on your tables
  • sql.ViewDropper to support dropping persisted views

• sql.Table interface. This interface will provide rows of values from your data source. You can also implement other interfaces on your table to unlock additional functionality:

  • sql.InsertableTable to allow your data source to be updated with INSERT statements.
  • sql.UpdateableTable to allow your data source to be updated with UPDATE statements.
  • sql.DeletableTable to allow your data source to be updated with DELETE statements.
  • sql.ReplaceableTable to allow your data source to be updated with REPLACE statements.
  • sql.AlterableTable to allow your data source to have its schema modified by adding, dropping, and altering columns.
  • sql.IndexedTable to declare your table's native indexes to speed up query execution.
  • sql.IndexAlterableTable to accept the creation of new native indexes.
  • sql.ForeignKeyAlterableTable to signal your support of foreign key constraints in your table's schema and data.
  • sql.ProjectedTable to return rows that only contain a subset of the columns in the table. This can make query execution faster.
  • sql.FilteredTable to filter the rows returned by your table to those matching a given expression. This can make query execution faster (if your table implementation can filter rows more efficiently than checking an expression on every row in a table).

You can see a really simple data source implementation in the memory package.

Testing your data source implementation

go-mysql-server provides a suite of engine tests that you can use to validate that your implementation works as expected. See the enginetest package for details and examples.

Indexes

go-mysql-server exposes a series of interfaces to allow you to implement your own indexes so you can speed up your queries.

Native indexes

Tables can declare that they support native indexes, which means that they support efficiently returning a subset of their rows that match an expression. The memory package contains an example of this behavior, but please note that it is only for example purposes and doesn't actually make queries faster (although we could change this in the future).

Integrators should implement the sql.IndexedTable interface to declare which indexes their tables support and provide a means of returning a subset of the rows based on an sql.IndexLookup provided by their sql.Index implementation. There are a variety of extensions to sql.Index that can be implemented, each of which unlocks additional capabilities:

• sql.Index. Base-level interface, supporting equality lookups for an index.
• sql.AscendIndex. Adds support for > and >= indexed lookups.
• sql.DescendIndex. Adds support for < and <= indexed lookups.
• sql.NegateIndex. Adds support for negating other index lookups.
• sql.MergeableIndexLookup. Adds support for merging two sql.IndexLookups together to create a new one, representing AND and OR expressions on indexed columns.

Custom index driver implementation

Index drivers provide different backends for storing and querying indexes, without the need for a table to storing and querying its own native indexes. To implement a custom index driver you need to implement a few things:

• sql.IndexDriver interface, which will be the driver itself. Not that your driver must return an unique ID in the ID method. This ID is unique for your driver and should not clash with any other registered driver. It's the driver's responsibility to be fault tolerant and be able to automatically detect and recover from corruption in indexes.
• sql.Index interface, returned by your driver when an index is loaded or created.
  • Your sql.Index may optionally implement the sql.AscendIndex and/or sql.DescendIndex interfaces, if you want to support more comparison operators like >, <, >=, <= or BETWEEN.
• sql.IndexLookup interface, returned by your index in any of the implemented operations to get a subset of the indexed values.
  • Your sql.IndexLookup may optionally implement the sql.Mergeable and sql.SetOperations interfaces if you want to support set operations to merge your index lookups.
• sql.IndexValueIter interface, which will be returned by your sql.IndexLookup and should return the values of the index.
• Don't forget to register the index driver in your sql.Catalog using catalog.RegisterIndexDriver(mydriver) to be able to use it.

To create indexes using your custom index driver you need to use extension syntax USING driverid on the index creation statement. For example:

CREATE INDEX foo ON table USING driverid (col1, col2)

You can see an example of a driver implementation inside the sql/index/pilosa package, where the pilosa driver is implemented.

Included pilosa index driver

go-mysql-server includes on index driver, pilosa, which does not require an external pilosa server. pilosa is not supported on Windows.

Metrics

go-mysql-server utilizes github.com/go-kit/kit/metrics module to expose metrics (counters, gauges, histograms) for certain packages (so far for engine, analyzer, regex, pilosa). If you already have metrics server (prometheus, statsd/statsite, influxdb, etc.) and you want to gather metrics also from go-mysql-server components, you will need to initialize some global variables by particular implementations to satisfy following interfaces:

// Counter describes a metric that accumulates values monotonically.
type Counter interface {
	With(labelValues ...string) Counter
	Add(delta float64)
}

// Gauge describes a metric that takes specific values over time.
type Gauge interface {
	With(labelValues ...string) Gauge
	Set(value float64)
	Add(delta float64)
}

// Histogram describes a metric that takes repeated observations of the same
// kind of thing, and produces a statistical summary of those observations,
// typically expressed as quantiles or buckets.
type Histogram interface {
	With(labelValues ...string) Histogram
	Observe(value float64)
}

You can use one of go-kit implementations or try your own. For instance, we want to expose metrics for prometheus server. So, before we start mysql engine, we have to set up the following variables:

import(
    "github.com/go-kit/kit/metrics/prometheus"
    promopts "github.com/prometheus/client_golang/prometheus"
    "github.com/prometheus/client_golang/prometheus/promhttp"
)

//....

// engine metrics
sqle.QueryCounter = prometheus.NewCounterFrom(promopts.CounterOpts{
		Namespace: "go_mysql_server",
		Subsystem: "engine",
		Name:      "query_counter",
	}, []string{
		"query",
	})
sqle.QueryErrorCounter = prometheus.NewCounterFrom(promopts.CounterOpts{
    Namespace: "go_mysql_server",
    Subsystem: "engine",
    Name:      "query_error_counter",
}, []string{
    "query",
    "error",
})
sqle.QueryHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "engine",
    Name:      "query_histogram",
}, []string{
    "query",
    "duration",
})

// analyzer metrics
analyzer.ParallelQueryCounter = prometheus.NewCounterFrom(promopts.CounterOpts{
    Namespace: "go_mysql_server",
    Subsystem: "analyzer",
    Name:      "parallel_query_counter",
}, []string{
    "parallelism",
})

// regex metrics
regex.CompileHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "regex",
    Name:      "compile_histogram",
}, []string{
    "regex",
    "duration",
})
regex.MatchHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "regex",
    Name:      "match_histogram",
}, []string{
    "string",
    "duration",
})

// pilosa index driver metrics
pilosa.RowsGauge = prometheus.NewGaugeFrom(promopts.GaugeOpts{
    Namespace: "go_mysql_server",
    Subsystem: "index",
    Name:      "indexed_rows_gauge",
}, []string{
    "driver",
})
pilosa.TotalHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "index",
    Name:      "index_created_total_histogram",
}, []string{
    "driver",
    "duration",
})
pilosa.MappingHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "index",
    Name:      "index_created_mapping_histogram",
}, []string{
    "driver",
    "duration",
})
pilosa.BitmapHistogram = prometheus.NewHistogramFrom(promopts.HistogramOpts{
    Namespace: "go_mysql_server",
    Subsystem: "index",
    Name:      "index_created_bitmap_histogram",
}, []string{
    "driver",
    "duration",
})

One important note - internally we set some labels for metrics, that's why have to pass those keys like "duration", "query", "driver", ... when we register metrics in prometheus. Other systems may have different requirements.

Powered by go-mysql-server

• dolt
• gitbase (defunct)

License

Apache License 2.0, see LICENSE