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.
FarCry (yes, it’s a CMS, but it’s also a framework) actually does this a really nice way, pretty similarly to the way you’re talking about (although the initial setup is done by an installer to set up the frameworks “required” tables). It has a COAPI management interface which introspects your CFC’s, and the “fourq” database abstraction layer (supports MSSQL, mySQL, Oracle and PostgreSQL I think) can go out and create tables (for deploying new types), create/delete fields (to cater for changes within the type) and change datatypes as well. It makes database changes super easy because you don’t touch the database at all (except for handling indexing, though I think with FarCry 4 it takes care of many of the indexes now too, which is cool). The only thing is I don’t think fourq can really be separated from FarCry, so it’s not a solution that you could just take anywhere (though I may be wrong). But I really do like the way it works
Hi,
I use the excellent DataManager project by Steve Bryant for this. It tackles many of the issues you mention, including the multiple db types, and ships with a very cool sim db which makes early prototyping almost shamefully easy.
stigg
+1 for DataMgr – you should really check it out.
You might want to check out Ant and DBUnit. You can use DBUnit to populate a database with test data from an xml file. I believe DBUnit works with just about any DB. While developing you might want to consider a API like Transfer or Reactor which allow you map objects to a database. Both Transfer and Reactor allow to write code that is independent of the DB platform.
@Justin Carter
Hmm I’m really going to have to look more into FarCry. I planned to spend a night installing it but when that only took 10 minutes I did something else with the rest of the night.
@Stigg and @Peter
I’d seen that one on Riaforge but hadn’t looked into it. Impressive what I’ve seen so far. Looking forward to reading more about it.
@Kurt Wiersma
DBUnit looks exactly like what I was looking for at first thought. Not sure I see anything for creating tables in it though, but lots of docs to look over. Very mature tool by the look of it.