sqle

README

A MySQL compatible database engine written in pure Go

go-mysql-server is a data-source agnostic SQL engine and server which runs queries on data sources you provide, using the MySQL dialect and wire protocol. A simple in-memory database implementation is included, and you can query any data source you want by implementing your own backend.

Dolt, a SQL database with Git-style versioning, is the main production database implementation of this package. Check out that project for reference a implementation. Or, hop into the Dolt discord here if you want to talk to the core developers behind go-mysql-server and Dolt.

Compatibility

With the exception of specific limitations (see below), go-mysql-server is a drop-in replacement for MySQL. Any client library, tool, query, SQL syntax, SQL function, etc. that works with MySQL should also work with go-mysql-server. If you find a gap in functionality, please file an issue.

For full MySQL compatibility documentation, see the Dolt docs on this topic.

Scope of this project

• SQL server and engine to query your data sources.
• In-memory database backend implementation suitable for use in tests.
• Interfaces you can use to implement new backends to query your own data sources.
• With a few caveats and using a full database implementation, a drop-in MySQL database replacement.

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 arbitrary data sources with SQL queries by implementing a handful of interfaces. The most complete real-world implementation is Dolt.

Installation

Add go-mysql-server as a dependency to your project. In the directory with the go.mod file, run:

go get github.com/dolthub/go-mysql-server@latest

Using the in-memory test server

The in-memory test server can replace a real MySQL server in tests. Start the server using the code in the _example directory, also reproduced below.

package main

import (
	"fmt"
	"time"

	sqle "github.com/dolthub/go-mysql-server"
	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/server"
	"github.com/dolthub/go-mysql-server/sql"
	"github.com/dolthub/go-mysql-server/sql/types"
)

var (
	dbName    = "mydb"
	tableName = "mytable"
	address   = "localhost"
	port      = 3306
)

func main() {
	ctx := sql.NewEmptyContext()
	engine := sqle.NewDefault(
		memory.NewDBProvider(
			createTestDatabase(ctx),
		))

	// This variable may be found in the "users_example.go" file. Please refer to that file for a walkthrough on how to
	// set up the "mysql" database to allow user creation and user checking when establishing connections. This is set
	// to false for this example, but feel free to play around with it and see how it works.
	if enableUsers {
		if err := enableUserAccounts(ctx, engine); err != nil {
			panic(err)
		}
	}

	config := server.Config{
		Protocol: "tcp",
		Address:  fmt.Sprintf("%s:%d", address, port),
	}
	s, err := server.NewDefaultServer(config, engine)
	if err != nil {
		panic(err)
	}
	if err = s.Start(); err != nil {
		panic(err)
	}
}

