sqlparser

SQL parser and static analyzer written in Dart. At the moment, this library targets the SQLite dialect only.

Features

This library supports every SQLite statement and expression, which includes parsing and detailed static analysis. We can resolve what type a column in a SELECT statement has, infer types for variables, find semantic errors and more.

This library supports most SQLite features:

• DQL: Full support, including joins, group by, nested and compound selects, WITH clauses and window function.
• DDL: Supports all CREATE and DROP statements supported by SQLite, including advanced features like foreign keys or virtual tables (when a matching module like fts5 is enabled). ALTER TABLE statements can also be parsed.
• Transactions: Supports parsing BEGIN, SAVEPOINT, RELEASE, COMMIT and ROLLBACK statements.
• Management: All statements (VACUUM, ATTACH, REINDEX, ANALYZE, PRAGMA).

Using the parser

To obtain an abstract syntax tree from an SQL statement, use SqlEngine.parse.

import 'package:sqlparser/sqlparser.dart';
                
                final engine = SqlEngine();
                final result = engine.parse('''
                SELECT f.* FROM frameworks f
                  INNER JOIN uses_language ul ON ul.framework = f.id
                  INNER JOIN languages l ON l.id = ul.language
                WHERE l.name = 'Dart'
                ORDER BY f.name ASC, f.popularity DESC
                LIMIT 5 OFFSET 5 * 3
                  ''');
                // result.rootNode contains the select statement in tree form
                

Analysis

Given information about all tables and an SQL statement, this library can:

