Show HN: 10 Years to Build a Free SQL Editor | Hacker News Reader

Show HN: 10 Years to Build a Free SQL Editor

timestored.com

355 pointsRyanHamilton90 comments

I have spent large parts of the last 10+ years building an SQL Editor. The tool is targeted at data analysts, a lot of effort has gone into charting, visualizing and excel export. If this sounds useful to you, it's Free, please give it a try and let me know any feedback.

Open articleView on HN

I've known Ryan for those 10 years, back when we met at Morgan Stanley. He is one of the most persistent and hard working engineers I know.

To people asking why he's giving it away for free: partially because it's content marketing (duh), but mostly because this has been a labor of love for him that he wants to let loose into the world.

I should say a massive thanks to all the users over the years that provided feedback and feature suggestions. Also a big thanks to the excellent JFreeChart/Flatlaf libraries that do most the work to make qStudio look good.

Hi - I am trying to run this on an Intel Mac. I got it to launch once, and attempted to open a small (~30mb) sqlite DB, and got a crash. It now hangs/freezes as soon as the application opens (taking over > a full core of CPU e.g. 120% plus utilization in Activity Monitor), even after deleting and re-downloading the .jar file. Any ideas? Would love to check this out!

edit: Apple silicon, which I note because the process type in activity monitor is 'Intel'

Ah! I figured it out. User error. I had hit "open file" instead of "open database" and the application decided to open the 30mb sqlite DB as text (e.g. a sql query), including syntax highlighting. Simply letting it grind away for 10 minutes or so allowed it actually open the file, at which point I could close the tab and performance is back to normal. Look forward to experimenting!

Hey, that's some amazing QA work. If I were the developer, I'd add a quick check to see if the file appears to be a SQLite database and do something useful in that case.

Probably good to have some basic checks too if it's expecting a text file of SQL queries- like a dismissible warning if it's over a certain size or doesn't appear to be plain text

Thanks for letting me know and great QA. Another user reported this just last week and I hopefully have it fixed: https://github.com/timeseries/qstudio/issues/49 qStudio 3.04+ now only opens .sqlite/.db/.parquet as data files not for text editing.

Grats on the launch!

Link of the repo: https://github.com/timeseries/qstudio

In response to the default Darcula theme on Windows, specifically the gray for the comments vs. the gray background: https://contrastrebellion.com/

In SQL Management Studio it is easy to work with stored procedures. The server starts the tree on the left, and all databases on the server are the next level down (not a separate connection for each database). Then beneath that (2 levels) each stored procedure is listed and can be opened easily for modification in the editor.

I will return to this app if I need to munge/import data in the future but it's not worth the effort for my normal work with stored procedures as best I can tell from a quick start. It's not immediately clear how to pull a stored procedure up in the editor without running a query manually to grab its definition.

Well I had an opportunity to munge some data, but the .CSV file had spaces in its name so the generated SQL failed with an error because it started by creating a DuckDB view named the same as the file, without escaping the spaces.

I tried putting []'s (brackets) around the name to escape the spaces but apparently that's not how DuckDB works.

Also, I'm used to using F5 to run the queries but that didn't work either.

Looks cool.

What is the benefit of registering? I skipped it to try it out.

Was very quick looking at my local MySql database. I liked being able to run sql against some local parquet files.

Would need to support Redshift via an ssh tunnel for me to use it on a regular basis. I don't want to manage the tunnel with a different tool.

Currently registration is eventually enforced. I need to remove some logic to make it restricted to only happen for one niche database as I use the email to reach out to large finance firms to finance the dev effort.

>>Would need to support Redshift via an ssh tunnel for me to use it on a regular basis. Redshift has definitely moved up the priority list lately. I will look into it.

I think SSH tunnels are exactly the type of thing that should be handled by a different tool. It's a lot of complexity to add to the app, when the app (and any DB client) inherently already supports connecting via an externally managed tunnel. An established tunnel managing program, or just your terminal, would be better at managing tunnels than a tunnel tool tacked onto this app, and the interoperability is already free.

