Improve your SQL Development experience: Deployer

Background

The foundation of Deployer began many years ago when I was a full stack software developer working on an iOS application for a startup company. In order to deliver my features to the app I had to do a significant amount of work in the database - create and alter stored procedures, functions, table schemas, etc.

It wasn’t uncommon to have 10 - 20 or more SQL changes that needed to be “deployed” to the QA, staging and production environments during the different phases of development.

The default approach to “deploying” these SQL scripts was to do just that: open the script in the SQL IDE, connect to the database, execute, and repeat. This is not so bad to do one time for a batch of scripts, but I found myself repeating this process, sometimes multiple times per day, for various reasons.

Crawl

So I built a small command line tool which used sqlcmd to iterate through the SQL scripts and execute them. This worked for a period of time, and was much better than the “old” way, but it lacked transparency to errors and didn’t provide a friendly user experience. I also found that other team members were intrigued by my solution but my tool wasn’t very shareable.

Walk

Mega Deployer was the next step - a Mac app that made it easy for the team to use, whether a developer or QA. This was a big step forward, but it still required some initial configuration - set paths, configure DB connections, etc. I wanted a way to make a single packaged app that anyone could use while configuring the absolute minimum.

And so I began working on another app which leveraged Docker which was a much better solution. Before work was finished on the Docker approach, it occurred to me that I would still be making a Mac app for the UI. This was going to leave out Windows and Linux users.

Run

Azure Data Studio turns out to be a much better platform to target, rather than targeting a specific OS. If you haven’t heard of it before, ADS is a “cross-platform hybrid data analytics tool” - in other words it’s a SQL IDE that will feel right at home to anyone using VSCode. In fact, VSCode and ADS have a lot in common, including the APIs they have access to.

And so was born the Deployer extension for Azure Data Studio which is a full-featured product developed by the team here at Riff (formerly Rollersoft). It’s also free.

Features

Deployer has come a long way since its original use case. It can still execute SQL files in sequence, but it also provides a set of features that make the lives of developers who use SQL much easier.

Execute SQL files

Deployer can execute SQL files in sequence, and provides a report of which files were executed successfully, which failed and why they failed.

Compare Stored Procedures

It can also quickly and easily provide a diff between a stored procedure you’ve updated locally with the version that is currently on a remote server. You can even select a series of stored procedures you want to compare, and Deployer will give you the ability to “deploy” them one-by-one as your review the changes. It’s pretty slick.

Optimize SQL (Beta)

We’re still working out the kinks here, but Deployer does a pretty good job of suggesting changes to a SQL file, thanks to OpenAI APIs. You can select your SQL file for optimization, and you’ll get a diff with the suggested changes right in your editor. PS, you’ll need to add your OpenAI API Key in order to use this feature.

All the things

Deployer also includes features like the ability to select from a table with a customizable “order by” and “top” clause, the ability to quickly pull up a remote stored procedure definition from the context menu, and more.

Conclusion

We hope you try out the Deployer extension and that it saves you time like it did for us. We’re still working on it and have regular releases, so keep an eye on the public repo for new features. If you have feature requests or bug reports please create an issue. Thanks for checking it out!