1. Determine which result columns a query is going to have, including types and nullability
2. Make an educated guess about what type the variables in the query should have (it's not really possible to be 100% accurate about this because SQLite is very flexible at types, but this library gets it mostly right)
3. Issue basic warnings about queries that are syntactically valid but won't run (references unknown tables / columns, uses undefined functions, etc.)

To use the analyzer, first register all known tables via SqlEngine.registerTable. Then, SqlEngine.analyze(sql) gives you an AnalysisContext which contains an annotated AST and information about errors. The type of result columns and expressions can be inferred by using AnalysisContext.typeOf(). Here's an example:

final id = TableColumn('id', const ResolvedType(type: BasicType.int));
                final content = TableColumn('content', const ResolvedType(type: BasicType.text));
                final demoTable = Table(
                  name: 'demo',
                  resolvedColumns: [id, content],
                );
                final engine = SqlEngine()..registerTable(demoTable);
                
                final context =
                    engine.analyze('SELECT id, d.content, *, 3 + 4 FROM demo AS d');
                
                final select = context.root as SelectStatement;
                final resolvedColumns = select.resolvedColumns;
                
                resolvedColumns.map((c) => c.name); // id, content, id, content, 3 + 4
                resolvedColumns.map((c) => context.typeOf(c).type.type); // int, text, int, text, int, int
                

But why?

Drift, a persistence library for Dart apps, uses this package to generate type-safe methods from SQL.

Thanks

• To Bob Nystrom for his amazing "Crafting Interpreters" book, which was incredibly helpful when writing the parser.

0.43.1

• Make NamedWindowDeclaration an AST node.
• Support parsing DROP, VACUUM, REINDEX, ATTACH, DETACH, SAVEPOINT RELEASE, ROLLBACK, ANALYZE, PRAGMA and ALTER TABLE statements.
• Add SchemaBuffer class to apply ALTER TABLE and DROP statements to CREATE statements.

0.43.0

• Allow parsing SQL from FileSpans, which is convenient for SQL embedded as text in outer structures.

0.42.1

• Support new features introduced in SQLite version 3.51.0.

0.42.0

• Fix unresolved references in CTEs resolving against the scope of the main query.
• Static analysis support for the PowerSync SQLite extension.

0.41.2

• Improve error message on unknown columns when it looks like the identifier should have been a string literal.
• Infer the output of lag, lead and nth_value to be nullable.

0.41.1

• Support new features introduced in SQLite version 3.50.0.

0.41.0

• Replace ColonNamedVariable with NamedVariable for all named variables.
• Analysis support for SQLite 3.48.
• Fix nullability analysis around fts5 tables (enabled when raising the version to 3.48 to preserve backwards-compatibility).

0.40.0

• Add support for the dbstat module.
• Prioritize null propagation in type resolver, leading to more accurate analysis on which columns are nullable.

0.39.2

• Fix false-positive lint for a parameter count mismatch on bm25().
• Fix type interference around fts5 function calls.

0.39.1

• Improve recovery in parser when encountering syntax errors.

0.39.0

• When using the parse methods on SqlEngine, tokenizer errors are now included in the result instead of throwing an exception.

0.38.1

• Fix upcoming unreachable_switch_default lint.

0.38.0

• Resolve json_extract return types from context if possible.
• Warn when INSTEAD OF triggers are not used on views.
• Represent writes to views in TableWrite.

0.37.1

• Refine nullability analysis for subquery expressions: Queries with aggregate invocations are no longer considered nullable (the aggregate invocation had to be a top-level expression before).

0.37.0

• Add support for sqlite 3.46.0.
• Make columns coming from subquery expressions nullable.

0.36.0

• Add support for the geopoly module.

0.35.1

• Fix IN expressions accepting aliases, causing some queries to get parsed incorrectly.

0.35.0

• Fix parsing binary literals.
• Expand support for IN expressions, they now support tuples on the left-hand side and the shorthand syntax for table references and table-valued functions.
• Drift extensions: Allow custom class names for CREATE VIEW statements.
• Drift extensions: Support the INT64 hint for CREATE TABLE statements.

0.34.1

• Allow selecting from virtual tables using the table-valued function syntax.

0.34.0

• Fix explicit NULL column constraints being dropped when converting nodes to SQL.
• Add analysis errors for illegal unqualified references to old and new in triggers.
• Analysis support for sqlite 3.45 and jsonb functions.

0.33.0

• Support the column-name-list syntax for updates, thanks to @tibotix.

0.32.1

• Treat the result of sum() as nullable when inferring types.
• Support features added in sqlite 3.44:
  • ORDER BY clauses as part of aggregate functions.
  • Support concat, concat_ws and string_agg.

0.32.0

• Turn ResolvedType.hints into a list, supporting multiple type hints.

0.31.3

• Fix star columns expanding to more columns than they should.

0.31.2

• Add CaseInsensitiveMap.of to wrap existing maps.

0.31.1

• Add the sqlite3_schema table to the builtin tables supported by every SqlEngine instance.
• Support the timediff and octet_length functions from sqlite 3.43.0.

0.31.0

• Add SqlEngine.parseMultiple to parse multiple statements into one AST.

0.30.3

• Fix WITH clauses not being resolved for compound select statements.

0.30.2

• Fix false-positive "unknown table" errors when the same table is used in a join with and then without an alias.

0.30.1

• Report syntax error for WITH clauses in triggers.

0.30.0

• Add previous and next fields for tokens

0.29.0

• Parser support for constructor names in WITH drift syntax.
• Support resolving IIF functions.
• Fix a crash when a CTE is used on an insert, update or delete statement.
• Fix wrong column names being reported for references in subqueries and CTEs.

0.28.1

• Fix false-positive warnings about AS aliases in subqueries used in triggers.

0.28.0

• Support the unhex function added in sqlite 3.41.0
• Support custom keyword sets when formatting SQL.

0.27.0

• Add mappedBy to ExpressionResultColumn when parsing in drift mode.

0.26.1

• Fix missing space when formatting aggregate functions.

0.26.0

• Remove token parameter from constructor in Literal subclasses and NumberedVariable.

0.25.0

• Better analysis support for ANY columns in STRICT tables.
• Assign resolved schema columns to synctactical ResultColumn in queries.

0.24.0

• Make Fts5Table constructor public.

0.23.3

• Analysis support for the spellfix extension.

0.23.2

• Support resolving the fts5vocab module when fts5 is enabled - thanks to @FaFre.
• Support the rtree extension - also thanks to @FaFre!
• Improve references resolving across subqueries.

0.23.1

• Gracefully handle tokenizer errors related to @ or $ variables.

0.23.0

• Apply type hints for date times on textual datetime functions as well.

0.22.0

• Refactor how tables and columns are resolved internally.
• Lint for DISTINCT misuse in aggregate function calls.
• Support the RIGHT and FULL join operators added in sqlite 3.39.0.
• Support IS DISTINCT FROM and IS NOT DISTINCT FROM syntax added in sqlite 3.39.0.
• Fix type inference for SUM() calls around int "subtypes" (like booleans).

0.21.0

• Analysis support for new features in sqlite version 3.38.
• Replace internal moor references with drift references.

0.20.1

• Fix SQL generation for upsert statements with a conflict target.

0.20.0

• Support LIST columns for a drift-specific feature.

0.19.2

• Improve handling of drift-specific column types in STRICT tables.

0.19.1

• Make the result of group_concat nullable.

0.19.0

• Support generated columns.
• Support features introduced in sqlite version 3.37, most notably STRICT tables.

0.18.1

• Fix the AST comparator missing errors for different amount of children.

0.18.0

• Fix unecessary errors around fts5 tables
• Merge all moor-specific nodes into a single visitMoorSpecific visitor method
• Parse BEGIN and COMMIT statements
• Improve type inference around RETURNING clauses.

0.17.2

• Fix nullability analysis of COALESCE and IFNULL

0.17.1

• Fix nullability analysis of references and star columns

0.17.0

• Refactor how tables and columns are resolved in statements
• The new ResultSetAvailableInStatement class describes a result set that has been added to a statement, for instance through a from clause
• A TableOrSubquery with an alias now introduces a TableAlias instead of the original table

0.16.0

• New analysis checks for RETURNING: Disallow table.* syntax and aggregate expressions
• Support RAISE expressions in triggers
• Fix resolving columns when RETURNING is used in an UPDATE FROM statement
• Fix aliases to rowid being reported as nullable

0.15.0

• Breaking: Change InsertStatement.upsert to a list of upsert clauses
  • Support multiple upsert clauses
  • Do not require a conflict target in the last clause
• Support RETURNING clauses in updates, deletes and inserts
• Support FROM clauses in UPDATE statements
• Support MATERIALIZED/NOT MATERIALIZED hints in common table expressions
• Add BuiltInMathExtension which corresponds to the -DSQLITE_ENABLE_MATH_FUNCTIONS compile-time option for sqlite.
• Add EngineOptions.version argument to specify the desired sqlite version. Using newer features will be reported as analysis warnings.
• Fix rank columns of fts5 tables being misreported as integers

0.14.0

• Fix views using common table expressions

0.13.0-nullsafety.0

• Parse ordering in table key constraints
  • Deprecate KeyClause.indexedColumns in favor of KeyClause.columns

0.12.0-nullsafety.0

• Migrate to null-safety
• Remove legacy type inference
• Parser support for new moor features

0.11.0

• New package:sqlparser/utils/node_to_text.dart library that turns an AST node back into a textual representation.
• Fix precedence of CASE expressions

0.10.1

• Scan identifiers with [bracket syntax]
• NumericToken now contains individual lexemes making up the number
• Improve error messages in some scenarios
• Fix type inference for binary expressions where the operands have incompatible types
• Improve type inference around NULL

0.10.0

• Breaking: Made RecursiveVisitor.visit, visitList and visitExcept an extension on AstVisitor.
• Support the transformer pattern to modify ast nodes
• Breaking: FrameBoundary, DeleteTarget, UpdateTarget, DefaultValues and InsertTarget are no longer constant
• Breaking: Removed visitQueryable. Use defaultQueryable instead.
• Support parsing and analyzing CREATE VIEW statements (see SchemaFromCreateTable.readView). Thanks to @mqus for their contribution!
• SqlEngine.parse will no longer throw when there's a parsing error (use ParseResult.errors instead).
• Parse DEFERRABLE clauses on foreign key constraints
• Parse NULLS FIRST and NULLS LAST on ORDER BY terms

0.9.0

• New package:sqlparser/utils/find_referenced_tables.dart library. Use it to easily find all referenced tables in a query.
• Support row values including warnings about misuse

0.8.1

• Support collate expressions in the new type inference ([#533](htt ps://github.com/simolus3/moor/issues/533))
• Added visitCollateExpression to the visitor classes

0.8.0

• Remove SqlEngine.withOptions constructor - the default constructor now takes options
• Changed SelectStatement.from from List<Queryable> to Queryable?. Selecting from multiple tables with a comma will now be parsed as a JoinClause.
• Changed SelectStatementAsSource.statement from SelectStatement to BaseSelectStatement and allow compound select statements to appear in a FROM clause
• Support the VALUES clause as select statement
• The new type inference engine is now enabled by default and the enableExperimentalTypeInference option has been removed. To continue using the old engine, the useLegacyTypeInference flag can be used.

0.7.0

• New feature: Table valued functions.
• Breaking: Removed the enableJson1 parameter on EngineOptions. Add a Json1Extension instance to enabledExtensions instead.
• Parse rowid as a valid reference when needed (SELECT rowid FROM tbl is now parsed correctly)
• Parse CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP
• Parse UPSERT clauses for insert statements

0.6.0

• Breaking: Added an argument type and argument to the visitor classes
• Experimental new type inference algorithm (SqlEngine.withOptions(EngineOptions(enableExperimentalTypeInference: true)))
• Support CAST expressions and the ISNULL / NOTNULL postfixes
• Support parsing CREATE TRIGGER statements
• Support parsing CREATE INDEX statements

0.5.0

• Optionally support the json1 module
• Optionally support the fts5 module

0.4.0

• Support common table expressions
• Handle special rowid, oid, __rowid__ references
• Support references to sqlite_master and sqlite_sequence tables

0.3.0

• parse compound select statements
• scan comment tokens
• experimental auto-complete engine (only supports a tiny subset based on the grammar only)
• some features that are specific to moor

0.3.0+1: Accept \r characters as whitespace

0.2.0

• Parse CREATE TABLE statements
• Extract schema information from parsed create table statements with SchemaFromCreateTable.

0.1.2

• parse COLLATE expressions
• fix wrong order in parsed LIMIT clauses

0.1.1

Attempt to recognize when a bound variable should be an array (eg. in WHERE x IN ?). Also fixes a number of parsing bugs:

• Parses tuples, proper type resolution for IN expressions
• Don't resolve references to tables that don't appear in the surrounding statement.
• Parse joins without any additional operator, e.g. table1 JOIN table2 instead of table1 CROSS JOIN table2.
• Parser now complains when parsing a query doesn't fully consume the input

0.1.0

Initial version, can parse most statements but not DELETE, common table expressions and other advanced features.