Tunnels have so many configuration options that you'd either have to support raw configuration in the app (at that point just DIY a tunnel in the terminal), implement something incomplete, or build a full featured SSH tunnel manager into the app.

SSH tunnels can be totally transparent to DB clients. Unlike DB application-layer stuff like credentials, where the DB client needs knowledge of them, I don't see the cost/benefit of integrating SSH tunnels into the app itself.

No reason why you couldn’t call the ssh binary if it’s available in $PATH. I’ve seen other tools do this too.

You’d need to be transparent in your GUI wording that this is forking SSH (in the case of Windows users) but it wouldn’t be an issue for Linux or macOS.

I don't think the guy who you replied to expects anyone to implement their own SSH client to build a tunneling feature. The sensible thing you would do is use a well known library for that.. but that's not any less work than having your program use the SSH binaries to make those features. Honestly it would almost certainly be easier to use a library.

Even easier to just have a doc give some examples of third party tunneling solutions and not have the app try to do anything more than normal connections.

ssh tunneling alone can be done in so many ways with different environments necessitating which might work. are we talking ssh tun/tap IP tunnels? simple port forwarding? either of the above but with a proxyjump required to get there?

Redshift support added: https://www.timestored.com/qstudio/database/redshift I need to think more about ssh tunnel.

Out of curiosity, what took you 10 years? Keen to hear your journey as someone building in the same space..

Good software takes ten years, at least according to Joel. That’s a significant application of course.

https://www.joelonsoftware.com/2001/07/21/good-software-take... - Reference. I read this for the first time. Thank you for sharing this.

> Out of curiosity, what took you 10 years

That's not the first release, that's "just" 3.0, they released QStudio 1.25 in 2013 (their first blog post) https://www.timestored.com/b/qstudio-kdb-ide-1-25-released/

Year 1 - Getting the first version out that worked for me and my colleagues. Year 2 - Cleaning it up enough to promote wider use. Documentation. Years 3-5 - Minor bug fixes only as I thought qStudio solved the problem. Years 6 - I realised restricting qStudio to only 1-2 database technologies was foolish. Major change to support many more databases. Improved generic SQL highlighting. Added a partial dark mode. Years 7-8 - Minor bug fixes. Year 9 - Added a proper Dark Mode and support for many themes by using FlatLaf. Now looking properly modern. Year 10 - Realise that I'm not fully solving the problem. That actually for most data analysts I should support creating the analysis (pivot table) and improve exporting (real excel export, not just nicely escaped CSV).

There were more learnings, like I should definitely have went fully open source at the start. It's harder to do later.

Impressive but I did find a few issues using my Intel Mac:

1) Being a Jar there’s no easy way to add it to the Applications folder and launch it “naturally”.

2) The colors I selected for each connection didn’t seem to stick.

3) I can’t find a way to have a connection go against multiple Sql Server databases.

4) Cloning a connection keeps the highlight in the source server in the Server Tree.

5) The Console isn’t easy to view table results on due to it using simple pipe (|) separation.

App crashes when opening the Settings on MacOS Sonoma (M2 Pro).

Thanks. I have opened an issue. I do test on Mac but I'll need to try that specific architecture.

FYI

Language: Java, GUI: Swing, Docking framework: DockingFrames (LGPL)

A massive shout out to DockingFrames + jsyntaxpane + FlatLaf. Java libraries just don't get the love or attention they deserve. Docking Frames has solved the multi-windows in docks better than any JS library I've used or looked at. It's the only one I've seen allows grouping multiples by end users seamlessly and this was over 10 years ago. Sadly Swing/jsyntaxpane have been left to stagnate. They work but are beginning to miss functionality compared to more modern alternatives.

Congratulations, 10 years is an impressive effort. With regards to the AI capabilities, there is a lot more opportunity hidden besides text-to-SQL generation like query validation, fixing, optimziation, data generation etc. Perhaps these generators can inspire you: https://www.sqlai.ai/app

Have you seen much end user feedback on the usefulness of this area? I only just added the AI functionality but even using it myself, I'm still unsure if I will keep using it longer term. Mostly I just use i tto spit out a few sample queries when I'm playing with a new data set.

