SQL Parser implemented in Go

Overview

sqlparser Build Status Coverage Report card GoDoc

Go package for parsing MySQL SQL queries.

Notice

The backbone of this repo is extracted from vitessio/vitess.

Inside vitessio/vitess there is a very nicely written sql parser. However as it's not a self-contained application, I created this one. It applies the same LICENSE as vitessio/vitess.

Usage

import (
    "github.com/xwb1989/sqlparser"
)

Then use:

sql := "SELECT * FROM table WHERE a = 'abc'"
stmt, err := sqlparser.Parse(sql)
if err != nil {
	// Do something with the err
}

// Otherwise do something with stmt
switch stmt := stmt.(type) {
case *sqlparser.Select:
	_ = stmt
case *sqlparser.Insert:
}

Alternative to read many queries from a io.Reader:

r := strings.NewReader("INSERT INTO table1 VALUES (1, 'a'); INSERT INTO table2 VALUES (3, 4);")

tokens := sqlparser.NewTokenizer(r)
for {
	stmt, err := sqlparser.ParseNext(tokens)
	if err == io.EOF {
		break
	}
	// Do something with stmt or err.
}

See parse_test.go for more examples, or read the godoc.

Porting Instructions

You only need the below if you plan to try and keep this library up to date with vitessio/vitess.

Keeping up to date

shopt -s nullglob
VITESS=${GOPATH?}/src/vitess.io/vitess/go/
XWB1989=${GOPATH?}/src/github.com/xwb1989/sqlparser/

# Create patches for everything that changed
LASTIMPORT=1b7879cb91f1dfe1a2dfa06fea96e951e3a7aec5
for path in ${VITESS?}/{vt/sqlparser,sqltypes,bytes2,hack}; do
	cd ${path}
	git format-patch ${LASTIMPORT?} .
done;

