How to get types to update after changing .sql file?

[charlesroddie]

Is there a way to get types to update after changing an sql file in SqlFile or SqlCommandProvider, without restarting Visual Studio?

[vasily-kirichenko]

Change the connection string or file path literal (add a space somewhere, etc.)

[charlesroddie]

That doesn't work, at least on VS2019. There must be some caching.

[vasily-kirichenko]

Ah, VS. Sorry, I don't use it. Maybe someone who does use it can help.

[smoothdeveloper]

@charlesroddie my approach is to autogenerate the file containing the queries from a script, and doing so only when the .sql files themselves changed, this way the F# project gets recompiled.

As for the editor picking up the changes, it depends the places you are using the command types, but it sometimes it can take a while before it becomes available, I generally don't bother and type the code I want (it is just matter of column and parameter identifiers) until I want to recompile.

I believe the type provider has provision for caching and filesystem watcher on the .sql file but I'm not acquainted with that area of the code.

Edit: https://github.com/fsprojects/FSharp.Data.SqlClient/blob/dabf2cb456f9d592cfaf97818882cba8601a08dd/src/SqlClient.DesignTime/SqlFileProvider.fs#L51

[charlesroddie]

So @smoothdeveloper you autogenerate an .fs file out of .sql files? Definitely changing the path literal should be a way to force a reread of the file, but it's not happening. I can't tell whether this is an FSharp.Data.SqlClient issue or a VS2019 issue. @jerry40 @monkieboy

[oikku]

@charlesroddie I had the same problem and I ended up doing this change to FSharp.Data.SqlClient code https://github.com/fsprojects/FSharp.Data.SqlClient/pull/332

It seems to fix the problem at least for me. Now if I modify SQL file it is enough to do some change in F# file after that and now SqlFile<....>.Text contains the modified content of SQL file. No need to modify connection string or anything. It is enough if you delete or add en empty line to F# file.

[smoothdeveloper]

So @smoothdeveloper you autogenerate an .fs file out of .sql files?

yes, when the content or file is outdated / non existing, my build script generates all the SqlCommandProvider instantiation code, which forces recompilation of projects depending on those.

I can't tell whether this is an FSharp.Data.SqlClient issue or a VS2019 issue.

@charlesroddie I've seen similar behaviour in VS2019 with edits around TP usage (not specific to SqlClient ones) I believe there was change in the tooling affecting this, but I wouldn't rule out that the mecanism in current implementation of the provider is buggy.

Would you be able to give a shot at @oikku PR to see if the issue is mitigated / solved?

[charlesroddie]

@smoothdeveloper We have moved to user-defined functions and stored procedures (in an ssdt project) instead of referencing sql files, but I think the PR extends to this.

Currently (with released Nuget package 2.0.2), after a change to a UDF (and building ssdt which includes local deploy):

• Unload and reload of the project gets the new UDFs
• Rebuild gets the new UDFs
• Changing the connection string and reverting does not get the new UDFs

It's actually not that bad to unload/reload or rebuild but it would be nice to have something faster.

If there is a pre-release nuget package I can test. @dmitry-a-morozov can you do this or add give @smoothdeveloper rights? If there isn't @monkieboy or @jerry40 may be able to look at it.

[smoothdeveloper]

@charlesroddie thanks for coming back, are you storing the UDF/USP in .sql files in the working copy?

@charlesroddie @cmeeren, a simple workaround would be to have a watcher process, but this is still significant work to get something robustly tested / effective.

I'm not bothered by this in my usage of the library, my workflow:

• do my .sql changes in working copy
• call my build script or the individual .fsx script taking care of regenerating the .fs in my projects which has all the type provider instanciations

in the CI, the build script always regenerate, even if the developer forgot to do the step, and breaking changes fail to compile, so there is no risk of shipping half of the changes if they are breaking changes.

For example, dealing with same kind of issues with assembly resources (which is 100% MS designed and integrated in IDE) is still more time consuming than the steps I'm describing.

If a simple msbuild/fsproj solution can be found, this would be the best.

Another way, would be to figure out a design fitting with dotnet watch and doing touch on the .fs with SqlFile being edited, so msbuild just rebuilds that project.

I'm definitely interested in the best solution for this, but the issue still feels user environment related and I don't having a clear picture on how to fix it with no false positive or misses in all environments.

[cmeeren]

a simple workaround would be to have a watcher process

Workaround? The way I see it, a watcher process (or similar) is the only complete fix to this issue. :)

but this is still significant work to get something robustly tested / effective.

Certainly. If I can help, let me know! I have never worked with type provider code before, so I don't know how much help I can be, and I'd need some pointers/tips/guidance. But this is an itch I want to scratch.

Also, currently it's "broken" anyway. :)

the issue still feels user environment related

The way I see it, we're simply wanting SqlProvider to support the most basic case: An SQL file referenced from an .fs file, no external tools, no CI. Just VS.

(Incidentally, this is how we're working at our company. No CI, no external tools. Just the IDE and manual deploys. I wish it were different, but it isn't and won't be for the foreseeable future, and I can imagine quite a lot of other enterprisey devs are in the same boat.)

[charlesroddie]

@smoothdeveloper UDF/USPs are in sql files in an SSDT project (so they get nice composition and intellisense). A pre-build powershell step on the FSharp.Data.SqlClient-using project takes the Create.sql build output of the SSDT project and deploys it to a localdb, from which it is accessed for design-time analysis.

If there were a watcher (and it's nothing critical for us) it would need to watch either the localdb or the Create.sql file.