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

On Microsoft SQL server theres a tool in SQL Server Data Tools called "Database Projects". This lets you have the whole database schema in a file tree that can be compiled into a binary object that the SqlPackage.exe executable can diff or publish migrations against a running database.

This provides compile-time analysis of your whole SQL schema.

It's a goddamned buggy disaster and the usability is basically zero, but the concept is cool.

We started using this in Visual Studio for managing the database schema of legacy projects and it works fantastically. You can update the schema files from the database and reverse that to update the database from the schema files. It allows us to put the database schema under version control with all the benefits of branching and merging for a project without traditional migrations.

I haven't found it to be buggy in my usage.

Same here. The previous method was a massive SQL file. Moving to the DB project made such a difference in maintainability.

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).

>It's a goddamned buggy disaster and the usability is basically zero, but the concept is cool.

Yep, the concept is pretty nice, the implementation can be harsh.

Another nice aspect of this is the tooling (Dlls) can be programmed yourself with a .NET language. Ive used the same tooling to build a pre-deploy check for any suspicious DROPs and ALTERs that force manual intervention - it gives you the ability to parse T-SQL exactly how the engine will do it.

Wait, so instead of using SqlPackage.exe you're using the .NET objects to do the deployment, and are auditing the changes as you deploy them? That's cool! Anything posted public on this?

Nothing public and the assembly is basically undocumented but it's basically start with this assembly and go down the rabbit hole :)


Our use case is SqlPackage builds the diff SQL, then we sanity check it using our custom code built on that parse for any DROPs / ALTERs and log/notify for confirmation

Applications are open for YC Summer 2020

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