schema-generation.md

Schema Generation

Creating the DDL script and keeping it up-to-date with the code is a time consuming task. There are a number of different ways to do this, and every every developer has their own preferences. Companies tend to work according to their own specific standards.

Castle ActiveRecord exposes a NHibernate feature to create and drop schemas. The relevant methods are:

• CreateSchema and DropSchema will create and drop the schema against the database
• GenerateCreationScripts and GenerateDropScripts generate a file with the scripts
• CreateSchemaFromFile executes a DDL script against the database

All of the above methods are exposed by the ActiveRecordStarter class and can only be used after you have invoked one of the Initialize method overloads.

Allowing ActiveRecord generate the schema

Allowing ActiveRecord generate and execute the creation schema is desirable in certain scenarios. For example, to prototype an application or to build a test database.

If you want to use this approach you will need to specify some additional properties on your active record attributes. These additional properties, which include NotNull and Length, are used to guide the schema generation. For more information see Attributes article.

⚠️ Warning: Be aware that the schema generation is not bullet proof. For some complex models the generated script might be buggy. For 95% of the cases it is acceptable.

Consider the following ActiveRecord type definition:

using Castle.ActiveRecord;

[ActiveRecord("Blogs")]
public class Blog : ActiveRecordBase
{
    private int id;
    private String name;
    private String author;

    [PrimaryKey(PrimaryKeyType.Native, "blog_id")]
    private int Id
    {
        get { return id; }
        set { id = value; }
    }

    [Property("blog_name", NotNull=true, Length=25)]
    public String Name
    {
        get { return name; }
        set { name = value; }
    }

    [Property("blog_author", NotNull=true, Length=50)]
    public String Author
    {
        get { return author; }
        set { author = value; }
    }
}

The following code will initialize the framework and generate the schema:

ActiveRecordStarter.Initialize(new XmlConfigurationSource("appconfig.xml"), typeof(Blog));
ActiveRecordStarter.CreateSchema();

ℹ️ The CreateSchema method will drop any existing tables with the same name before it creates new tables. Be very cautious about performing this operation on a production database.

Executing an external script file

This may be a better alternative, especially if you work on team that has someone dedicated to keep the database schema up to date. The SQL files can be part of the project and can be executed during initialization.

ActiveRecordStarter.Initialize(new XmlConfigurationSource("appconfig.xml"), typeof(Blog) );
ActiveRecordStarter.CreateSchemaFromFile("myscript1.sql");
ActiveRecordStarter.CreateSchemaFromFile("myscript2.sql");