project-gen-ai

Generate a full Express backend + React frontend CRUD project from a database name and SQL CREATE TABLE statements. One command → working app.

What it generates

For every table in your SQL:

| Input | Output | |---|---| | Table with plain columns | React page with typed inputs (text / number / date) | | Column with FOREIGN KEY | React <select> dropdown fetching from the referenced table | | All tables | Express route file with GET / GET:id / POST / PUT / DELETE | | DB name | db.js MySQL pool + .env template |

Install

# From npm (once published)
npm install -g project-gen-ai

# Or run locally from this folder
npm install
npm link          # makes `project-gen` available globally

Usage

npm create numz-app

The CLI will ask you:

| Question | Example answer | |---|---| | Project name | sims | | MySQL database name | simsdb | | Output directory | (leave blank = current folder) | | Paste your SQL | (paste all CREATE TABLE blocks, press Enter) |

Example SQL input

CREATE TABLE Users (
  UserID   INT PRIMARY KEY AUTO_INCREMENT,
  Username VARCHAR(100) NOT NULL,
  Password VARCHAR(255) NOT NULL,
  Role     VARCHAR(50)  DEFAULT 'staff'
);

CREATE TABLE SpareParts (
  PartID     INT PRIMARY KEY AUTO_INCREMENT,
  Name       VARCHAR(100) NOT NULL,
  Category   VARCHAR(50),
  Quantity   INT          DEFAULT 0,
  UnitPrice  DECIMAL(10,2),
  TotalPrice DECIMAL(10,2)
);

CREATE TABLE StockIn (
  StockInID       INT PRIMARY KEY AUTO_INCREMENT,
  PartID          INT NOT NULL,
  StockInQuantity INT NOT NULL,
  StockInDate     DATE NOT NULL,
  FOREIGN KEY (PartID) REFERENCES SpareParts(PartID)
);

CREATE TABLE StockOut (
  StockOutID         INT PRIMARY KEY AUTO_INCREMENT,
  PartID             INT NOT NULL,
  UserID             INT NOT NULL,
  StockOutQuantity   INT,
  StockOutUnitPrice  DECIMAL(10,2),
  StockOutTotalPrice DECIMAL(10,2),
  StockOutDate       DATE,
  FOREIGN KEY (PartID) REFERENCES SpareParts(PartID),
  FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

Generated project structure

sims/
├── backend/
│   ├── package.json
│   ├── server.js          ← Express app, registers all routers
│   ├── db.js              ← mysql2 connection pool
│   ├── .env               ← DB credentials template
│   └── routes/
│       ├── users.js
│       ├── spareparts.js
│       ├── stockin.js
│       └── stockout.js
└── frontend/
    ├── package.json
    ├── vite.config.js     ← proxies /api → localhost:5000
    ├── index.html
    └── src/
        ├── main.jsx
        ├── index.css
        ├── App.jsx        ← react-router-dom routes
        ├── Navbar.jsx
        └── pages/
            ├── Login.jsx
            ├── Users.jsx
            ├── SpareParts.jsx
            ├── StockIn.jsx   ← PartID = dropdown ✓
            └── StockOut.jsx  ← PartID + UserID = dropdowns ✓

After generation — next steps

# 1. Create the MySQL database and run your SQL schema
mysql -u root -p -e "CREATE DATABASE simsdb;"
mysql -u root -p simsdb < schema.sql

# 2. Start the backend
cd sims/backend
npm install
# Edit .env with your DB credentials
npm run dev       # nodemon server.js on port 5000

# 3. Start the frontend
cd sims/frontend
npm install
npm run dev       # Vite on http://localhost:5173

How FK detection works

The parser reads FOREIGN KEY (col) REFERENCES table(col) constraints.
When generating a React page, any column that is a FK becomes a <select> dropdown:

<select name="PartID" value={form.PartID} onChange={handleChange} className="border p-2 rounded">
  <option value="">Select SpareParts</option>
  {sparePartsList.map((r) => (
    <option key={r.PartID} value={r.PartID}>{r.Name}</option>
  ))}
</select>

The display label for each option is chosen automatically — it looks for a column named Name, name, Title, Username, etc. on the referenced table.

SQL type → input type mapping

| SQL type | HTML input type | |---|---| | INT, BIGINT, SMALLINT | number | | DECIMAL, FLOAT, DOUBLE | number | | DATE | date | | DATETIME, TIMESTAMP | datetime-local | | VARCHAR, CHAR, ENUM | text | | TEXT, LONGTEXT | text |

Tech stack of generated project

Backend: Node.js · Express · mysql2 · dotenv · cors
Frontend: React 18 · Vite · Tailwind CSS · Axios · React Router v6