Lightweight SQLite Editor for Windows | Hacker News Reader

Lightweight SQLite Editor for Windows

github.com

272 pointsbbkane110 comments

I love that this looks very much like Query Analyzer, the lightweight querying tool that came with SQL Server 2000. Back then, server management, querying and profiling were handled by separate utilities that did one thing well.

They were all replaced with SSMS, which was a slow abomination.

Query Analyzer is my favourite SQL editing environment to this day. It was incredibly snappy, could render thousands of rows without skipping a beat and had a nice explain visualisation built in. If there was a modern rewrite that supported PostgreSQL, I'd buy it in an instant.

There are screenshots in these articles if anyone's interested:

http://etutorials.org/SQL/microsoft+sql+server+2000/Part+II+...

https://www.sqlservercentral.com/articles/using-query-analyz...

I don’t think it has the explain visualization, but otherwise these screenshots look a lot like DBeaver to me. Perhaps worth a try if you haven’t stumbled upon it yet.

Dbeaver is many things, but I would not call it lightweight. It is written in Java, after all.

It is great, but Query Analyzer-alike it is not.

dBeaver is written in Java, but it feels very lightweight. I'm running it on a Linux desktop I've been using since 2014. It's also cross platform so I run it at work on a Windows laptop.

You think? Just looking at the interface and all the buttons/options screams ‘exhaustive/heavy’ to me.

It’s pretty fast for a Java tool, but I wouldn’t call it snappy.

Written correctly, Java can be both very fast and light. Eclipse IDE, written with Java consumes less resources than a comparable Atom/VSCode instance, with more features enabled.

Electron apps are not exactly lightweight either; it is about first time that someone uses them in an argument.

Query Analyzer was written with win32-api. No electron, no java, no c#, nothing (thought it might used MFC, not sure about that). It was snappier on Pentium 3 than Dbeaver is on M1 or Threadripper. Existence of slow molasses like Electron won't change that.

I didn't use DBEaver, but written some Java and using Eclipse for 20 years.

Java's UI libraries are extremely prone to wrong initialization. If you skew from the reference guide's implementation, Java's libraries try to mend the problems themselves, but this adds observable latency to the UI.

Of course, Java is never as fast as natively compiled language, but asymptotically approach to that speed and come pretty close. The funny thing is, some of the today's native applications are both heavier and less responsive w.r.t. their Java counterparts. This is because we tend to add loads of dependencies which aims to simplify development in said language, but that price is always paid at the end. Either once by the developer, or repeatedly by the user.

If you want an abomination, the Azure data studio makes SSMS look like query analyzer.

It's not feature complete, but Data Studio felt like a breath of fresh air compared to SSMS being all the slowness of Visual Studio coupled with about 1500 places where configuration might be.

