title: NHibernate SchemaUpdate never updates my schema
body: <p>I'm working on a project that makes use of NHibernate and FluentNHibernate (I'm a newby with both technologies) and I need to update the schema of my SQLite production database.
  I read many posts that clearly state that this is possible with SchemaUpdate (provided that the update is not destructive like for example adding new nullable columns and not deleting them) but I never managed to achieve it.</p>
  
  <p>I need to do it with SQLite but I tested it also with Postgres 9.3 and SQL Server 2008 R2 always unsuccessfully.</p>
  
  <p>To make it simple I created the following simple program in order to create a simple table and populate it the first time I launch it. Before launching a second time I uncommented the three lines with "StringValue2". As a result I was expecting to extend my simple table with the new column "StringValue2" but the table never got updated.</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Data;
  using System.Data.SQLite;
  using NHibernate;
  using FluentNHibernate.Cfg.Db;
  using System.Diagnostics;
  using FluentNHibernate.Cfg;
  using NHibernate.Tool.hbm2ddl;
  using FluentNHibernate.Mapping;
  
  namespace SchemaUpdatePOC
  {
      class Program
      {
          static ISessionFactory sessionFactory = null;
  
          static void Main(string[] args)
          {
              try
              {
                  sessionFactory = CreateSessionFactory();
                  AddRow("aaa1", 10, DateTime.Now, true, "123");
                  AddRow("bbb1", 20, DateTime.Now.AddHours(1), false, "456");
                  AddRow("ccc1", 30, DateTime.Now, true, "789");
              }
              catch (Exception ex)
              {
                  throw;
              }
          }
  
          private static ISessionFactory CreateSessionFactory()
          {
              try
              {
                  // SQLite
                  string connString = "Data Source=TestDB.s3db;Version=3;";
                  IPersistenceConfigurer configurer = SQLiteConfiguration.Standard.ConnectionString(connString);
  
                  // Postgres
                  //string connString = "Server=localhost;database=TestDB;user id=postgres;password=postgres";
                  //IPersistenceConfigurer configurer = PostgreSQLConfiguration.Standard.ConnectionString(connString);
  
                  // SQL Server 2008
                  //string connString = @"data source=.\SQLEXPRESS;initial catalog=TestDB;Integrated Security=True;";
                  //IPersistenceConfigurer configurer = MsSqlConfiguration.MsSql2008.ConnectionString(connString);
  
                  return Fluently.Configure()
                                 .Database(configurer)
                                 .Mappings(m =&gt; m.FluentMappings.Add(typeof(TestTblMap))
                                 //.ExportTo(@"C:\delme\")
                                 )
                                 .ExposeConfiguration(BuildSchema)
                                 .BuildSessionFactory();
              }
              catch (Exception ex)
              {
                  throw;
              }
          }
  
          private static void BuildSchema(NHibernate.Cfg.Configuration config)
          {
              //new SchemaExport(config).Create(true, true);
              new SchemaUpdate(config).Execute(true, true);
          }
  
          public static void AddRow(string stringValue, int intValue, DateTime datetimeValue, bool boolValue, string stringValue2)
          {
              try
              {
                  using (var session = sessionFactory.OpenSession())
                  {
                      using (var transaction = session.BeginTransaction())
                      {
                          TestTbl item = new TestTbl();
                          item.BoolValue = boolValue;
                          item.IntValue = intValue;
                          item.StringValue = stringValue;
                          item.DateTimeValue = datetimeValue;
                          //item.StringValue2 = stringValue2; // UNCOMMENT THIS LINE THE SECOND TIME YOU LAUNCH THE APPLICATION
                          session.Save(item);
                          transaction.Commit();
                      }
                  }
              }
              catch (Exception ex)
              {
                  throw;
              }
          }
  
          public class TestTbl
          {
              public virtual int Id { get; protected set; }
              public virtual string StringValue { get; set; }
              //public virtual string StringValue2 { get; set; } // UNCOMMENT THIS LINE THE SECOND TIME YOU LAUNCH THE APPLICATION
              public virtual DateTime DateTimeValue { get; set; }
              public virtual int IntValue { get; set; }
              public virtual bool BoolValue { get; set; }
          }
  
          public class TestTblMap : ClassMap&lt;TestTbl&gt;
          {
              public TestTblMap()
              {
                  Table("\"TestTbl\"");
                  //Id(x =&gt; x.Id).CustomSqlType("Serial").Column("\"Id\"").Not.Nullable(); // use with postgres
                  Id(x =&gt; x.Id).Column("\"Id\"").Nullable();
                  Map(x =&gt; x.StringValue).Column("\"StringValue\"").Not.Nullable();
                  //Map(x =&gt; x.StringValue2).Column("\"StringValue2\"").Nullable(); // UNCOMMENT THIS LINE THE SECOND TIME YOU LAUNCH THE APPLICATION
                  Map(x =&gt; x.DateTimeValue).Column("\"DateTimeValue\"").Not.Nullable();
                  Map(x =&gt; x.IntValue).Column("\"IntValue\"");
                  Map(x =&gt; x.BoolValue).Column("\"BoolValue\"");
              }
          }
      }
  }
  </code></pre>
  
  <p>when I launched the application a second time (after uncommenting the three lines with "StringValue2") the script I got on the console was always the following:</p>
  
  <pre><code>create table "TestTbl" (
      "Id"  integer primary key autoincrement,
      "StringValue" TEXT not null,
      "StringValue2" TEXT,
      "DateTimeValue" DATETIME not null,
      "IntValue" INT,
      "BoolValue" BOOL
  )
  </code></pre>
  
  <p>From what I understood reading on the internet I was expecting an "alter table" instead of the "create table", but apart from this the new column was never created and the following insert statements failed because of the missing "StringValue2" column with the following messages:</p>
  
  <pre><code>could not insert: [SchemaUpdatePOC.Program+TestTbl][SQL: INSERT INTO "TestTbl" ("StringValue", "StringValue2", "DateTimeValue", "IntValue", "BoolValue") VALUES (?, ?, ?, ?, ?); select last_insert_rowid()]
  
  SQL logic error or missing database
  table TestTbl has no column named StringValue2
  </code></pre>
  
  <p>The NHibernate xml file I got uncommenting the line ".ExportTo(@"C:\delme\")" is:</p>
  
  <pre><code>&lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"&gt;
    &lt;class xmlns="urn:nhibernate-mapping-2.2" name="SchemaUpdatePOC.Program+TestTbl, SchemaUpdatePOC, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="&amp;quot;TestTbl&amp;quot;"&gt;
      &lt;id name="Id" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"&gt;
        &lt;column name="&amp;quot;Id&amp;quot;" not-null="false" /&gt;
        &lt;generator class="identity" /&gt;
      &lt;/id&gt;
      &lt;property name="StringValue" type="System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"&gt;
        &lt;column name="&amp;quot;StringValue&amp;quot;" not-null="true" /&gt;
      &lt;/property&gt;
      &lt;property name="StringValue2" type="System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"&gt;
        &lt;column name="&amp;quot;StringValue2&amp;quot;" not-null="false" /&gt;
      &lt;/property&gt;
      &lt;property name="DateTimeValue" type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"&gt;
        &lt;column name="&amp;quot;DateTimeValue&amp;quot;" not-null="true" /&gt;
      &lt;/property&gt;
      &lt;property name="IntValue" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"&gt;
        &lt;column name="&amp;quot;IntValue&amp;quot;" /&gt;
      &lt;/property&gt;
      &lt;property name="BoolValue" type="System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"&gt;
        &lt;column name="&amp;quot;BoolValue&amp;quot;" /&gt;
      &lt;/property&gt;
    &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>What am I doing wrong? 
  It's now a whole week that I'm struggling with this very basic thing and I'm driving mad! :(
  Is UpdateSchema supported on the databases I tried?</p>
  
  <p>The version of the libraries I'm using is:</p>
  
  <pre><code>NHibernate.dll 3.3.3.4000
  FluentNHibernate.dll 1.3.0.733
  System.Data.SQLite.dll 1.0.93.0 (when using SQLite)
  npgsql.dll 2.0.13.91 (when using Postgres)
  </code></pre>
  
  <p>Any help is really appreciated!</p>