Hacker News new | past | comments | ask | show | jobs | submit login

We've used it on our on-premise product for the past maybe 5 years. I still think it's the future for maintaining a SQL database, and have wanted to do something similar for Postgres/OSS.

I'm curious what bugs you've run into, we have generally stopped being worried about the reliability of the diff scripts (we use those, though, we don't generate them on every deployment i.e. the .exe).




There are a few tools providing this workflow on open source databases. I'm the author of one, https://github.com/skeema/skeema, for MySQL and MariaDB. Or for Postgres check out https://github.com/djrobstep/migra.


I've got the sqlpackage.exe hooked into my CI system - I trust that part, generally.

It just has a lot of jagged edges to cut yourself on.

1) Recompiling views - it does not properly figure out the dependency graph of the views before recompiling them, so if view A depends on view B which depends on table C and you're making changes to all 3 views, I have seen changes to View B not properly appear in View A.

2) Performance is bad on large projects. It's gotten better, but still the compile step is very slow. I have a legacy project that used to take 45 minutes to compile in VS2012, then "20 minute coin-toss success or crash" in VS2015, and now finally seems to work okay.

3) It uses the SQL server's server-level case-sensitivity for diffing schema. Most SQL servers have their schema case-insensitive (it's a dangerous server-wide change to change it). This means changes that things that differ only by case will not get published. That can produce cosmetic problems at the very least. We had a very embarassing bug where somebody was formatting a date in a view as "yyyy-mm-dd" which means year minute day. The change to "yyyy-MM-dd" did not get published.

4) The SSDT release for a build-server is really painful. Before it was accessible through some elaborate parameters in the MSBuild headless installer, but now it's been released as a Nuget package... and they forgot to include master.dacpac and msdb.dacpac, so if your databases have dependencies on those you have to copy them internally.

5) Releasing something that you have to access globally as a nuget package is... odd. I still haven't found the exact correct workflow for getting it onto our CI server. Everything I try feels like an ugly hack.

6) None of the build tool installers set up the needed environment variables, leaving you to figure it out yourself on every developer machine and the CI server. This includes path config, which will tempt you to reference the SqlPackage.exe by its fully resolved path... BUT

7) New versions of the SSDT suite break old versions. So don't reference the old version of SqlPackage.exe by its fully-resolved file path because it's broken.

8) It uses the SqlCmd.exe SQL preprocessor but many tools in the MS suite have minimal-to-no support for the directives. Imagine using a C editor where half the tools freak out at the sight of a #DEFINE. The powershell tool "Invoke-SqlCmd" in particular is a serious "unsafe at any speed" quality product.

9) The differ, in particular, fails at (8). Any inter-database references should be done with precompiler aliasing so you can swamp out that inter-database dependency... which is cool, but the differ then finds false-positives because it thinks.

[$(MyDatabaseReference)] in your source is different from [MyDatabase] in the running SQL server, when that's just before/after the token replacement.

10) Timing out on connecting to a database. When connecting to a large database, it can time-out when opening the connection. The setting to fix this is in the registry - there's no file or command-line setting to change it.

11) The Publish UI often opens a modal behind the main Visual Studio UI. Modal behind UI = "why is my window locked!!!"

12) Canceling a compile in-progress basically is the "crash the IDE" button.

I could go on and on...

That's not getting into a lot of the features that we see everywhere else in the MS stack that are conspicuously missing from Database Projects (like Nuget references).




Applications are open for YC Summer 2020

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: