Packaging database into application seamlessly for users

I want to create a desktop application that uses a relational database (such as postgres - let's say my best case scenario is to use postgres in this application).

I want users to be unaware of the database. Currently, I had to install postgres into my local computer and have my application communicate with that.

I am using Go.

How can I avoid this?

You're looking for an embedded database.

This isn't an ideal job for PostgreSQL, but you can use it that way with a bit of care.

Please don't bundle the installer and run it unattended. Users who later go to install PostgreSQL will be very confused when they see it's already on their computer but they don't know why, who installed it, or what the password is.

Instead initdb a new datadir inside your app's %APPDATA% or (for multiuser shared) in %PROGRAMDATA%. Set a custom port (don't use the default 5432). Create a new service with pg_ctl register, running as NETWORKSERVICE, or just start/stop on demand with pg_ctl. That way you won't get in the way of any existing PostgreSQL installs or new ones and have a private PostgreSQL just for your app.

Please offer users the option of instead supplying a connection string for an existing PostgreSQL though. It's a pain if apps insist on using their own embedded copy when you don't want them to.

Often it's better to look at using SQLite, H2, Derby, Firebird, or one of the other embedded DBs, though.

Short version, you really can't, your best bet is to use SQLite or similar.

Long version, well, if you really really want to, you can create multiple unattended installers for your database that targets each platform you want, embed it into your application and install it on the first run.

Now that is just ugly and most users (myself included) would outright never use it.

You can always mention that your software depends on X and Y and provide information about how to manually install the dependencies.

This email thread shine some light into proper ways to do it in ancient windows. Most likely there are similar posts in which they go more into detail.
https://www.postgresql.org/message-id/4D2FFF07.6060409@aimproductions.be

Hi,



On 14/01/2011 7:35, Craig Ringer wrote:
> On 01/14/2011 06:26 AM, Jensen Somers wrote:
>
>> I know that some applications (Poker Tracker 3 is the first one that
>> comes to my mind)
>
> It's not a good example, either, as demonstrated by the number of 
> questions that pop up about it on this list, and the incredibly 
> ancient versions of Pg that they bundle.
>
>> install PostgreSQL during their installation process
>> too and setup the initial database. I sort of figured out how to do
>> this, but I don't know how to deal with an already existing installation
>> during setup.
>
> I take it you're talking about doing a silent install using the 
> postgresql exe installer, by invoking it as part of your own app's 
> installer?
>
> Personally, that's not how I'd do it if I were bundling Pg in my 
> (Windows) app, because as you mentioned it may interfere with any 
> existing or future Pg install the user wants to do manually. It'll 
> also show up separately in add/remove programs, which I think is 
> undesirable when it's just being installed as a component of your app.
>
> If my app required Pg, I'd probably bundle the Pg installation tree in 
> my installer and copy it into my program's install directory. I'd then 
> create a non-login user account named after my application (NOT 
> "postgres"), set up the service (again named for my application), and 
> invoke initdb to create the database under that service account. I'd 
> generate a postgresql.conf with a semi-random fairly high port number 
> that wasn't already in use on the target machine, to avoid conflicting 
> with the commonly used postgresql port. All this can be done using the 
> scripting languages provided by most installers, or via simple Windows 
> command line tools like "net.exe" and friends. If your installer is 
> particularly limited, you can always write and bundle a simple helper 
> program for it to invoke to do the work. My uninstaller would prompt 
> the user to ask if they wanted to remove data as well as the program; 
> if they said yes I'd remove the datadir and the user account I'd 
> created during installation.
>
> This way, your instance of PostgreSQL is private to your app and 
> doesn't conflict with anything the user might want to do. You can 
> upgrade it when you upgrade your app, provide backup facilities for it 
> in your app, etc etc without the user having to care what's behind the 
> scenes.
>
>
> If that wasn't viable, the only other option  I'd consider would be 
> providing a postgresql installer and asking the user to install it if 
> they didn't already have it installed. I'd then prompt for the 
> database host, port, username, password and database name to connect 
> to, and would just use what was provided to me. This is almost 
> certainly how it should be done on UNIX/Linux platforms.
>
> -- 
> Craig Ringer



Bundling it as part of my application is even better. I didn't knew if 
that would be possible, but it would solve some of the issues. Mainly 
data protection. The data that needs to be stored should not be altered 
by users. If they have access to the database via a root password, which 
would be the case when using the installer or an existing server they 
can manipulate the data. Implementing your suggested solution would 
prevent all that, which makes it a perfect solution.



Thanks, I know what I'll be doing today!



 - Jensen