How Do You Create and Populate Your Database?

Getting a database setup and populated with some sample data is almost inevitably one of the first things to happen once development on a project begins. Even still I don’t know of any good way to set this up in ColdFusion.

Basically there needs to be a way of creating your database as code so that abstracts the SQL so it’ll work on multiple databases. Right now, for instance, I’m developing on MySQL but plan to move to MSSQL (to show off some CF8 goodness). I could create the database in either, or find some kind of a conversion script, but that doesn’t seem like a decent plan.

The idea of creating a database locally and then having to export it, then import into my production environment isn’t the kind of solution I’m looking for here. cfrails solved this problem by creating it’s own version of Ruby on Rails Migrations which you can read about in the getting started guide.

The sad thing is ColdFusion is really not the best language for scripting this kind of thing. Ideally you want something that can be repeated as many times as needed like the cfrails implementation, but you’d also want it to be able to be executed from inside Eclipse.

Some smart alec at my previous job created a great .NET program that searched through a set of folders for your database objects and executed all the SQL scripts for table creation, functions, sps and all that (creating them in order of their foreign keys mind you).

While this is a huge step up from executing them individually, or anything CF could do from Eclipse, it doesn’t address the multiple database issue. I’m wondering how others have solved this problem? I’d love to be able to build and rebuild my database right from eclipse and knowing the huge java userbase there’s probably already a good java solution for this.