sqlqueryidentifier

README

sql-query-identifier (Go)

This is a Go port of the NPM sql-query-identifier library. It identifies the type of each statement in a SQL query string, providing the start and end position of each statement.

Features

• Multi-Statement Support: Parses a string containing multiple SQL statements separated by semicolons.
• Dialect-Aware: Supports multiple SQL dialects, including MySQL, PostgreSQL, MSSQL, and more.
• Statement Identification: Identifies a wide range of statement types (e.g., SELECT, INSERT, CREATE TABLE).
• Execution Type Analysis: Classifies statements by their behavior (LISTING, MODIFICATION).
• Parameter Extraction: Identifies positional (?, $1) and named (:name) parameters.
• Strict & Non-Strict Modes: Choose whether to error on unknown statement types or classify them as UNKNOWN.

Installation

go get github.com/Infisical/sql-query-identifier

Usage

The following example demonstrates how to parse a query string and print the identified statements as a JSON object.

package main

import (
	"encoding/json"
	"fmt"
	"log"

	sqlqueryidentifier "github.com/Infisical/sql-query-identifier"
)

func main() {
	query := `
		INSERT INTO Persons (PersonID, Name) VALUES (1, 'Jack');
		SELECT * FROM Persons;
`

	// Use Dialect constants for type safety
	dialect := sqlqueryidentifier.DialectMySQL
	strict := false

	options := sqlqueryidentifier.IdentifyOptions{
		Dialect: &dialect,
		Strict:  &strict,
	}

	results, err := sqlqueryidentifier.Identify(query, options)
	if err != nil {
		log.Fatalf("Failed to identify query: %v", err)
	}

	// Marshal the results to JSON for clear output
	jsonOutput, err := json.MarshalIndent(results, "", "  ")
	if err != nil {
		log.Fatalf("Failed to marshal results to JSON: %v", err)
	}

	fmt.Println(string(jsonOutput))
}

API

Identify(query string, options IdentifyOptions) ([]IdentifyResult, error)

• query (string): The raw SQL string to be processed.
• options (IdentifyOptions): Configuration for the parser.
  • Strict (*bool): If false, will classify unknown statements as UNKNOWN instead of returning an error. Defaults to true.
  • Dialect (*Dialect): The SQL dialect to use for parsing. Defaults to generic.

Supported Dialects

• mssql
• sqlite
• mysql
• oracle
• psql
• bigquery
• generic (default)

Supported Statement Types

Data Manipulation

• SELECT
• INSERT
• UPDATE
• DELETE
• TRUNCATE

Data Definition

• CREATE_DATABASE
• CREATE_SCHEMA
• CREATE_TABLE
• CREATE_VIEW
• CREATE_TRIGGER
• CREATE_FUNCTION
• CREATE_INDEX
• CREATE_PROCEDURE
• DROP_DATABASE
• DROP_SCHEMA
• DROP_TABLE
• DROP_VIEW
• DROP_TRIGGER
• DROP_FUNCTION
• DROP_INDEX
• DROP_PROCEDURE
• ALTER_DATABASE
• ALTER_SCHEMA
• ALTER_TABLE
• ALTER_VIEW
• ALTER_TRIGGER
• ALTER_FUNCTION
• ALTER_INDEX
• ALTER_PROCEDURE

SHOW (MySQL and generic dialects)

• SHOW_BINARY
• SHOW_BINLOG
• SHOW_CHARACTER
• SHOW_COLLATION
• SHOW_COLUMNS
• SHOW_CREATE
• SHOW_DATABASES
• SHOW_ENGINE
• SHOW_ENGINES
• SHOW_ERRORS
• SHOW_EVENTS
• SHOW_FUNCTION
• SHOW_GRANTS
• SHOW_INDEX
• SHOW_MASTER
• SHOW_OPEN
• SHOW_PLUGINS
• SHOW_PRIVILEGES
• SHOW_PROCEDURE
• SHOW_PROCESSLIST
• SHOW_PROFILE
• SHOW_PROFILES
• SHOW_RELAYLOG
• SHOW_REPLICAS
• SHOW_SLAVE
• SHOW_REPLICA
• SHOW_STATUS
• SHOW_TABLE
• SHOW_TABLES
• SHOW_TRIGGERS
• SHOW_VARIABLES
• SHOW_WARNINGS