Is it possible to quickly see trends (line graph) for a query result.. If I want to see count of orders over time to see if there are any outliers for potential data issues. That'd be neat. or esp if AI can just check the data... if i have average of 100 orders per day but there is one day that has just 5, I'd want to see that easily.

Yes. All charts render directly from queries. It sounds like you want either time-series or step-plot.

i'll give it a shot, thanks!

If I wanted to use this in a corporate environment as a dev, I didn't see any links telling me pricing or who to contact. Is this user error on my part?

It's absolutely Free for all databases except one very niche database only used in finance. Please raise issues on github https://github.com/timeseries/qstudio/issues/new or email me.

Congratulations on the launch! is there any write up on how it's different than other popular db management tools?

It's aimed as an SQL tool for Data Analysts not DBAs. e.g. It has 10+ charts builtin that can be generated directly from queries. It allows creating pivots using a UI. It does not allow DBA tasks like altering columns. It features a unique integration with DuckDB that let's you save any result from any database to a local duckdb instance.

This looks cool. What kept you motivated to continue building this when similar tools available?

Most the tools today didn't exist when I started. At each point in time I looked up and didn't see a better tool. The initial competition was focussed on DBA tasks, my colleagues and I mostly analyse large data. So we don't perform a lot of DBA tasks or manual editing of data (it's too large for that). We mostly select or create new tables that are cleaned up version using coded fixes.

There are better tools now, partly why qStudio is free as I don't believe it could compete with the paid offerings. My main profit making focus has moved onto a new niche that I think is underserved....building real-time apps based on a database. qStudio acts as a good advertisement and brand awareness for that much heavier product.

I'll check this out, I've been looking for a SQL editor with plotting support!

Ten years in the making and the main features are AI-based. Amazing timing.

On Linux with HiDPI a monitor I had to run:

GDK_SCALE=2 java -jar qstudio.jar

I'm impressed by the speed, it's probably the fastest such tool I've ever used. Congrats!

Thanks! I think I'm mostly benefitting from it being so old and hardware has got so much faster.

Perhaps, but you've also resisted rewriting the entire application just to take advantage of the newest GUI frameworks. There's a lot to be said about an application that gets things done, and quickly, regardless of whether it was the initial intention or not. Congrats on the release!

Absolutely love Swing look.

Congrats on the launch!!

Congrats on launching!

I haven't gotten very far into it, but here's a little feedback about what it took to get to the point where I could try it out.

1. I'm running MacOS 14.4.1.

2. I downloaded the jar file.

3. I double-clicked the jar file. It wouldn't open. I've seen this before, so I navigated to the jar file, right-clicked it and chose open, and it gave me the option to launch it even though it was unsigned after a warning.

4. Then it told me I needed a java runtime, and I should go to java.com to get one.

5. I went to java.com to get one, downloaded the java 8 runtime things defaulted to, and when I went to install it, I got a scary warning that Oracle might sue me if I used this for commercial use. This is my personal machine, but I didn't know what might count as commercial use, so I noped out and decided to look for an open source runtime.

6. I figured out that `brew install java` seemed like what I want and ran that.

7. Once that completed, I double-clicked the jar again. No luck.

8. Since the SQL editor site advised running from the command line if double-clicking didn't work, I tried that. An error message directed me to java.com.

9. I didn't want to go back there, so I did more web searches. I learned that if I ran `sudo ln -sfn /opt/homebrew/opt/openjdk/libexec/openjdk.jdk /Library/Java/JavaVirtualMachines/openjdk.jdk` things might work. So I tried that and went back to right-click the jar and run it. I again accepted the warning. It launched!

10. It asked me for an email address. I just wanted to try out the software, not sign up for email. I declined to enter one.

11. It asked me for a license code. I declined to enter one.

12. Then it told me I was in a 30 day trial mode once the GUI came up.

I'm still interested, but that's the end of my timebox today. I hope I remember to come back and try it some more.

But what's the deal? Is it a 30 day trial for a paid product? Or is it really free? Or do you need email to make it really free?

