sqlparser

SQL Parser implemented in Go

1,466

238

1,466

View on GitHub

Top Related Projects

vitess

18,397

Vitess is a database clustering system for horizontal scaling of MySQL.

cockroach

29,856

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.

Quick Overview

xwb1989/sqlparser is a Go library for parsing SQL statements. It provides a robust parser that can handle various SQL dialects, including MySQL and TiDB. The library offers functionality to parse SQL queries into an Abstract Syntax Tree (AST), which can be useful for analyzing, transforming, or validating SQL statements programmatically.

Pros

Supports multiple SQL dialects, including MySQL and TiDB
Provides a comprehensive set of AST nodes for representing SQL structures
Actively maintained with regular updates and bug fixes
Well-documented codebase with clear examples

Cons

Limited support for more advanced SQL features or newer SQL standards
May require additional work to integrate with specific database systems
Performance might be a concern for parsing very large or complex SQL statements
Not as feature-rich as some commercial SQL parsing solutions

Code Examples

Parsing a simple SELECT statement:

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

sql := "SELECT id, name FROM users WHERE age > 18"
stmt, err := sqlparser.Parse(sql)
if err != nil {
    // Handle error
}
selectStmt := stmt.(*sqlparser.Select)
// Now you can access the parsed structure

Modifying a parsed SQL statement:

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

sql := "SELECT * FROM users"
stmt, _ := sqlparser.Parse(sql)
selectStmt := stmt.(*sqlparser.Select)

// Add a WHERE clause
selectStmt.Where = &sqlparser.Where{
    Type: sqlparser.WhereStr,
    Expr: &sqlparser.ComparisonExpr{
        Left:     &sqlparser.ColName{Name: sqlparser.NewColIdent("age")},
        Operator: ">",
        Right:    sqlparser.NewIntVal([]byte("18")),
    },
}

// Convert back to SQL string
modifiedSQL := sqlparser.String(stmt)

Extracting table names from a complex query:

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

sql := "SELECT u.name, o.product FROM users u JOIN orders o ON u.id = o.user_id"
stmt, _ := sqlparser.Parse(sql)
tables := sqlparser.GetTableNames(stmt)
// tables will contain "users" and "orders"

Getting Started

To use xwb1989/sqlparser in your Go project, follow these steps:

Install the library:
```
go get github.com/xwb1989/sqlparser
```
Import the library in your Go code:
```
import "github.com/xwb1989/sqlparser"
```

Parse SQL statements:

sql := "YOUR SQL QUERY HERE"
stmt, err := sqlparser.Parse(sql)
if err != nil {
    // Handle parsing error
}
// Use the parsed statement (stmt) for further processing

Competitor Comparisons

parser

1,402

A MySQL Compatible SQL Parser

Pros of parser

More comprehensive SQL dialect support, including MySQL-specific features
Actively maintained with regular updates and contributions
Part of a larger ecosystem (TiDB), potentially benefiting from wider community support

Cons of parser

Larger codebase and potentially more complex to use
May include unnecessary features for simpler use cases
Tied to TiDB ecosystem, which could be a limitation for some projects

Code Comparison

sqlparser:

stmt, err := sqlparser.Parse("SELECT * FROM users WHERE id = 1")
if err != nil {
    log.Fatal(err)
}

parser:

p := parser.New()
stmt, err := p.ParseOneStmt("SELECT * FROM users WHERE id = 1", "", "")
if err != nil {
    log.Fatal(err)
}

Both libraries provide similar functionality for parsing SQL statements, but parser offers more advanced features and customization options. The sqlparser library has a simpler API, which may be preferable for basic use cases. However, parser's additional capabilities make it more suitable for complex SQL parsing needs, especially when working with MySQL-specific syntax or within the TiDB ecosystem.

vitess

18,397

Vitess is a database clustering system for horizontal scaling of MySQL.

Pros of Vitess

Comprehensive database scaling and sharding solution
Supports multiple database backends (MySQL, MariaDB, Percona)
Active development with a large community and enterprise support

Cons of Vitess

More complex setup and configuration
Higher resource requirements
Steeper learning curve for implementation

Code Comparison

Vitess (Go):

package main

import (
    "vitess.io/vitess/go/vt/sqlparser"
)

func main() {
    stmt, _ := sqlparser.Parse("SELECT * FROM users WHERE id = 1")
    // Further processing...
}

sqlparser (Go):

package main

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

func main() {
    stmt, _ := sqlparser.Parse("SELECT * FROM users WHERE id = 1")
    // Further processing...
}

Both libraries provide SQL parsing capabilities, but Vitess offers a more comprehensive solution for database scaling and management. sqlparser is focused solely on SQL parsing and is lighter weight, making it suitable for simpler use cases or as a component in other projects. Vitess, on the other hand, is a full-featured database orchestration system that includes SQL parsing as part of its broader functionality.

cockroach

29,856

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.

Pros of Cockroach

Full-featured distributed SQL database system, not just a parser
Highly scalable and resilient with automatic sharding and replication
Active development with frequent updates and a large community

Cons of Cockroach

Much larger and more complex codebase, harder to understand and contribute to
Heavier resource requirements for running and testing
Steeper learning curve for developers new to distributed systems

Code Comparison

sqlparser:

func Parse(sql string) (Statement, error) {
    tokens := lex(sql)
    return parse(tokens)
}

Cockroach:

func (p *Parser) Parse(sql string) (stmts Statement, err error) {
    p.lexer.init(sql)
    if stmts, err = p.parseStmts(); err != nil {
        return nil, err
    }
    return stmts, nil
}

Summary

While sqlparser focuses solely on SQL parsing, Cockroach offers a complete distributed database solution. Cockroach provides more features and scalability but comes with increased complexity. sqlparser is simpler and easier to integrate for basic SQL parsing needs, while Cockroach is better suited for building large-scale, distributed database applications.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

sqlparser

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

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot