Project and SQL Setup

Matt Toegel matthew.toegel@njit.edu

First Steps for Project Setup

Tool was revamped 6/2025, make sure you’re using the latest version.

It’s recommended to use the same casing for the folder names as shown above to avoid issues when deploying to a server. For example, use project/sql instead of Project/SQL.

init_db.php Overview

  • Scans the directory for .sql files generally with CREATE TABLE or ALTER TABLE statements

  • SQL files must start with a three-digit number for sorting (e.g., 001, 002)

  • Queries the database (once) to check for existing tables and prevents re-running CREATE TABLE

    • You’ll see a notice saying it was skipped (This saves query count in limited environments)

  • Runs the remaining queries and shows success/failure

  • Run this script after adding new .sql files to update the database structure

  • (2025) Added a rate limit feature to deter abuse; 10 requests per minute

updated to have a more pleasant output and to handle errors better

Use Cases for init_db.php

  • Quick recovery after database failure/purge

  • Migration to another server; recreates the entire structure

Not intended for production; it’s a development tool, but you’re free to build upon this outside of this course

Creating the First Table

  • Create a user table with fields

    • id - Auto increment; primary key

    • email - VARCHAR; unique constraint

    • password - VARCHAR; stores hashed passwords

    • created - Timestamp; set automatically

    • modified - Timestamp; set or updated automatically

CREATE TABLE IF NOT EXISTS `Users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(100) NOT NULL,
    `password` VARCHAR(60) NOT NULL, -- password will be hashed, 60 is the length for the hash
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE (`email`)
)

Using VS Code

  • Refresh the MySQL extension panel to view tables (refresh icon at the top)

  • Use the panel for query testing; avoid direct changes

  • Persist code in .sql (structural) or .php (interaction) files

using vscode mysql
Figure 1. VS Code MySQL Extension Panel

Summary

  • Make structural changes in the project/sql folder

  • Run the /project/sql/init_db.php script after adding new files

    • This only needs to be done once per batch of changes since our local, qa, and prod share the same database connection

Generally your lanes have split databases in real-world projects, but for this course we will use the same database across all environments to simplify the process

  • Preserve history; don’t modify CREATE TABLE directly; use ALTER TABLE instead

  • Ensure .sql files start with three digits (e.g., 001) for proper order

  • Push changes to the ProjectSetup branch and create a PR to qa

  • Complete the PR

  • Update local to prepare for the next lesson

    • git checkout qa

    • git pull origin qa

PR stands for Pull Request and you’ll likely see the abbreviation used more often going forward.