(Of course, what do I know. I use DataGrip for my databasing these days. It's fine.)

My experience is that SSMS is slow to open. ADS is fast to open but otherwise slower and unreliable.

I still mostly use ADS for the git integration, file comparison, terminal, and file picker though.

SSMS only for those admin tasks where it has a nice GUI, or when ADS breaks in some way (happens a lot).

Oh this looks good. I've been using: https://sqlitebrowser.org/

This seems to offer way more.

Been using the same, and I've been very content with it too.

Curious about this one.

I've never tried this, but it appears to be possible to use Oracle SQL Developer to browse SQLite tables.

https://github.com/Trivadis/sqldev-jdbc-proxy

Oracle SQL Developer is a Java clone of Toad from Quest software:

https://www.oracle.com/database/sqldeveloper/

Toad also bears a resemblance to the Windows app in the post.

>sqlitebrowser

Been using this for years. Fantastic tool.

I love DB Browser.

It's fast, does what I need it to, is open source ... great little tool.

It's a blessing. I loath the slow feedback cycles your get in the BigQuery Web UI. Even for "small" tables, that would fit on your machine. When I expect a longer data exploration or debugging phase, I dump the BQ tables to SQLite. Opening that with SQLite browser lets me iterate very fast.

Interesting idea, thanks. I never, ever use the BQ UI for querying unless I absolutely have to. We use Metabase for BI, which we plug into all of our development datasets and do the exploration and iterating there which still requires network round trips and I could see how your approach would be faster.

Googler opinions are my own. I know little about BQ.

My understanding of Big query is that at least some of the tech it's based on allows for handling very large datasets. I'm talking being able to search BI that is petabytes in size. I don't think it's really designed for small datasets.

If you can stick the data in SQLite, I think you're using the wrong tool.

Searching around, it looks like BI Engine may help in cases like this. See: https://cloud.google.com/blog/topics/developers-practitioner...

That's true. Most of our production data is in the "petabyte" category. But often we start out with a small amount of data, for example for a new feature or product. That's when a lot of the exploration and query building happens. Once that's done, we leverage BigQuery to scale this up, serving a few hundred million users. There's a platform/standardisation component to this as well. We want our devs to use a single tool for all of their OLAP uses cases.

Totally makes sense, I guess I do the same thing internally (using partitioned tables, so the queries are a lot smaller, or making sure to only select the minimum columns to prove my query is correct). I also have no clue if my internal queries end up using something like BI Engine where it'll cache data in memory (much of this is opaque to me), but turn around is at least a few seconds per request. Your point is valid here, thanks for raising it.

As a thought - unlike the current stable build - our nightly builds can open multiple "Browse Data" tabs at the same time. Aka: view multiple tables simultaneously.

https://nightlies.sqlitebrowser.org/latest/

We're really overdue for making a new official release, but the nightly builds are pretty stable in the meantime. :)

It’s funny but I only just had this thought: in the dark ages of text editing we would use ed to write commands that edited line by line, and then another command to show the changes.

What a chore! Enter vi where you can see a window into large parts of the file while editing it at the same time. It was a quantum leap in productivity for sure especially because I could work form a terminal (as opposed to jumping to a different system more geared towards word processing etc.)

But for SQLite databases I am somehow still fooled into thinking I am content, when in a terminal, to issue INSERT/UPDATE commands to make changes and SELECT commands to see those changes is adequate.

If this is the equivalent of ed…

  > sqlite3 my.db
  UPDATE table …
  SELECT …

…and if this post is linking to a GUI equivalent of Microsoft Word 6.0, then is there instead a vi equivalent for editing database tables, and what is it?

I've been wanting to dip my toes into old-school Win32 development for a while now, this codebase looks like a great example to pore over.

Dave's Garage on YouTube has a couple good videos that I can recall.

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

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

Oh! I gonna dive into this pretty soon. Thank you!

It looks like a pretty clean example from what I can recall.

WinMain builds the window and starts the message pump. The pump is implement starting at line 425. It's basically a while loop that reads the next message on the stack, translates it, then sends it to the object it belongs to.

It's the precursor to today's modern event-driven designs. Basically, this is now done for you by the runtime.

The bulk of the action happens in the created window's "wndproc", cbMainWindow (Line 455 - 2897).

There's a giant switch statement, with each handled message being a separate case. There's also a WM_COMMAND message, which is essentially a message holding another message inside.

This is probably the best book on Win32 API programming out there.

https://www.amazon.com/Programming-Windows%C2%AE-Fifth-Devel...

Yes, it's unfortunate that Programming Windows after 5ed became about XAML and C#. Those are good tools, but it's sad that they stopped updating the Win32/C++ resources as well.

Use ChatGPT! It's night and day for Win32 dev in my experience, and the code it produces is really good.

One thing to note before going into Win32: You're going to have a pixelated, upscaled ui if you don't take care of high dpi support yourself, and that involves patching system controls. So prepare yourself for lots of dpi-work if that's a concern. (One thing I've been experimenting with is superclassing the system controls and "overwriting" the original by registering a superclass of eg. the edit control as simply "edit". This makes other system controls, like the combobox, use your dpi scaled/custom version).

Exactly my experience. In general, for the sane person, it is impossible to write more than a few lines of Win32 API code without copy/paste... ChatGPT came quite handy here.

I'm confused. Are you sure high DPI needs patching system controls to avoid pixelation? Doesn't dpiAwareness avoid the pixelation? I certainly haven't needed to patch anything for this in the past. If you have an example you could point to, that would be great, since I don't know if I'm missing something here.

Also, question: do you know of a good way to test for high-DPI mode on a low-DPI screen?

