title: SQLite3 - Create multiple tables in one statement
body: <p>I'm learning node js at the mo, and using SQLite3 to create a portable project/task/workflow tool (it'll eventually end up on github).</p>
  
  <p>If it's the first time a user is running the node app (and the SQLite database file does not exist), I want it to run an install SQL script to create all the tables it'll need to work.</p>
  
  <p>I could do them all as separate transactions, but that looks messy and it's much easier to do it all in one call.</p>
  
  <p>In Oracle I know I can wrap them all in as follows:</p>
  
  <pre><code>BEGIN CREATE TABLE1...; CREATE TABLE2...; END;
  </code></pre>
  
  <p>SQLite though, I don't know as much about (and don't know if I can do what I am trying to do).</p>
  
  <p>The code below is only create the first table (and not the others).</p>
  
  <p>Thanks.</p>
  
  <p><strong>Express Node JS app calling the SQL on startup</strong></p>
  
  <pre><code>// Declare Express
  var express   = require('express');
  
  // Instantiate Express
  var app       = express();
  
  // Create a http server with express
  var server    = require('http').createServer(app);
  
  // Instantiate socket on the http express server
  var io        = require('socket.io').listen(server);
  
  // Declare filesystem
  var fs        = require('fs');
  
  // Database File  
  var db_file   = './sqlite/db-rhubarb.sqlite';
  
  // Check if the dataexists
  var db_exists = fs.existsSync(db_file);
  
  // Declare sqlite3
  var sqlite3   = require('sqlite3').verbose();
  
  // Instantiate sqlite3 database
  var db        = new sqlite3.Database(db_file);
  
  // Check if a database exists - create if on first run
  db.serialize(function() {
    //if (!fs.existsSync(db_file)) {
      console.log("Can't find a SQLite database, creating one now...");
      var install_sql = fs.readFileSync('./sqlite/sql/install.sql', 'utf-8');
      db.run(install_sql);
  
    //}
  });
  
  // Application port (this is what eg localhost:1227)
  var port      = 1127;
  
  // Tell server listen on port
  server.listen(port);
  console.log("Running application on port: "+port);
  </code></pre>
  
  <p><strong>Install SQL</strong></p>
  
  <pre><code>/* Create the contacts table */
  CREATE TABLE IF NOT EXISTS 'main'.'contacts' ( "contact_id" INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL  UNIQUE, 
                                             "date_added" DATETIME DEFAULT CURRENT_TIMESTAMP, 
                                             "user_id_created" INTEGER );
  
  /* Create the contacts attribute table */
  CREATE TABLE IF NOT EXISTS "main"."contact_attributes" ( "cont_attr_id" INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL  UNIQUE, 
                                                       "contact_id" INTEGER, 
                                                       "date_created" DATETIME DEFAULT CURRENT_TIMESTAMP, 
                                                       "user_id_created" INTEGER, 
                                                       "attr_name" VARCHAR, 
                                                       "attr_value" VARCHAR );
  
  /* Create the task table */
  CREATE TABLE IF NOT EXISTS "main"."tasks" ( "task_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
                                          "date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
                                          "user_id_created" INTEGER,
                                          "task_value" VARCHAR );
  
  /* Create the tag table */
  CREATE TABLE IF NOT EXISTS "main"."tags" ( "tag_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, 
                                         "date_created" DATETIME DEFAULT CURRENT_TIMESTAMP, 
                                         "user_id_created" INTEGER, 
                                         "tag_value" VARCHAR UNIQUE );
  
  /* Create the tag mapping table */
  CREATE TABLE IF NOT EXISTS "main"."map_tag_task" ( "map_id" INTEGER PRIMARY KEY  AUTOINCREMENT     NOT NULL  UNIQUE, 
                                                 "date_created" DATETIME DEFAULT  CURRENT_TIMESTAMP, 
                                                 "user_id_created" INTEGER, 
                                                 "tag_id" INTEGER, 
                                                 "task_id" INTEGER );
  
  /* Create the log table */
  CREATE TABLE IF NOT EXISTS "main"."logs" ( "log_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, 
                                          "date_created" DATETIME DEFAULT CURRENT_TIMESTAMP, 
                                          "user_id_created" INTEGER, 
                                          "task_id" INTEGER,
                                          "log_value" VARCHAR );
  
  /* Create the task messages table */
  CREATE TABLE IF NOT EXISTS "main"."messages" ( "message_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
                                             "date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
                                             "user_id_created" INTEGER,
                                             "task_id" INTEGER,
                                             "message_value" VARCHAR );
  
  /* Create the users table */
  CREATE TABLE IF NOT EXISTS "main"."users" ( "user_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
                                          "forename" VARCHAR,
                                          "surname" VARCHAR,
                                          "password" VARCHAR,
                                          "salt" VARCHAR );
  </code></pre>