lens

README

sql-lens

Fully-offline Go library that answers two questions about a SQL string:

1. What is it? — classification into eight coarse buckets (ReadOnly, DML, DDL, DCL, Script, Transaction, DataMovement, Auxiliary) plus a fine-grained Kind (CREATE_TABLE, MERGE, EXECUTE_IMMEDIATE, SHOW, …), grounded in each dialect's full grammar.
2. How many columns does it return? — exact when resolvable from the SQL text alone (including SELECT * over same-query CTEs), -1 when a * makes the count schema-dependent.

Dialects: BigQuery (GoogleSQL), Snowflake, Databricks (Spark SQL). Pure Go, stdlib only — no CGO, no sidecars, no network, no credentials.

Install

go get github.com/akshaysangma/sql-lens

Usage

import lens "github.com/akshaysangma/sql-lens"

l, _ := lens.New(lens.BigQuery)

// Fast path: classification only (leading-keyword tier, no full tokenize).
c, _ := l.Classify("MERGE t USING s ON t.id = s.id WHEN MATCHED THEN DELETE")
// c.Bucket == lens.DML, c.Kind == lens.KindMerge

// Full analysis: classification + output-column count for queries.
r, _ := l.Analyze("WITH t AS (SELECT 1 AS a, 2 AS b) SELECT * FROM t")
switch v := r.(type) {
case *lens.QueryResult:     // ReadOnly — carries ColumnCount
    _ = v.ColumnCount       // 2 (CTE stars resolve from the text alone)
case *lens.ScriptResult:    // multi-statement / procedural — flagged only
case *lens.StatementResult: // everything else (DML/DDL/DCL/…)
}

r, _ = l.Analyze("SELECT * FROM prod.ds.users")
// r.(*lens.QueryResult).ColumnCount == -1   (schema-dependent)

Errors are always *lens.ParseError — malformed SQL, or a statement form the selected dialect does not have (feeding Snowflake syntax to the BigQuery lens is caught when it changes statement identity). sql-lens identifies statements; it is not a full-grammar validator.

Semantics worth knowing

• Script is structural: ≥2 real statements, or a single procedural construct (BEGIN…END, DECLARE, IF, …). A trailing ; is not a script. CALL, EXECUTE IMMEDIATE, and transaction statements are their own kinds.
• Dialects genuinely disagree and sql-lens follows each vendor: TRUNCATE TABLE is DML in BigQuery/Snowflake but DDL in Databricks; SET is script-only in BigQuery but a session/config statement in Snowflake/Databricks; a bare BEGIN is a transaction in Snowflake but a block opener in Databricks; leading WITH can prefix DML in Databricks (WITH … INSERT).
• ColumnCount == -1 always means "not resolvable from the text without a schema", never a parse failure.

Testing

go test ./... — table-driven suites plus an exhaustive matrix (simple → complex per dialect, every bucket, every fixed Kind, cross-dialect rejection) with a coverage assertion that fails if any cell goes unexercised. Column-count expectations are sqlglot-verified: the corpora under testdata/ carry ground truth generated with sqlglot 30.12.0 (regeneration recipe in docs/regenerate-ground-truth.md); deliberate divergences live in one explicit accepted-degrades list, each with its rationale.

Status

v0 — the API may still change; dialect semantics are pinned by the test suite and won't change silently. Classification tables were built from each vendor's documented grammar and hardened by adversarial testing against sqlglot as an oracle.

License

MIT

Documentation

Overview

Package lens classifies SQL statements and counts their output columns, fully offline, for BigQuery (GoogleSQL), Snowflake, and Databricks (Spark SQL).

The behavior contract is pinned by the test suite and the oracle-verified corpora under testdata/ (see docs/regenerate-ground-truth.md).

Index

• type Bucket
  • func (b Bucket) String() string
• type Classification
  • func (c Classification) Header() Classification
• type Dialect
  • func (d Dialect) String() string
• type Kind
• type Lens
  • func New(d Dialect) (*Lens, error)
  • func (l *Lens) Analyze(sql string) (Result, error)
  • func (l *Lens) Classify(sql string) (Classification, error)
• type ParseError
  • func (e *ParseError) Error() string
• type QueryResult
• type Result
• type ScriptResult
• type StatementResult

Examples

• Lens.Analyze
• Lens.Analyze (ResultShapes)
• Lens.Analyze (SchemaDependent)
• Lens.Classify
• ParseError

Types

type Bucket

type Bucket int

Bucket is the coarse routing category of a statement.

const (
	ReadOnly     Bucket = iota + 1 // SELECT and friends: WITH…SELECT, (SELECT), pipe FROM, VALUES
	DML                            // INSERT, UPDATE, DELETE, MERGE (TRUNCATE per dialect)
	DDL                            // CREATE / ALTER / DROP / UNDROP …
	DCL                            // GRANT, REVOKE
	Script                         // ≥2 statements or a procedural-only construct
	Transaction                    // BEGIN/START TRANSACTION, COMMIT, ROLLBACK
	DataMovement                   // COPY INTO, LOAD DATA, EXPORT …, PUT/GET/LIST/REMOVE
	Auxiliary                      // SHOW, DESCRIBE, EXPLAIN, USE, SET, CALL, ASSERT, OPTIMIZE, VACUUM …
)

func (Bucket) String

func (b Bucket) String() string

type Classification

type Classification struct {
	Dialect Dialect
	Bucket  Bucket
	Kind    Kind
}

Classification is the tier-1 result: what the statement is.

func (Classification) Header

func (c Classification) Header() Classification

Header returns the classification itself, satisfying Result.

type Dialect

type Dialect int

Dialect selects the SQL grammar. Fixed at construction.

const (
	BigQuery Dialect = iota + 1
	Snowflake
	Databricks
)

func (Dialect) String

func (d Dialect) String() string

type Kind

type Kind string

Kind is sql-lens's own fine-grained, dialect-neutral statement vocabulary (no vendor publishes a complete one). DDL kinds compose as VERB_OBJECT (e.g. "CREATE_TABLE", "ALTER_MATERIALIZED_VIEW"), so the set is open; the constants below name the fixed, non-composed kinds.

const (
	KindSelect           Kind = "SELECT"
	KindValues           Kind = "VALUES"
	KindGraphQuery       Kind = "GRAPH_QUERY"
	KindInsert           Kind = "INSERT"
	KindUpdate           Kind = "UPDATE"
	KindDelete           Kind = "DELETE"
	KindMerge            Kind = "MERGE"
	KindTruncateTable    Kind = "TRUNCATE_TABLE"
	KindGrant            Kind = "GRANT"
	KindRevoke           Kind = "REVOKE"
	KindScript           Kind = "SCRIPT"
	KindBeginTransaction Kind = "BEGIN_TRANSACTION"
	KindCommit           Kind = "COMMIT"
	KindRollback         Kind = "ROLLBACK"
	KindCall             Kind = "CALL"
	KindExecuteImmediate Kind = "EXECUTE_IMMEDIATE"
	KindAssert           Kind = "ASSERT"
	KindShow             Kind = "SHOW"
	KindDescribe         Kind = "DESCRIBE"
	KindExplain          Kind = "EXPLAIN"
	KindUse              Kind = "USE"
	KindSet              Kind = "SET"
	KindUnset            Kind = "UNSET"
	KindCopyInto         Kind = "COPY_INTO"
	KindLoadData         Kind = "LOAD_DATA"
	KindExportData       Kind = "EXPORT_DATA"
	KindExportModel      Kind = "EXPORT_MODEL"
	KindPut              Kind = "PUT"
	KindGet              Kind = "GET"
	KindList             Kind = "LIST"
	KindRemove           Kind = "REMOVE"
	KindOptimize         Kind = "OPTIMIZE"
	KindVacuum           Kind = "VACUUM"
	KindAnalyzeTable     Kind = "ANALYZE_TABLE"
	KindMsckRepair       Kind = "MSCK_REPAIR"
	KindCache            Kind = "CACHE"
	KindUncache          Kind = "UNCACHE"
	KindRefresh          Kind = "REFRESH"
	KindDeclareVariable  Kind = "DECLARE_VARIABLE"
	KindExecuteTask      Kind = "EXECUTE_TASK"
)

type Lens

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