You just need to add a manifest to your app indicating that you support high DPI. To test your app just change the DPI in the display settings.

I guess I was hoping for something little clunky than that, but thanks for the suggestion.

Nope, you get the pleasure of implementing dpi support yourself.

https://building.enlyze.com/posts/writing-win32-apps-like-it...

Thanks for the link. Glancing at it, I still don't see where there's a need to patch system controls for high DPI support, though. The closest thing I see there is for changing their fonts, but that seems different from patching them (no superclassing that I can see, etc.). Moreover, it's for dynamic DPI support (i.e. supporting DPI changes), not high DPI support. Is that what you were referring to?

I think you should try a plain win32 program on a high dpi and just see for yourself.

That's not exactly a... helpful answer. In fact I already have a Win32 (dialog-based, if that makes any difference) one that looks just fine on 200% DPI on Windows 10. All it required handling was scaling the obvious stuff (icon sizes, overall window size, etc.) and the manifest, nothing that required superclassing or otherwise patching common controls. i.e. I just haven't run into the problem you're mentioning. I'm kind of confused why you linked to an article that doesn't support your claim either. Hence why I'm asking you instead of spending hours trying to reproduce what you're saying.

I don't know why you assume I'm here to give you helpful answers, but I'll bite.

Try a combo box on high dpi. The arrow button gets a lot thinner than on 96 dpi.

I didn't assume you'd give a helpful answer, but I didn't expect you to give that kind of an answer, either. You're always welcome not to respond if this is taking too much of your time. I figured maybe at least one of us would learn something about high DPI issues, hence why I engaged.

And I did just try what you said and the combo box fine to me: https://imgur.com/a/Mb83tPj

That is not the default Win32 combobox. I don't know what you're using, but it's not the plain c api.

That is the default combobox if you enable "visual styles" (which you should do on Windows XP and above)

binjooou: The whole point for me when using win32 is to use the default look, since it's a better ui compared to todays flat design. But I acknowledge that there could be a little less dpi work associated with enabling visual styles.

The best UI is the UI your users are used to, and 1% of Windows users are used to the Win 2000 gray UI.

Win XP came out 22 years ago.

oblio: Have you seen the other comments for this post? There's plenty of people who think the default win32 gui is awesome.

This is most definitely the default combo box. In fact, I just tried the version without visual styles as well, and that one looks fine under high DPI too (see screenshot). At this point I'm very confused what you are doing. Perhaps you're not on Windows 10?

dataflow: That is indeed a nice looking combobox in your new screenshot. I'm not getting that result on win10 when using the Win32 c api, and if you're not using something like WinForms, MFC or another abstraction, it's hard to say why it works for you. I'm on a 4k resolution with 200% scaling and the button is way too thin.

I'm not sure if this makes a difference but do you see that problem with dialogs (resources + DialogBox) rather than basic windows?

If I remember & get a chance later I can try to make a minimal example.

Edit: Based on this comment [1] I'm guessing you're on something like Windows 8.1 rather than a recent version of Windows 10 or later.

[1] https://www.reddit.com/r/programming/comments/d77mf7/comment...

No, win10

When you load a dialog from resource, windows scales it proportionally to specified font size (DS_FONT style).

I love that this has been built with the old-school API! I've done a bit over the years and find it kind of pleasurable (maybe I enjoy torture!).

Agreed! Sadly, lean GUI apps are mostly a thing of the past now.

I had the same idea until I opened main.cpp and scrolled through 8911 lines of code

This also brought back memories for me of writing Win32 apps. If you're looking for a good book, checkout Programming Windows by Charles Petzold. It really demystified a lot of things for me and I bet it's still relevant if you want to write stuff like this.

Personally I really love SQLiteStudio which I use pretty much daily: https://sqlitestudio.pl/

No mention of https://sqlitestudio.pl/ in the comments which is cross platform ...

If you can do with an even leighter-weight client, you can try my very own https://sqliteviewer.app. It's all browser/wasm-based and works offline, but has some important shortcomings.

How is that lighter weight than a simple win32 app?

If you don't have Windows it would probably qualify as lighter weight.

Kudos to the author for a meaningful screenshot right at the start.

And with a basic Win32 GUI I can bet my pumpkin latte what it as fast as SQLite itself.

