Show HN: Base, an SQLite database editor for macOS

Useful comment because I always assume these "Show HN" products are minimally viable... whereas, it's good to know it's been battle tested for at least 15 years.

The name chosen probably didn't help here. “Base” is a very generic term.

That's why marketing/distribution/sales is very important. As a developer most of us think if we build a good product everyone will start using it, which is very wrong in 99% of the cases.

I feel the same way. I've searched for sqlite guis many times and this has never come up.

The question is, does it actually earn enough money for the person to live off?

Thanks, I'll make a note of that. It's not a behaviour I've seen before.

I didnt know there are dozens of us! When I saw the lack of native support i decided to use binary blobs. I mean, why waste many bytes if few bytes do trick? In the SQLite studio app im using its garbled and annoying to browse.

Too bad this is mac only. I mean, im a mac user (among other things) but i don't want to depend on platform specific tooling.

See, I would be terrified of doing that because different RDBMSs and languages store and sort UUIDs differently. A UUID is not just a number. It's a structured data format.

Both MariaDB and SQL Server have dedicated data types for UUIDs, and they sort in an unexpected order if you're unfamiliar with the structure of a UUID or the endianness of certain portions of it. Oracle assumes it's going to be binary, but the generating function SYS_GUID() has some endianness issues you can run into. Meanwhile, PostgreSQL kist sorts them like a string!

Similarly, if you're using .Net to generate a native GUID type and passing that through your RDBMS provider, it may arrive and be stored differently due to that endianness problem.

Expecting that every SQLite database is going to be storing UUIDs in an identical manner seems insane to me.

Sometimes a binary blob contains perfectly printable characters.

A binary blob of 7-bit-clean ASCII still fits within a binary blob.

I think macOS core UI is better looking, that's the reality, and it has been for a very long time.

Just a random comparison: Paint.net and Pixelmator.

Without going too far into the details of their capabilities, why does Pixelmator look so much more pleasing?

If you built Pixelmator for Windows using a Windows-native look, it would just not look as good.

That’s one of my many reasons for sticking with Mac. I know the bar is higher and I just enjoy the consistency between first party and third party applications.

To my (biased) mind the advantages are:

- It fits with the system better and behaves more like other macOS apps

- I believe Base has better create/alter table support

However Base doesn’t (currently) have support for SQLCipher.

Polish and decent UI...

Base is nicely done, I’ve found it worthwhile over other options.

In my experience, DB Browser for SQLite keeps the connection open in a way where an application that accesses the database may throw a lock error. Maybe it can be configured, but I haven't had that issue with Base.

A nicer user interface. Personally I use datagrip but I want to encourage real native Mac software so will likely buy this anyway.

Unfortunately sqlite browser was too unstable for me. I purchased another app just because I can’t stand crashing apps.

sqlitebrowser is 100% open source, cross platform and free.

I don't think Base is at all and only supports macOS

Prettier UI?

wondering the same thing

Thanks!

Auto-enabling foreign keys absolutely should be an option. I'll make a note of it.

I don't know about auto-loading extensions though. Will have a look. That might not mix well with the App Store rules and I'm reluctant to let the direct/app store versions drift apart in terms of features.

Sort of, yes. But mostly that I don't want the app to become the "everything to everyone" style of editor. I want it to remain tightly focused on the features that SQLite provides.

It's unlikely that I'll add a form builder or report generator to the app. I don't want it to grow until it can send email.

I kinda wish someone would take SQLite and make something like Access on top of it.

Many years ago, there was a project that tried to do just that using Tcl/Tk, which seems like a fine choice - Tcl is very simple and easy to understand even for on-programmers while its deficiencies aren't really important in this niche, and Tk is one of the easiest UI frameworks around to use. Unfortunately it petered out and I can't even remember the name anymore...

Thank you for the support :)

Thanks! While it has been quite the career change, I am absolutely up for discussing both dairy farming, software development and their overlap. That’s where Base came from!

> I believe the macOS app OmniGraffle can do this, like Microsoft Visio on Windows.

I don't think Omnigraffle offers direct synchronization for editing database structures between its diagrams and SQL code.

I am launching one very soon; it's similarly built in Swift & macOS only. Here's the web version I built almost a year ago you can play with in the meantime.

https://sidequery.dev

not as slick as this tool but https://dbeaver.io/ handles duckdb databases as well as a myriad of others.

`duckdb —-ui` is quite powerful, although edits still have to happen through written queries.

TablePlus perhaps.

Ultorg doesn't handle schema modification but is my favorite tool for data inspection

not similar, but more like jupyter

https://duckdb.org/docs/stable/core_extensions/ui

Thank you, that's very kind. I've always like the idea of TUIs, but found it hard to get used to them.

Thank you for the support! It means a lot.

