Database tools I was surprised existed
But you already know all the tools for working with databases don’t you?
That’s what I thought too before I started working on SQL Schema Explorer. Even after 18 years working with databases it turns out I only knew a fraction of the tools that are out there.
Working on SQL Schema Explorer gave me a reason I didn’t have before to search the internet in new ways relating to databases, and now that I’m reaching out to people about SQL Schema Explorer people are in turn sharing their favourite tools with me.
I’d hate for this gift to sit hidden away in my private product trello board, so I’ve turned it into this article so that you can skip the wilderness years and gain a zen-like awareness of all the tools of your trade. I’m sure you won’t like or use all of them, and many overlap in function, but the following list will allow you to try out and asses each one against your needs; perhaps you’ll find a new tool that will save you hours, or make your job that much more enjoyable. Never again will you have to say to a new colleague “oh I hadn’t heard of that one!”.
Microsoft Sql Server
SSMS Tools Pack
An add-on to management studio that improves many pieces of of ssms and adds new capabilities.
- €30 for first computer to €5000 for unlimited enterprise use (SSMS Toolpack Pricing page)
A friend of mine saved many hours using the “CRUD” stored procedure generation capabilities.
Sql Server Management Studio (SSMS)
Okay you know this one but I have to mention it.
It has awkward but functional diagram support. You can version control these diagrams and move them between servers with https://github.com/timabell/database-diagram-scm which is worth knowing about if you ever use the ssms diagrams.
You can now run MSSQL on open source
Did you know Microsoft SQL Server (aka mssql) is now available on both linux natively and in docker containers? It’s the real deal, not like mono vs .net
Here’s all it takes to fire up mssql, the only pre-requisite is docker itself.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=your_new_sa_passwod' \ -p 1433:1433 --name mssql1 \ -d mcr.microsoft.com/mssql/server:2017-latest
This isn’t a tool per-se, but this is too important to not mention, especially as it’s quite new and not everyone is aware of this exciting change from redmond.
I don’t know about you but one less reason to fire up the Windows VM sure does make me happy. Combined with dotnet core I haven’t fired up Windows in months now.
“RazorSQL is an SQL query tool, database browser, SQL editor, and database administration tool for Windows, macOS, Mac OS X, Linux, and Solaris. RazorSQL has been tested on over 40 databases, can connect to databases via either JDBC or ODBC”
EZ Data Browser
- $29.99 per computer
- Licence key by email
- pay via PayPal of credit card
“software from Softimum Solutions to help users to browse and to edit SQL Server databases quickly and easily”
Provides a configurable MSAccess-like interface to a database.
Cross-database query builder/analyser. Feature rich - data/schema compare, pivot queries, diagrams etc.
“A powerful SQL tool to transform or reverse complex queries (generated by OBIEE, Microstrategy, Cognos, Hyperion, Pentaho …) into diagrams to ease visualization and analysis. A graphical query builder that permits to create complex SQL queries easily. The GUI with multi-connections supports virtually all JDBC drivers, including ODBC bridge, Oracle, MySQL, PostgreSQL, Firebird, HSQLDB, H2, CsvJdbc, SQLite. And top of that, everything is open-source!”
- SQLeo demo video on youtube
- Sourceforge page (ugh) https://sourceforge.net/projects/sqleo/
- Fork on github https://github.com/ojwanganto/SQLeo
Live online sql editor / runner
“DbSchema is an SQL and No-SQL database designer featuring interactive diagrams, HTML and PDF documentation, schema versioning and migration, relational data browse, random data generator, visual query builder, SQL editor and database reports.”
“Free Universal Multi-Database SQL Prompt - DbPrompt can execute queries on multiple databases, transfer data between databases, upload result files on ftp servers, execute complex SQL scripts using Java Groovy and cron-schedule scripts for execution and report failures per email. DbPrompt supports all SQL and NoSQL databases, like MySql, Cassandra, PostgreSql, MongoDb, Redshift, SqlServer, Azure, Oracle, Teradata and more. DbPrompt can work on all operating systems. DbPrompt is free of charge. “
Unsorted - omg will it never end
- https://github.com/ajdeziel/SQL-Data-Viewer - abandoned
- https://www.jetbrains.com/datagrip/ - https://www.youtube.com/watch?v=Xb9K8IAdZNg
- https://jetbrains.com/ intellij/rider/rubymine etc all have the datagrip capabilities built in
- https://www.red-gate.com/ sql prompt etc
- Azure Data Studio - PostgreSQL extension
- SAP PowerDesigner https://www.sap.com/products/powerdesigner-data-modeling-tools.html - I have no idea what this is. Sounds enterprisey.
Places to find even more database tools and learn more
- http://www.agiledata.org/essays/dataModeling101.html - I had no idea what data modelling really meant before reading this.
If you got this far you probably need to get back to work. It’s clear this article is a bit ridiculous now so I’m not going to carry on sorting it out. The internet is littered with catalog articles of varying completeness and quality. To make it complete and good would make it dull and endless.
Watch this space, I think I’ll try and work out what people actually are trying to do and cater for that. Maybe we can all build a collaborative index somewhere.
I hope you found at least a few you didn’t know about and that they make your life better in some way. Please do tell me the story of how this helped you on email or twitter. Did I miss something? If you wish to improve this article please ping me a PR with additions here: https://github.com/timabell/sdv-website or just email me.
I’m not being paid to promote these, these are not affiliate links, I share this learning with you all for free so that we can all enjoy our work with databases more, and create better more reliable databases for ourselves, our clients and our projects.
If you want to be notified of new articles, sign up to the mailing list (which currently is also the trial download list).
Till next time!