>Kudos to the author for a meaningful screenshot right at the start.

I was thinking about this recently that so many projects on Github missing screenshots. I was thinking to make PRs to at least show some basic functionality.

That's because adding a screenshot is surprisingly hard if you've never discovered that you can create an issue, paste an image into it, and then copy the URL that gives you into your readme without ever actually submitting the issue.

Can't you just put the image as a file inside your repo and reference it from the README.md file ?

Not if your readme also has to work on "not Github" like npm or pypi, or anything that repackages your release while using your readme.md as page content. Those images are going to have to be fully qualified URLs.

https://github.com/little-brother/sqlite-gui/blob/master/res...

I love that it's just 12 files, straight to the point, no unnecessary boilerplates

You didn't happen to look at those files? main.cpp is 8900 lines, dialogs.cpp something almost as long. It's probably super performant but I wouldn't volounteer to maintain that codebase unless getting paid (And the commit history only shows the author...)

A good IDE makes it very easy to navigate through files like that. You navigate to functions, structures, variables, etc... instead of files.

That's nice until it breaks down, besides doesn't help much if you have 2400 lines long functions ( cbMainWindow ) that mixes Win32 API drudgery with indentation logic and SQLite fiddling.

Sure it's an cute and to-the-point codebase, but even if I have experience with using these API's I'd never volunteer to work on this codebase because it's obviously also one that's a creation of the authors memorized experience.

I strongly recommend TablePlus for sqlite/postgres/other databases, it's made my life significantly easier.

https://tableplus.com/

Hmm, looks like they’ve improved. I should try this again. Only negative is it doesn’t have any linux support.

They have linux app, just not as good as in Windows, Mac.

https://tableplus.com/linux

Have you tried DBeaver? If so, how would you compare TablePlus to it?

I believe I speak for everybody when I say that win2k was the best UI. So...is that demo gif running on win2k?

Not necessarily Windows 2000. It might be Windows 7 or Windows XP with classic theme as well (or Windows Server 2003-2008, either R2 or non-R2).

This looks inspired by the excellent SQLYog, which is one of the apps I missed most when I switched from Windows to macOS. I still haven't found a good macOS equivalent.

Same. I've tried DBeaver (its UI is an abomination) and TablePlus (some of the UI is better but e.g hiding the database search where they do - why?) and TablePlus is the one I'm sticking with, but I still miss SQLYog. Especially the permissions editor for database users.

TablePlus is the best I've found so far too. I tried Sequel Pro for a couple years. Haven't found anything that competes with SQLYog for browsing the schema while writing a query.

At my last job, I built a SQLite editor as an internal tool with C++ and ImGUI. It had custom features/views for our custom needs.

It was super fun and the code was super easy to read and edit. Immediate mode made our job much easier.

Was just looking for this! There is a decent viewer on the store (aka can install in a pinch while in S mode).

But you couldn't even copy paste!

Dumb question for those of you familiar with coding Win API these days -- can you easily build 64-bit Windows applications using Win32 API? I did a little Win32 C coding many years ago, but that was long before 64-bit Windows hit the scene. Is there a Win64 API now?

Some typedefs were promoted to 64-bit but it's the same Win32 API, even the name has not changed

Don't miss the "Powerful" link goes to a six minute YouTube video[1] showing it to be packed with nice touches / demonstrating the features.

[1] https://youtu.be/lctkqWD7HTk

But why is it Windows only? Even as a Windows user, my professional day is spent on a Mac even if my Windows machine is running at the same time. What arcane library does it rely on that it can't just cross-compile for all three operating systems?

Perhaps the author only use Windows? It doesn't use any cross-platform library like Qt, wxWidgets, etc. Just good old Windows API.

> What arcane library does it rely on

Win32

> can't just cross-compile

Suppose that you only use a specific operating system. Even if you could cross compile, how would you verify that it actually works? How would you know the specifics of each operating system and adapt you application to work well in such situation so that user don't flame you? It's just not that easy. And don't get me started on making GUI applications.

Side note: As a Windows user, In this case I really appreciate the look and feel of the native GUI. It feels like a "real" Windows application. You would not get that if you cross compiled.

