SQLiteStudio: Create, edit, browse SQLite databases | Hacker News Reader

SQLiteStudio: Create, edit, browse SQLite databases

sqlitestudio.pl

626 pointsthunderbong123 comments

Author here. I'm surprised and honored to have my pet project here ;) As mentioned in another comment, I'm currently in the process of bugfixing/polishing 3.4.x branch. Then I will focus more on 3.5.0, which will bring many big features. One of them being ERD (read & write).

Thank you! This is great software. I don't use it much (and recently almost not at all), but I still love. It's fast, it's easy to use. I just checked your website and it looks there are tons of features I didn't know about :). Thanks again.

SQLiteStudio is fantastic, I've been using it on and off for a few years already and it's saved my ass so many times. Once, we were doing many many meetings discussing a potential implementation for a sales incentive scheme and it was very difficult to get everyone onboard. Fed up with this I built a demo database in sqlite using a portable SQLiteStudio instance and prepared a bunch of queries. This "reference implementation" made it possible to get everyone aligned in record time !! This would not have been possible at all with the "frictions" of a convential RDBMS. Also, analyzing and cleaning up client data during project UATs is so damn convenient in SQLiteStudio. Thanks !!!!

How much money have you paid the author of SQLiteStudio?

I don't see how publicly shaming someone (and yes, this is how I interpret the intend of your question) for the act of thanking the author of a project is going to help anyone.

It is offered free of charge, so why should it be despicable to use it free of charge? Maybe they do actually donate to the project, contribute code, or support in other means.

For example this very post where they thank the author is probably a source of motivation and acknowledgement that might have a positive impact on the project. They could have refrained from doing this but instead they took the time to write a very enthusiastic comment.

Sure it's offered free of charge -- and immediately next to the big "Download" button is a big "Donate" button.

> Maybe they do actually donate to the project, contribute code, or support in other means.

Maybe instead of shaming, the question is a cue for them to mention one of those things.

---

In the US it's Thanskgiving week. It's nice to give thanks. It can also be nice to give other things -- like support to a project that has saved/made your company non-trivial money. Not required, but nice.

To be clear, I think it would be fair if they answer something like: "I am trying to get my company to contribute... but as my original story showed, my company is pretty shitty at making simple decisions." :)

I say this all as someone who has paid for SQLiteStudio: if you don't see the connection between paying for open-source software, and open-source software sustainability (aka "having nice things"), then your brain is totally cooked. Money is energy, and without it, there will continue to be yet another "why open-source desperately needs funding" front page post every week.

Not one other person in these comments mentions paying for this work. That is worth embarrassing those who are all talk, no action. They are doing worse than ordinary virtue signalling--they're phony virtue signaling.

Giving compliments are fine, but put them in the donation message box.

If every positive comment on this thread doesn’t start by mentioning the exact percentage of their salary donated I think I might explode.

It's a great tool. My use-case is a bit unusual: I decommissioned an Oracle Portal instance and decided to keep a copy of the tables in SQLite so that I can recover files people may later remember they need. It's much easier than maintaining an Oracle instance.

It's a nice feature of SQLiteStudio that you can click on a blob and see the image, if it's an image file.

How did you get the tables from Oracle into SQLite?

With a little Perl: https://github.com/glgraca/ora2sqlite

Cannot stop laughing on the claim about easier to maintain SQLite than am an oracle instance

love it! thank you so much!!

Where have you been all my life? :)

Seriously, I needed this 10 years ago.

Funny. The screenshots are almost exactly 10 years old…

See the gallery page

Thanks for your work on this. It was super helpful as a student learning SQL. Having the visual feedback to check the statements made or queries ran on my test data was invaluable.

I learned about it just a week ago, and the thing I wanted to do with it worked flawlessly the first time on terribly formatted data. Thank you for your hard work!

It says portable, and

> No need to install or uninstall. Just download, decompress and run.

but the main download button is an installer for windows.

That's why you click on "downloads" which takes you to https://github.com/pawelsalawa/sqlitestudio/releases because a homepage button offers people "the most likely installer their OS/Browser combo suggests they probably want", so you click through the full list of downloads to explicitly pick the version you want. Just like you'd do if you wanted to download the Linux and Mac installers even though you're currently on Windows.

