Sherlock
This is probably the most useful side project I’ve ever made.
Every business of a certain size has daily fundamental domain questions like:
- “Why can’t user X see product Y?”
- “What contracts is branch L connected to?”
- “What’s the referral link / API key for user X?”
…these can be answered in any number of ways, like a custom tool, an MI report, or just first-class support in the back office app. Those require dev effort, and a deployment, and maybe someone has to log a change request somewhere to make it happen, etc.
Many times a dev will end up writing a SQL script to answer one of these questions. And we’ll keep it on our hard drive because you know someone else will come along with the same question later.
Sometime in 2020, I thought “What if the asker could just put their parameters straight into my query?” and that’s how SqlSherlock came about.
This month I’ve turned three dev-centric maintenance processes into self-service by spending half a day on each making them into Sherlock flows (i.e. writing about 9 SQL files in total) – so I’m feeling good about the economic value this simple tool creates.
What is Sherlock?
It’s a turn-key data portal configured by a folder full of SQL. You run it on-premises and it accesses your databases like any .Net (framework) app, using a connection string in web.config.
It turns this folder of SQL:
Into this web UI:
The open source project has some more details about config.
Going fast (and hopefully not breaking things)
To add a feature to your usual back office tool, perhaps you have to take a branch, do the dev, test locally, push, PR, get approval, deploy to an appropriate test environment (check that you’re not going to clash with anyone else), get a release window, and deploy to prod.
With Sherlock, you can try out a new SQL file locally, put it onto the QA or UAT server, demo it with stakeholders, get code review and then move that SQL to prod. As Sherlock hangs off the side rather than being part of your major app codebase, we sidestep a lot of the risks and blockers of the merge and deploy process.
There are provisos here about process being there for a reason. I’d say that processes are there to give us confidence, and there are other ways of doing that; we had reliable software development before we had the git flow.
Tech stack
I used good-old .Net Framework 4.7.2 and ASP.Net MVC on this, and it’s targeted at folks using IIS, SQL Server, and Windows – a big niche. I guess it would work on Azure SQL and App Services too… haven’t tested it.
The UI is Vue 2.6 used in progressive enhancement fashion (no build step). Not the new shiny, but solid and performant. Page load is under 400kb.
Cookbook
I’d like to make a cookbook of things you can do with Sherlock, and pop that on the open source project, because with some care and attention you can design flows that add, remove, and modify data, even adding dry-runs and confirmation checkboxes.
Wishlist
I’d really like to limit the scope of this project as it’s doing 90% of everything it would ever need to do, right now. But, one feature I’d like it “text only steps”, where instead of a SQL file, we can add a numbered TXT file which will show some advice in between logical steps. You could consider markdown for this, but it would be overkill and seriously inflate the project and its package exposure. Perhaps a subset of md supporting **bold** and `code` would be good.
So far I’ve made two binary releases of Sherlock, and they happened to be 364 days apart 😂 so for any major revisions, please check back in April 2026 😁
Have fun building out there 🏗🎠