win32 apps should run flawlessly on linux using wine. Haven't tested this one but I'm using notepad++ and heidisql on linux since I'm used to these tools. I even run word 2000 on wine when I need to open doc files!

Great question. What arcane libraries do all those Mac-specific text editors, IDEs, etc that Mac users rave about use, that they can't be cross-compiled for all three operating systems?

I have the same question. Any time someone releases an OS-specific piece of software, that's a question they should already have an answer to (especially given how much open source exists to smooth out the differences)

That looks absolutely beautiful (the classic windows ui style helps, of course)

This is awesome! I've been looking for a good SQL Lite GUI!

A lot of times, I just want a tool to do ad-hoc CSV work, and don't want to fiddle with a terminal.

Wish there was a great free open source GUI which works for for Sybase or even jdbc.

Looks very cool! We use logicloop.com to connect across databases, use ChatGPT to write, fix and explain SQL queries too

Linux version, please!

If it were written for Linux first, it would even be available for Windows simultaneously.

It would also be much more bloated (and not feel native) on Windows. Qt is what now? 30 or 50 MB? GTK probably much better these days. For that size you can almost go to Electron and get better look and feel (at the cost of RAM) ;-)

This has the intuitive scent of a project that works directly in Wine without any problems. Haven't tried it myself though.

Kinda like OpenMPT...indicate the supported Wine version in the Downloads maybe, and then after seeing it running just fine, one could argue that there's not even much point to a Linux port.

This codebase is very much tied to the win32 API, maybe with the winelib wrapper but otherwise I wouldn't bother.

Sibling post here mentioned using under wine but I'm not sure if that's the best idea since wine might not translate locking semantics in the same way and those might be plenty important to avoid corruption with SQLite databases.

SQLite is so very extremely popular that I don’t think you have to worry about corruption under Wine—if it was broken, a lot of software would be broken.

I'm not considering Wine emulation in isolation, I'm considering a scenario where someone would use this as a UI to inspect a SQLite database powering for example a Linux based NodeJS or PHP application.

Would the semantics of the Linux based application (with SQLite compiled for Linux) correspond to the UI (with SQLite compiled for Windows) running through an API emulation layer (Wine) that potentially translates semantics to run Windows applications as "natively" as possible.

Sure it might, but I wouldn't put money on it until verified. (Luckily backing up SQLite databases isn't too hard)

Ah, mixed Linux and Windows. Fair enough. Yeah, I’d assume that to be equivalent to a network share and accessing the database from multiple machines.

dbeaver is cross-platform and is a great general db client. I highly recommend it.

Windows-only seems to me like the most rapidly shrinking market.

What a selfish comment. Shame on you. It's open source. You don't like it, you're welcome to add support for another OS, or use something else.

It was neither selfish nor a demand. It was an observation.

Adding support to a tool for a different is that is developed for winapi is really not a thing. If it was written for GTK or Qt or electron then it is possible. But I would guess you should just best write a new app instead?

Don't worry the PC market is shrinking.

haven't tried, but it almost certainly works fine in wine...

edit: tested it, and yes it works (though sometimes you have to resize window to get updates for some reason...)

which is a shame because I miss old-school highly-functional win32 apps like this—bursting with soul and a flagrant disregard for modern app design/development principles.

You know, a tool, made by a tool maker, in the relevant trade, making a tool meant to be used by tool makers.

As opposed to the stuff nowadays that seem to be marketed first, made by whoever has the least power to ignore the job, and using some abysmal javascript GUI framework that can't even keep up with desktop framerate.

I also miss the days when the tools I used to do my job didn't HIDE shit from me, like error messages. Sure, I might not know what "ExtremelyExplicitNullException in doFrobThrob" means, but when I paste that error message in the support email, the engineer on the other side knows the exact line of code that error was generated on, and which variable was broken, and 9/10 times that's enough to actually find the root cause and fix it. No telemetry required. I'm so tired of vague, numbered error messages that nobody knows what line of code it corresponds to and nobody cares anyway because it's just going into a giant pile in the telemetry system that nobody except one mediocre PM is allowed to pull bugs from.

But it is a huge market!!! And o so many people I work with in telcos and such work on their company provided windows laptops

Reply on news.ycombinator.com