Thank you very much for this amazing piece of software.

Thank you for this great app! I have used it for a while now on both, Windows and Linux. Love it! I have recommended it here on HN in the comments a few times.

Ha! I was going to reach out to you through a different channel, but here you are, on HN. :)

The import function is really slow.

I was recently playing with my pet project which is building an sqlite database from IMDB public datasets. It's 6Gb of CSV files into a ~12Gb database after a vacuum. With nodejs I can import the data within 6 minutes and create indexes and vacuum in another 4, which gives me a fully indexed database in just 10 minutes. With SQLStudio the import alone takes at least half an hour.

Probably not a typical use case for a sqlite database, but nevertheless, a decent benchmark.

BTW. Dobra robota! Dzięki! :)

In few days there will be another 3.4.x branch release (3.4.7), which addresses this exact problem - https://github.com/pawelsalawa/sqlitestudio/issues/5119

...or if you didn't mean the import() SQL function, but in general importing functionality (through Import Dialog) and it's still slow there, then please contact me and I will see what can be done to improve it - https://sqlitestudio.pl/contact/

Nice to meet the author here. I use SQLIteStudio since few years and I am still annoyed by its bad performances when dealing with a table that contains columnq that holds json data, at least each row of this column has json data between 500kb to 1 mb, then the app freeze and is quite unable to deal with its datas. I can provide example if you want

Yes, please! You can contact me directly through email, or through github discussions or issues. Details are at https://sqlitestudio.pl/contact/

Thanks Pawel!

Okay, never mind. I managed to reproduce the situation from your description. That's a tough one, but I will try to do something about it.

Screenshots here: https://sqlitestudio.pl/gallery/

It's built in C++ and Qt, is GPL licensed, looks like it's been in development for just under ten years. https://github.com/pawelsalawa/sqlitestudio

Actually the project is much older. It started in 2007. More details on this can be found at https://sqlitestudio.pl/about/

How does this compare with https://sqlitebrowser.org/ ?

My goto as well.

That's my Swiss knife.

Super handy in a lot of scenarios, and I use it side-by-side with Jetbrains' DataGrip.

I've used both. Sqlitestudio is far more powerful, intuitive and easy to use. Fast and efficient. Flies even in old PCs. In Linux you won't find It in the repositories. You have to download and run a handy installer.

It's in AUR

It's GPL, so why no Debian packaging, if it is popular?

Anecdotally, but I see less and less software distributed through apt these days

The ITP was in 2016, but it seems like it didn't really progress from there - https://bugs.debian.org/827236

FYI to anyone on Nix/NixOS, sqlitestudio is available on unstable branch of nixpkgs:

https://search.nixos.org/packages?channel=unstable&show=sqli...

It's in gentoo's default portage tree

I have been using sqlitebrowser as well. Fairly satisfied, except for the poor 'export to csv' that seems to fail on respecting csv separations in some cases. Does SQLiteStudio handle this correctly?

Do you have the specifics of the failure cases? We (sqlitebrowser.org devs) tend to fix bugs like that when we have a reproducer.

Thx for replying here.

My issue had to do with exporting text fields that have multi-line content and then importing that data into Microsoft Excel. I have quickly looked more deeply into this as for now I used a workaround by exporting to JSON.

Upon investigation it does not seem like sqlitebrowser is doing anything explicitly wrong. It quotes texts correctly when necessary, in my case specifically strings that contain 'LF', and does not do it when it is not needed.