You might want to look at how JetBrains bundles the JRE with datagrip on Mac. The level of effort needed to try this was maybe higher than I'd consider ideal.

Very helpful - TY

Massive thanks for this. I've opened an issue to ensure I improve this: https://github.com/timeseries/qstudio/issues/54 To be honest I've delayed improving Mac as I don't use the platform myself so I don't know how people usually install java apps on mac. I guess it's time I dove in and properly solved this. With the upcoming windows spyware I may have to move platform anyway.

This could well be a good opportunity to dig into jlink and jpackage and use that to build native installs for the different platforms, bundling the JDK.

In my experiments, I was able to pull it off for Mac and Windows, but Linux was still a bit wild west as to how to properly package it (for me), so they just get a fat jar. It's doable, I just didn't do it.

It helps if you use the Java modularity to trim down the runtime.

I was able to distribute my FX app (which requires platform specific binaries) that way.

JavaPackager as you're already using should work, you would just need to build the Mac packages on a Mac build agent. For something more future-proof and performant, GraalVM native images (https://www.graalvm.org/latest/reference-manual/native-image...) seem to be the future of native packaging for JVM applications.

The messaging is quite confusing. The title makes it seem like it took you ten years to release a first version, but the website says it’s version 3.0 and claims it’s “the leading SQL Editor […]”¹. There is a GitHub repo² but the history is quite unhelpful, with a bunch of commits named the same and some older commits after newer ones.

¹ Unless it were the only one, it would be impossible to be leading right after launch.

² https://github.com/timeseries/qstudio

It leads in having the most number of contributors named Ryan Hamilton.

Yeah... what's up with these commits that just add a dot or ""? Looks very much like just boosting the commit count.

90% of the commits are just adding nonsense.

update to latest = 21+16

release = 4+1

bugfix = 3+2

update readme = 3+35+35+35+35+35+35+35+14

total = 309 out of 341 commits

The commits stopped at Jun 25, 2016 when it was just a "Custom JDBC Driver and Authenticator Example" and then suddenly Feb 23, 2023 it became "qStudio - Free SQL Analaysis Tool"

Yes, the first and only "real" commit has been this one https://github.com/timeseries/qstudio/commit/c7c39ff69b90762..., where they committed the sources. After that mostly useless changes in the readme, even the "bugfixes" do the same (nothing).

The story is: I made a big push to open source it a few years ago but none of my users seemed to care. They mostly cared it's Free. Doing the boring work of getting the build working on github etc. doesn't seem valuable to my users vs adding functonality. I'm open to being wrong so I opened an issue and if anyone comments, I will open source at least one file for each comment: https://github.com/timeseries/qstudio/issues/43

Some people judge project quality by commit number. The empty commits I did generate as I believe it more closely resembles the effort and commits that have been put into the project than if I left it on 10 commits starting 2 years ago.

Seeing this makes me not take you or your project seriously. Bumping commit count and playing some engagement farming game. Either open source or don't.

> Doing the boring work of getting the build working on github etc. doesn't seem valuable to my users vs adding functonality.

You don't need this to open source it, you can provide source without setting up builds

