Third-Level Schema Validation

Level 3 is QueryLens schema-backed validation. At this level, QueryLens validates SQL against a loaded database snapshot so it can catch missing tables, missing columns, missing stored procedures, and schema-backed write restrictions.

Current Support Status

SQL Server: Load Schema and Validate Table, Column, and Procedure Names

QueryLens.Reset();
QueryLens.Dialect = DialectProfile.SqlServer2019;
QueryLens.LoadDbSchemaFrom(connectionString);

Assert.True(QueryLens.HasLoadedSchema);

SQL Server Table Example

var missingTable = new SqlQuery("SELECT * FROM dbo.missing_orders");

Assert.False(missingTable.IsValid);
Assert.Contains(missingTable.Errors, issue => issue.Code == "QL0010");

SQL Server Column Example

var missingColumn = new SqlQuery("SELECT missing_column FROM dbo.orders");

Assert.False(missingColumn.IsValid);
Assert.Contains(missingColumn.Errors, issue => issue.Code == "QL0011");

SQL Server Stored Procedure Example

var missingProcedure = new SqlStoredProc("EXEC dbo.usp_missing_orders @BatchId = @BatchId");

Assert.False(missingProcedure.IsValid);
Assert.Contains(missingProcedure.Errors, issue => issue.Code == "QL0012");

PostgreSQL: Current Limitation

PostgreSQL profiles support Level 2 dialect validation today, but Level 3 schema-backed validation is not currently implemented. That means QueryLens can validate PostgreSQL syntax and functions, but not loaded PostgreSQL tables, columns, or procedures from a schema snapshot.

QueryLens.Reset();
QueryLens.Dialect = DialectProfile.PostgreSql14;

var query = new SqlQuery("SELECT now()");
Assert.True(query.IsValid);

// QueryLens.LoadDbSchemaFrom(...) is not currently available for PostgreSQL-backed Level 3 validation.

Oracle: Current Limitation

Oracle profiles also support Level 2 dialect validation today, but Level 3 schema-backed validation is not currently implemented. QueryLens can validate Oracle-specific syntax and functions, but not Oracle schema objects through a loaded snapshot.

QueryLens.Reset();
QueryLens.Dialect = DialectProfile.Oracle19c;

var query = new SqlQuery("SELECT employee_id FROM employees");
Assert.True(query.IsValid);

// QueryLens.LoadDbSchemaFrom(...) is not currently available for Oracle-backed Level 3 validation.

When To Use This

• Use Level 3 when schema drift and object-name regressions are important failure modes.
• Use SQL Server profiles for real schema-backed validation today.
• Use PostgreSQL and Oracle in Level 2 dialect mode until schema-backed support exists for those engines.

Related

• First Schema-Backed Validation
• Load a Database Schema Snapshot
• Dialect Profiles