# Apply patches to the dependencies
cd ${XWB1989?}
git am --directory dependency -p2 ${VITESS?}/{sqltypes,bytes2,hack}/*.patch

# Apply the main patches to the repo
cd ${XWB1989?}
git am -p4 ${VITESS?}/vt/sqlparser/*.patch

# If you encounter diff failures, manually fix them with
patch -p4 < .git/rebase-apply/patch
...
git add name_of_files
git am --continue

# Cleanup
rm ${VITESS?}/{sqltypes,bytes2,hack}/*.patch ${VITESS?}/*.patch

# and Finally update the LASTIMPORT in this README.

Fresh install

TODO: Change these instructions to use git to copy the files, that'll make later patching easier.

VITESS=${GOPATH?}/src/vitess.io/vitess/go/
XWB1989=${GOPATH?}/src/github.com/xwb1989/sqlparser/

cd ${XWB1989?}

# Copy all the code
cp -pr ${VITESS?}/vt/sqlparser/ .
cp -pr ${VITESS?}/sqltypes dependency
cp -pr ${VITESS?}/bytes2 dependency
cp -pr ${VITESS?}/hack dependency

# Delete some code we haven't ported
rm dependency/sqltypes/arithmetic.go dependency/sqltypes/arithmetic_test.go dependency/sqltypes/event_token.go dependency/sqltypes/event_token_test.go dependency/sqltypes/proto3.go dependency/sqltypes/proto3_test.go dependency/sqltypes/query_response.go dependency/sqltypes/result.go dependency/sqltypes/result_test.go

# Some automated fixes

# Fix imports
sed -i '.bak' 's_vitess.io/vitess/go/vt/proto/query_github.com/xwb1989/sqlparser/dependency/querypb_g' *.go dependency/sqltypes/*.go
sed -i '.bak' 's_vitess.io/vitess/go/_github.com/xwb1989/sqlparser/dependency/_g' *.go dependency/sqltypes/*.go

# Copy the proto, but basically drop everything we don't want
cp -pr ${VITESS?}/vt/proto/query dependency/querypb

sed -i '.bak' 's_.*Descriptor.*__g' dependency/querypb/*.go
sed -i '.bak' 's_.*ProtoMessage.*__g' dependency/querypb/*.go

sed -i '.bak' 's/proto.CompactTextString(m)/"TODO"/g' dependency/querypb/*.go
sed -i '.bak' 's/proto.EnumName/EnumName/g' dependency/querypb/*.go

sed -i '.bak' 's/proto.Equal/reflect.DeepEqual/g' dependency/sqltypes/*.go

# Remove the error library
sed -i '.bak' 's/vterrors.Errorf([^,]*, /fmt.Errorf(/g' *.go dependency/sqltypes/*.go
sed -i '.bak' 's/vterrors.New([^,]*, /errors.New(/g' *.go dependency/sqltypes/*.go

Testing

VITESS=${GOPATH?}/src/vitess.io/vitess/go/
XWB1989=${GOPATH?}/src/github.com/xwb1989/sqlparser/

cd ${XWB1989?}

# Test, fix and repeat
go test ./...

# Finally make some diffs (for later reference)
diff -u ${VITESS?}/sqltypes/        ${XWB1989?}/dependency/sqltypes/ > ${XWB1989?}/patches/sqltypes.patch
diff -u ${VITESS?}/bytes2/          ${XWB1989?}/dependency/bytes2/   > ${XWB1989?}/patches/bytes2.patch
diff -u ${VITESS?}/vt/proto/query/  ${XWB1989?}/dependency/querypb/  > ${XWB1989?}/patches/querypb.patch
diff -u ${VITESS?}/vt/sqlparser/    ${XWB1989?}/                     > ${XWB1989?}/patches/sqlparser.patch
Issues
  • parse empty statements as nil instead of an error

    parse empty statements as nil instead of an error

    Previously empty statements would result in parse errors. This can happen in cases where they do not initially appear empty, e.g. if the contained only comments. For example, something similar to this appears in mysqldump output:

    /* some comment*/;
    /* another comment */;
    CREATE TABLE ...
    

    Lex usually discards comments until it finds a token to return but in the above case that is nil since it is an empty statement.

    This change extends the parser to handle the empty statement (after lex does its comment discarding in the above example).

    Fixes #31.

    opened by dt 4
  • Syntax error for

    Syntax error for "using" in join

    This is a fantastically complete and fast parser, but I have one problem case to report. It doesn't seem to understand queries where the "using" shorthand is used in a join.

    Reproduce with: Parsing: select * from t1 join t2 using(id) yields error: syntax error at position 31 near 'using'

    opened by IronSavior 3
  • Syntax error

    Syntax error

    sql := "SELECT * FROM table WHERE a = 'abc'"
    stmt, err := sqlparser.Parse(sql)
    if err != nil {
    	panic(err)
    }
    

    I got this error

    http: panic serving [::1]:50432: syntax error at position 20 near 'table'
    goroutine 19 [running]:
    net/http.(*conn).serve.func1(0xc0000a2960)
    	/usr/local/go/src/net/http/server.go:1769 +0x139
    panic(0x7624e0, 0xc000087020)
    	/usr/local/go/src/runtime/panic.go:522 +0x1b5
    github.com/flume-cloud-services/database/controllers.CreateQuery(0x8565a0, 0xc000136000, 0xc000112400)
    	/home/rerwan/go/src/github.com/flume-cloud-services/database/controllers/query.go:25 +0x27b
    net/http.HandlerFunc.ServeHTTP(0x7ece30, 0x8565a0, 0xc000136000, 0xc000112400)
    	/usr/local/go/src/net/http/server.go:1995 +0x44
    github.com/flume-cloud-services/database/middleware.AuthMiddleware.func1(0x8565a0, 0xc000136000, 0xc000112400)
    	/home/rerwan/go/src/github.com/flume-cloud-services/database/middleware/auth.go:60 +0x351
    net/http.HandlerFunc.ServeHTTP(0xc000090440, 0x8565a0, 0xc000136000, 0xc000112400)
    	/usr/local/go/src/net/http/server.go:1995 +0x44
    net/http.(*ServeMux).ServeHTTP(0xadf1a0, 0x8565a0, 0xc000136000, 0xc000112400)
    	/usr/local/go/src/net/http/server.go:2375 +0x1d6
    net/http.serverHandler.ServeHTTP(0xc0000932b0, 0x8565a0, 0xc000136000, 0xc000112400)
    	/usr/local/go/src/net/http/server.go:2774 +0xa8
    net/http.(*conn).serve(0xc0000a2960, 0x856ee0, 0xc00009e440)
    	/usr/local/go/src/net/http/server.go:1878 +0x851
    created by net/http.(*Server).Serve
    	/usr/local/go/src/net/http/server.go:2884 +0x2f4
    

    I am using the latest version of sqlparser And Golang 1.12

    opened by dimensi0n 2
  • "SET NAMES utf8;" doesn't work

    Hi, I got another one:

    r := strings.NewReader(`SET NAMES utf8;`)
    
    tokens := sqlparser.NewTokenizer(r)
    for {
    	stmt, err := sqlparser.ParseNext(tokens)
    	if err == io.EOF {
    		break
    	}
    	fmt.Println(sqlparser.String(stmt))
    	if err != nil {
    		fmt.Println(err)
    	}
    }
    

    This results only in this output:

    set

    The rest is missing?

    opened by philiplb 2
  • `LOCK TABLES` and `UNLOCK TABLES` treated as syntax errors

    `LOCK TABLES` and `UNLOCK TABLES` treated as syntax errors

    Discovered while looking at mysqldump's default output, which contains a couple things that currently cause syntax errors.

    A mysqldump file consists of SQL statements that create tables and insert rows into them. By default it places LOCK TABLES and UNLOCK TABLES statements around its inserts, but currently these are not handled by the parser.

    opened by dt 2
  • Support for current_timestamp

    Support for current_timestamp

    I create a table with

    UpdateDatetime TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    it shows

    syntax error at position 471 near 'current_timestamp'

    opened by yupengfei 2
  • Date arithmetic INTERVAL keyword not handled correctly

    Date arithmetic INTERVAL keyword not handled correctly

    Minimal example:

    package main
    
    import (
        "fmt"
        "github.com/xwb1989/sqlparser"
    )
    
    func main() {
        sql := "SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)"
        tree, err := sqlparser.Parse(sql)
        if err != nil {
            fmt.Println("Error: ", err)
            panic("oh no")
        }
        fmt.Println(tree)
    }
    
    $ go run main.go
    Error:  syntax error at position 34 near 1
    panic: oh no
    
    [trace info]
    

    Looks like it's failing to handle the MONTH after INTERVAL.

    opened by ikirker 2
  • Not supported OFFSET

    Not supported OFFSET

    I got an error for a query which contain OFFSET. It should be a valid query.

    syntax error at position 42 near OFFSET for query - SELECT * FROM AccessToken LIMIT 10 OFFSET 13
    
    opened by PumpkinSeed 2
  • Recompile parsed query into SQL?

    Recompile parsed query into SQL?

    Hi there!

    Thank you so much for taking the time to extract this into its own package, it's awesome! I was wondering if you could provide any guidance if there were any functions exposed that takes a parsed query and can recompile it back into an SQL statement?

    Thank you so much for your time!

    opened by nathanielks 1
  • A question about using sqlparser to parse sql with placeholders

    A question about using sqlparser to parse sql with placeholders

    HI. When I use sqlparser to parse sql with "?", and then call function stmt.Format, the "?" in the sql will be replaced with ":v1". Is this a bug? Here is an example.

    sql := "select * from tbl where id = ?"
    stmt, _ := sqlparser.Parse(sql)
    buf := sqlparser.NewTrackedBuffer(nil)
    stmt.Format(buf)
    newSql := buf.String()
    fmt.Println(newSql) // the newSql is "select * from tbl where id = :v1"
    

    Thank you in advance for your answer.

    opened by XH-JMC 0
  • Node position

    Node position

    Is it possible to obtain the position of each node in the AST? I believe it's not currently being tracked. Any sense of what would be involved to add support for that? I'd be happy to implement if someone could point me in the right direction.

    Ideally, it would look something like this:

    {
      Start: { Offset: 23, Line: 5, Column: 6 },
      End:   { Offset: 25, Line: 5, Column: 8 }
    }
    
    opened by stevenmiller888 0
  • How to parse SQL procedure CALL

    How to parse SQL procedure CALL

    I have an SQL procedure in MariaDB called bookUPDATE such as this:

    BEGIN
    	UPDATE book
    		SET
    			book.Title= v_Title,
    			book.ReleaseDate = v_ReleaseDate,
    			book.Description = v_Description,
    		WHERE book.ID = v_ID;
    END
    

    I have an SQL builder for this procedure which creates a string:

    q := "CALL bookUPDATE(?, ?, ?, ?); CALL bookUPDATE(?, ?, ?, ?);"
    

    When I do the sqlparser.Parse(q) I get an error syntax error at position 5 near 'call'.

    How am I supposed to Parse calling the procedure?

    opened by Delicious-Bacon 0
  • Does it support nested queries/sub-quries

    Does it support nested queries/sub-quries

    I want to validate some SQL queries that are nested and have sub-queries at multiple depths. The sqlparser.Parse says the following query is invalid:-

    SELECT ( SELECT ( SELECT ( SELECT * FROM table) FROM table_2 ) FROM table_1 WHERE column = ( SELECT * FROM table_3 ) ) FROM table
    

    Am I doing something wrong or does the library not support sub-packages?

    opened by antrikshmisri 0
  • parse error

    parse error

    func main() { sql := "SELECT * FROM table WHERE a = 'abc'" stmt, err := sqlparser.Parse(sql) if err != nil { fmt.Printf("%s",err.Error()) }

    // Otherwise do something with stmt
    switch stmt := stmt.(type) {
    case *sqlparser.Select:
    	_ = stmt
    case *sqlparser.Insert:
    }
    
    fmt.Printf("stmt :%v", stmt)
    

    }

    result err: syntax error at position 20 near 'table'

    opened by xinlushi 2
  • Add non-reserved keyword 'groups'

    Add non-reserved keyword 'groups'

    While using this library, I noticed that it does not add a backtick to MySQL tables named groups, which causes an SQL error when executing the generated queries.

    This PR is a proposed fix for this.

    opened by peppelan 1
