Suggestion: db schema as "additional file"; apply validation

[mgravell]

context: https://twitter.com/IanStockport/status/1702313925810024716

imagine a "dotnet tool":

dapper schema {conn-key} 

which:

• resolved dev-time connection-key conn-key
• connected to the database
• probed the server-kind
• dumped the schema to a file such as dbschema.txt
• optional: find the csproj, check the additional file exists; if not, add it

Where dbschema.txt could be something like (is there prior art here? plain text?):

(note on order: objects are invariant alphabetical; columns/parameters/etc are positional)

default schema dbo
server version 16.0.1000.6

table dbo.Foo
column Id int notnull identity readonly
column Name string
column Label string computed readonly

table someSchema.Bar
column Id int notnull identity readonly

view someView
column Id notnull null
...

we would then load the dbschema.txt and use it to validate commands at build time, without needing constant SQL access. Schema should be implied via "default schema" when omitted.

Possible checks:

• object exists
• column exists (including views and functions)
• capitalization
• appropriate data types
• treat views as immutable
• don't allow update/insert to readonly columns
• correct parameters on functions
• correct function kind (scalar vs tabular UDF)
• system functions vs server version
• stored procedure parameters (columns are probably a stretch, unless we get the sp_helptext-etc source and parse that too?)
• server version syntax rules (currently we always assume "latest")

Rewriting the db schema would be a case of re-running the tool, with the changes visible in source control.

Note: advanced SQL analysis is currently limited to SQL Server; we should probably at least not assume that when connecting (use the provider model), but... whatever works.

[IanRingrose]

Sort the table names, and the column names before writing file so diff/merge more likely to work in a senible way. The file should always be 100% same if database is logically the same.

Also think of how a build server could run tool to check that file is the same when database creation script is run then checked in file. But leave details of implementation to the users build management team.

[xPaw]

For inspiration, this exists in PHP land: https://github.com/staabm/phpstan-dba

[amoerie]

Not sure if you're aware about this, but I think this exists in F# already as a type provider: https://fsprojects.github.io/SQLProvider/