The fault lies with the Excel importer that in this case does not correctly derive that it should use QuoteStyle=QuoteStyle.Csv (it uses QuoteStyle=QuoteStyle.None even when you instructed it to base its derivation on the entire dataset. I do not know if any accommodations on the exporting application can (or should) be made to compensate for Excel's import heuristics failures.

P.S. for those running into the same issue (there seem to be many and I have not seen a solution from a quick Goolge that worked), in Excel when doing the import from CSV, select "Transfrom Data", open up the "Advanced Query Editor" and in the first line you will see something like

let Source = Csv.Document(File.Contents("the path to your CSV file"),[Delimiter="#(tab)", Columns=13, Encoding=65001, QuoteStyle=QuoteStyle.None]), ...

Just replace the QuoteStyle.None with QuoteStyle.Csv and you should be good to go.

My apologies to the sqlitebrowser devteam for my initial misconception.

No worries at all. I kind of wonder if using LibreOffice for the initial import -> Save to (say) Excel format would work better, than opening that in Excel?

ie might be easier for people who aren't comfortable changing the default settings in things

It is a weird bug given that you explicitly select "Import from CSV file" In Excel, so no guessing should be needed.

And it is not an obscure edge case You find many people across different fora asking about this, and the suggedtions they get are impracticle workarounds (remove the LF before importing) or do not work (add an 'instruction' line to the file explicitly declaring a separator such as sep=;).

Routing through LibreOffice might work, but there could be a large overlap between those unwilling to open the advanced editor and those unwilling to install LibteOffice.

Google sheets could be another option to try, but only for those that do not mind their data leaving premise.

Hey! Thanks! Y'all do great work! Your tool is critical to my ability to keep sane.

Awesome, you're welcome. :)

This week I needed to quickly have a peek at what was saved in a testing database, and I wondered "does VisiData support this?" and sure enough

    vd test.sqlite3

gave me a list of tables, right there in the terminal, and choosing a table with arrows and Return showed me the table data in a grid view with all of vd's filtering and sorting commands right there.

I forgot about VisiData. Great tool too.

I've been using this client lately: https://dbgate.org

Anyone else?

Just tried it, it might replace DBeaver for me! Vim mode without plugin is amazing. Need a few days to fully try it out

I wanted to replace DBeaver for a long time, but I have some not really common connections there (e.g. Sybase SQL Anywhere), that no other client seems to support

There's Dbvisualizer if you want to try an alternative. Though I moved from the company provided dbvis pro version to DBeaver Community when I discovered it.

I just tried it out, but for some reason it's complaining about missing a pivot_vtab module when I try to open an existing database. (MacOS ARM/Ventura)

I've been using It for several years, in Windows and now in Linux. Fast, slick and very powerful. Flies on my humble Atom laptop. By far the best free sqlite manager. Thanks Paweł Salawa for this great piece of software!

This looks great. For Emacs users, v29 introduced sqlite-mode. I've experimented with some convenience extensions which can be handy for quick sqlite views and edits https://lmno.lol/alvaro/sqlite-mode-goodies

Be very careful using this over Samba, even with WAL mode enabled. I corrupted an important testing DB this way. Thankfully .recover came to the rescue and only a small amount of data was lost (but the test team had to wait a couple hours for me to bring the test environment back online).

The WAL journal mode does not work over Samba. See the first disadvantage from https://www.sqlite.org/wal.html:

> All processes using a database must be on the same host computer; WAL does not work over a network filesystem. This is because WAL requires all processes to share a small amount of memory and processes on separate host machines obviously cannot share memory with each other.

The presence of the `-shm` file is one of the signs that the database is currently operating in WAL mode, and must only be accessed from the machine hosting the database file.

Looking at the list of journal modes supported (https://www.sqlite.org/pragma.html#pragma_journal_mode), you should see if the problem happens with the default `DELETE` journal mode.

Also, see https://www.sqlite.org/atomiccommit.html#_broken_locking_imp... for warnings about the SQLite that ships with macOS.

That is not the only limitation of WAL mode.

“It is not possible to change the page size after entering WAL mode.”

“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”

https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf

“SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”

https://www.sqlite.org/lang_attach.html

Excellent product that behaves as expected and adheres to Sqlite's unique requirements when updating schema objects.

Solid tool. But on Windows it has a tendency to freeze and remain unresponsive if you leave it open without using it for an extended period eg overnight.

It's a minor annoyance

Author here. This was never reported. I haven't noticed it for myself either. Feel free to get in touch through the official email (mentioned on the homepage) or through GitHub issues. I'm in the process of polishing 3.4.x branch, eliminating as many bugs as possible, before focusing on 3.5.0.

Cool, will do.

I've used it for many years. Never faced this problem.

I'm very thankful for this tool, it is powerful, doesn't stand in your way and does the job.

It is as others have said, lightwight due to it being build with Qt. The interface is mostly intuitive, and utilizes screen space properly.

If you need an open source SQLite editor, this is a solid choice.

Going to give this a try. I use DBeaver occasionally when working with SQLite db's but it's designed for persistent connections so opening random files isn't it's strong suit. (Also JDBC driver for SQLite)

Very nice "pet" project! I was about to ask what would make it more interesting than HeidiSQL or DB Browser for SQLite and then I remembered those two are crashing on few of my .sqlite files while SQLiteStudio does not!

I've been using this tool pretty much every day for almost a decade. It has a few quirks but it is still the best desktop tool for interacting with SQLite databases.

This behaves very strangely on macOS 10.14.6: the installer downloaded from the website immediately logs you out without warning.

Weird behavior indeed. I don't observe it myself on MacOS 10.11 and I had feedback from couple of other Mac users, where it worked fine - even on older MacOS than yours. I really cannot imagine what could cause your MacOS to log out. Sorry.

Just tried it out.

It seems to freeze on Windows with large sqlite views. I have a 89GB sqlite file and it doesn't like it.

sqlite file I used https://btrfs.openfreemap.com/areas/monaco/20241022_231001_p...

The file you linked is around 388kB in size. Are you sure it's the one you intended to link? I've checked it anyway and I see no problem for SQLite to open and query views from that db file.

Sorry, wrong one https://btrfs.openfreemap.com/areas/planet/20241120_001001_p...

I've got your file and I see where the problem is. I've created an issue to track and improve it for upcoming release.

Thanks!!

What does this have that the SQLite command line program doesn’t? Because every time I try one of these I go back to the cli.

Maybe UI

UI is quite useful to me

Agreed, rip the band-aid off folks. You will be so glad you did.

Here's one use case: while I don't use this particular GUI, it really does help when you have some columns containing RTL text such as Arabic and you want to browse through a table. RTL handling in most terminal emulators I've used is really lacking, though I can't blame them.

That's actually a really interesting case I had never considered. I actually just took it for granted that RTL in the console was a solved problem

Not just in consoles. While RTL is solved for most cases, I generally avoid writing Arabic in code for example (hard-coded strings, regex, etc) and just use Unicode escape sequences. Some issues include ASCII punctuation appearing (visually) in the wrong order and very awkward text selection

One day I set out to resolve this, though I can not find in my notes' files what the solution was (for MySQL). It might have been to simply use MyCLI instead of the standard MySQL CLI. Have you tried it?

https://github.com/dbcli/mycli

For example it has context-aware syntax autocompletion, easy D&D for tables between databases, and many more - you can see longer list at https://sqlitestudio.pl/features/

A good alternative to the sqlite cli is litecli [1]. I've been a happy user for quite some time.

[1] https://litecli.com

Lots of things! It gives you sqlite superpowers. It makes you more productive and saves you lots if time. You can edit several databases at the same time. And editing them is far easier. For example, it generates and executes the sql code to add new columns for you. You can edit the data of several rows directly on a query response, as if It was a spreadsheet, just by clicking on a column value (or you can use a column value editor). Super handy. You can view and edit blobs. The sql editor has autocompletition and you can execute a statement just by having the cursor on this statement, so you can quickly test multiple independent queries in a single editor window. It shows the execution times, so you can easely compare the speed of several query strategies. You can view the query optimizer info by clicking a button. It supports several scripting lenguajes, and the list goes on and on. Check the features here: https://sqlitestudio.pl/features/

Allows people who are not versed in SQL to interact with and edit sqlite files as if it was a spreadsheet (for better or worse)

All the benefits of a normal GUI-based DB manager. So "too many things to list, have a look at the website, it should be pretty self-explanatory" =D

This interface is similar to Toad from Quest Software, or SQL Developer from Oracle.

If you don't like either of those, then you likely won't like this.

After a quick search it is possible to load a JDBC driver into SQL Developer.

https://www.reddit.com/r/sqlite/comments/ci1wd2/sqlite_conne...

I went from company F to company V, used Toad at F all the time. I was told at V that Toad was not approved nor money for it. So I bought my own copy. Within 6 months all of the DBA team owned copies of Toad, a few months later V bought it for Developers and paid back all the DBA team for their copies.

I am stuck with stubborn developers who are holding on to Oracle 8-era Toad. Everybody else is on SQL Developer.

I wish I could erase it.

Maybe if a) Oracle did upgrades to SQL-D and b) Didn't charge for SQL-D like it was made of spun Unicorn horn you would have better luck.