Owner
Wenbin Xiao
Wenbin Xiao
Simple SQL parser

gosqlparser gosqlparser is a simple SQL parser. Installation As simple as: go get github.com/krasun/gosqlparser Usage ... Supported Statements CREATE

Dmytro Krasun 35 Jul 28, 2022
write APIs using direct SQL queries with no hassle, let's rethink about SQL

SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val

Mohammed Al Ashaal 2k Aug 1, 2022
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

null 0 Oct 20, 2021
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

null 0 Jan 25, 2022
A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

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.

DoltHub 861 Jul 29, 2022
Implemented PostgreSQL with Golang

Customer Information Web Api Implemented PostgreSQL with Golang docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=Password! -d

bseyhan 2 Nov 15, 2021
PostgreSQL style Parser splitted from CockroachDB

What's this PostgreSQL style Parser splitted from CockroachDB See: Complex SQL format example

auxten 142 Jul 27, 2022
Go package for sharding databases ( Supports every ORM or raw SQL )

Octillery Octillery is a Go package for sharding databases. It can use with every OR Mapping library ( xorm , gorp , gorm , dbr ...) implementing data

BlasTrain Co., Ltd. 167 Jul 26, 2022
Prep finds all SQL statements in a Go package and instruments db connection with prepared statements

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

