Inserting ID with case

I use sqlite3 for a lot of small projects. It has many features that are very impressive for such a small DB engine.

One thing I like to do is set the value of an index field using MAX(id)+1 from the table itself but that does not work on a blank table. To make this work with a blank table a CASE statement can be used.

CREATE TABLE microServices (
   id INT NOT NULL, 
   packageName CHAR(45) NULL, 
   serviceName CHAR(45) NULL, 
   deployTargets CHAR(45) NULL, 
PRIMARY KEY (id));

INSERT INTO microServices (
   id, 
   packageName, 
   serviceName) 
   SELECT CASE 
      WHEN MAX(id) > 0 
         THEN MAX(id)+1 
      ELSE 1 
   END, 
   'trucks', 
   'trucksd' 
FROM microServices;

Sqlite, the DB engine that could

sqlite3 is a great little DB engine for projects that need a small DB. It uses common SQL commands to store data in a local file. The file created by using the sqlite3 commands is the only database. Simple and affective it can be added to any script, or program with very little code.


BASH

echo "SELECT MAX(id)+1 FROM mytable;" | sqlite3 mydata.db 

PERL

use DBI;
$dbh = DBI->connect("dbi:SQLite:dbname=mydata.db", "", "", { sqlite_use_immediate_transaction => 1, RaiseError =>1 }) or die $DBI::errstr;
my $NextId = $dbh->selectall_arrayref("SELECT MAX(id)+1 FROM mytable;")->[0][0];