I use Paddle (https://paddle.com/) as merchant of record because I don't want to deal with the paperwork of doing more myself. In practical terms, it's a key emailed after purchase.

That’s not me. I was quite surprised - in a good way - to find it there.

What you just described sounds like the kind of marketing I feel is missing from the site: if you're already using a multi-db tool, what's compelling about this product?

Weirdly I’ve been building something along those lines for the last year. Not SQLite backed, but fully local and native (and also does non-local integrations, which you can also script yourself). Should be ready in a month or so if you’re interested!

There’s two main reasons. One is that this is the first paid update in 14 years. The second is that it’s just such a pain to do that on the App Store and I don’t want to have a two-tier system. I’ve tried to strike a balance on the price bearing this in mind.

Yes, it's OK to do that, but not to cross the streams. You can't advertise direct purchases from the Mac App Store version.

That is a limitation of SQLite no?

Who in the world would prefer a web-based database tool which you must run locally over a normal web database tool which runs locally?

The “thick” tool will always be more efficient than a browser-based tool. It will always be more native and integrate with the OS better. It will perform better (though that may not always be visible).

Yes I am seriously asking. The web is the worst application platform ever created, despite being maybe the only true cross-platform platform.

Potentially about anyone who used Access without the forms interfaces... While I'm not the biggest fan, I know a lot of people who prefer a visual editor for table schema creation. They'll use SQL Management Studio, or whatever equivalent with other DBMS and edit that way instead of DB specific queries... For example, re-ordering fields is destructive in a lot of DBMS and the queries get painful, a visual editor eases this burden.

As to importing/exporting data (csv, etc), You can't exactly run queries against a CSV generally (I mean you can with JET/ODBC, but still)... it's a chore.

To another point, I often feel that sqlite is a great backup format for data, it's portable, you can query directly, etc. I kind of wished that Azure Data Studio (now discontinued) had directly supported loading CSV/TXT etc into an in-memory or temp db for queries and portability similar to mentioned between various data sources (sqlite, mssql, pgsql, etc).

You call do everything with the sqlite cli tool, but GUIs are very convenient to explore and play with the databases.

I have regularly used sqlitebrowser. You can even get create table statements corresponding to tables you have drafted with it, and you can copy-paste these statements in your code. Or build and test a sql statement incrementally, and paste it in your code when you are happy.

Or even to plain browse data, explore database from other tools, etc. There are a lot of occasions where such a tool can be convenient.

A lot of people use it as a step up from Excel.

I’ve got quite a few scientists using it for data analysis, a whole bunch of people importing sales data for querying.

A good number of folks never write any SQL and just use the GUI to sort/filter data.

I’ve also heard from people who use Base to design a schema and then export the SQL into their codebase.

EDIT: I should add that Base does nothing that that the command line tool can't. But it does offer more convenience, particularly for those who might be less comfortable writing SQL.

> And thus these kind of statements happen within an application.

Who made that rule? Most database tools I have used do this. I can go into SQLite's command interface and do it. It's not limited to the application.

Not all of us want to write code for everything. Sometimes when I'm developing an app or an idea, the last thing I want to do is keep messing with cruft I don't have to. It's a waste of time to write yet another alter/create/etc script. I just click and change it. That's it, and I move on. I don't care until my design is done to export/create the creation/alter scripts, if I even need them, because I can ship a fully set up DB file with the app, I don't actually have to have scripts for that stuff. Sure, later I may need to on an update, but when rapidly prototyping I'd rather just have the quickest way to deal with it. Else, the argument becomes "Why not just use the SQLite terminal interface instead of a graphical tool?" or "Why would you create or rename a directory using Finder rather than a shell script to do it?"

> IE, who's using SQLite in this way, and what are they using it for?

Anyone that wants to do such things? I just recently wrote an app that runs in a browser using only javascript, but in the end I wanted the data and it exports it to JSON so I can load it into another app. I haven't gotten around to integrating these (and I probably won't) as the app is a pure browser application, but the other is a tool for creating TTML2 documents, and I need that data. So, I can easily import that. It's not a commercial thing, it's my own. So, I don't want to merge the two. I don't mind the step. This sort of thing is good. What is wrong with the ability to do so? PostgreSQL can do it. You don't believe it could be useful to export your data so that someone that isn't using SQLite can view it? Dumping a table for viewing in Excel is so common that I don't really understand why you're asking.

There are literally billions of devices using SQLite. And within those devices, countless applications are using the thing. Do you believe it has a limited set of use cases? Heck no.

Your questions seem like bait and I bit.

Visual browsers are very nice for exploring and pulling data out of databases that aren’t under your control, such as those used by apps. For example, first-party Apple apps nearly all use SQLite locally, and so with a browser you can peer into and hack on their data storage.

Not using it this way, but wanting to: a friend of mine is working on a TTRPG. He has items, powers, other things to track - and right now it's in a mess of a Word document.

