Question: Creating a provider without an access to DB

[AlexeyRaga]

Hi, thanks for the lib, looks great!

One question: it is possible (or can it be possible) to instantiate providers without requiring to have an access to DB?
Give them a schema file, or somehow similar?

This would allow building in CI easier, more reproducible builds, etc...

[jackfoxy]

Pretty sure there are other SQL TPs that work off provided schemas. This project needs some fixes to be net5.0 compatible., most especially #374

[charlesroddie]

You need to create a db based on a schema file. For example in our SSDT project we have this post-build event:

for /f "delims=" %%a in (' powershell -NoProfile -ExecutionPolicy Bypass "$(ProjectDir)SetPowershellPath.ps1" ') do set "psPath=%%a"

"%psPath%" -NoProfile -ExecutionPolicy Bypass -Command "& '$(ProjectDir)create_db.ps1' '$(ProjectDir)'"

The powershell files are:

param( 
    [string]$BasePath
)

$isEnv64Bit = [Environment]::Is64BitProcess
if (-Not ($isEnv64Bit)) {
	return [System.Environment]::ExpandEnvironmentVariables("%SystemRoot%\sysnative\WindowsPowerShell\v1.0\powershell.exe")
}

return [System.Environment]::ExpandEnvironmentVariables("%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe")

and

param( 
    [string]$ProjectDir
)

write-output "--- STARTING DB BUILD ---"
write-output "STARTING WITH PROJECT DIR: " $ProjectDir $Test
$isEnv64Bit = [Environment]::Is64BitProcess
if (-Not ($isEnv64Bit)) {
	write-output "THE POWERSHELL MUST RUN IN 64 BIT MODE"
	exit 1
}

write-output "GETTING MODULES..."
Import-Module -Name SqlServer
# Remove | Out-Null to print the output
Get-Command -Module SqlServer | Out-Null

write-output "DATABASE INFO..."
SqlLocalDB.exe info "MSSQLLocalDB"
write-output "STOPPING..."
SqlLocalDB.exe stop "MSSQLLocalDB"
write-output "DROPPING..."
Invoke-Sqlcmd -ServerInstance "(localdb)\MSSQLLocalDB" -Query "DROP DATABASE IF EXISTS MainDB" -ErrorAction 'Stop'

write-output "INVOKING DB INSTANCE WITH PATH:"

$create_sql = "$ProjectDir" + "bin\Output\MainDB_Create.sql"
write-output $create_sql

Invoke-Sqlcmd -ServerInstance "(localdb)\MSSQLLocalDB" -InputFile "$create_sql" -ErrorAction 'Stop'

IF ($LASTEXITCODE -ne 0) {
    Write-Host "--- DB CREATION FAILED ---"
    Write-Host $LASTEXITCODE
    exit 1
}
ELSE {
    Write-Host "--- DATABASE BUILD COMPLETE ---"
}

Took ages to get this right because of the terrible languages involved, but once setup the process becomes very smooth.

[smoothdeveloper]

One question: it is possible (or can it be possible) to instantiate providers without requiring to have an access to DB?
Give them a schema file, or somehow similar?

I've investigated approach of serializing the stuff to have the SqlCommandProvider working on top of a serialized .json of the schema metadata used by the provider at design time.

It is a viable approach, but it still requires, when updating .sql files (which invalidates the .json cache) to have a dbserver.

The usual approach, as far as I'm concerned with using this library, is to have db preparation in the build script, and indeed, having that db available on the development / integration machine.

I don't have free time to commit into taking this investigation/prototype towards a stable implementation and updated version of the library, I've seen the pros/cons of the above while experimenting and it doesn't help much in my current usecase.