[SOLVED] Re-Initializing a SQLite3 Database
lazarus / 16-Databases

----------------------------------------------------------------------
1HuntnMan · 2025-09-19 14:05

How would you correctly re-initialize a database?
I could just delete the Database and recreate everything.  Or drop
then re-create all the tables, the same routine with the indexes. My
issue is in developing the application and testing with made up
records throughout the database tables, etc., I want to wipe-out all
this test data. I could just delete the database file and recreate
everything. But, thinking just drop the tables one-by-one and recreate
all the tables like I did when I first created the database in the
first place, still have that unit that I did in the beginning of the
project. Then drop the indexes and recreate.

Question, when you drop the table are the indexes still okay as long
as you recreate the tables with the exact same names? Or it appears to
drop the table, drop it's indexes and then recreate the tables and
indexes. I have the original unit I wrote that created the database in
the beginning.

----------------------------------------------------------------------
JanRoza · 2025-09-19 15:14

Why DROP a table and do everything over again?
Use the SQL DELETE command to delete all records in a table, that way
the structure of the table stays intact.
See the SQLite homepage for a complete overview of all SQL commands
(https://www.sqlite.org).

----------------------------------------------------------------------
1HuntnMan · 2025-09-19 18:11

I want the autoincrement key field to be reset to 0...

----------------------------------------------------------------------
1HuntnMan · 2025-09-19 18:20

I've looked at DELETE and VACUUM but can't figure out if after DELETE,
weather any of the auto-increment fields are reset.

----------------------------------------------------------------------
cdbc · 2025-09-19 19:28

Hi
· rename your db-file to e.g.: 'testdb.db3'
· use your unit, to create the whole 'shebang' in a new file with your
db-name.
· now you have the chance to inspect your creation-sqls and compare
the new
  db-file to the old saved working one with the test data.
· if you think of new features, you can always test them on your test-
db --
  just change the databasename in object-inspector...
Anyway, that's what I'd do...
Regards Benny

----------------------------------------------------------------------
1HuntnMan · 2025-09-19 20:12

cdbc, cool, I'll try that and let you know.  Thanks...

----------------------------------------------------------------------
GAN · 2025-09-20 01:39

Hi, may be this help you.

https://www.designcise.com/web/tutorial/how-to-reset-autoincrement-
number-sequence-in-sqlite

----------------------------------------------------------------------
Hansvb · 2025-09-20 06:17

With SQllite you can reset the autoincrement fields so that they start
again with 1 when you have deleted all records. I do that as follows.
The table was created with ID as the autoincrement field:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQL_CREATE_ITEMS = 'create table if not exists ' + ITEMS + ' (' +    
              'ID                 INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT UNIQUE, ' +   <---- Autoincrement field                
  ... Then I clean up the autoincrement fields
with: [code=pascal]procedure
TAppDatabaseMaintainer.ResetTableAutoIncrement(const
TableName:string);begin  try    FSQLiteConnection.DatabaseName:=
ConnectionFile;    FSQLiteConnection.Open;    FSQLTransaction.Active:=
True;     FSQLQuery.SQL.Text:= 'DELETE FROM sqlite_sequence WHERE name
= :TABLE_NAME';  <--- here: this does the trick. see:
https://sqlite.org/autoinc.html   
FSQLQuery.Params.ParamByName('TABLE_NAME').AsString:= TableName;   
FSQLQuery.ExecSQL;     FSQLTransaction.Commit;   
FSQLiteConnection.Close;     AddMessage(amtInfo, 'Reset auto-increment
for table: ' + TableName);  except    on E:Exception do begin     
AddMessage(amtError, 'Failed to reset auto-increment: ' + E.Message); 
  end;  end;end;

----------------------------------------------------------------------
Hansvb · 2025-09-20 06:24

Some more,

if you want to clean up all autoincrement:


Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TAppDatabaseMaintainer.ResetAllAutoIncrements;begin  try   
FSQLiteConnection.DatabaseName:= ConnectionFile;   
FSQLiteConnection.Open;    FSQLTransaction.Active:= True;    
FSQLQuery.SQL.Text:= 'DELETE FROM sqlite_sequence';    <--- Here    
FSQLQuery.ExecSQL;     FSQLTransaction.Commit;   
FSQLiteConnection.Close;     AddMessage(amtInfo, 'Reset all auto-
increment counters');  except    on E:EDatabaseError do begin     
AddMessage(amtError, 'Failed to reset auto-increments: ' +
E.Message);      MessageDlg('Error', 'Failed to reset auto-
increments', mtError, [mbOK], 0);    end;  end;end;

----------------------------------------------------------------------
Lutz Mändle · 2025-09-20 20:36

Deleting a table from a SQLite3 database with resetting the
autoincrement counter and compressing the file works for me with the
following code sequence:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ... 
VarConn.ExecuteDirect(Format('DELETE FROM "%s"',[sVarTableName])); 
VarConn.ExecuteDirect(Format('UPDATE "sqlite_sequence" SET "seq"=0
WHERE "name"=''%s''',[sVarTableName]));  VarConn.ExecuteDirect('End
Transaction');  VarConn.ExecuteDirect('VACUUM'); 
VarConn.ExecuteDirect('Begin Transaction');... 
The string variable sVarTableName contains the actual name of the
table to delete. VarConn is a TSQLConnector with the property
ConnectorType set to SQLite3, a TSQLite3Connection should work alike.

----------------------------------------------------------------------
1HuntnMan · 2025-09-21 11:35

Awesome, thanks all, just what I was looking for and the examples!
GAN, thanks for the link.  Got it!

----------------------------------------------------------------------
Zvoni · 2025-09-22 10:38

Quote from: 1HuntnMan on September 19, 2025, 08:11:27 pmI want the
autoincrement key field to be reset to 0...
Don't use AUTOINCREMENT in your CREATE TABLE-Statement.

As easy as that.

And yes: If you don't pass a Value for your ID in your INSERT, Sqlite
will still count up automagically as long as your Primary Key is of
Type Integer

Bottom Line: Don't use AUTOINCREMENT for your Primary Key ID on CREATE
TABLE
You don't need it
It only creates overhead