Which database should I use for home project use?

Question

I've looked through all the "related questions" with two or three different titles and didn't see anything, so here goes.

I've used SQL Server and Oracle in the recent past at work. I want to futz around at home, mostly with Java, perhaps with Ruby on Rails or Python. I'm running Windows 7. I usually make web applications, might like to host this remotely eventually, and beyond that, have no specific requirements upfront.

In this case, I'm not interested in learning the ins and outs of SQL further; I'll probably use ORM to abstract this out, so that it's automatically dialect agnostic. I'm also going to work with a few nonrelational data stores, but I have specific ones in mind there.

What databases should I consider?

1. I installed SQL Server Express, and it seems fairly limited to just working with .NET, plus the SQL Server Express install doesn't even seem to be integrated properly with the Visual Studio C# Express edition that it came with. (I can't create a database automatically, for example.)
2. I'm considering Oracle XE, except if I ever wanted to make any project code of mine publicly available, finding hosting for Oracle at a reasonable price seems a stumbling block.
3. SQLite seems the way to go for fast and quick, if I don't need much concurrency.
4. MySQL seems to be the way to go for something that's easily hosted, and may be the overall winner. Here's to hoping the GUI isn't still awful.

Answer 1

If you're already comfortable with Oracle, then Oracle Express Edition is vastly easier to install than the full-blown monstrosity.

Answer 2

I would go with MySQL for this. As far as GUI is concerned, I used to use EMS SQL Manager for MySQL Freeware quite a lot and its ok... http://sqlmanager.net/products/mysql/manager

Answer 3

If you know SQL Server from work, why not use the absolutely free SQL Server Express??

There's also a bunch of tools around it, i.e. SQL Server Management Studio Express and more. It even supports advanced features like fulltext search and more.

Answer 4

Why does Oracle at home makes you shudder. It works fine on a fairly small laptop and there is a free version that you can use for non-commercial use (with a limit on database size, just like sql server express). That said, mysql on windows would probably use even less resources. As for the UI they are both equally bad really. But there are third-pary tools that can be used.

Answer 5

This depends on what the application you are developing requires. Is it a web app that will run on another machine, or is it a desktop app that would be best served with an embedded database?

That said, you can't go wrong with MySql, and it will fit most of your needs.

Answer 6

I've enjoyed working with SQLite3-- the Python interface is very simple and the whole database is just one file!

Answer 7

"MySQL had a very mediocre GUI last time I worked with it"

Is that a limitation for choosing a database? There are quite a few good GUI (If you are GUI guy)

You can either use PhpMyAdmin or MySQL query browser. I would say MySQL is better, but if your application doesn't involve much of write operations (since its a home project) you can consider using SQLite.

Answer 8

If you need a database for practise web apps for example, I'm a big fan of sqlite, it works with PHP etc., it's an embedded database so there is no database admin to speak of. The SQL dialect is slightly different from the others though, but not enough to be a show stopper.

It's also quite fast and 'public domain' licence, so you can use it in commercial apps too.

Answer 9

PostgreSQL is an excellent open source database that can be used with Java, Python, Ruby, .NET, and more.

You can easily find lots of comparisons of PostgreSQL versus MySQL. I've used and liked both.

Answer 10

If you use SQL Server and oracle at work then I'd go for one of those. In fact, why not go for both? Installing the express edition of both (assuming they'll co-exist) and learn the pros and cons of each one sounds like a worthwhile approach.

You could even try developing for database independence as well as using the syntax and specific features of each one on their own. Sounds very educational to me.

Answer 11

Go for MySQL. So many people are using it and its getting better all the time.

Answer 12

I still think Oracle might be useful. Start with XE on your desktop and migrate to web easily, or start with the publicly hosted Apex. The APEX GUI is easy to learn, intuitive and robust. You can create web pages as easily as you can create forms in Access. In my opinion, it can be used freely for small projects that could potentially grow to very large projects -- thus the heavy infrastructure. But as I mentioned, if you use their hosted app, you have no resource issues on your own machine.

Answer 13

Go with Sqlite then, maybe you'd like to distribute the stuff easily to your friends : )

Answer 14

I'd recommend setting up a WAMP server - this simulates a LAMP environment on windows and comes with your own MySql/PhpMyAdmin interface, making MySql easy to work with.

Answer 15

I'd go MySQL for the community and price if nothing else.

"MySQL seems to be the way to go for easily hosted, and may be the overall winner. Here's to hoping the GUI isn't still awful."

Err, what GUI? You can use one of many 3rd party app's to do simple CRUD stuff... phpMyAdmin, Navicat, etc.

Answer 16