This is the way IDEs should be. We should be able to run things locally without the overhead of a webbrowser

Is there a tool that allows multiple people to work in the same sql workspace? I thinking an application like mysql workbench but for collaboration where everyone shares the same editor, terminal and everything else.

That would accelerate the brainstorming a lot when working in a team remotely.

How did you make a cross platform native UI like that? I keep trying to start writing native apps but give up because of the GUI

EDIT: Oh looks like QT - I should learn how to use QT from Rust...

To echo some other comments here I use DBeaver as my daily database IDE but it doesn’t work well with SQLite. This looks like a great tool for handling that use case

Beekeeper Studio is fantastic as well

I just use Datagrip. Works with SQLite and many more dbs

Datagrip is a paid, proprietary and closed source commercial product.

True, but it’s one of the strongest offerings out there. Not everything has to be OSS. If you work with SQLite professionally (as I do), spending a few bucks on a good tool shouldn’t be a problem.

I still think SqliteStudio is a phenomenal piece of software. Super lightweight, just works, cross-platform… really not a lot to complain about. And incredibly well maintained.

MacOS refused to run the installer on Sequoia. Signing it is not all that hard.

You can right-click and run from the context menu, in which case it should start. Contributions are welcomed.

Same problem here. Also on Seqouia

Apple changed this to be a little more annoying with Sequoia: System Settings > Privacy & Security > "Security" heading > "Open Anyway" button.

