Generating database tables from object definitions

Question

I know that there are a few (automatic) ways to create a data access layer to manipulate an existing database (LINQ to SQL, Hibernate, etc...). But I'm getting kind of tired (and I believe that there should be a better way of doing things) of stuff like:

1. Creating/altering tables in Visio
2. Using Visio's "Update Database" to create/alter the database
3. Importing the tables into a "LINQ to SQL classes" object
4. Changing the code accordingly
5. Compiling

What about a way to generate the database schema from the objects/entities definition? I can't seem to find good references for tools like this (and I would expect some kind of built-in support in at least some frameworks).

It would be perfect if I could just:

1. Change the object definition
2. Change the code that manipulates the object
3. Compile (the database changes are done auto-magically)

Answer 1

You may be looking for an Object Database.

Answer 2

I believe this is the problem that the Microsofy Entity Framework is trying to address. Whilst not specifically designed to "Compile (the database changes are done auto-magically)" it does address the issue of handling changes to the domain model without a huge dependance on the underlying data model.

Answer 3

Some of the really big dogs, such as ERwin Data Modeler, will go object to DB. You need to have the big bucks to afford the product though.

Answer 4

As Jason suggested, object db might be a good choice. Take a look at db4objects.

Answer 5

What you described is GORM. It is part of the Grails framework and is built to work with Hibernate (maybe JPA in the future). When I was first using Grails it seemed backwards. I was more comfortable with a Rails style workflow of making the tables and letting the framework generate scaffolding from the database schema. GORM persists your domain objects for you so you create and change the objects, it manages database create/update. This makes more sense now that I have gotten used to it. Sorry to tease you if you aren't looking for a new framework but it is on the roadmap for release 1.1 to make GORM available standalone.

Answer 6

I kept digging around some of the "major" frameworks and it seems that Django does exactly what I was talking about. Or so it seems from this screencast.

Does anyone have any remark to make about this? Does it work well?

Answer 7

When we built the first version of our own framework (Inon Datamanager) I had it read pre-existing SQL tables and autogenerate Java objects from them.

When my colleagues who came from a Smalltalkish background built the second version, they started from the objects and then autogenerated the tables.

Actually, they forgot about the SQL part altogether until I came back in and added it. But nowadays we just run a trigger on application startup which iterates over the object model, checks if the tables and all the right columns exist, and creates them if not. Very convenient.

This turned out to be a lot easier than you might expect - if your favourite tool doesn't support a similar process, you could probably write it in a couple of hours - assuming the relational to object mapping is relatively simple.

But the point is, it seems to depend on whether you're culturally an object person or a database person - you can regard either one as the authoritative source.

Answer 8

Yes, Django works well.

yes, it will generate your SQL tables from your data model definitions (written in python)

It won't always alter existing tables if you update your structure, you might have to run an ALTER table manually

Ruby on Rails has an even more advanced version of these features (Rails migrations), but I don't like the framework as much, I find ruby and rails pretty idiosyncratic

Answer 9

Check out DataObjects.Net - is is designed to support exactly this case. Code only, and nothing else. Its schema upgrade layer is probably the most featured one you can find, and it really fully abstracts schema upgrade SQL.

Check out product video - you'll notice nothing additional is made to sync the schema. Schema upgrade sample shows the intended usage of this feature.