Other

• ANON_BLOCK (BigQuery and Oracle dialects only)
• UNKNOWN (only available if strict mode is disabled)

Execution Types

Execution types classify the behavior of a query.

• LISTING: The query lists or retrieves data.
• MODIFICATION: The query modifies the database structure or data.
• INFORMATION: The query shows information, such as profiling data.
• ANON_BLOCK: The query is an anonymous block which may contain multiple statements.
• UNKNOWN: The query type could not be determined (only available if strict mode is disabled).

How It Works

This library uses AST and parser techniques to identify the SQL query type. It does not validate the entire query; instead, it validates only the required tokens to identify the statement type.

The identification process is:

1. Tokenizing: The input string is broken down into tokens (keywords, strings, comments, etc.).
2. Parsing: The stream of tokens is parsed to identify the statement boundaries and types.
   • Comments and string contents are ignored to prevent false positives.
   • Keywords are expected at the beginning of a statement.
   • Semicolons are used to identify the end of a statement.

Because the library does not perform a full SQL validation, it is recommended to use it on queries that have already been successfully executed by a SQL client.

License

This project is licensed under the MIT License.

Documentation

Index

• Variables
• type AcceptToken
• type ConcreteStatement
• type Dialect
• type ExecutionType
  • func GetExecutionType(command StatementType) ExecutionType
• type IdentifyOptions
• type IdentifyResult
  • func Identify(query string, options IdentifyOptions) (results []IdentifyResult, err error)
• type ParamTypes
  • func DefaultParamTypesFor(dialect Dialect) *ParamTypes
• type ParseOptions
• type ParseResult
  • func Parse(input string, isStrict bool, dialect Dialect, identifyTables bool, ...) *ParseResult
• type State
• type Statement
  • func (s *Statement) ToConcrete() ConcreteStatement
• type StatementParser
• type StatementType
• type Step
• type StepValidation
• type Token
  • func ScanToken(state *State, dialect Dialect, paramTypes *ParamTypes) Token
• type TokenType

Variables

var DIALECTS = []Dialect{
	DialectMSSQL,
	DialectSQLite,
	DialectMySQL,
	DialectOracle,
	DialectPSQL,
	DialectBigQuery,
	DialectGeneric,
}

