[HN Gopher] SQLiteStudio: Create, edit, browse SQLite databases
___________________________________________________________________
SQLiteStudio: Create, edit, browse SQLite databases
Author : thunderbong
Score : 374 points
Date : 2024-11-25 00:36 UTC (22 hours ago)
(HTM) web link (sqlitestudio.pl)
(TXT) w3m dump (sqlitestudio.pl)
| djsnoopy wrote:
| 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.
| dayeye2006 wrote:
| Maybe UI
| kyawzazaw wrote:
| UI is quite useful to me
| emptiestplace wrote:
| Agreed, rip the band-aid off folks. You will be so glad you
| did.
| owobeid wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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
| googie wrote:
| 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 wrote:
| A good alternative to the sqlite cli is litecli [1]. I've been
| a happy user for quite some time.
|
| [1] https://litecli.com
| hochmartinez wrote:
| 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/
| dagw wrote:
| 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)
| TheRealPomax wrote:
| 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
| chasil wrote:
| 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...
| hysan wrote:
| How does this compare with https://sqlitebrowser.org/ ?
| jksmith wrote:
| My goto as well.
| knighthack wrote:
| That's my Swiss knife.
|
| Super handy in a lot of scenarios, and I use it side-by-side
| with Jetbrains' DataGrip.
| hochmartinez wrote:
| 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.
| 0points wrote:
| It's in AUR
| dotancohen wrote:
| It's GPL, so why no Debian packaging, if it is popular?
| pmarreck wrote:
| FYI to anyone on Nix/NixOS, sqlitestudio is available on
| unstable branch of nixpkgs:
|
| https://search.nixos.org/packages?channel=unstable&show=sqli.
| ..
| Jach wrote:
| It's in gentoo's default portage tree
| PeterStuer wrote:
| 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 wrote:
| 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 wrote:
| 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.
| thechao wrote:
| Hey! Thanks! Y'all do great work! Your tool is critical to
| my ability to keep sane.
| simonw wrote:
| 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
| googie wrote:
| Actually the project is much older. It started in 2007. More
| details on this can be found at https://sqlitestudio.pl/about/
| skc wrote:
| 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
| cess11 wrote:
| Really?
|
| To me that's a major annoyance, an obvious defect in the
| software. It's like a car going unresponsive from standing
| still at a red light and needing me to turn it off and waiting
| for five seconds before I start it again before I can drive
| away, or having to turn it off when stopping and then starting
| again when the light changes.
| Temporary_31337 wrote:
| Then report it via the appropriate channels with as much
| detail as possible and steps to reproduce. It looks like you
| are complaining without having even used the software.
| cess11 wrote:
| No, I'm just not going to use the software if I encounter
| this.
| gchamonlive wrote:
| You'll have to stick to pen and paper, although pens stop
| working if you don't use it for an extended period of
| time, so I guess it's an obvious defect with the product
| and you shouldn't use it.
| cess11 wrote:
| No, I spend something like ten to sixteen hours a day in
| front of software that doesn't freeze up just because I
| don't cuddle it.
|
| I've got some devices behind a NAT I'm lazy with, I
| reboot them maybe once a year or so.
| cztomsik wrote:
| You pay for a car. This is free and OSS.
| googie wrote:
| 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.
| thunderbong wrote:
| I've used it for many years. Never faced this problem.
| DecoPerson wrote:
| 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 wrote:
| 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 wrote:
| 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
| killingtime74 wrote:
| I just use Datagrip. Works with SQLite and many more dbs
| turblety wrote:
| Datagrip is a paid, proprietary and closed source commercial
| product.
| JaggerFoo wrote:
| Excellent product that behaves as expected and adheres to
| Sqlite's unique requirements when updating schema objects.
| hu3 wrote:
| I've been using this client lately: https://dbgate.org
|
| Anyone else?
| Oxodao wrote:
| Just tried it, it might replace DBeaver for me! Vim mode
| without plugin is amazing. Need a few days to fully try it out
| thinker5555 wrote:
| 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)
| googie wrote:
| 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).
| muhehe wrote:
| 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.
| tolai wrote:
| 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 !!!!
| forinti wrote:
| 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.
| macmac wrote:
| How did you get the tables from Oracle into SQLite?
| forinti wrote:
| With a little Perl: https://github.com/glgraca/ora2sqlite
| bpiroman wrote:
| love it! thank you so much!!
| confiq wrote:
| Where have you been all my life? :)
|
| Seriously, I needed this 10 years ago.
| shigawire wrote:
| 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.
| pie_flavor wrote:
| 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!
| bmacho wrote:
| 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 wrote:
| 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.
| Nickersf wrote:
| Thank you very much for this amazing piece of software.
| hochmartinez wrote:
| 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 Pawel Salawa
| for this great piece of software!
| SonOfLilit wrote:
| 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.
| Alifatisk wrote:
| 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.
| pjturpeau wrote:
| 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!
| xenodium wrote:
| 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
| coldcode wrote:
| MacOS refused to run the installer on Sequoia. Signing it is not
| all that hard.
| googie wrote:
| You can right-click and run from the context menu, in which
| case it should start. Contributions are welcomed.
| nolito wrote:
| Same problem here. Also on Seqouia
| jbverschoor wrote:
| Not on Sequoia / Lockdown Mode. Signing is not that hard
| indeed
| TheRealPomax wrote:
| 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.
| JaggerFoo wrote:
| 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.
| DonnyV wrote:
| 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...
| googie wrote:
| 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.
| nbevans wrote:
| 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.
| dkjaudyeqooe wrote:
| This behaves very strangely on macOS 10.14.6: the installer
| downloaded from the website immediately logs you out without
| warning.
| googie wrote:
| 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.
| sirjaz wrote:
| This is the way IDEs should be. We should be able to run things
| locally without the overhead of a webbrowser
| mey wrote:
| 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)
___________________________________________________________________
(page generated 2024-11-25 23:00 UTC)