SQLiteStudio: Create, edit, browse SQLite databases

(sqlitestudio.pl)

554 points | by thunderbong 2 days ago

105 comments

  • googie a day ago

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

    • tolai a day ago

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

      • davisr 14 hours ago

        How much money have you paid the author of SQLiteStudio?

        • jabiko 7 hours ago

          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.

          • davisr 3 hours ago

            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.

          • greghendershott 6 hours ago

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

    • forinti a day ago

      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.

    • GGerome 13 hours ago

      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

      • googie an hour ago

        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.

      • googie 12 hours ago

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

    • shigawire a day ago

      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.

    • zeroq 16 hours ago

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

    • mytdi 20 hours ago

      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.

    • muhehe a day ago

      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.

    • pie_flavor a day ago

      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!

    • confiq a day ago

      Where have you been all my life? :)

      Seriously, I needed this 10 years ago.

      • Gys 7 hours ago

        Funny. The screenshots are almost exactly 10 years old…

        See the gallery page

    • Nickersf a day ago

      Thank you very much for this amazing piece of software.

    • bpiroman a day ago

      love it! thank you so much!!

    • bmacho a day ago

      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.

      • TheRealPomax a day ago

        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.

  • simonw 2 days ago

    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

  • hysan 2 days ago

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

    • hochmartinez a day ago

      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.

    • PeterStuer a day ago

      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?

      • justinclift a day ago

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

        • PeterStuer a day ago

          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.

          • justinclift 19 hours ago

            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

            • PeterStuer 13 hours ago

              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.

        • thechao a day ago

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

    • knighthack 2 days ago

      That's my Swiss knife.

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

    • jksmith 2 days ago

      My goto as well.

  • SonOfLilit a day ago

    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.
    • seanw444 3 hours ago

      I forgot about VisiData. Great tool too.

  • hu3 2 days ago

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

    Anyone else?

    • Oxodao 2 days ago

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

      • ctm92 7 hours ago

        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

    • thinker5555 a day ago

      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)

  • hochmartinez a day ago

    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!

  • DecoPerson 2 days ago

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

    • CaliforniaKarl 2 days ago

      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.

      • chasil a day ago

        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

  • xenodium a day ago

    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

  • JaggerFoo 2 days ago

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

  • mey a day ago

    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)

  • skc 2 days ago

    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

    • googie a day ago

      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.

      • skc 12 hours ago

        Cool, will do.

    • thunderbong a day ago

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

  • yupyupyups 19 hours ago

    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.

  • pjturpeau a day ago

    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!

  • sirjaz a day ago

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

  • dkjaudyeqooe a day ago

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

    • googie a day ago

      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.

  • Alifatisk a day ago

    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.

  • djsnoopy 2 days ago

    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.

    • owobeid 2 days ago

      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.

      • bilekas a day ago

        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

        • owobeid a day ago

          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

      • dotancohen a day ago

        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

    • hochmartinez a day ago

      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/

    • chasil a day ago

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

      • AstroJetson 17 hours ago

        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.

    • kyawzazaw 2 days ago

      UI is quite useful to me

    • dagw a day ago

      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)

    • googie a day ago

      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/

    • rmbyrro a day ago

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

      [1] https://litecli.com

    • dayeye2006 2 days ago

      Maybe UI

    • TheRealPomax a day ago

      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

    • emptiestplace 2 days ago

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

  • nbevans a day ago

    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.

  • killingtime74 2 days ago

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

    • turblety 2 days ago

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

      • earthnail 13 hours ago

        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.

  • DonnyV a day ago

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

  • coldcode a day ago

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

    • dathery 20 hours ago

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

      • tensor 20 hours ago

        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.

        • dathery 20 hours ago

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

          • coldcode 7 hours ago

            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.

            • lxgr 7 hours ago

              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.

          • tensor 18 hours ago

            Oh yes. For sure. Agree completely.

    • JaggerFoo a day ago

      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.

    • googie a day ago

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

      • jbverschoor a day ago

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

        • TheRealPomax a day ago

          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.

          • jbverschoor 12 hours ago

            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.

            • ansonhoyt 4 hours ago

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

            • TheRealPomax 2 hours ago

              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.

      • nolito a day ago

        Same problem here. Also on Seqouia