var ExecutionTypes = map[StatementType]ExecutionType{
	StatementSelect:          ExecutionListing,
	StatementInsert:          ExecutionModification,
	StatementDelete:          ExecutionModification,
	StatementUpdate:          ExecutionModification,
	StatementTruncate:        ExecutionModification,
	StatementCreateDatabase:  ExecutionModification,
	StatementCreateSchema:    ExecutionModification,
	StatementCreateTable:     ExecutionModification,
	StatementCreateView:      ExecutionModification,
	StatementCreateTrigger:   ExecutionModification,
	StatementCreateFunction:  ExecutionModification,
	StatementCreateIndex:     ExecutionModification,
	StatementCreateProcedure: ExecutionModification,
	StatementShowBinary:      ExecutionListing,
	StatementShowBinlog:      ExecutionListing,
	StatementShowCharacter:   ExecutionListing,
	StatementShowCollation:   ExecutionListing,
	StatementShowCreate:      ExecutionListing,
	StatementShowEngine:      ExecutionListing,
	StatementShowEngines:     ExecutionListing,
	StatementShowErrors:      ExecutionListing,
	StatementShowEvents:      ExecutionListing,
	StatementShowFunction:    ExecutionListing,
	StatementShowGrants:      ExecutionListing,
	StatementShowMaster:      ExecutionListing,
	StatementShowOpen:        ExecutionListing,
	StatementShowPlugins:     ExecutionListing,
	StatementShowPrivileges:  ExecutionListing,
	StatementShowProcedure:   ExecutionListing,
	StatementShowProcesslist: ExecutionListing,
	StatementShowProfile:     ExecutionListing,
	StatementShowProfiles:    ExecutionListing,
	StatementShowRelaylog:    ExecutionListing,
	StatementShowReplicas:    ExecutionListing,
	StatementShowSlave:       ExecutionListing,
	StatementShowReplica:     ExecutionListing,
	StatementShowStatus:      ExecutionListing,
	StatementShowTriggers:    ExecutionListing,
	StatementShowVariables:   ExecutionListing,
	StatementShowWarnings:    ExecutionListing,
	StatementShowDatabases:   ExecutionListing,
	StatementShowKeys:        ExecutionListing,
	StatementShowIndex:       ExecutionListing,
	StatementShowTable:       ExecutionListing,
	StatementShowTables:      ExecutionListing,
	StatementShowColumns:     ExecutionListing,
	StatementDropDatabase:    ExecutionModification,
	StatementDropSchema:      ExecutionModification,
	StatementDropTable:       ExecutionModification,
	StatementDropView:        ExecutionModification,
	StatementDropTrigger:     ExecutionModification,
	StatementDropFunction:    ExecutionModification,
	StatementDropIndex:       ExecutionModification,
	StatementDropProcedure:   ExecutionModification,
	StatementAlterDatabase:   ExecutionModification,
	StatementAlterSchema:     ExecutionModification,
	StatementAlterTable:      ExecutionModification,
	StatementAlterView:       ExecutionModification,
	StatementAlterTrigger:    ExecutionModification,
	StatementAlterFunction:   ExecutionModification,
	StatementAlterIndex:      ExecutionModification,
	StatementAlterProcedure:  ExecutionModification,
	StatementUnknown:         ExecutionUnknown,
	StatementAnonBlock:       ExecutionAnonBlock,
}

maps statement types to their execution behavior

Types

type AcceptToken

type AcceptToken struct {
	Type  string
	Value string
}

type ConcreteStatement

type ConcreteStatement struct {
	Start         int
	End           int
	Type          StatementType
	ExecutionType ExecutionType
	EndStatement  *string
	CanEnd        *bool
	Definer       *int
	Algorithm     *int
	SQLSecurity   *int
	Parameters    []string
	Tables        []string
	IsCte         *bool
}

type Dialect

type Dialect string

represents a specific SQL dialect

const (
	DialectMSSQL    Dialect = "mssql"
	DialectSQLite   Dialect = "sqlite"
	DialectMySQL    Dialect = "mysql"
	DialectOracle   Dialect = "oracle"
	DialectPSQL     Dialect = "psql"
	DialectBigQuery Dialect = "bigquery"
	DialectGeneric  Dialect = "generic"
)

type ExecutionType

type ExecutionType string

represents the behavior of a statement (e.g., LISTING, MODIFICATION)

const (
	ExecutionListing      ExecutionType = "LISTING"
	ExecutionModification ExecutionType = "MODIFICATION"
	ExecutionInformation  ExecutionType = "INFORMATION"
	ExecutionAnonBlock    ExecutionType = "ANON_BLOCK"
	ExecutionUnknown      ExecutionType = "UNKNOWN"
)

func GetExecutionType

func GetExecutionType(command StatementType) ExecutionType

type IdentifyOptions

type IdentifyOptions struct {
	Strict         *bool
	Dialect        *Dialect
	IdentifyTables *bool
	ParamTypes     *ParamTypes
}

provides configuration for the Identify function

type IdentifyResult

