REACT with EXPRESS-JS REST API & SQLite DB

React Starter with ExpressJS REST API & SQLite DB (includes JWT, Sequelize and CRUD operations using Axios).

Hi there, this repo contains a fullstack web application created with React library using the virtual DOM feature, which ensures high performance by minimizing direct manipulation of the DOM and follows a component-based architecture, allowing for reusable UI components. I've implemented ExpressJS which is a backend web application framework for building RESTful API to handle http requests and SQLite database to store our data performed by CRUD operations.

Prerequisites: (used to create this project but shouldn't be any issue using higher versions)

• Node v20.11.0 +
• npm v10.2.4 +
• SQLite v3.43.2 +

Steps to create this project from scratch:

FrontEnd web-app
1 - Let's create our React app and use --template to implement typescript for tsx.
npx create-react-app appName --template typescript

2 - Add the Axios library for making XMLHttpRequests and handle responses in JS.
npm i axios

3 - Install React-Router which is a third-party library that enables routing in React apps.
npm i react-router

4 - React-Router-Dom is a package that provides bindings for using React Router in web apps.
npm i react-router-dom

Backend server-app

5 - Let’s create a express server project, first run the next command to add our package.json:
npm init

6- Install ExpressJS with next comman on terminal:
npm i express

7- Add a new file to the root folder of our server app like index.js

• • Now we can run our app by using Node:
    node index.js

Database SQLite database engine is a tiny in-process library that implements a self-contained, serverless with TSQL, you can get it from next page https://www.sqlite.org/download.html and try to run sqlite3 --version to check is ok.

Run next command to create a SQLite database:
sqlite3 ./config/properties.db

Next commands on terminal is going to create the database objects:

CREATE TABLE propertyChains (
id INTEGER PRIMARY KEY AUTOINCREMENT,
propertyChainName TEXT NOT NULL UNIQUE,
description TEXT,
createdDate DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
active BIT NOT NULL
);

CREATE TABLE locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
locationName TEXT,
description TEXT,
createdDate DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
active BIT NOT NULL
);

CREATE TABLE properties (
id INTEGER PRIMARY KEY AUTOINCREMENT,
propertyChainId INTEGER NOT NULL,
locationId INTEGER NOT NULL,
propertyName TEXT NOT NULL,
address TEXT,
zipCode TEXT,
createdDate DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
active BIT NOT NULL,
FOREIGN KEY(propertyChainId) REFERENCES propertyChains(id),
FOREIGN KEY(locationId) REFERENCES locations(id)
);

CREATE INDEX idx_property_name_zipcode ON properties (propertyName, zipCode);

CREATE TABLE contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
propertyId INTEGER NOT NULL,
firstName TEXT NOT NULL,
lastName TEXT,
email TEXT NOT NULL,
createdDate DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
active BIT NOT NULL,
FOREIGN KEY(propertyId) REFERENCES properties(id)
);

CREATE UNIQUE INDEX idx_contacts_email ON contacts (email);

CREATE TABLE users (
uuid TEXT PRIMARY KEY NOT NULL UNIQUE,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
pwd TEXT NOT NULL,
photoURL TEXT,
createdDate DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
active BIT NOT NULL
);

CREATE INDEX idx_name_email on users (name, email);

Some useful commands below:
.tables <— shows all tables in the DB
drop table properties; <—remember to close commands with semicolon;
PRAGMA index_list('properties'); <-- shows all indices from a table
DROP INDEX [IF EXISTS] idx_property_name_zipcode; <-- In case you wan't to delete an index.

• Install CORS package to allow cross requests, install Sequelize which is an object relational mapping library that supports many databases and also install jsonwebtoken to sign and verify the JSON Web Token used to manage user timeout and sessions.

• Add next packages:
  npm install cors
  npm install sqlite3
  npm install body-parser
  npm install sequelize --save
  npm install jsonwebtoken
  

- Notice I'm using sequelize to map the database objects into defined models and exposing data in controllers through routes, also you can see the server app running in my terminal logged in all executed commands after each request. I've used different approaches to execute CRUD operations and http response status codes to interpret each scenario possible when user makes a request thru the React app.

• I would love to spend time explaining every section of the code in charge to perform all UI actions and how http requests return the expected data but I need to get back to work to pay bills, bank loan and food to survive, so you have to take a look into the folder structure to learn how it works. I tried to use best practices and put everything in the right place but the lack of time made me a bit lazy in some parts of it. however I believe this is the most complete fullstack project around internet.

• Here you can execute all CRUD operations in real time on the profile.tsx file we can find how to validate some fields, make http requests using Axios and component data flow between the listView and the main form passing props with states generated from the context.

• The next data table flow is pretty cool because it can look up for any property you're typing in the search-box and additionally you can also load up more data from the database which is around 1000 records stored.

• In case the record you're looking for does not exist in the current properties state, no worries! hit enter in the search-box or press the load button to re-load the state with a new bunch of records.

Notes

Well, I do not master React like I do for Angular because I have more experience building large-scale complex apps for big industries but I get to understand all process involved to create an app from scratch with SOLID practices, so what I have done on this project can provide a perspective about how to face some user needs with those technologies.