3

I am new to software development in general. My skills are probably best described as intermediate. I have done a lot of programming, but I have not mastered any particular language. I have much studying to do to learn all the skills necessary for this.

I want to develop a desktop accounting application like PeachTree or QuickBooks. All data is hosted on the local machine and there is no need for a web server. At some point, I may want to be able to host a local server on a network and access the app through clients on other computers. Also, there is no need to have multiple users access the same database at the same time. Both of these conditions may change in the future but are not necessary for me to address at this time (I don't believe).

Years ago, I built an accounting application using Microsoft Access (not sure if that would be considered an application) with a split database design. The entire database is contained within two files: mydb and mydb_be (front and back end). I have not used those other accounting apps in many years, but as I recall, the data was stored in either a single file or a single directory, but certainly NOT in a central repository nested deep within the database program's default file structure and data directory like it is with Postgres (and probably many/all of the others) with names like '16254' instead of 'MyBiz'.

In the case of my MS Access app, I can move, copy and delete mydb using the typical OS commands like cut/paste/delete and drag and drop. There is nothing to export or import. Again, as I recall, it was something similar with the commercial applications.

I may have 15 different databases, each for a different entity. I may want to change file structures for my "accounting" directory or copy to a usb drive and move it to another computer, open it there, or maybe back up to an external drive. The directory for each entity may contain files or folders (e.g. a directory for taxes) other than the database files and they all need to be kept together. I cannot expect my target users (maybe 10+ years old) to perform such database management tasks. Among other things, the app is designed to teach children how to do accounting, not database management.

So, the question is, is there a way to store individual Postgres databases in different locations in a packaged way where all the necessary data for an individual database is stored in a single file (like MS Access) or in a single directory, such that I can move the files around and open them from my application? In other words, clicking open file would allow me to navigate to ~/myaccounting/entity1/entity1db.file, or /entity2/entity2.file, or perhaps a directory instead of a file. Maybe my file extension would be 'kac' so I would double click on a file with the .kac extension to launch the app.

I plan to use PostgreSQL and Python to develop an interactive console app to start. Once I have proven the concept, I will develop the GUI.

Clearly, there are many concepts I haven't yet learned. I feel like I need to understand this before I get to deep into my application.

EDIT And if not, what else should I look into (thanks Adrian Klaver)? What keywords should I search or what concepts should I study?

Thanks for the help.

Improve this question
2
  • 4
    No. If you want that look at Sqlite3. Even then you could not launch the app from clicking on the database file. It would have to be click on app file load database file. Commented Mar 27, 2021 at 17:11
  • Thanks for that. I am not stuck on any one component other than Python. Commented Mar 27, 2021 at 17:26

1 Answer 1

Reset to default
5

Is it possible to store individual PostgreSQL databases in individual files

No. Postgres is designed to be a powerful reliable centralized database server for the enterprise. Postgres was never intended to be bundled within a desktop app.

Embedded database

What you are looking for is called an embedded database.

H2 Database Engine is one such product. H2 is a relational database, driven by SQL. Being compatible with Postgres is one of its goals. Built in pure Java, so it can run on many kinds of host operating systems. H2 can be run in embedded mode for access only by your app, or in server mode to accept connections from various apps simultaneously.

A comment mentioned SQLite. While that product is an embedded database, be aware that it is indeed “lite”. It’s creator intended the product to be a step up from simple files, not as a competitor to powerful database engines… to quote: “SQLite is not a replacement for PostgreSQL. SQLite is a replacement for fopen()” (source). While many people have done many projects successfully with SQLite, asking SQLite to support a full accounting package may be asking too much. Research carefully if you are considering this product.

Cloud

Another avenue is to use the Cloud. Many companies have started offering database as a service. Often called a “managed database” as you do not have full super user access for obvious security reasons, and because the service company handles back-ups, failover, software updates, and so on. Vendors include Digital Ocean, Heroku, Amazon AWS, Microsoft Azure, and more.

Postgres and MySQL are common offerings, with some offering Microsoft SQL Server or Oracle.

If you can reasonably expect users to have an internet connection while using your product, this might be viable. But I hesitate to recommend this route. While the internet connection need not have large throughput for your purpose, the internet connection does need to be reliable. But “reliable” and “internet connection” do not often go hand-in-hand.

In using a DBaaS, you would have to code your database to be very robust in the face of interrupted network access. Of course your should always do that. But in practice most of us get lax in that area, especially if we can count on very reliable connections. Such as when the database server is local to the same machine, or when the database server is connected to an app server nearby in a server room.

Another issue is that having different customers share a single database server raises problems of multitenancy.

Further discussion is really off-topic for Stack Overflow. I suggest two sister sites, Database Administrators Stack Exchange, and Software Recommendations Stack Exchange.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

In defense of Sqlite , it only omits a hadful of SQL features Omissions. Also the lead developer models it on Postgres PgCon talk The part that does bite people is item '3) Flexible Typing' from here Quirks.
Thanks for all of that information as it gives me much to research (I didn't even know what keywords to search for before). I have no interest in cloud computing, especially for my project. For me, it ranks right up there with software subscriptions LOL. I will bookmark those other two stack exchanges and check them out. While I can always use more information, you answered my question.
After spending the day researching, I am not happy with the options for embedded databases. There seem to be few options, limited capabilities (SQLite), limited development (H2) and licensing issues (MySQL). I could make something work I think (like Firebird) but I am not getting warm and fuzzy feeling. I am going to have to rethink my model as I would really like to use Postgres. Between Python and Postgres, there isn't much to worry about regarding licensing and they are both mature, highly capable packages. This has been very educational, thanks again for the input.
@KingWm I do not understand what you meant by “limited development (H2)”. H2 is very actively developed, as you can see from its release history. The project is open-source. Its principal developer, Thomas Mueller, has had a long career in database engines. And H2 seems to be quite popular.
@BasilBourque Frankly, I was not experienced enough to have made that comment back then and I have learned much since. I am using Postgres, C++ and Qt for my application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.