Yet to find one of these (Microsoft SQL's query editor, Oracle's query editor, DBeaver, pgadmin 4) that I really like. The basic problem I have is that the structure of the typical relational db which uses primary and foreign keys (even if they aren't declared) is a graph. A user interface that lets me pick out individual tables and columns for one little query tab which gets lost in a sea of tabs just doesn’t cut it. (I need to see the whole graph relevant to a problem on the screen at once, not just look at it through a keyhole… Even very smart autocomplete isn’t that big of an advantage over editing SQL in vim and using another window to look at the schema)

Practically I am often understanding how to write certain queries by starting out with a certain table or a certain row and then looking at the resulting subgraphs of tables or rows that are linked to it. I want to be able to bookmark a row and come back to it immediately.

Also tools like this also implement "save" in a way that is useless at best and sadistic at worst. Who really saves a bunch of SQL files in a directory like "Query001.sql"? If I want to "save" queries and updates I don't want to save them in a strange SQL-specific project. Depending on my project these are either going to be incorporated inside source code files or possibly saved in files that live inside a project I manage with a non-SQL IDE. If I want to "save" DDL these are either saved in the database themselves by executed or could be managed as part of a migration system, which I haven't seen a tool of this sort support. Although I never "save" files in that kind of tool, it is certain to try to stop me from turning my computer off so it is another thing that makes turning my computer off like

https://www.youtube.com/watch?v=kyOEwiQhzMI

It's interesting to hear your perspective thanks. FYI there is no need to save before shutdown. I stole notepad++s excellent idea of autosaving regularly.

Edit: Actually your description reminded me of jailer: https://github.com/Wisser/Jailer Does that do what you want?

I've put a whole 5 minutes of thought into this over the years, but it's something I've always thought I would like in some kind of DB tool.

Notably, being able to follow relationships easily.

Trivial use case, selecting a bunch of order rows, clicking on the "customer_id" of one of the rows, and it brings up the associated row, following the foreign key. Clicking on something else to open up the dependent tables (like the order detail lines). After a bit of clicking around, you have a subset of the DB ERD (save it has data). Then, maybe, using that as a base, you can start clicking on columns to form up a query, with all of the joins in place.

As I said, 5 minutes of thought, 30,000 ft view. But I've not seen anything like that, perhaps it's just not practical.

DBeaver has what I think you're describing (at least for me with Postgres) - when browsing tables in the 'data' view, and also for query results there is a little icon beside a data field that is a FK, and you can click it to go to the related row. Not quite the full diagram thing you're describing but I think it can do a lot more than I am aware of.

All IDEs from JetBrains support interactive database diagrams.

https://www.jetbrains.com/help/idea/creating-diagrams.html#d...

I have a genuine question, intended in good faith: why? Why spend 10 years working on this, only to give it way for free?

They look to be setup as a consultancy. While the title is not wrong it reads different when you realized the parent company has been around for 10years and they list 330+active clients with 850+ projects done.

Woops. Definitely not a consultancy. We make and sell software. The graphic I think you seen had been on the page for years as it came as part of the website template. I had mentally just been blocking it out without realising. I've now removed it.

So they can collect your email address for marketing purposes. OXKCD#792

Yes, you are correct. though I individually email people and historically it's only been people that use one particular database and that work in one sector (Finance). I have corporate paying users for one niche database that allows me to make it free for all others.

I originally built it for myself to use with one niche database that had no IDE support at all. I then gave it to my team and then others. Years ago I began to sell it and I still have large banks and finance firms that pay to use it with that one database. Adding support for other databases was actually very little effort however I don't want to have to do the much harder work of: a) Marketing to those users. b) Competing with excellent paid alternatives. c) The various hassle of support / payment processing for many smaller firms. So the large finance firms pay me for the one niche database supported, everyone else gets it free but "unsupported". If a major bank that pays emails a problem, I may have to log in even from holiday to fix it. I'm not doing that for the free users :)

Be careful on some of these claims > Supports every database Does it.

No Cassandra :-/

qStudio allows loading your own .jar file with JDBC drivers: https://www.timestored.com/pulse/help/connections-custom-jdb... In theory Cassandra has a JDBC driver: https://github.com/ing-bank/cassandra-jdbc-wrapper It's not pretty and not seamless but it should work and if it doesn't and I can fix it, I'm happy to help.

I created a Custom JDBC Server connection, but I can't figure out how to specify the driver file. Clicking the Test button offers to download one, but fails with an IndexOutOfBoundsException. I don't see anything relevant in the Settings, and I don't see anything resembling the DBeaver driver manager.

I don't know how to translate that Pulse FAQ to qStudio. FWIW, I'm using Java 11 on macOS 14. I have a single qstudio.jar file.

What JDBC driver are you trying? I can try it tomorrow possibly even on mac.

Driver download:

https://intersystems-community.github.io/iris-driver-distrib...

Example connection strings:

https://docs.intersystems.com/irislatest/csp/docbook/DocBook...