func createTestDatabase(ctx *sql.Context) *memory.Database {
	db := memory.NewDatabase(dbName)
	db.EnablePrimaryKeyIndexes()
	table := memory.NewTable(tableName, sql.NewPrimaryKeySchema(sql.Schema{
		{Name: "name", Type: types.Text, Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "email", Type: types.Text, Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "phone_numbers", Type: types.JSON, Nullable: false, Source: tableName},
		{Name: "created_at", Type: types.Datetime, Nullable: false, Source: tableName},
	}), db.GetForeignKeyCollection())
	db.AddTable(tableName, table)

	creationTime := time.Unix(0, 1667304000000001000).UTC()
	_ = table.Insert(ctx, sql.NewRow("Jane Deo", "janedeo@gmail.com", types.MustJSON(`["556-565-566", "777-777-777"]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("Jane Doe", "jane@doe.com", types.MustJSON(`[]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("John Doe", "john@doe.com", types.MustJSON(`["555-555-555"]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("John Doe", "johnalt@doe.com", types.MustJSON(`[]`), creationTime))
	return db
}

This example populates the database by creating memory.Database and memory.Table objects via golang code, but you can also populate it by issuing CREATE DATABASE, CREATE TABLE, etc. statements to the server once it's running.

Once the server is running, connect with any MySQL client, including the golang MySQL connector and the mysql shell.

> mysql --host=localhost --port=3306 --user=root mydb --execute="SELECT * FROM mytable;"
+----------+-------------------+-------------------------------+----------------------------+
| name     | email             | phone_numbers                 | created_at                 |
+----------+-------------------+-------------------------------+----------------------------+
| Jane Deo | janedeo@gmail.com | ["556-565-566","777-777-777"] | 2022-11-01 12:00:00.000001 |
| Jane Doe | jane@doe.com      | []                            | 2022-11-01 12:00:00.000001 |
| John Doe | john@doe.com      | ["555-555-555"]               | 2022-11-01 12:00:00.000001 |
| John Doe | johnalt@doe.com   | []                            | 2022-11-01 12:00:00.000001 |
+----------+-------------------+-------------------------------+----------------------------+

Limitations of the in-memory database implementation

The in-memory database implementation included with this package is intended for use in tests. It has specific limitations that we know of:

• Not threadsafe. To avoid concurrency issues, limit DDL and DML statements (CREATE TABLE, INSERT, etc.) to a single goroutine.
• No transaction support. Statements like START TRANSACTION, ROLLBACK, and COMMIT are no-ops.
• Non-performant index implementation. Indexed lookups and joins perform full table scans on the underlying tables.

Custom backend implementations

You can create your own backend to query your own data sources by implementing some interfaces. For detailed instructions, see the backend guide.

Technical documentation for contributors and backend developers

• Architecture is an overview of the various packages of the project and how they fit together.
• Contribution guide for new contributors, including instructions for how to get your PR merged.

Powered by go-mysql-server

• dolt
• gitbase (defunct)

Are you building a database backend using go-mysql-server? We would like to hear from you and include you in this list.

Security Policy

go-mysql-server's security policy is maintained in this repository. Please follow the disclosure instructions there. Please do not initially report security issues in this repository's public GitHub issues.

Acknowledgements

go-mysql-server was originally developed by the {source-d} organzation, and this repository was originally forked from src-d. We want to thank the entire {source-d} development team for their work on this project, especially Miguel Molina (@erizocosmico) and Juanjo Álvarez Martinez (@juanjux).

License

Apache License 2.0, see LICENSE

Documentation

Index

• Variables
• type ColumnWithRawDefault
• type Config
• type Engine
  • func New(a *analyzer.Analyzer, cfg *Config) *Engine
  • func NewDefault(pro sql.DatabaseProvider) *Engine
  • func (e *Engine) AnalyzeQuery(ctx *sql.Context, query string) (sql.Node, error)
  • func (e *Engine) BoundQueryPlan(ctx *sql.Context, query string, parsed sqlparser.Statement, ...) (sql.Node, error)
  • func (e *Engine) Close() error
  • func (e *Engine) CloseSession(connID uint32)
  • func (e *Engine) EngineAnalyzer() *analyzer.Analyzer
  • func (e *Engine) EnginePreparedDataCache() *PreparedDataCache
  • func (e *Engine) InitializeEventScheduler(ctxGetterFunc func() (*sql.Context, func() error, error), ...) error
  • func (e *Engine) IsReadOnly() bool
  • func (e *Engine) PrepQueryPlanForExecution(ctx *sql.Context, query string, plan sql.Node) (sql.Schema, sql.RowIter, error)
  • func (e *Engine) PrepareParsedQuery(ctx *sql.Context, statementKey, query string, stmt sqlparser.Statement) (sql.Node, error)
  • func (e *Engine) PrepareQuery(ctx *sql.Context, query string) (sql.Node, error)
  • func (e *Engine) Query(ctx *sql.Context, query string) (sql.Schema, sql.RowIter, error)
  • func (e *Engine) QueryWithBindings(ctx *sql.Context, query string, parsed sqlparser.Statement, ...) (sql.Schema, sql.RowIter, error)
  • func (e *Engine) WithBackgroundThreads(b *sql.BackgroundThreads) *Engine
• type PreparedDataCache
  • func NewPreparedDataCache() *PreparedDataCache
  • func (p *PreparedDataCache) CacheStmt(sessId uint32, query string, stmt sqlparser.Statement)
  • func (p *PreparedDataCache) CachedStatementsForSession(sessId uint32) map[string]sqlparser.Statement
  • func (p *PreparedDataCache) DeleteSessionData(sessId uint32)
  • func (p *PreparedDataCache) GetCachedStmt(sessId uint32, query string) (sqlparser.Statement, bool)
  • func (p *PreparedDataCache) UncacheStmt(sessId uint32, query string)
• type ProcessList
  • func NewProcessList() *ProcessList
  • func (pl *ProcessList) AddConnection(id uint32, addr string)
  • func (pl *ProcessList) AddPartitionProgress(pid uint64, tableName, partitionName string, total int64)
  • func (pl *ProcessList) AddTableProgress(pid uint64, name string, total int64)
  • func (pl *ProcessList) BeginQuery(ctx *sql.Context, query string) (*sql.Context, error)
  • func (pl *ProcessList) ConnectionReady(sess sql.Session)
  • func (pl *ProcessList) EndQuery(ctx *sql.Context)
  • func (pl *ProcessList) Kill(connID uint32)
  • func (pl *ProcessList) Processes() []sql.Process
  • func (pl *ProcessList) RemoveConnection(connID uint32)
  • func (pl *ProcessList) RemovePartitionProgress(pid uint64, tableName, partitionName string)
  • func (pl *ProcessList) RemoveTableProgress(pid uint64, name string)
  • func (pl *ProcessList) UpdatePartitionProgress(pid uint64, tableName, partitionName string, delta int64)
  • func (pl *ProcessList) UpdateTableProgress(pid uint64, name string, delta int64)
• type TemporaryUser

Variables

var ExperimentalGMS bool

Types

type ColumnWithRawDefault

type ColumnWithRawDefault struct {
	SqlColumn *sql.Column
	Default   string
}

type Config

type Config struct {
	// VersionPostfix to display with the `VERSION()` UDF.
	VersionPostfix string
	// IsReadOnly sets the engine to disallow modification queries.
	IsReadOnly     bool
	IsServerLocked bool
	// IncludeRootAccount adds the root account (with no password) to the list of accounts, and also enables
	// authentication.
	IncludeRootAccount bool
	// TemporaryUsers adds any users that should be included when the engine is created. By default, authentication is
	// disabled, and including any users here will enable authentication. All users in this list will have full access.
	// This field is only temporary, and will be removed as development on users and authentication continues.
	TemporaryUsers []TemporaryUser
}

Config for the Engine.

type Engine

type Engine struct {
	Analyzer          *analyzer.Analyzer
	LS                *sql.LockSubsystem
	ProcessList       sql.ProcessList
	MemoryManager     *sql.MemoryManager
	BackgroundThreads *sql.BackgroundThreads
	ReadOnly          atomic.Bool
	IsServerLocked    bool
	PreparedDataCache *PreparedDataCache

	Version        sql.AnalyzerVersion
	EventScheduler *eventscheduler.EventScheduler
	// contains filtered or unexported fields
}

Engine is a SQL engine.

func New

func New(a *analyzer.Analyzer, cfg *Config) *Engine

New creates a new Engine with custom configuration. To create an Engine with the default settings use `NewDefault`. Should call Engine.Close() to finalize dependency lifecycles.

func NewDefault

func NewDefault(pro sql.DatabaseProvider) *Engine

NewDefault creates a new default Engine.

func (*Engine) AnalyzeQuery

func (e *Engine) AnalyzeQuery(
	ctx *sql.Context,
	query string,
) (sql.Node, error)

AnalyzeQuery analyzes a query and returns its sql.Node

func (*Engine) BoundQueryPlan

func (e *Engine) BoundQueryPlan(ctx *sql.Context, query string, parsed sqlparser.Statement, bindings map[string]*querypb.BindVariable) (sql.Node, error)

BoundQueryPlan returns query plan for the given statement with the given bindings applied

func (*Engine) Close

func (e *Engine) Close() error

func (*Engine) CloseSession

func (e *Engine) CloseSession(connID uint32)

CloseSession deletes session specific prepared statement data

func (*Engine) EngineAnalyzer

func (e *Engine) EngineAnalyzer() *analyzer.Analyzer

func (*Engine) EnginePreparedDataCache

func (e *Engine) EnginePreparedDataCache() *PreparedDataCache

func (*Engine) InitializeEventScheduler

func (e *Engine) InitializeEventScheduler(ctxGetterFunc func() (*sql.Context, func() error, error), status eventscheduler.SchedulerStatus, period int) error

InitializeEventScheduler initializes the EventScheduler for the engine with the given sql.Context getter function, |ctxGetterFunc, the EventScheduler |status|, and the |period| for the event scheduler to check for events to execute. If |period| is less than 1, then it is ignored and the default period (30s currently) is used. This function also initializes the EventScheduler of the analyzer of this engine.

func (*Engine) IsReadOnly

func (e *Engine) IsReadOnly() bool

func (*Engine) PrepQueryPlanForExecution

func (e *Engine) PrepQueryPlanForExecution(ctx *sql.Context, query string, plan sql.Node) (sql.Schema, sql.RowIter, error)

PrepQueryPlanForExecution prepares a query plan for execution and returns the result schema with a row iterator to begin spooling results

func (*Engine) PrepareParsedQuery

func (e *Engine) PrepareParsedQuery(
	ctx *sql.Context,
	statementKey, query string,
	stmt sqlparser.Statement,
) (sql.Node, error)

PrepareParsedQuery returns a partially analyzed query for the parsed statement provided

func (*Engine) PrepareQuery

func (e *Engine) PrepareQuery(
	ctx *sql.Context,
	query string,
) (sql.Node, error)

PrepareQuery returns a partially analyzed query

func (*Engine) Query

func (e *Engine) Query(ctx *sql.Context, query string) (sql.Schema, sql.RowIter, error)

Query executes a query.

func (*Engine) QueryWithBindings

func (e *Engine) QueryWithBindings(ctx *sql.Context, query string, parsed sqlparser.Statement, bindings map[string]*querypb.BindVariable) (sql.Schema, sql.RowIter, error)

QueryWithBindings executes the query given with the bindings provided. If parsed is non-nil, it will be used instead of parsing the query from text.

func (*Engine) WithBackgroundThreads

func (e *Engine) WithBackgroundThreads(b *sql.BackgroundThreads) *Engine

type PreparedDataCache

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

PreparedDataCache manages all the prepared data for every session for every query for an engine. There are two types of caching supported: 1. Prepared statements for MySQL, which are stored as sqlparser.Statements 2. Prepared statements for Postgres, which are stored as sql.Nodes TODO: move this into the session

func NewPreparedDataCache

func NewPreparedDataCache() *PreparedDataCache

func (*PreparedDataCache) CacheStmt

func (p *PreparedDataCache) CacheStmt(sessId uint32, query string, stmt sqlparser.Statement)

CacheStmt saves the parsed statement and associates a ctx.SessionId and query to it

func (*PreparedDataCache) CachedStatementsForSession

func (p *PreparedDataCache) CachedStatementsForSession(sessId uint32) map[string]sqlparser.Statement

CachedStatementsForSession returns all the prepared queries for a particular session

func (*PreparedDataCache) DeleteSessionData

func (p *PreparedDataCache) DeleteSessionData(sessId uint32)

DeleteSessionData clears a session along with all prepared queries for that session

func (*PreparedDataCache) GetCachedStmt

func (p *PreparedDataCache) GetCachedStmt(sessId uint32, query string) (sqlparser.Statement, bool)

GetCachedStmt retrieves the prepared statement associated with the ctx.SessionId and query. Returns nil, false if the query does not exist

func (*PreparedDataCache) UncacheStmt

func (p *PreparedDataCache) UncacheStmt(sessId uint32, query string)

UncacheStmt removes the prepared node associated with a ctx.SessionId and query to it

type ProcessList

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

ProcessList is a structure that keeps track of all the processes and their status.

func NewProcessList

func NewProcessList() *ProcessList

NewProcessList creates a new process list.

func (*ProcessList) AddConnection

func (pl *ProcessList) AddConnection(id uint32, addr string)

func (*ProcessList) AddPartitionProgress

func (pl *ProcessList) AddPartitionProgress(pid uint64, tableName, partitionName string, total int64)

AddPartitionProgress adds a new item to track progress from to the process with the given pid. If the pid or the table does not exist, it will do nothing.

func (*ProcessList) AddTableProgress

func (pl *ProcessList) AddTableProgress(pid uint64, name string, total int64)

AddTableProgress adds a new item to track progress from to the process with the given pid. If the pid does not exist, it will do nothing.

func (*ProcessList) BeginQuery

func (pl *ProcessList) BeginQuery(
	ctx *sql.Context,
	query string,
) (*sql.Context, error)

func (*ProcessList) ConnectionReady

func (pl *ProcessList) ConnectionReady(sess sql.Session)

func (*ProcessList) EndQuery

func (pl *ProcessList) EndQuery(ctx *sql.Context)

func (*ProcessList) Kill

func (pl *ProcessList) Kill(connID uint32)

Kill terminates all queries for a given connection id.

func (*ProcessList) Processes

func (pl *ProcessList) Processes() []sql.Process

Processes returns the list of current running processes.

func (*ProcessList) RemoveConnection

func (pl *ProcessList) RemoveConnection(connID uint32)

func (*ProcessList) RemovePartitionProgress

func (pl *ProcessList) RemovePartitionProgress(pid uint64, tableName, partitionName string)

RemovePartitionProgress removes an existing item tracking progress from the process with the given pid, if it exists.

func (*ProcessList) RemoveTableProgress

func (pl *ProcessList) RemoveTableProgress(pid uint64, name string)

RemoveTableProgress removes an existing item tracking progress from the process with the given pid, if it exists.

func (*ProcessList) UpdatePartitionProgress

func (pl *ProcessList) UpdatePartitionProgress(pid uint64, tableName, partitionName string, delta int64)

UpdatePartitionProgress updates the progress of the table partition with the given name for the process with the given pid.

func (*ProcessList) UpdateTableProgress

func (pl *ProcessList) UpdateTableProgress(pid uint64, name string, delta int64)

UpdateTableProgress updates the progress of the table with the given name for the process with the given pid.

type TemporaryUser

type TemporaryUser struct {
	Username string
	Password string
}

TemporaryUser is a user that will be added to the engine. This is for temporary use while the remaining features are implemented. Replaces the old "auth.New..." functions for adding a user.