SQLite is a very good option. It is just a single file, and fast. No configuration, transactions are atomic, supports terabyte-sized databases. There are language-bindings for Ada, C#, Lisp, Fortran, Java, Lua, PHP, Perl, Python, Ruby, Tcl, etc...

It has good GUI admin options:

1. SQLite Admin for Windows
2. SQLite Browser for Linux, Mac, and Windows screenshots
3. Even a Firefox plugin for viewing/editing data
4. Many more

It is used in industry:

1. It's the base database the Firefox uses internally for bookmarks and history. It can also be used by plugins for them to store internal data. link
2. The iPhone also uses it internally to store data, and can be used by apps themselves for their own data storage. Here is a series of tutorials on using it within your own apps. link
3. Adobe uses it as the file format for Lightroom. Apple uses it for Mail and Aperture. Skype uses it in their clients for Mac and Windows. Lastly, it seems to be an integral part of Symbian, Nokia's phone OS. users page

Answer 17

If your interest lay in developing some cool applications at home, I'd not worry too much about which database to go with. Personally I'd keep it as simple as possible - which to my mind is SQLite3 (as already recommended by others).

On the other hand, if you have an interest in persitence and how data is stored, use the opportunity to explore approaches outside the comfort zone of an RDBMS. Java has a neat open source graph database available for it, and numerous non-relational alternatives are out there to install and muck about with.

Answer 18

Since your intentions are a little vague (will you want to develop on this and then run in a hosted environment?) - I'd consider portability and general functionality.

Given that I'd recommend postgresql. It's sql is much more ansi-compliant than mysql - so later using oracle, sql server, sqlite, etc should be very straight-forward. And sure, you could (and should if practical) use an ORM - but you're still going to deal with the database syntax for reporting or very complex sql.

SQLite is also very ansi-compatible. But it is missing some critical features and its typelessness can affect your portability. Otherwise, great database.

Oracle Express can be easy to work with - but as you point out, it is typically very expensive to host in production. SQL Server is also great to work with - but is also very expensive in production. It also has a windows-way of doing things that I've found more awkward for process automation than what you'd typically do with unix-oriented databases. Note, you could also use DB2 Express - it's free for 2 CPUs, 4 GB memory and as much data as you want. All of these are great for commercial settings, but you won't find any on the menu at the cheap hosting sites.

MySQL is of course another option. It's a free market leader. But the portability issues are a pain and the bugginess and quiet errors cause additional work for development - if data quality is important. Then there's the complexity of licensing and uncertainty of its future. In 2002 I'd say MySQL definitely, but in 2009 I'd say Postgresql instead most of the time.

Answer 19

Given that you say you'll mostly be using Java, I'd also consider HSQLDB as an alternative to SQLite.

Answer 20

You may try Firebird too.

It's easy to deploy and embedd, have a very small footprint (less then 5 Mb in some cases) and really low memory consumption.

And, if it really wasn't it all, it have zero administration. It's a really interesting alternative.

Answer 21

MySQL and for the GUI use DBVis, it's free and compatible with a ton of DB's like MSSQL, Oracle, etc...

Answer 22

I use 2 different open-source based database engines at home:

1. PostgreSQL
2. Sqlite3 (built-in drivers starting with python 2.5)

As you are coming from a background of Oracle and SQL Server, I would definitely recommend going the PostgreSQL route, it runs native on Windows (as a service, much like MSSQL/Oracle) as well as on *NIX systems. It also has a pretty decent cross-platform GUI called pgAdmin. Also, starting with version 8.3 of PostgreSQL, it is pretty much on-par performance wise with MySQL, it isn't like the older 7.x versions which were s-l-o-w.

The biggest reason why I recommend PostgreSQL to people who have more Oracle/MSSQL experience (as opposed to MySQL) is that the SQL Syntaxes and general feel of the database engine match pretty closely. In fact PostgreSQL acts a lot like Oracle in how it handles database locking and simultaneous reading.

Answer 23

it depends on your requirements.

first try: if you need a fast and embedded database, and have not so much data (no clustering, no distribution) i would take H2. this database is fast, is usable with hibernate/orm and if you like to use special features of this database (is easy to extend by a java-developer with functions ...)

second try: you have not so much relational data, want to use graphs of objects of different types. you want fast and completly embedable solution, than i would use db4o or another db in object-oriented-inmemory style. (no orm)

third try: if you want to store much more data (f.e. million of entries in a table) but without clustering, replication, distribution, than i would use mysql. is fast and nice to use. i've found, that postgres is in my expirience is slower as mysql. mysql is perfectly to use in java directly and in hibernate and co. also.

fourth try: if you have much much much more data ... oh sorry, i've forgot you've asked about a home solution :)

in the above recomendation i've used only free or open-source products for non-commercial usage.

about a graphical tool: you can find many of them for different databases, but also database-independent-solution...

have fun!