I want to have an open source web application all on one VPS. I will use Linux, Apache, and PHP. I want the database to store locations (addresses in the USA). There will be a program that will search the addresses in the database. I do not want to spend money on licenses. The data will be updated weekly. The schemas and tables should not change that frequently at all.

Strong data types are optional.

I am going to set it up myself.

What open source database product should I use for my project?

  • I would always prefer Postgres over MySQL. For reliability and modern SQL features. It also has more momentum in its feature development Feb 28 '17 at 16:11
  • How long is a piece of string? Can you define "regularly", in " The database will be updated regularly" - hundreds of times per second? Or every few days? Can you tell us how much data will eventually be stored? Meagbytes ? Petabytes? You could go with Sqlite, but since most LAMP stacks got with MySQl, you may as well use MySql. It plays well with PHP, (be sure to use PDO - Google for lot of confirmation ). I think that a GIS database would be overkill for you, unless you want to do any mapping with those addresses, and maybe not even then. Can you give us more idea of what you are hoping to a Mar 1 '17 at 12:28
  • 1
    Moved prior research by asker as a comment: "My thoughts I know Postgres is ACID compliant. But the LAMP stack is common, so MySQL would have plenty of books and online documentation. I see MySQL as a practical solution. I see Postgres as a more secure choice." With this, @Alejandro's worry is solved.
    – Nicolas Raoul
    Mar 2 '17 at 5:07

You should check MariaDB, it's a free, open source, drop in replacement for MySQL made by the original founders of MySQL. Everything designed to run on MySQL should run on Maria without need to change code. Also the Cluster version is free.

I've used it on LAMP setup without problems.

  • I will secodn that & upvote it. I have used XAMPP as a Windows LAMP (WAMP) for 10ro 15 years, and only recently noticed that they had quietly changed to MAriaDB and I had not even noticed. Mar 1 '17 at 12:31

You may want to check out sqlite: https://sqlite.org/.

Its very lightweight with its limitations (not many strong data types, some more specialized SQL syntax is not supported), but its very easy to install (basically enable the driver of sqlite in php) and to manage (the whole database is stored in a single file which you can copy wherever you want).

To play a little with sqlite I'd recommend to have a look at http://sqlitebrowser.org/

To use it in php:

$dbh = new PDO('sqlite:/my-path/data.db');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.