Instructions for starting a container:

https://docs.intersystems.com/irislatest/csp/docbook/DocBook...

Honestly, it shouldn't matter whether you took 10 days or 10 yrs. If its good its good.

The point is to communicate a large amount of personal investment - love, sweat, and tears. As opposed to someone who scraped together some side project in a weekend and hasn’t yet faced the demon horde of edge cases.

While you're correct, there's generally a correlation with something that's not only existed for that long, but IN USE for that long. I think that's the bigger selling point. Maybe I only get this idea from going through all comments on HN, I see your comment is from 8 hours ago, and I don't know what comments existed at the time you commented. I apologize if you didn't have much context when making this comment.

Apache 2 license that requires email registration. I don't think such a thing exists.

You can click "Cancel"

And get a trial period.

I don't think open source means what you think it does.

https://github.com/timeseries/qstudio/tree/master

I intentionally have avoided saying "open source" as it's not yet. Please let me know where it says it, as I may have made a mistake. I just haven't seen enough demand from current or potential users to justify the effort. If you feel strongly on this, please leave a comment here to help justify the effort: https://github.com/timeseries/qstudio/issues/43

You host binary blob releases in the qstudio GitHub alongside a License file in the repo indicating apache license. If you didn't open source everything that made up those releases including it's dependencies, you should remove or clarify what exactly is covered by the license to avoid the confusion.

I added the releases at the specific request of 1 large finance firm, a top 3 American bank by size. They requested it as their procurement department trusted downloads from github more than timestored.com. It probably sounds insane to you (or anyone sensible) especially as the downloads are digitally signed but I could write essays on the crazy hoops large firms ask you to jump through to provide software.

It doesn't sound crazy for those of us that have dealt with large firms, especially banking. The issue would be including an Apache license with the binary blob, when that is not the correct license.

I've dealt with extremely customized licenses for businesses (financial) that wanted software built for their use, but also to have the ability to resell the software that was built specifically for them. Not much surprises me anymore when it comes to expectations of legal departments. You just don't want to get into any kind of legal battle in the future over your software. I don't know anything about the size of your business, or who uses your software, but I do know that when it gets ugly and someone is looking for rights to software, you need to have things clearly laid out or you could end up devastated by a company that has funds to keep putting into legal fees that you may not.

I hope that this scenario never plays out, because I think releasing this is something that really adds value to the community that deals with databases. I just don't want to see someone trying to make a living get hurt by a licensing issue. Unfortunately, when it comes to high-end business, it's not so uncommon when things don't go the way your client wishes them to go. Best of luck to you!

FYI, it is kind of ridiculous that you support all these RDBMS but it doesn't mention anything other than DuckDB on the landing page. Your GitHub README is infinitely more useful. You should probably fix that, because the LP gives a very incorrect impression of the capabilities and features of your product!

Edit: just tried it on Windows and you have mojibaked text on the welcome dialog [0].

[0]: https://imgur.com/a/qeIdLGN

Edit 2: Unfortunately your product has some architectural/design issues. Way too much latency, UI hangs a lot simply following the action links in the welcome dialog, trying to open a 3 mib database hung the software until I killed it, etc. The interface is highly non-idiomatic and doesn't follow ui patterns common to any of the major desktops (in response to keyboard shortcuts, mouse operations, etc), and doesn't do a lot of what I would consider to be basic safety/sanity checks (i.e. tries to open a binary database as a text sql file, shows garbled unicode).

Thanks. I've opened an issue to fix the text https://github.com/timeseries/qstudio/issues/55. The binary file issue should definitely be fixed in 3.04+

>>The interface is highly non-idiomatic Ouch this hurts but I'm open to improving it. Have you any specific suggestions?

What's the ROI on building a free SQL editor in 10 years?

Ha Terrible! I don't recommend it at all but it's a great feeling of satisfaction when I walk into a random office and see someone using it. Or more rarely when someone emails thanks.

If this is your passion, keep at it, please! You need to make a living, but not everything needs to be a front-page success story either.

Reply on news.ycombinator.com