Lightweight SQLite Editor for Windows

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.

Jgrubb• 3 years ago

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.

kyrra2• 3 years ago

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...

justinclift• 3 years ago

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. :)

psychphysic• 3 years ago

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!

solarkraft• 3 years ago

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

2h• 3 years ago

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.

Brian_K_White• 3 years ago

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

roywashere2• 3 years ago

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?

ezconnect• 3 years ago

Don't worry the PC market is shrinking.

cozzyd• 3 years ago

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...)

adamrezich• 3 years ago

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.

mrguyorama• 3 years ago

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.

roywashere2• 3 years ago

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

cellularmitosis• 3 years ago

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.

waselighis• 3 years ago

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

hansoolo• 3 years ago

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

bena• 3 years ago

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...

shortrounddev• 3 years ago

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.

jansommer9• 3 years ago

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).

zerr• 3 years ago

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.

dataflow7• 3 years ago

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?

bananaboy• 3 years ago

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!).

poo-yie3• 3 years ago

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

dncornholio• 3 years ago

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

zackangelo• 3 years ago

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.

ijidak• 3 years ago

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.

guavaNinja• 3 years ago

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.

TehShrike2• 3 years ago

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.

pbowyer• 3 years ago

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.

TehShrike2• 3 years ago

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.

justsomehnguy2• 3 years ago

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.

haunter• 3 years ago

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.

TheRealPomax4• 3 years ago

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.

pkage• 3 years ago

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

https://tableplus.com/

Aeolun2• 3 years ago

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

aha_throwaway• 3 years ago

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

https://tableplus.com/linux

piaste• 3 years ago

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

petepete• 3 years ago

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...

chrismeller• 3 years ago

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.

vetinari2• 3 years ago

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.

smackeyacky• 3 years ago

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

easton• 3 years ago

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.)

LikesPwsh• 3 years ago

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).

hochmartinez• 3 years ago

Linux version, please!

trelane• 3 years ago

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

Circuitstorm513• 3 years ago

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) ;-)

themodelplumber• 3 years ago

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.

whizzter4• 3 years ago

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.

chrismorgan2• 3 years ago

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.

leke2• 3 years ago

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

anditherobot• 3 years ago

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

whizzter4• 3 years ago

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...)

RedShift1• 3 years ago

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

akisej• 3 years ago

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

gnoffa• 3 years ago

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

rr808• 3 years ago

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

tenken• 3 years ago

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

jodrellblank• 3 years ago

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

TheRealPomax4• 3 years ago

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?

anta40• 3 years ago

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

rlv-dan• 3 years ago

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.

spapas82• 3 years ago

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!

oblio2• 3 years ago

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?

TheRealPomax4• 3 years ago

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)

qwtel• 3 years ago

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.

Narishma• 3 years ago

How is that lighter weight than a simple win32 app?

poo-yie3• 3 years ago

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

dusted• 3 years ago

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

gorgoiler• 3 years ago

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?

betimsl• 3 years ago

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

mkup• 3 years ago

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).

poo-yie3• 3 years ago

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?

philkrylov• 3 years ago

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

Lightweight SQLite Editor for Windows

Related Stories