Question: Creating a provider without an access to DB #409

Activity

1. jackfoxy commented on Jun 30, 2021

   

   jackfoxy

   on Jun 30, 2021

   More actions

   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
2. charlesroddie commented on Aug 4, 2021

   

   charlesroddie

   on Aug 4, 2021

   More actions

   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.
3. smoothdeveloper commented on Sep 26, 2021

   

   smoothdeveloper

   on Sep 26, 2021

   Collaborator

   More actions

   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.
4. github-actions

   added

   question

   on Mar 7, 2026
5. github-actions commented on Mar 14, 2026

   

   github-actionsBot

   on Mar 14, 2026 – with GitHub Actions

   Contributor

   More actions

   🤖 This is an automated response from Repo Assist.

   This question is closely related to #406 (DACPAC support) and #404 (generated types). Here's a summary of the options:

   Option 1: Local DB from schema (current best workaround)
   As @charlesroddie described, the practical approach is to create a local DB from your SSDT schema file as part of the build process. This gives you a real DB available at design time and in CI, without needing a full SQL Server instance.

   Option 2: DACPAC-based offline schema parsing (see #406)
   @Thorium pointed out that SQLProvider's Ssdt.DacpacParser.fs (at fsprojects/SQLProvider) can parse a DACPAC file without a live DB connection. Adapting this for FSharp.Data.SqlClient would allow the type provider to work entirely offline using schema metadata extracted from a DACPAC. This would be a significant feature addition.

   Option 3: Generated types (see #404)
   A generated type provider would run once at build time, persist the generated code, and allow dependents to use it without a DB. This is the most ergonomic long-term solution but requires substantial design and implementation work.

   Option 4: Schema metadata caching
   As @smoothdeveloper investigated: serialising the schema metadata to JSON works, but still requires the DB when SQL files change. This reduces but doesn't eliminate the DB dependency.

   If CI access to a DB is the main pain point, Option 1 with LocalDB is usually the most practical path. A minimal PowerShell or bash script that creates the LocalDB from your DACPAC's deploy script can be added as a CI step.

   Generated by Repo Assist · ◷

   To install this agentic workflow, run

   gh aw add githubnext/agentics/workflows/repo-assist.md@346204513ecfa08b81566450d7d599556807389f