- https://support.apple.com/guide/mac-help/open-a-mac-app-from...

Not on Sequoia / Lockdown Mode. Signing is not that hard indeed

Much like all previous versions of MacOS: settings -> privacy and security -> click "open anyway". No need for open source to give in to Apple's signing demands.

In Lockdown mode there's only: "App Store" and "App Store & Known Developers".

There is no "open anyway" button when using Lockdown mode.

edit: interestingly the button suddenly appeared. Perhaps you need to keep the privacy&setting screen open.

Possibly:

> This button is available for about an hour after you try to open the app.

https://support.apple.com/guide/mac-help/open-a-mac-app-from...

You get that option only when an application gets blocked, and it doesn't hang around forever (nor does it stack, you don't get multiple options if you try to run multiple blocked apps): you have to click it when it's relevant, and if you don't, it disappears after a short while.

I use it all the time on Sonoma OS with no problems. I have yet to upgrade to Sequoia due to the numerous reported problems on Reddit.

Don't you have to pay an annual fee to Apple to sign software? It looks like they have a fee waiver program but for some reason it doesn't cover open source or even let an individual person apply at all: https://developer.apple.com/support/fee-waiver/

I'm not sure it is reasonable to demand that open source devs pay a fee to Apple so that you don't have to right-click the app and whitelist it...

I thinks it's very reasonable. They provide an important security service, also the windows equivalent is many hundreds of dollars per year, per application, vs $99 per year which would cover any number of applications.

My comment was about whether it is reasonable for random people to demand open source devs pay a platform fee, not about whether the fee itself is reasonable.

Quoting the last line of my comment again with added emphasis in case it was confusing:

> I'm not sure it is reasonable *to demand that open source devs pay a fee to Apple* so that you don't have to right-click the app and whitelist it...

Oh yes. For sure. Agree completely.

It has nothing to do with open source, it's about security. Installing an app with no connection to anything invites lots of people to build apps that steal your stuff (sure this app might be trustworthy, but not all). Every Mac and iOS developer has the same requirements. Installing an app with no signature is asking for people to get hacked. Do you leave your house open so that some contractor you don't know can enter your house and do whatever they please? $99 a year means Apple can trust that they at least know who the contractor is. There are people who want to steal your stuff (or your mom's) much smarter than you or I. This isn't an onerous requirement at all. I shipped my first Mac app in 1987, back then security was not an issue. It is today.

It really is for some developers. $99 goes a long way in some places, and not everybody has a credit card either.

Also, how does paying $99 mean that Apple “knows who you are”? It identifies you as somebody willing to spend $99, nothing more. I bet that’s not a problem at all for many bad actors.

If it's that important for you, contact the dev and pay them the 99$.

Reply on news.ycombinator.com