It would be so much better if he could store these things in SQL - for non developers, they often use Excel, when a SQL DB is the correct solution, just because they can't figure out SQL at all. A visual editor/manager is perfect for them - create a table with columns, enter data, later integrate with other tools that can pull it out.

I know researchers (think field biology) who need to enter data into SQLite databases with bespoke apps but on occasion it’s imperative to edit or search the database directly. I’ve helped them do that on some butt-ugly app they had installed whose name escapes me. Looking at Base, my immediate thought was to buy it for them.

Additionally, while I know my way around and even enjoy the command-line, on occasion I bump into some app’s SQLite database that I want to explore. I can see myself using a GUI for those instances to help me find the data I’m looking for faster.

For quite some projects, before coding anything I tested out if my logic is sane by hand-crafting a prototyle sqlite db. Really helps visualizing my thoughts how to layout the data before I start a project, work with an ORM etc.

You're not sure of the target audience for an ad hoc SQL query analyzer? You've never needed to figure out a complex join for your application without trying to do it from the command line client?

https://datasette.io is a similar tool for journalists to disseminate and share data

I'm a programmer and I barely understand how to use SQL so this would be useful for me

Because I don't have the capacity for maintaining compatibility between too many versions of macOS. For v3, I've stated the goal - not guarantee - of supporting the current and previous versions of macOS.

Since Tahoe is likely to be released in the next month or so, I judged it better to start with Sequoia and keep it supported rather than start with Sonoma and risk needing to drop it shortly after launch.

Because targeting the latest release lets the developer use new APIs/features that are not available on older OSes making development more productive and pleasant.

Of course, there’s a balance to be struck to support users, but macOS update uptake is usually pretty fast.

Because you pay once and that's it.

Now if you did that and used it on your 3 versions out of date macOS, then an update came that only worked on current-2, you'd be pissed.

Mostly only software with subscriptions can afford to keep supporting multiple versions.

In practice - no. There's Darling https://darlinghq.org/ but it only supports basic cli things for now. There was some idea for gui support https://github.com/darlinghq/darling-appkit-gui but that is not working and the project is very incomplete.

Nobody else is working on the GUI side. I can tell because I'm working on the ibtool replacement and none of the relevant constants or required code exists anywhere on Google. It's a complete reverse engineering from scratch for anything added in the last decade.

Thank you. I don't have any other public apps at the moment.

I am launching a similar tool very soon; it's built in Swift & macOS only. Here's the web version I built almost a year ago you can play with in the meantime.

https://sidequery.dev

It's not required, but you can if you want :)

It's not for everyone but I can think of 2 use cases immediately:

1. If you're a developer, then you're probably using sqlite to store data and having a GUI for checking/modifying the database is probably handy.

2. Most application by other developers (even massive companies) use sqlite to store data under the hood even if the file doesn't appear to be named `.sqlite`. So if you want to tweak certain settings that aren't exposed to the end user, then you can use this to do that in a more user friendly way rather than crack out a command line tool for sqlite changes.

To add to what xmprt and msephton have said, people have told me they use it for:

  - Storing results for scientific research
  - Local analysis of data exported from server-based databases
  - Experimenting with database designs before exporting SQL to codebases
  - Maintaining relational data where a website or app are not needed (eg. tutors keeping client records)
  - Recovering data from databases used by other products (eg. phone backups, discontinued apps)

It's for manipulating SQLite databases with a GUI, rather than in a web page or at the command line. I previously used V2 of Base and the user interface was excellent, and this looks to be even better.

40 USD, this is ridiculous

this is on par with AAA games that have tons of creative assets, art, music, thousands of people work on them for years.. and here is a utility that did not even invent SQLite itself, not a dollar goes to SQLite creators either. rather a light wrapper over standard UI build by Apple - SwiftUI. what is the marginal contribution from creator here for this price? what a ripoff

So the OSS thing is a preference, obviously, if you like OSS tools, just use them - it's not like you're short of options.

But... expensive? Really? A one-off cost (not a subscription, mind) for a tool that might make an expensive developer/data person even marginally more productive? You have to be kidding. It blows my mind how much developers, who of all users should be the ones who appreciate how much work goes into something like this, complain so often about the cost of basic tools.

As others have said, this is written using AppKit and SwiftUI, which are only available for macOS.

This makes for a much nicer app for mac users, even if it does cut your potential audience.

Because it a GUI that uses Mac-specific UI libraries that aren't available on Linux and Windows?

While the database stuff works on Linux and Windows, building UIs for the 3 platforms is very different, unless you use some cross platform wrapper layer. Which isn't the purpose of this one. You might want sqlitebrowser[1] if you're looking for something like that.

[1]:https://sqlitebrowser.org

Probably because it’s written using native Mac UI instead of half-assed with electron.