title: Why doesn't my SQLite database add data until I close my application?
body: <p>My Delphi application is using FireDac and an SQLite database. I've noticed that updates are being saved in a journal file and the database file is not actually updated until I close my application.</p>
  
  <p>The application is making lots of 'batch updates' to the database. Each individual update is inside a TFDQuery.StartTransaction ... TFDQuery.Commit pair. Despite this, it seems all updates are held in the journal file until the application ends.</p>
  
  <p>How can I force SQLite to update the database after each batch of updates rather than when my application finishes?</p>
  
  <p>I've tried changing the SQLite db to WAL but the same thing happens.</p>
  
  <p>Despite using 'StartTransaction' and 'Commit' the data stays in the journal until the application ends.</p>
  
  <pre><code>try
    Query.Connection := FDConnection1;
    FDConnection1.Open;
    FDConnection1.StartTransaction;
    Query.SQL.Text := 'select 1 from t_Manufacturers where m_Name = ' + QuotedStr(ManString);
    Query.Open;
    if Query.RecordCount = 0 then begin
      { not found, so add }
      Query.SQL.Text := 'insert into t_Manufacturers (m_Name, m_ManUID) values (:Name, null)';
      Query.ParamByName('Name').AsString := ManString;
      Query.ExecSQL;
      { save m_ManUID for logging }
      Query.SQL.Text := 'select m_ManUID from t_Manufacturers where m_Name = ' + QuotedStr(ManString);
      Query.Open;
    end;
    Result := Query.FieldByName(m_ManUID).AsInteger;
    FDConnection1.Commit;
  except
    on E : EDatabaseError do begin
      MessageDlg('Database error adding manufacturer: ' + E.Message, mtError, [mbOk], 0);
      FDConnection1.Rollback;
    end;
  </code></pre>
  
  <p>No error messages or issues. Providing the application finishes OK, the database is updated as expected, so I'm happy that my programming and SQL is doing exactly what I need in that respect.</p>