siteQL is my first major undertaking to build a full-stack web application using NextJS, TypeScript and various libraries associated.
The application is built to be a modular SQL database/website aplication installer with credentials created and hashed for first user login.
The installer goes through a guided form and finalises with a database configuration and reroute.
To run siteQL locally you will need to install NodeJS alongside a SQL service of your choice. I recommend either a predefined docker image focused on the LAMP stack or the XAMPP stack
In addition whilst not required a front end tool such as phpMyAdmin would be useful in visually validating SQL data structures during the installation process.
To install siteQL locally
Make sure you have completed the prerequisites.
Fork the repository as your own repo if you wish to build a project on top of siteQL.
First, navigate to the folder where you want to store the project on your local machine using the terminal. Then run this command to clone your fork:
git clone https://github.com/<YOUR_GITHUB_ACCOUNT_NAME>/siteQL.git Ensure to Replace <YOUR_GITHUB_ACCOUNT_NAME> with your actual GitHub username. This command creates a copy of the repository on your computer where you can make changes.
After cloning is complete, change your current directory to the newly created project folder:
cd siteQLThis command opens the project folder where you'll find all the source code and project files. Note that the directory name is case-sensitive, so make sure to use the exact same capitalization as shown in the previous step.
Run the following command in your terminal to install all the necessary packages defined in the project's package.json file:
npm iThis command (shorthand for npm install) will automatically download and set up all required Node.js packages, including the NextJS framework and other dependencies needed for the front-end. The installation process may take several minutes depending on your internet connection speed.
Before running the application, ensure your SQL database service is active and running:
- If using XAMPP: Open the XAMPP Control Panel and click the "Start" button next to the MySQL service. The status indicator should turn green when the service is running.
- If using a standalone SQL Server: Verify that the service is running in your system's Services management console.
- If using a cloud-hosted SQL database: No action needed, as the service should be continuously available.
This step is crucial because siteQL requires an active database connection to function properly. Without a running SQL service, you may encounter connection errors when trying to start the application.
npm run devOpen your browser and visit http://localhost:3000 to see the application running.
- checkInstallation
- createDatabase
- createTable
- finalInstallCheck
- login
- siteDataRender
- sqlServicePing
- updateCredentials
- updateDatabase
Request Type: GET()
Primary Function:
The checkInstallation API endpoint verifies the application's connection status to the SQL backend database. It examines local environment variables (accessed through process.env) and returns:
- The current connection status to the database
- A list of environment variables, indicating which are configured and which are missing or unconfigured
This endpoint is served through a popover component to help quickly identify configuration issues during setup or troubleshooting. It enables verification that all required database connection parameters are properly set before attempting to use the application's data-dependent features.
Request Type: POST()
Primary Function:
The createDatabase API endpoint initializes the application's database infrastructure by:
- Creating a Primary database for the application.
- Establishing a
maintable within this databse with the following schema:
Table Name: main
Table Fields:
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_email VARCHAR(255) NOT NULL UNIQUE,
user_password VARCHAR(255) NOT NULL,
full_name VARCHAR(255),
location VARCHAR(255),
app_title VARCHAR(255),
about_app VARCHAR(255),
pfp_image VARCHAR(255),
app_image VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Upon successful creation of both the database and table, this endpoint automatically updates the application's environment variables by writing the validated database connection details to an ENV file.
Request Type: POST()
Primary Function:
The createTable API endpoint handles the creation and management of database tables based on validated POST data from the application. Its key functions include:
- Validating the table specification data received in the POST request
- Determining whether a requested table already exists
- Creating new tables when they don't exist, using the validated specifications
- Updating existing tables according to the provided schema when modifications are needed
Request Type: GET() POST()
Primary Function:
The finalInstallCheck API endpoint serves a dual purpose through bouth GET and POST requests.
- GET() Request:
- Performs validation of database connectivity by executing SQL queries
- Evaluates each environment variable against relevant test cases to verify proper configuration
- Returns detailed diagnostics about the success or failure of each test, providing actionable feedback for troubleshooting
The results of this request are rendered by the FinaliseInstallation component.
- POST Request:
Upon receiving confirmation that all tests have passed successfully the POST() request writes a final environment variable that signals installation completion this enables the application to transition from setup mode to full operational functionality.
Request Type: POST()
Primary Function:
The login API endpoint processes authentication requests from the LoginForm component by:
- Accepting user credentials (typically email/username and password) from the form submission
- Validating these credentials against user records stored in the
maindatabase - Employing bcrypt to securely handle password verification through cryptographic comparison instead of plain text matching
- Generating and returning appropriate authentication tokens or session information upon successful validation
Request Type: GET()
Primary Function:
The siteDataRender API endpoint retrieves information from the main database to render on relevant components. The current implementation returns:
- user_email
- full_name
- location
- app_title
- about_app
- pfp_image
- app_image
Request Type: GET()
Primary Function:
The sqlServicePing API endpoint essentially pings the SQL Server (based on details in env file) and returns a status of success or fail which in turn can be rendered in various ways. Currently it is being utlised to render the ServicePing component.
Request Type: POST()
Primary Function:
The updateCredentails API endpoint is used to create and store credentials (full name, user email, password) within the main database allowing the user to login post installation. Should the user already exist the API will overwrite the current username and password with the updated details.
Request Type: POST()
Primary Function:
The updateDatabse API endpoint is used to create and store information associated with the application in the main database (location, app title, about app). Should information already exist the endpoint will overwrite details accordingly
Request Type: POST()
Primary Function:
The image uploader API endpoint ensures that images are stored in the public/img folder. It categorizes and datestamps each image to maintain a history for future reference. This system serves the most recently uploaded image as the primary profile picture for both users and applications, while also keeping previous versions accessible