Lens analyzes SQL for one dialect. Zero-value is invalid; use New.

func New

func New(d Dialect) (*Lens, error)

New returns a Lens for the given dialect.

func (*Lens) Analyze

func (l *Lens) Analyze(sql string) (Result, error)

Analyze classifies the statement and, for ReadOnly statements, counts its output columns.

Example

package main

import (
	"fmt"

	lens "github.com/akshaysangma/sql-lens"
)

func main() {
	l, _ := lens.New(lens.BigQuery)
	r, _ := l.Analyze("WITH t AS (SELECT 1 AS a, 2 AS b) SELECT * FROM t")
	q := r.(*lens.QueryResult)
	fmt.Println(q.Bucket, q.Kind, q.ColumnCount)
}

Output:
ReadOnly SELECT 2

Example (ResultShapes)

Result is a sealed sum type: a type switch over the three shapes is exhaustive.

package main

import (
	"fmt"

	lens "github.com/akshaysangma/sql-lens"
)

func main() {
	l, _ := lens.New(lens.Databricks)
	for _, sql := range []string{
		"SELECT a, b FROM t",
		"BEGIN SELECT 1; SELECT 2; END",
		"OPTIMIZE events ZORDER BY (ts)",
	} {
		r, _ := l.Analyze(sql)
		switch v := r.(type) {
		case *lens.QueryResult:
			fmt.Println("query with", v.ColumnCount, "columns")
		case *lens.ScriptResult:
			fmt.Println("script")
		case *lens.StatementResult:
			fmt.Println(v.Bucket, v.Kind)
		}
	}
}

Output:
query with 2 columns
script
Auxiliary OPTIMIZE

Example (SchemaDependent)

A star over a base table is schema-dependent: the count is -1, never a guess.

package main

import (
	"fmt"

	lens "github.com/akshaysangma/sql-lens"
)

func main() {
	l, _ := lens.New(lens.Snowflake)
	r, _ := l.Analyze("SELECT * FROM prod.sales.orders")
	fmt.Println(r.(*lens.QueryResult).ColumnCount)
}

Output:
-1

func (*Lens) Classify

func (l *Lens) Classify(sql string) (Classification, error)

Classify returns the statement's classification without counting columns.

Example

package main

import (
	"fmt"

	lens "github.com/akshaysangma/sql-lens"
)

func main() {
	l, _ := lens.New(lens.BigQuery)
	c, _ := l.Classify("MERGE t USING s ON t.id = s.id WHEN MATCHED THEN DELETE")
	fmt.Println(c.Bucket, c.Kind)
}

Output:
DML MERGE

type ParseError

type ParseError struct {
	Dialect Dialect
	Msg     string
}

ParseError is the only error kind Analyze and Classify return for bad or unrecognized SQL.

Example

Statements that don't exist in the selected dialect are rejected with a typed *ParseError.

package main

import (
	"fmt"

	lens "github.com/akshaysangma/sql-lens"
)

func main() {
	l, _ := lens.New(lens.BigQuery)
	_, err := l.Analyze("SHOW TABLES") // SHOW is not BigQuery syntax
	fmt.Println(err)
}

Output:
sql-lens (bigquery): unrecognized statement "SHOW"

func (*ParseError) Error

func (e *ParseError) Error() string

type QueryResult

type QueryResult struct {
	Classification
	ColumnCount int
}

QueryResult is the Analyze result for ReadOnly statements. ColumnCount is exact when the select list is resolvable from the SQL text alone (including stars over same-query CTEs); -1 when a star makes it schema-dependent.

type Result

type Result interface {
	Header() Classification
	// contains filtered or unexported methods
}

Result is the sealed outcome of Analyze. Exactly three shapes exist: *QueryResult (ReadOnly — carries ColumnCount), *ScriptResult, and *StatementResult (everything else). A type switch over these is exhaustive.

type ScriptResult

type ScriptResult struct {
	Classification
}

ScriptResult is the Analyze result for scripts. It never carries column info — scripts are detected and flagged only.

type StatementResult

type StatementResult struct {
	Classification
}

StatementResult is the Analyze result for every non-ReadOnly, non-Script statement (DML, DDL, DCL, Transaction, DataMovement, Auxiliary).