type IdentifyResult struct {
	Start         int           `json:"start"`
	End           int           `json:"end"`
	Text          string        `json:"text"`
	Type          StatementType `json:"type"`
	ExecutionType ExecutionType `json:"executionType"`
	Parameters    []string      `json:"parameters"`
	Tables        []string      `json:"tables"`
}

represents a single parsed SQL statement

func Identify

func Identify(query string, options IdentifyOptions) (results []IdentifyResult, err error)

type ParamTypes

type ParamTypes struct {
	Positional *bool
	Numbered   []rune // '?' | ':' | '$'
	Named      []rune // ':' | '@' | '$'
	Quoted     []rune // ':' | '@' | '$'
	Custom     []string
}

func DefaultParamTypesFor

func DefaultParamTypesFor(dialect Dialect) *ParamTypes

returns the default parameter types for a given SQL dialect

type ParseOptions

type ParseOptions struct {
	IsStrict       bool
	Dialect        Dialect
	IdentifyTables bool
	ParamTypes     *ParamTypes
}

type ParseResult

type ParseResult struct {
	Type   string              `json:"type"`
	Start  int                 `json:"start"`
	End    int                 `json:"end"`
	Body   []ConcreteStatement `json:"body"`
	Tokens []Token             `json:"tokens"`
}

func Parse

func Parse(input string, isStrict bool, dialect Dialect, identifyTables bool, paramTypes *ParamTypes) *ParseResult

type State

type State struct {
	Start    int
	End      int
	Position int
	Input    []rune
}

type Statement

type Statement struct {
	Start         int
	End           int
	Type          *StatementType
	ExecutionType *ExecutionType
	EndStatement  *string
	CanEnd        *bool
	Definer       *int
	Algorithm     *int
	SQLSecurity   *int
	Parameters    []string
	Tables        []string
	IsCte         *bool
}

func (*Statement) ToConcrete

func (s *Statement) ToConcrete() ConcreteStatement

type StatementParser

type StatementParser interface {
	AddToken(token Token, nextToken Token)
	GetStatement() *Statement
}

type StatementType

type StatementType string

represents the type of a SQL statement (e.g., SELECT, INSERT)

