If you write code to SQL Server then you might be interested in this: at the end of 2017 I wrote a tSQLt tdd training course which has helped over 300 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club. The course is free if you are happy to wait 10 weeks to complete it, with 1 lesson being made available per week - if you are in more of a hurry or you would like to help support the project you can purchase the course which makes it instantly available to you.
The general goals of this are not to re-created the ssdt experience in visual studio but to provide a lighter, faster way of developing sql code, if I can have an extension that:
- is fast
- is light weight - memory is important for dev machines and 2 gb for a large db project is limiting
- gives us the important things like pre,post deploy scripts, refactoring and the ability to generate dacpac
I am not really interested in providing ui’s like the schema compare - for that use SSDT or spend some money on the Redgate tools.
I am also not interested in replacing what the sql tools team are doing, I am happy to leave them to do the harder, important but less interesting things to me like t-sql formatting so with that in mind I have started a new project that is very hacky at the moment, more an experiment to see if it will work but a vs code extension that builds dacpacs:
This is basically just a wrapper around the DacFx so there shouldn’t be anything too hard and also because it is windows only for now (until the DacFx is cross platform it will only ever be windows, but I hold out hope for cross platform DacFx one day!).
This works similarly to the sql tools team extension in that there is a .net app that is called by the vs code extension (typescript running on node.js) so if you wanted to try this, download the repo, run the SSDTWrap exe (not under a debugger or you will face t-sql parsing first chance exception performance hell). Then in vs code open the folder “src\s2” and the “extension.ts” file and F5 - this will open a new vs code window - open a folder with your .sql files and it will create a t-sql model and report any errors.
If you do ctrl+shift+p to open the command pallette and then do “build dacpac” it will generate a dacpac for you from the sql files. You will need to put this ssdt.json file in the root of the directory you open in vscode:
"path": "C:\\Program Files (x86)\\Microsoft Visual Studio 14.0\\Common7\\IDE\\Extensions\\Microsoft\\SQLDB\\Extensions\\SqlServer\\140\\SQLSchemas\\master.dacpac"
It doesn’t really support a lot of things at the moment but I will add in the support needed to build a dacpac including refactorlog.xml, pre/post deploy scripts and references that we all know and love.
I tested with a folder of 2000 procedures, I tried testing 10,000 but I couldn’t get ssdt to load them into a project without crashing (on a top of the range i7, 32gb ssd etc laptop) - in the end I settled for 2000 procs and to build the dacpac the times were:
Visual Studio / SSDT
so as well as handling larger projects it is faster as well, a small project (one proc/table) was about 17 seconds to build the dacpac.
Anyway, it is all a bit of fun and pretty hacky at the moment but I like using vs code anyway and am finding it much more light weight than visual studio so will likely invest some more time in it.
If you feel like trying it out, good luck :)
May 5, 2017 - 21:19
The DACFx public model API
The DACFx public model API was originally designed as a formal interface for SSDT to interoperate with DACFx, replacing its various internals visible access points. That never happened (and frankly, I’d be shocked if it ever did happen), but it’s interesting to see the public API used here as it was originally intended. Cool stuff!