4 ms·

Use LibreOffice Base as a GUI for an SQLite Database in OS X (2016)

92 points by MonkeyClub 4y ago

happyjack 4y ago
I've used this setup before as well! LibreOffice Base is a great GUI and the form field is super customizable.
ilyt 4y ago
I used similar setup ages ago with MS access and MySQL ODBC connector, we used it for license management in helpdesk, basically a way to easily create CRUD apps
zie 4y ago
Using LibreOffice 7.4.3.2 on MacOS Monterrey(12.6.1) on x86 I couldn't make it work, due to this bug: https://bugs.documentfoundation.org/show_bug.cgi?id=133056 https://bugs.documentfoundation.org/show_bug.cgi?id=133056 Which has been around 2yrs and hasn't been fixed yet. The work around of un-signing didn't work either. So as cool as this is, it doesn't seem to be very usable, which might be why nobody bothers to document it.
account-5 4y ago
I've attempted to use Libreoffice Base as a replacement for Access for a very simple single user database a while back, as I didn't want to fork out for Access. I failed miserably, everything seemed hard to do compared to Access (I'm aware this might be somewhat down to familiarity). Unlike Access there seems to be little in the way of help online for Base. I couldn't get the GUI to populate a listbox from a query, and the GUI appears very limited; very bad experience. I think the GUI would be the last one I would use as a front end for anything, it seems designed in a way that necessitates Googling to do the similist of things, but like I said above returns next to nothing useful. Very frustrating.
- MonkeyClub 4y ago
  100% this. I really want LibreOffice Base to be usable as an Access replacement, but it seems that it’s simply there to tick off the box “database tool for the office suite”. So it implements the necessary features that this box would require, but the corners haven’t been rounded off. I’m sure people must be using it to various extents, but it definitely lacks the basic accessibility to its features that Access provides. I sort of posted the link to also check how other HNers may use it, and it seems that your experience matches mine: I’d love for it to be easier to use.
- petilon 4y ago
  You can use https://airforms.com/ https://airforms.com/ as a replacement for Access, for the forms side of things. It doesn't have reporting though.
rout39574 4y ago
I've been looking for something like this; I've described it as "Paradox for DOS for sqlite". Simple TUI for CRUD operations with foreign key enforcement. Libreoffice as a toolkit, so it becomes "Paradox for windows for sqlite" would be OK.. :P Not a webapp. Something you can run inside a git checkout, and get an essentially equivalent UX in any terminal. I'd expected that Python would be rife with such projects, but it seems that "Just build a webapp with my favorite toolkit" is a solution that satisfies most of the itches.
- saulpw 4y ago
  Maybe VisiData is up your alley? It doesn't do foreign key enforcement (yet) and is more for exploring than modifying, but with the new vdsql functionality I could see it being some part of what you want. Disclaimer: am author.
bachmeier 4y ago
Related: You can use the Fossil bug tracker as a generic SQLite table. You can modify the interface for any type of input. While you can edit the schema if you want to use it as a traditional SQL database, I've found it easiest to treat one of the text columns as JSON and do queries on that. The advantages: You get a web interface for free (using CGI) if you want one, it's distributed and version controls your database automatically. You can view reports graphically, at the command line, or from any other app that read an SQLite database. And in terms of stability, since Fossil is developed by the SQLite project, you can be confident that it'll be maintained for the rest of your life. https://fossil-scm.org/home/doc/trunk/www/index.wiki https://fossil-scm.org/home/doc/trunk/www/index.wiki
ComputerGuru 4y ago
Some years back we considered developing some sort of spreadsheet-esque frontend to a database (SQLite primarily, but also MySQL and Postgres) that would let you "live manipulate" either tables directly or, much more conveniently, "views" generated via queries or from virtual tables (with changes propagating backwards to the original source). The problem was that the userbase for such a tool is almost non-existent. People are either using Excel (or even MS Access!) and not looking to use anything else, or else they're more technical and can get by with a combination of manual queries plus the visual table mode in a tool like DB Browser for SQLite and its ilk.
LeoPanthera 4y ago
It's weird that in the 80s and 90s, "database" was a standard and integral part of pretty much every office suite from every software company. Other than Access, that seemed to change overnight, and now it barely is a thing you can do with your computer at all, and even Access is aimed exclusively at enterprise usage. I wonder why. I often think that a desktop GUI database app would be very useful, but with the possible exception of the aforementioned LibreOffice Base, this barely exists at all.
- kevin_thibedeau 4y ago
  Spreadsheets became more capable (larger screens, better formatting options) so now the target audience for a small scale database is just using that instead.
- jojobas 4y ago
  Main use cases for Access and the like seemed to be roll-your-own mini accounting/inventory control applications. Good riddance.
- eternityforest 4y ago
  I can't think of any real uses for it, unless they can make it sync between devices, in a way that is better than just using a cloud DB. The idea of data that lives on one local machine is so completely alien to the modern view of software(At least to me) that I'd have to actively spend time looking for use cases. A database that was as easy to use as SyncThing would be revolutionary though. So many spreadsheets could be replaced.
janvdberg 4y ago
I know this is not about MS Access perse, but I still feel there is a void concerning decent MS Access FOSS alternatives. MS Access hit a certain sweetspot between functionality and ease of use that I haven't seen anywhere since. Retool or Airtable are always named as viable alternatives. But they're not imho. I have built many MS Access applications and they always were surprisingly resilient. I believe even to this day there is a MS Access database GUI application running somewhere I built 15 years ago. Standalone, not connected to the web or anything. Just plowing away, just a single self contained file (mind the Office dlls etc.), backup and recovery is as easy as copying the complete file over to anywhere else and you're good. I think I have tried every alternative out there, but none hit that sweetspot (they always lack something).
- bb88 4y ago
  It's sometimes surprising that the simpler the solution the more effective. Cloud based can be very convoluted as a solution as it has to solve all the corner cases. And then you have to worry if the cloud provider will drop your data or let you export it easily for backup -- which they may or may not let you do easily.
- sseagull 4y ago
  I worked at a large non-tech (well, kinda tech) company as a chemist. They used an Access DB as a Lab Information Management System. Basically, a way to log samples in, assign work, report results, bill hours, etc. It worked pretty well! I believe it was home grown, but eventually expertise in it left and they were looking to move onto something else, which IMO was probably a mistake - it may have been easier to just fix the bugs and not try a difficult migration.
tracker1 4y ago
I'm actually half surprised there isn't an office database app that is simply sqlite3 file/tables that uses a couple special tables to store the forms layout/logic with a relatively simple player application. Funny how useful MS Access is after all these years, despite a few competing options over the years, with none really taking hold and no really good open-source alternatives.
- chunkyks 4y ago
  That's what kexi was supposed to be. https://kexi-project.org/ https://kexi-project.org/ I tracked it for a long time, just was never able to make it fit my needs

92 points by MonkeyClub 4y ago

I've used this setup before as well! LibreOffice Base is a great GUI and the form field is super customizable.

ilyt 4y ago

I used similar setup ages ago with MS access and MySQL ODBC connector, we used it for license management in helpdesk, basically a way to easily create CRUD apps

zie 4y ago

Using LibreOffice 7.4.3.2 on MacOS Monterrey(12.6.1) on x86 I couldn't make it work, due to this bug: https://bugs.documentfoundation.org/show_bug.cgi?id=133056 https://bugs.documentfoundation.org/show_bug.cgi?id=133056 Which has been around 2yrs and hasn't been fixed yet. The work around of un-signing didn't work either. So as cool as this is, it doesn't seem to be very usable, which might be why nobody bothers to document it.

account-5 4y ago

I've attempted to use Libreoffice Base as a replacement for Access for a very simple single user database a while back, as I didn't want to fork out for Access. I failed miserably, everything seemed hard to do compared to Access (I'm aware this might be somewhat down to familiarity). Unlike Access there seems to be little in the way of help online for Base. I couldn't get the GUI to populate a listbox from a query, and the GUI appears very limited; very bad experience. I think the GUI would be the last one I would use as a front end for anything, it seems designed in a way that necessitates Googling to do the similist of things, but like I said above returns next to nothing useful. Very frustrating.

MonkeyClub 4y ago
100% this. I really want LibreOffice Base to be usable as an Access replacement, but it seems that it’s simply there to tick off the box “database tool for the office suite”. So it implements the necessary features that this box would require, but the corners haven’t been rounded off. I’m sure people must be using it to various extents, but it definitely lacks the basic accessibility to its features that Access provides. I sort of posted the link to also check how other HNers may use it, and it seems that your experience matches mine: I’d love for it to be easier to use.
petilon 4y ago
You can use https://airforms.com/ https://airforms.com/ as a replacement for Access, for the forms side of things. It doesn't have reporting though.

rout39574 4y ago

I've been looking for something like this; I've described it as "Paradox for DOS for sqlite". Simple TUI for CRUD operations with foreign key enforcement. Libreoffice as a toolkit, so it becomes "Paradox for windows for sqlite" would be OK.. :P Not a webapp. Something you can run inside a git checkout, and get an essentially equivalent UX in any terminal. I'd expected that Python would be rife with such projects, but it seems that "Just build a webapp with my favorite toolkit" is a solution that satisfies most of the itches.

saulpw 4y ago
Maybe VisiData is up your alley? It doesn't do foreign key enforcement (yet) and is more for exploring than modifying, but with the new vdsql functionality I could see it being some part of what you want. Disclaimer: am author.

bachmeier 4y ago

Related: You can use the Fossil bug tracker as a generic SQLite table. You can modify the interface for any type of input. While you can edit the schema if you want to use it as a traditional SQL database, I've found it easiest to treat one of the text columns as JSON and do queries on that. The advantages: You get a web interface for free (using CGI) if you want one, it's distributed and version controls your database automatically. You can view reports graphically, at the command line, or from any other app that read an SQLite database. And in terms of stability, since Fossil is developed by the SQLite project, you can be confident that it'll be maintained for the rest of your life. https://fossil-scm.org/home/doc/trunk/www/index.wiki https://fossil-scm.org/home/doc/trunk/www/index.wiki

ComputerGuru 4y ago

Some years back we considered developing some sort of spreadsheet-esque frontend to a database (SQLite primarily, but also MySQL and Postgres) that would let you "live manipulate" either tables directly or, much more conveniently, "views" generated via queries or from virtual tables (with changes propagating backwards to the original source). The problem was that the userbase for such a tool is almost non-existent. People are either using Excel (or even MS Access!) and not looking to use anything else, or else they're more technical and can get by with a combination of manual queries plus the visual table mode in a tool like DB Browser for SQLite and its ilk.

LeoPanthera 4y ago

It's weird that in the 80s and 90s, "database" was a standard and integral part of pretty much every office suite from every software company. Other than Access, that seemed to change overnight, and now it barely is a thing you can do with your computer at all, and even Access is aimed exclusively at enterprise usage. I wonder why. I often think that a desktop GUI database app would be very useful, but with the possible exception of the aforementioned LibreOffice Base, this barely exists at all.

kevin_thibedeau 4y ago
Spreadsheets became more capable (larger screens, better formatting options) so now the target audience for a small scale database is just using that instead.
jojobas 4y ago
Main use cases for Access and the like seemed to be roll-your-own mini accounting/inventory control applications. Good riddance.
eternityforest 4y ago
I can't think of any real uses for it, unless they can make it sync between devices, in a way that is better than just using a cloud DB. The idea of data that lives on one local machine is so completely alien to the modern view of software(At least to me) that I'd have to actively spend time looking for use cases. A database that was as easy to use as SyncThing would be revolutionary though. So many spreadsheets could be replaced.

janvdberg 4y ago

I know this is not about MS Access perse, but I still feel there is a void concerning decent MS Access FOSS alternatives. MS Access hit a certain sweetspot between functionality and ease of use that I haven't seen anywhere since. Retool or Airtable are always named as viable alternatives. But they're not imho. I have built many MS Access applications and they always were surprisingly resilient. I believe even to this day there is a MS Access database GUI application running somewhere I built 15 years ago. Standalone, not connected to the web or anything. Just plowing away, just a single self contained file (mind the Office dlls etc.), backup and recovery is as easy as copying the complete file over to anywhere else and you're good. I think I have tried every alternative out there, but none hit that sweetspot (they always lack something).

bb88 4y ago
It's sometimes surprising that the simpler the solution the more effective. Cloud based can be very convoluted as a solution as it has to solve all the corner cases. And then you have to worry if the cloud provider will drop your data or let you export it easily for backup -- which they may or may not let you do easily.
sseagull 4y ago
I worked at a large non-tech (well, kinda tech) company as a chemist. They used an Access DB as a Lab Information Management System. Basically, a way to log samples in, assign work, report results, bill hours, etc. It worked pretty well! I believe it was home grown, but eventually expertise in it left and they were looking to move onto something else, which IMO was probably a mistake - it may have been easier to just fix the bugs and not try a difficult migration.

tracker1 4y ago

I'm actually half surprised there isn't an office database app that is simply sqlite3 file/tables that uses a couple special tables to store the forms layout/logic with a relatively simple player application. Funny how useful MS Access is after all these years, despite a few competing options over the years, with none really taking hold and no really good open-source alternatives.

chunkyks 4y ago
That's what kexi was supposed to be. https://kexi-project.org/ https://kexi-project.org/ I tracked it for a long time, just was never able to make it fit my needs