const (
	StatementInsert          StatementType = "INSERT"
	StatementUpdate          StatementType = "UPDATE"
	StatementDelete          StatementType = "DELETE"
	StatementSelect          StatementType = "SELECT"
	StatementTruncate        StatementType = "TRUNCATE"
	StatementCreateDatabase  StatementType = "CREATE_DATABASE"
	StatementCreateSchema    StatementType = "CREATE_SCHEMA"
	StatementCreateTable     StatementType = "CREATE_TABLE"
	StatementCreateView      StatementType = "CREATE_VIEW"
	StatementCreateTrigger   StatementType = "CREATE_TRIGGER"
	StatementCreateFunction  StatementType = "CREATE_FUNCTION"
	StatementCreateIndex     StatementType = "CREATE_INDEX"
	StatementCreateProcedure StatementType = "CREATE_PROCEDURE"
	StatementShowBinary      StatementType = "SHOW_BINARY"
	StatementShowBinlog      StatementType = "SHOW_BINLOG"
	StatementShowCharacter   StatementType = "SHOW_CHARACTER"
	StatementShowCollation   StatementType = "SHOW_COLLATION"
	StatementShowCreate      StatementType = "SHOW_CREATE"
	StatementShowEngine      StatementType = "SHOW_ENGINE"
	StatementShowEngines     StatementType = "SHOW_ENGINES"
	StatementShowErrors      StatementType = "SHOW_ERRORS"
	StatementShowEvents      StatementType = "SHOW_EVENTS"
	StatementShowFunction    StatementType = "SHOW_FUNCTION"
	StatementShowGrants      StatementType = "SHOW_GRANTS"
	StatementShowMaster      StatementType = "SHOW_MASTER"
	StatementShowOpen        StatementType = "SHOW_OPEN"
	StatementShowPlugins     StatementType = "SHOW_PLUGINS"
	StatementShowPrivileges  StatementType = "SHOW_PRIVILEGES"
	StatementShowProcedure   StatementType = "SHOW_PROCEDURE"
	StatementShowProcesslist StatementType = "SHOW_PROCESSLIST"
	StatementShowProfile     StatementType = "SHOW_PROFILE"
	StatementShowProfiles    StatementType = "SHOW_PROFILES"
	StatementShowRelaylog    StatementType = "SHOW_RELAYLOG"
	StatementShowReplicas    StatementType = "SHOW_REPLICAS"
	StatementShowSlave       StatementType = "SHOW_SLAVE"
	StatementShowReplica     StatementType = "SHOW_REPLICA"
	StatementShowStatus      StatementType = "SHOW_STATUS"
	StatementShowTriggers    StatementType = "SHOW_TRIGGERS"
	StatementShowVariables   StatementType = "SHOW_VARIABLES"
	StatementShowWarnings    StatementType = "SHOW_WARNINGS"
	StatementShowDatabases   StatementType = "SHOW_DATABASES"
	StatementShowKeys        StatementType = "SHOW_KEYS"
	StatementShowIndex       StatementType = "SHOW_INDEX"
	StatementShowTable       StatementType = "SHOW_TABLE"
	StatementShowTables      StatementType = "SHOW_TABLES"
	StatementShowColumns     StatementType = "SHOW_COLUMNS"
	StatementDropDatabase    StatementType = "DROP_DATABASE"
	StatementDropSchema      StatementType = "DROP_SCHEMA"
	StatementDropTable       StatementType = "DROP_TABLE"
	StatementDropView        StatementType = "DROP_VIEW"
	StatementDropTrigger     StatementType = "DROP_TRIGGER"
	StatementDropFunction    StatementType = "DROP_FUNCTION"
	StatementDropIndex       StatementType = "DROP_INDEX"
	StatementDropProcedure   StatementType = "DROP_PROCEDURE"
	StatementAlterDatabase   StatementType = "ALTER_DATABASE"
	StatementAlterSchema     StatementType = "ALTER_SCHEMA"
	StatementAlterTable      StatementType = "ALTER_TABLE"
	StatementAlterView       StatementType = "ALTER_VIEW"
	StatementAlterTrigger    StatementType = "ALTER_TRIGGER"
	StatementAlterFunction   StatementType = "ALTER_FUNCTION"
	StatementAlterIndex      StatementType = "ALTER_INDEX"
	StatementAlterProcedure  StatementType = "ALTER_PROCEDURE"
	StatementAnonBlock       StatementType = "ANON_BLOCK"
	StatementUnknown         StatementType = "UNKNOWN"
)

type Step

type Step struct {
	PreCanGoToNext  func(token *Token) bool
	Validation      *StepValidation
	Add             func(token Token)
	PostCanGoToNext func(token *Token) bool
}

type StepValidation

type StepValidation struct {
	RequireBefore []string
	AcceptTokens  []AcceptToken
}

type Token

type Token struct {
	Type  TokenType `json:"type"`
	Value string    `json:"value"`
	Start int       `json:"start"`
	End   int       `json:"end"`
}

func ScanToken

func ScanToken(state *State, dialect Dialect, paramTypes *ParamTypes) Token

type TokenType

type TokenType string

const (
	TokenWhitespace    TokenType = "whitespace"
	TokenCommentInline TokenType = "comment-inline"
	TokenCommentBlock  TokenType = "comment-block"
	TokenString        TokenType = "string"
	TokenSemicolon     TokenType = "semicolon"
	TokenKeyword       TokenType = "keyword"
	TokenParameter     TokenType = "parameter"
	TokenTable         TokenType = "table"
	TokenUnknown       TokenType = "unknown"
)