title: SQLite "add column if not exists"
body: <p>I need to check if a column already exists in a table and add it if not. It is <strong>NOT</strong> possible to check this in the application, because the SQL scripts/files are called successively because of migration. If we add a new column to our database, the column should automatically get added when the app (with a new version) is started.</p>
  
  <p>I have two <code>SQL Statements</code>:</p>
  
  <h3>Add Column <code>skiptime</code> into <code>recipes</code></h3>
  
  <pre><code>ALTER TABLE recipes ADD COLUMN skiptime INTEGER NOT NULL DEFAULT 5;
  </code></pre>
  
  <h3>Check if the <code>Column</code> already exists</h3>
  
  <pre><code>SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'recipes' AND sql LIKE '%skiptime%'
  </code></pre>
  
  <p>I get a result depending if the <code>column</code> exists. Now I want to combine these two statements. But I don't know how.</p>
  
  <h1>Added some code (see comments)</h1>
  
  <p><code>RunMigrations()</code> is called one time on the the <code>Application</code> startup.</p>
  
  <pre><code>ProviderInvariantName
  </code></pre>
  
  <p>can be <code>SQLite</code> or <code>PostgreSQL</code>.</p>
  
  <pre><code>/// &lt;summary&gt;
  /// Reads all resource names from the assembly and saves those containing the db namespace in a private list
  /// &lt;/summary&gt;
  private void _CacheDbRessources()
  {
      _RessourcesNamespace = $"FooName.Data.{_ProviderInvariantName}";
      _RessourceNames = Assembly.GetExecutingAssembly().GetManifestResourceNames().Where(item =&gt; item.Contains(_RessourcesNamespace));
  }
  
  /// &lt;summary&gt;
  /// Does the first-time setup for the database and runs migrations. 
  /// &lt;/summary&gt;
  private void _RunMigrations()
  {
      IEnumerable&lt;string&gt; migrations =
          _RessourceNames.Where(item =&gt; item.Contains("_Migrations"))
              .Select(s =&gt; s.Replace(_RessourcesNamespace + ".", ""))
              .OrderBy(item =&gt; item);
  
      if (migrations.Any())
          _Logger.Debug("Running database migrations...");
  
      using (DbConnection conn = GetConnection())
      {
          foreach (string item in migrations)
          {
              using (DbTransaction tran = conn.BeginTransaction())
              {
                  _Logger.Debug("Executing query '{0}'...", item);
                  using (DbCommand cmd = conn.CreateCommand(GetQueryResource(item), tran))
                  {
                      int rows = cmd.ExecuteNonQuery();
                      _Logger.Debug($"{rows} rows where affected.");
                  }
  
                  tran.Commit();
              }
          }
      }
  }
  </code></pre>
  
  <h3>000_InitialCreate.sql (SQLite)</h3>
  
  <pre><code>/* users */
  CREATE TABLE IF NOT EXISTS "users" (
      "ident" INTEGER PRIMARY KEY AUTOINCREMENT,
      "user_name" TEXT NOT NULL UNIQUE,
      "real_name" TEXT,
      "password" TEXT NOT NULL,
      "userlevel" INTEGER NOT NULL DEFAULT 0,
      "enabled" INTEGER NOT NULL DEFAULT 1,
      "language" TEXT,
      "lock" TEXT
  );
  </code></pre>
  
  <h3>000_InitialCreate.sql (Postgres)</h3>
  
  <pre><code>/* users */
  CREATE TABLE IF NOT EXISTS "users" (
      "ident" SERIAL PRIMARY KEY,
      "user_name" TEXT NOT NULL UNIQUE,
      "real_name" TEXT,
      "password" TEXT NOT NULL,
      "userlevel" INTEGER NOT NULL DEFAULT 0,
      "enabled" INTEGER NOT NULL DEFAULT 1,
      "language" TEXT,
      "lock" TEXT
  );
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/IuZPs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IuZPs.png" alt="resources"></a></p>