How to manage hobby project database?

Question

What would be the best way to manage the databases of hobby projects?

Requirements:

1. It should be as easy as possible. If it is hard, the project will stall.
2. It should provide easy way to get testing copy to avoid doing development in production.
3. It should provide reliable way to upgrade the production database schema to new version.
4. It should provide a way to make a new database. Note that as there is usually the only one production environment, this won't usually get used. This is more to have the possibility in case of a problem.
5. It would be nice to be able to use the same thing for different databases. For example PostgreSQL for projects needing a real database and SQLite for projects where the database need to be copied between computers.
6. It needs to be free and work in Linux.

I'm mostly doing my hobby projects with Python.

Answer 1

SQLAlchemy using sqlalchemy-migrate. They seem to be made just for what you want, since they fit exactly your description. Feel free to ask for details if you need.

Answer 2

A real quick and dirty solution is to keep a database backup file and a .sql file that contains changes to the schema made since the backup. As you make changes to the schema (or static data) you append SQL statements to the .sql file. Over time, you can update the baseline image so long as you clear out all lines of the sql file that pre-date the backup that you're taking. So long as you're using source control on your project this should be quite easy.

Creating a new testing database is a case of restoring the backup and running the SQL file on it.

Upgrading production involves running all the lines of the SQL file that have been added since it was last upgraded.

Answer 3

Gerald could be one helpful tool. Cons: It is alpha code and doesn't support SQLite.

Answer 4

This is a really good question.

If you are comfortable writing your own table create statements, I'd suggest keeping a SQL script that will create your database from scratch, and a second SQL script that will populate a minimal working dataset (including data for any reference tables that are required). Then a third script that creates test accounts that can be used during development and by unit tests, but should not be included in production.

Keep these SQL scripts in your version control system. They're source code.

Make a wrapper script that will run all three of them to create a clean database to work on. Use it regularly during the dev process. Again, this is source code.

If you find yourself altering existing schema a lot, try keeping a log of those scripts, with a separate directory for each release. Number those scripts (01-add_t-shirt_size.sql, 02-create_phone_number_table.sql) so it's obvious what order to run them in. This is source code and needs to be included with released versions of your code to facilitate upgrades.

Odds are, with a single developer you won't need anything super elaborate. Just keeping working copies of scripts that will cleanly create everything from scratch and the update scripts will do the job. It's easy to do (no big new tools to learn) and easy to explain to other developers.

I'm a big fan of making things very easy. Requiring special apps makes it harder to do things the right way.

Answer 5

Try Oracle 10g XE for linux. It is free. It comes with a browser based management tool that is quite intuitive.