How do I develop database-utilizing application in an agile/test-driven-development way?

Question

I want to add databases (traditional client/server RDBMS's like Mysql/Postgresql as opposed to NoSQL, or embedded databases) to my toolbox as a developer. I've been using SQLite for simpler projects with only 1 client, but now I want to do more complicated things (ie, db-backed web development). I usually like following agile and/or test-driven-development principles. I generally code in Perl or Python. Questions:

1. How do I test my code such that each run of the test suite starts with a 'pristine' state? Do I run a separate instance of the database server every test? Do I use a temporary database?
2. How do I design my tables/schema so that it is flexible with respect to changing requirements?
3. Do I start with an ORM for my language? Or do I stick to manually coding SQL? One thing I don't find appealing is having to change more than one thing (say, the CREATE TABLE statement and associated crud statements) for one change, b/c that's error prone. On the other hand, I expect ORM's to be a low slower and harder to debug than raw SQL.
4. What is the general strategy for migrating data between one version of the program and a newer one? Do I carefully write ALTER TABLE statements between each version, or do I dump the data and import fresh in the new version?

Answer 1

Some answers:

1. You should have an initialization script that rebuilds the database in the "pristine" state for the integration tests. This will also be useful when you go-live to make sure the initial database is initialized properly. For unit testing you may want to mock the data with in-memory mocks of the database for faster test runs and reduced dependency. There are different strategies for mocking depending on the ORM or data access layer that is chosen.
2. Try to model your DB schema to as closely resemble your application domain model as much as possible when you design the entities, attributes, and relationships. Some ORM's can help you do this automatically by generating the database from the application's classes.
3. ORM's can be useful to reduce boiler plate code and can help address the other questions raised here. They are usually slower than raw SQL methods but the reduced development time can offset these issues.
4. Write (or generate if the ORM can do it) the SQL statements to update to the next version and also statements to roll back to the previous version after the update in case it is required. Also make sure these (and any other database code) are committed to your source control repository like any other application code.

Answer 2

The features and questions that have been asked, can be found in a good ORM tooling.

As an example, in Entity Framework ORM we are able to have an approach of modeling your database schema in your code through Models. And incrementally migrate your changes to the actual database of your application. - This approach is code first approach in EF starting from 4.1 version.

Although previously EF supported only MS SQL server, nowadays it supports all other major databases as well. Thus, i would look at ORM frameworks that meets your needs.

As an alternative you may also look at NHibernate : Code First approach with fluent NHibernate