Max Chechel 31 Jan 23, 2022
pggen - generate type safe Go methods from Postgres SQL queries

pggen - generate type safe Go methods from Postgres SQL queries pggen is a tool that generates Go code to provide a typesafe wrapper around Postgres q

Joe Schafer 176 Aug 7, 2022
🐳 A most popular sql audit platform for mysql

?? A most popular sql audit platform for mysql

Henry Yee 6.7k Aug 1, 2022
sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jason Moiron 12.4k Aug 9, 2022
A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL

query2metric A tool to run db queries in defined frequency and expose the count as prometheus metrics. Why ? Product metrics play an important role in

S Santhosh Nagaraj 19 Jul 1, 2022
Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

?? Dumpling Dumpling is a tool and a Go library for creating SQL dump from a MySQL-compatible database. It is intended to replace mysqldump and mydump

PingCAP 261 Jul 14, 2022
Universal command-line interface for SQL databases

usql A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQ

XO 7.5k Aug 9, 2022
auto generate sql from gorm model struct

gorm2sql: auto generate sql from gorm model struct A Swiss Army Knife helps you generate sql from gorm model struct. Installation go get github.com/li

Danieliu 114 Jul 21, 2022
a golang library for sql builder

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

DiDi 1.4k Aug 8, 2022
Fluent SQL generation for golang

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

null 5k Aug 1, 2022
SQL Optimizer And Rewriter

文档 | FAQ | 变更记录 | 路线图 | English SOAR SOAR(SQL Optimizer And Rewriter) 是一个对 SQL 进行优化和改写的自动化工具。 由小米人工智能与云平台的数据库团队开发与维护。 功能特点 跨平台支持(支持 Linux, Mac 环境,Wind

Xiaomi 8k Aug 4, 2022