😁Study MERN CRUD applications Todo App Back End , Front End Part 1(ok)

Project Start 👇

769KB

test-study.rar

Part 1:

Back-end: Create FetchData

src\server\db\index.ts

import * as todos from './tables/todos'; 
export default { 
  todos 
}

src\server\db\connection.ts

import mysql from "mysql2/promise";
export default mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'todo_app'
});

src\server\db\queryUtils.ts

import pool from './connection'; 
import type { ResultSetHeader } from 'mysql2'; 
export async function SelectQuery<T>(queryString: string, params?: any) { 
  const [results] = await pool.execute(queryString, params); 
  return results as T[]; 
} 
export async function ModifyQuery<T>(queryString: string, params?: any) {
  const [results] = await pool.query(queryString, params); 
  return results as T[]; 
}

src\server\db\tables\todos.ts

import {
  SelectQuery,
  ModifyQuery
} from '../queryUtils';
export interface ITodosRow {
  id: number;description: string;isCompleted: 0 | 1;
}
export function getAll() {
  return SelectQuery < ITodosRow > ('SELECT * FROM todos;');
}
export function getOne(id: number) {
  return SelectQuery < ITodosRow > ('SELECT * FROM todos WHERE id = ?;', [id]);
}
export function insert(todoItem: string) {
  return ModifyQuery('INSERT INTO todos (description) VALUE (?);', [todoItem])
}
export function update(id: number, description: string, isCompleted: number) {
  return ModifyQuery('UPDATE todos SET description=?,isCompleted=? WHERE id=?;', [description, isCompleted, id]);
}

src\server\routes\index.ts

import {
  Router
} from 'express';
import todosRouter from './todos';
const router = Router();
router.use('/todos', todosRouter);
export default router;

src\server\routes\todos.ts

import {
  Router
} from 'express';
import db from '../db';
const router = Router();
router.get('/', async (req, res) => {
  try {
    const todos = await db.todos.getAll();
    res.json(todos);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
export default router;

src\server\server.ts

import express from 'express';
import cors from "cors";
import apiRouter from './routes';
const app = express();
const corsOptions = {
  origin: 'http://localhost:9000',
  optionsSuccessStatus: 200 
}
app.use(cors(corsOptions))
app.use(express.json());
const port = process.env.PORT || 3000;
var hostct = 'localhost';
app.use('/api', apiRouter);
app.listen(port, () => console.log(`Server running on port http://${hostct}:${port}`));

Front-end List Todo

src\client\App.tsx

import React from "react";
import { BrowserRouter, Routes, Route, Link } from "react-router-dom";
import Home from "./views/Home";
import Todos from "./views/Todos";
interface AppProps { }
const App = (props: AppProps) => {
  return (
    <BrowserRouter>
      <div className="px-5 py-2">
        <Link to="/">Home</Link>
      </div>
      <Routes>
        <Route path="/" element={<Home />} />
        <Route path="/todos" element={<Todos />} />
      </Routes>
    </BrowserRouter>
  );
}
export default App;

src\client\views\Todos.tsx

import React, { useState, useEffect } from "react";
import { fetchData } from '../services/fetchData';
import { ITodo } from "../types";
import { Link } from "react-router-dom";
interface TodoProps { }
const Todos = (props: TodoProps) => {
  const [list, setList] = useState<ITodo[]>([]);
  useEffect(() => {
    fetchData('/api/todos').
      then(list => setList(list));
  },[]);
  return (
    <main className="container mt-5" >
      <section className="row justify-content-center" >
        <div className="col-12 col-md-6">
          <ul className="list-group">
            {list.map(todo => (
              <li key={`todo-item-${todo.id}`} className="list-group-item d-flex justify-content-between align-items-center">
                <span>{todo.description}</span>
                <Link to={`/todos/${todo.id}`} className="btn btn-small btn-secondary">Details</Link>
              </li>
            ))}
          </ul>
        </div>
      </section>
    </main>
  );
}
export default Todos;

src\client\types\index.ts

export interface ITodo {
  id: number;
  description: string;
  isCompleted: 0 | 1;
}

src\client\services\fetchData.ts

const BASE_URL = process.env.NODE_ENV === 'development' ? 'http://localhost:3000' : '';
export async function fetchData(endpoint: string, method: string = "GET", payload: any = null) {
  try {
    const options: RequestInit = {
      method,
      headers: {}
    }
    if (payload && method != "GET") {
      options.headers = {
        'Content-Type': 'application/json'
      }
      options.body = JSON.stringify(payload);
    }
    const response = await fetch(`${BASE_URL}${endpoint}`, options);
    if (!response.ok) throw new Error(`HTTP Error Status: ${response.status}`);
    const todos = await response.json();
    return todos;
  } catch (error) {
  }
}

Part 2:

Back-end: Create router router.get('/:id', async (req, res) => {}

router.get('/:id', async (req, res) => {});

src\server\routes\todos.ts

import {
  Router
} from 'express';
import db from '../db';
const router = Router();
router.get('/', async (req, res) => {
  try {
    const todos = await db.todos.getAll();
    res.json(todos);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
router.get('/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    const [todo] = await db.todos.getOne(id);
    res.json(todo);
  } catch (error) {
    console.log(error)
    res.status(500).json({ message: 'Internal Server Error', error });
  }
});
export default router;

Front-end Detail Todo

src\client\App.tsx

import React from "react";
import { BrowserRouter, Routes, Route, Link } from "react-router-dom";
import Home from "./views/Home";
import Todos from "./views/Todos";
import Details from "./views/Details";
interface AppProps {}
const App = (props: AppProps) => {
  return (
    <BrowserRouter>
      <div className="px-5 py-2">
        <Link to="/">Home</Link>
      </div>
      <Routes>
        <Route path="/" element={<Home />} />
        <Route path="/todos" element={<Todos />} />
        <Route path="/todos/:id" element={<Details/>} />
      </Routes>
    </BrowserRouter>
  );
}
export default App;

src\client\views\Details.tsx

import React, { useState, useEffect } from 'react';
import { Link, useParams } from 'react-router-dom';
import type { ITodo } from '../types';
import { fetchData } from '../services/fetchData';
interface DetailsProps { }
const Details = (props: DetailsProps) => {
  const { id } = useParams();
  const [data, setData] = useState<ITodo | null>(null);
  useEffect(() => {
    fetchData(`/api/todos/${id}`).then(data => setData(data));
  }, []);
  return (
    <main className="container mt-5" >
      <section className="row justify-content-center" >
        <div className="col-12 col-md-6">
          <div className="card shadow">
            <div className="card-body">
              <h2 className="card-title">Todo Item # {id}</h2>
              <p className="card-text">{data?.description}</p>
              <Link to="/todos" className='btn btn-outline-info'>Go Back</Link>
            </div>
          </div>
        </div>
      </section>
    </main>
  );
};
export default Details;

Part 3

Back-end: Create router router.post('/', async (req, res) => {}

src\server\routes\todos.ts

import {
  Router
} from 'express';
import db from '../db';
import { ResultSetHeader } from 'mysql2';
const router = Router();
router.get('/', async (req, res) => {
  try {
    const todos = await db.todos.getAll();
    res.json(todos);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
router.get('/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    const [todo] = await db.todos.getOne(id);
    res.json(todo);
  } catch (error) {
    console.log(error)
    res.status(500).json({ message: 'Internal Server Error', error });
  }
});
router.post('/', async (req, res) => {
  try {
    const newTodo = req.body;
    const result:any = await db.todos.insert(newTodo.description);
    res.json({ message: 'Todo created', id: result.insertId });
  } catch (error) {
    console.log(error)
    res.status(500).json({ message: 'Internal Server Error', error });
  }
});
export default router;

Front-end Add Todo

src\client\App.tsx

import React from "react";
import { BrowserRouter, Routes, Route, Link } from "react-router-dom";
import Home from "./views/Home";
import Todos from "./views/Todos";
import Details from "./views/Details";
import Add from "./views/Add";
interface AppProps {}
const App = (props: AppProps) => {
  return (
    <BrowserRouter>
      <div className="px-5 py-2">
        <Link to="/">Home</Link>
        <Link to="/todos" className="mx-2">Todos</Link>
        <Link to="/todos/add" className="mx-2">Add</Link>
      </div>
      <Routes>
        <Route path="/" element={<Home />} />
        <Route path="/todos" element={<Todos />} />
        <Route path="/todos/:id" element={<Details/>} />
        <Route path="/todos/add" element={<Add />} />
      </Routes>
    </BrowserRouter>
  );
}
export default App;

src\client\views\Add.tsx

import React, { useState } from 'react';
import { useNavigate } from 'react-router-dom';
import { fetchData } from '../services/fetchData';
interface AddProps { }
const Add = (props: AddProps) => {
  const navigate = useNavigate();
  const [value, setValue] = useState<string>('');
  const handleSubmit = (e: React.MouseEvent<HTMLButtonElement>) => {
    e.preventDefault();
    fetchData('/api/todos', 'POST', { description: value }).then(data =>
      navigate(`/todos/${data.id}`)
    );
  };
  return (
    <main className="container mt-5" >
      <section className="row justify-content-center" >
        <div className="col-12 col-md-6">
          <form className="p-4 shadow border">
            <label htmlFor="description">Todo Item Description</label>
            <input value={value} onChange={e => setValue(e.target.value)} type="text" className="form-control" placeholder="Do All The Things" />
            <button onClick={handleSubmit} className="mt-3 btn btn-primary">Add Todo</button>
          </form>
        </div>
      </section>
    </main>
  );
};
export default Add;

Part 4

Back-end: Create router router.put('/', async (req, res) => {}

src\server\routes\todos.ts

import {
  Router
} from 'express';
import db from '../db';
import { log } from 'console';
const router = Router();
router.get('/', async (req, res) => {
  try {
    const todos = await db.todos.getAll();
    res.json(todos);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
router.get('/:id', async (req, res) => {
  let id = parseInt(req.params.id);
  try {
    const [todo] = await db.todos.getOne(id);
    res.json(todo);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
router.post('/', async (req, res) => {
  const result:any = await db.todos.insert(req.body.description);
  res.json({message: 'Todo created',id: result.insertId});
});
router.put('/', async (req, res) => {
  try {
    console.log(req.body);
    const newTodo = req.body;
    const result = await db.todos.update(newTodo.id,newTodo.description,newTodo.isCompleted);
    res.json({ message: 'Update created', id: result })
  } catch (error) {
    console.log(error)
    res.status(500).json({ message: 'Internal Server Error', error });
  }
});
export default router;

src\client\services\fetchData.ts

const BASE_URL = process.env.NODE_ENV === 'development' ? 'http://localhost:3000' : '';
export async function fetchData(endpoint: string, method: string = 'GET', payload: any =null) {
  try {
    const options: RequestInit = {
      method,
      headers: {}
    }
    if (payload && method !== 'GET') {
      options.headers = {
        'Content-type': 'application/json'
      };
      options.body = JSON.stringify(payload);
    }
    const response = await fetch(`${BASE_URL}${endpoint}`, options);
    if (!response.ok) {
      throw new Error(`HTTP Error Status: ${response.status}`);
    }
    const data = await response.json();
    return data;
  } catch (error) {
    console.error(`Fetch error: ${error}`);
    throw error;
  }
}

src\server\db\tables\todos.ts

import {
  SelectQuery,
  ModifyQuery
} from '../queryUtils';
export interface ITodosRow {
  id: number;
  description: string;
  isCompleted: 0 | 1;
}
export function getAll() {
  return SelectQuery < ITodosRow > ('SELECT * FROM todos;');
}
export function getOne(id: number) {
  return SelectQuery < ITodosRow > ('SELECT * FROM todos WHERE id = ?;', [id]);
}
export function insert(todoItem: string) {
  return ModifyQuery('INSERT INTO todos (description) VALUE (?);', [todoItem])
}
export function update(id: number, description: string, isCompleted: number) {
  return ModifyQuery('UPDATE todos SET description=?,isCompleted=? WHERE id=?;', [description, isCompleted, id]);
}

Front-end Edit Todo

src\client\views\Edit.tsx

import React, { useState, useEffect } from 'react';
import { useNavigate, useParams } from 'react-router-dom';
import { fetchData } from '../services/fetchData';
import { ITodo } from "../types/ITodo";
interface AddProps { }
const Edit = (props: AddProps) => {
  const navigate = useNavigate();
  const { id } = useParams();
  const [InputDes, setDataName] = useState("");
  const [InputIs, setDataIs] = useState(1);
  const handleNameChange = (name:string) => {
    setDataName(name);
  };
  const handleNumberChange = (isCom:any) => {
    setDataIs(isCom);
  };
  useEffect(() => {
    fetchData(`/api/todos/${id}`).then(data => {
      console.log(data);
      setDataName(data.description);
      setDataIs(data.isCompleted);
    });
  }, []);
  const handleSubmit = (e: React.MouseEvent<HTMLButtonElement>) => {
    e.preventDefault();    
    fetchData('/api/todos', 'PUT', {id: id, description:InputDes,isCompleted:InputIs}).then(dat =>
      {
        navigate(`/todos/${id}`);
      }
    );
  };
  return (
    <main className="container mt-5" >
      <section className="row justify-content-center" >
        <div className="col-12 col-md-6">
          <form className="p-4 shadow border">
            <label htmlFor="description">Todo Item Description</label>
            <input id="description" name="description" value={InputDes}  type="text" onChange={e => handleNameChange(e.target.value)} className="form-control" placeholder="Do All The Things" />
            <label htmlFor="isCompleted">Todo Item isCompleted</label>
            <input id="isCompleted" name="iscompleted" value={InputIs}  type="number" onChange={e => handleNumberChange(e.target.value)} className="form-control" placeholder="Do All The Things" />
            <button onClick={handleSubmit} className="mt-3 btn btn-primary">Edit Todo</button>
          </form>
        </div>
      </section>
    </main>
  );
}
export default Edit;

Hoàn thành dự án Front-end && Back-end sử dụng nextjs

4KB

test.sql

Back-end next16\app\api\todos\route.ts

app\api\todos\route.ts

import { NextResponse } from 'next/server';
const DATA_SOURCE_URL = "http://localhost:9000/api/todos";
export async function GET(request: Request) {
  const url = new URL(request.url);
  const seachParams = new URLSearchParams(url.searchParams);
  const id = seachParams.get("id");
  if(id){
    const res = await fetch(`${DATA_SOURCE_URL}/${id}`);
    const todo: Todo = await res.json();
    return NextResponse.json(todo);
  }else {
    const res = await fetch(DATA_SOURCE_URL);
    const todos: Todo[] = await res.json();
    return NextResponse.json(todos);
  }
}
export async function POST(request: Request) {
  const { description, isCompleted }: Partial<Todo> = await request.json();
  if (!description) return NextResponse.json({ "message": "Missing required data" })
  const res = await fetch(DATA_SOURCE_URL, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      description,
      isCompleted
    })
  })
  const newTodo: Todo = await res.json();
  return NextResponse.json(newTodo);
}
export async function PUT(request: Request) {
  const { id, description,isCompleted }: Todo = await request.json()
  if (!description) return NextResponse.json({ "message": "Missing required data" })
  const res = await fetch(`${DATA_SOURCE_URL}`, {
    method: 'PUT',
    headers: {
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      id,
      description,
      isCompleted
    })
  })
  const updatedTodo: Todo = await res.json()
  return NextResponse.json(updatedTodo)
}
export async function DELETE(request: Request) {
  const { id }: Partial<Todo> = await request.json();
  if (!id) return NextResponse.json({ "message": "Todo id required" })
  await fetch(`${DATA_SOURCE_URL}/${id}`, {
    method: 'DELETE',
    headers: {
      'Content-Type': 'application/json'
    }
  })
  return NextResponse.json({ "message": `Todo ${id} deleted` })
}

app\layout.tsx

import "./globals.css";
import Link from 'next/link'
export default function RootLayout({ children }: Readonly<{ children: React.ReactNode; }>) {
  return (
    <html lang="en">
      <body className={`antialiased`}>
        <Link href="/">Home</Link>
        <Link href="/todos">Todos</Link>
        <main className="container mx-auto px-4">
          {children}
        </main>
      </body>
    </html>
  );
}

app\page.tsx

export default function HomePage() {
  return (
    <div className="home">
      Home
    </div>
  );
}

app\types.d.ts

type Post = {
  "userId": number,
  "id": number,
  "title": string,
  "body": string,
}
type User = {
  "id": number,
  "name": string,
  "username": string,
  "email": string,
  "address": {
    "street": string,
    "suite": string,
    "city": string,
    "zipcode": string,
    "geo": {
      "lat": string,
      "lng": string
    }
  },
  "phone": string,
  "website": string,
  "company": {
    "name": string,
    "catchPhrase": string,
    "bs": string
  }
}
type Result = {
  pageid: string,
  title: string,
  extract: string,
  thumbnail?: {
    source: string,
    width: number,
    height: number,
  }
}
type SearchResult = {
  query?: {
    pages?: Result[],
  },
}
type BlogPost = {
  id: string,
  title: string,
  date: string,
}
type Todo = {
  id:  number
  description: string,
  isCompleted: number
}

Front-end next16\components\layout.tsx

components\layout.tsx

import "@/app/globals.css";
/* eslint-disable @typescript-eslint/no-explicit-any */
export default function Layout({ children }: any) {
  return (
    <div className="container p-3 mx-auto">
      {children}
    </div>
  )
}

pages\_document.tsx

import { Html, Head, Main, NextScript } from 'next/document';
export default function Document() {
  return (
    <Html>
      <Head />
      <body>
        <Main />
        <NextScript />
      </body>
    </Html>
  );
}

pages\_app.tsx

import "@/app/globals.css";
import Layout from '@/components/layout';
import type { AppProps } from "next/app";
export default function App({ Component, pageProps }: AppProps) {
  return <Layout><Component {...pageProps} /></Layout>;
}

pages\todos\index.tsx

import type { InferGetStaticPropsType, GetStaticProps } from 'next';
import Link from 'next/link';
import { useRouter } from 'next/router';
type Repo = {
  id: number,
  description: string,
  isCompleted: number
}
export const getStaticProps = (async () => {
  const res = await fetch(`http://localhost:3000/api/todos`);
  const repo = await res.json();
  return {
    props: {
      repo
    }
  }
}) satisfies GetStaticProps<{ repo: Repo }>
export default function Todos({ repo }: InferGetStaticPropsType<typeof getStaticProps>) {
  const router = useRouter();
  const handleDelete = async (id: number) => {
    const result = await fetch(`http://localhost:3000/api/todos`,
      {
        method: 'DELETE',
        body: JSON.stringify({
          id
        })
      }
    );
    const res = await result.json();
    router.replace(router.asPath);
  };
  return (
    <section className="row justify-content-center" >
      <ul className="list-decimal">
        {
          repo.map((todo: Repo) => (
            <li key={`todo-item-${todo.id}`}>
              <span className='block'>{todo.description}</span>
              <Link href={`/todos/${todo.id}`} className="px-4  font-semibold text-sm bg-cyan-500 text-white rounded-full shadow-sm">Detail</Link>
              <Link href={`/todos/${todo.id}/edit`} className="px-4  font-semibold text-sm bg-cyan-700 text-white rounded-full shadow-sm">Edit</Link>
              <button onClick={() => handleDelete(todo.id)} className="px-4  font-semibold text-sm bg-red-600 text-white rounded-full shadow-sm">Delete</button>
            </li>
          ))}
      </ul>
    </section>
  )
}

pages\todos\create.tsx

import { useState } from "react";
export default function Create() {
  const [value, setValue] = useState<string>('');
  const handleSubmit = async (e: React.MouseEvent<HTMLButtonElement>) => {
    e.preventDefault();
    const isCompleted = 0;
    const description = value;
    await fetch(`http://localhost:3000/api/todos`,
      {
        method: 'POST',
        body: JSON.stringify({
          description,
          isCompleted
        })
      }
    );
  };
  return (
    <section className="row justify-content-center" >
      <form className="p-4 shadow border">
        <label htmlFor="description" className="text-gray-700">Description</label>
        <input value={value} onChange={e => setValue(e.target.value)} type="text" className="mt-1 block w-full rounded-md bg-gray-100 border-transparent focus:border-gray-500 focus:bg-white focus:ring-0" placeholder="Add Todo" />
        <button onClick={handleSubmit} className="text-gray-100 mt-2 text-xs leading-5 font-semibold bg-lime-950 rounded-full py-1 px-3 flex items-center space-x-2 hover:bg-lime-700 dark:highlight-white/5">Add Todo</button>
      </form>
    </section>
  )
}

pages\todos\[id]\index.tsx

import type { InferGetStaticPropsType, GetStaticProps } from 'next';
import { ParsedUrlQuery } from 'querystring';
type Repo = {
  id: number,
  description: string,
  isCompleted: number
}
export default function Detail({ repo }: InferGetStaticPropsType<typeof getStaticProps>) {
  return (
    <div className='m-3 p-4 w-60 h-auto rounded-lg bg-white shadow-md'>
      <h2>Id: {repo.id}</h2>
      <h3>Description:</h3>
      <p>{repo.description}</p>
      <h4>isCompleted:</h4>
      <p>{repo.isCompleted}</p>
    </div>
  )
}
export const getStaticProps = (async ({params}) => {
  const {id}:any = await params;
  const res = await fetch(`http://localhost:3000/api/todos/?id=${id}`);
  const repo = await res.json()
  return { props: { repo } }
}) satisfies GetStaticProps<{ repo: Repo }>
export async function getStaticPaths() {
  const response = await fetch('http://localhost:3000/api/todos');
  const postList: Repo[] = await response.json();
  return {
    paths: postList.map((post) => {
      return {
        params: {
          id: `${post.id}`,
        },
      }
    }),
    fallback: false
  }
}

pages\todos\[id]\edit.tsx

import { useParams } from "next/navigation";
import { useEffect, useState } from "react";
export default function Edit() {
  const params: any = useParams();
  var id = params?.id as number ?? 1;
  const [obj, setData] = useState({ description: "", completed: 0 });
  useEffect(() => {
    const fetchData = async () => {
      const result = await fetch(`http://localhost:3000/api/todos?id=${id}`);
      const repo = await result.json();
      const description = repo.description as string;
      const completed = repo.isCompleted as number;
      setData({ ...obj, description, completed });
    };
    fetchData();
  }, []);
  const handleNameChange = (description: string) => {
    console.log({ ...obj, description });
    setData({ ...obj, description });
  };
  const handleNumberChange = (completed: number) => {
    console.log({ ...obj, completed });
    setData({ ...obj, completed });
  };
  const handleSubmit = async (e: React.MouseEvent<HTMLButtonElement>) => {
    e.preventDefault();
    await fetch(`http://localhost:3000/api/todos`,
      {
        method: 'PUT',
        body: JSON.stringify({
          ...obj,
          id
        })
      }
    );
  };
  return (
    <section className="row justify-content-center" >
      <form className="p-4 shadow border">
        <label className="text-gray-700">Description</label>
        <input value={obj.description} onChange={e => handleNameChange(e.target.value)} type="text" className="mt-1 block w-full rounded-md bg-gray-100 border-transparent focus:border-gray-500 focus:bg-white focus:ring-0" placeholder="Edit Todo" />
        <label className="text-gray-700">isCompleted</label>
        <input value={obj.completed} onChange={e => handleNumberChange(parseInt(e.target.value))} type="text" className="mt-1 block w-full rounded-md bg-gray-100 border-transparent focus:border-gray-500 focus:bg-white focus:ring-0" />
        <button onClick={handleSubmit} className="text-gray-100 mt-2 text-xs leading-5 font-semibold bg-lime-950 rounded-full py-1 px-3 flex items-center space-x-2 hover:bg-lime-700 dark:highlight-white/5">Edit Todo</button>
      </form>
    </section>
  )
}

Database node\src\index.ts

src\index.ts

import router from './routes/proGrammingLanguagesRoute';
import apiRouter from './routes';
var cors = require('cors');
const express = require('express');
const dotenv = require('dotenv');
dotenv.config();
const app = express();
const port = process.env.PORT;
const corsOptions = {
  origin: 'http://localhost:3000',
  optionsSuccessStatus: 200 
}
app.use(cors(corsOptions));
app.use(express.static('landing-page'));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.get("/", (req: any, res: any) => {
  res.send("Lionel Start Learn Nodejs");
});
app.get('/about', (req: any, res: any) => {
  res.sendFile('./landing-page/about.html', {
    root: __dirname
  });
});
app.use("/programming-languages", router);
app.use('/api', apiRouter);
app.listen(port, () => {
  console.log(`[server]: Server is running at http://localhost:${port}`);
});

src\helper.ts

export function getOffset(currentPage = 1, listPerPage: any) {
  return (currentPage - 1) * <any>[listPerPage];
}
export function emptyOrRows(rows: any) {
  if (!rows) {
    return [];
  }
  return rows;
}

src\config.ts

const config = {
  /* don't expose password or any sensitive info, done only for demo */
  host: "localhost",
  user: "root",
  password: "",
  database: "test",
  listPerPage: 10,
};
export default config;

src\services\programmingLanguagesServic.ts

import query from "./db";
import * as helper from '../helper';
import config from "../config";
export async function getMultiple(page = 1) {
  const offset = helper.getOffset(page, config.listPerPage);
  const rows = await query(
    `SELECT id, name, released_year, githut_rank, pypl_rank, tiobe_rank FROM programming_languages LIMIT ${offset},${config.listPerPage}`
  );
  const data = helper.emptyOrRows(rows);
  const meta = { page };
  return {
    data,
    meta
  }
}
export async function create(programmingLanguage: any) {
  console.log(programmingLanguage);
  const result: any = await query(
    `INSERT INTO programming_languages (name, released_year, githut_rank, pypl_rank, tiobe_rank) VALUES ('${programmingLanguage.name}', '${programmingLanguage.released_year}', '${programmingLanguage.githut_rank}', '${programmingLanguage.pypl_rank}', '${programmingLanguage.tiobe_rank}');`
  );
  let message = 'Error in creating programming language';
  if (result.affectedRows) {
    message = 'Programming language created successfully';
  }
  return {
    message
  };
}
export async function update(id: number, programmingLanguage: any) {
  const result: any = await query(
    `UPDATE programming_languages  SET name="${programmingLanguage.name}", released_year=${programmingLanguage.released_year}, githut_rank=${programmingLanguage.githut_rank}, pypl_rank=${programmingLanguage.pypl_rank}, tiobe_rank=${programmingLanguage.tiobe_rank} WHERE id=${id}`
  );
  let message = 'Error in updating programming language';
  if (result.affectedRows) {
    message = 'Programming language updated successfully';
  }
  return { 
    message 
  };
}
export async function remove(id:number) {
  const result:any = await query(
    `DELETE FROM programming_languages WHERE id=${id}`
  );
  let message = 'Error in deleting programming language';
  if (result.affectedRows) {
    message = 'Programming language deleted successfully';
  }
  return { message };
}

src\services\db.ts

import mysql from 'mysql2/promise';
import config from "../config";
async function query(sql:any, params:any=[]) {
  const connection = await mysql.createConnection({
    host: config.host,
    user: config.user,
    password: config.password,
    database: config.database
  });
  const [results] = await connection.execute(sql, params);
  return results;
}
export default query;

src\routes\index.ts

import {Router} from 'express';
import todosRouter from './todos';
const router = Router();
router.use('/todos', todosRouter);
export default router;

src\routes\proGrammingLanguagesRoute.ts

import * as programmingLanguages from '../services/programmingLanguagesServic';
const express = require('express');
const router = express.Router();
router.get('/', async function (req: any, res: any, next: any) {
  try {
    res.json(await programmingLanguages.getMultiple(req.query.page));
  } catch (err: any) {
    console.error(`Error while getting programming languages `, err.message);
    next(err);
  }
});
router.post("/", async function (req: any, res: any, next: any) {
  try {
    res.json(await programmingLanguages.create(req.body));
  } catch (err: any) {
    console.error(`Error while getting programming languages `, err.message);
    next(err);
  }
});
router.put('/:id', async function(req: any, res: any, next: any) {
  try {
    res.json(await programmingLanguages.update(req.params.id, req.body));
  } catch (err: any) {
    console.error(`Error while updating programming language`, err.message);
    next(err);
  }
});
router.delete('/:id', async function(req: any, res: any, next: any) {
  try {
    res.json(await programmingLanguages.remove(req.params.id));
  } catch (err:any) {
    console.error(`Error while deleting programming language`, err.message);
    next(err);
  }
});
export default router;

import {Router} from 'express';
import db from '../../db';
const router = Router();
router.get('/', async (req, res) => {
  try {
    const todos = await db.todos.getAll();
    res.json(todos);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
router.post('/', async (req, res) => {
  const result:any = await db.todos.insert(req.body.description);
  res.json({message: 'Todo created',id: result.insertId});
});
router.put('/', async (req, res) => {
  try {
    console.log(req.body);
    const newTodo = req.body;
    const result = await db.todos.update(newTodo.id,newTodo.description,newTodo.isCompleted);
    res.json({ message: 'Update created', id: result })
  } catch (error) {
    console.log(error)
    res.status(500).json({ message: 'Internal Server Error', error });
  }
});
router.get('/:id', async (req, res) => {
  let id = parseInt(req.params.id);
  try {
    const [todo] = await db.todos.getOne(id);
    res.json(todo);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
router.delete('/:id', async (req, res) => {
  let id = parseInt(req.params.id);
  try {
    const [todo] = await db.todos.drop(id);
    res.json(todo);
  } catch (error) {
    console.log(error);
    res.status(500).json({
      message: 'Internal Server Error',
      error
    });
  }
});
export default router;

src\landing-page\about.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta http-equiv="X-UA-Compatible" content="ie=edge" />
    <title>Document</title>
  </head>
  <body>
    <h1>About Us</h1>
  </body>
</html>

db\index.ts

import * as todos from './tables/todos';
export default { todos }

db\tables\todos.ts

import {SelectQuery,ModifyQuery} from './queryUtils';
export interface ITodosRow {
  id: number; description: string; isCompleted: 0 | 1;
}
export function getAll() {
  return SelectQuery<ITodosRow>('SELECT * FROM todos;');
}
export function getOne(id: number) {
  return SelectQuery<ITodosRow>('SELECT * FROM todos WHERE id = ?;', [id]);
}
export function insert(todoItem: string) {
  return ModifyQuery('INSERT INTO todos (description) VALUE (?);', [todoItem])
}
export function update(id: number, description: string, isCompleted: number) {
  return ModifyQuery('UPDATE todos SET description=?,isCompleted=? WHERE id=?;', [description, isCompleted, id]);
}
export function drop(id: number) {
  return ModifyQuery('DELETE FROM todos WHERE id=?;', [id]);
}

db\tables\queryUtils.ts

import pool from './connection'; 
import type { ResultSetHeader } from 'mysql2'; 
export async function SelectQuery<T>(queryString: string, params?: any) { 
  const [results] = await pool.execute(queryString, params); 
  return results as T[]; 
} 
export async function ModifyQuery<T>(queryString: string, params?: any) {
  const [results] = await pool.query(queryString, params); 
  return results as T[]; 
}

db\tables\connection.ts

import mysql from "mysql2/promise";
export default mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'todo_app'
});

package.json

{
  "name": "node",
  "version": "1.0.0",
  "main": "src/index.ts",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "dev": "nodemon src/index.ts"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "devDependencies": {
    "@types/express": "^5.0.0",
    "@types/mysql": "^2.15.26",
    "@types/node": "^22.10.5",
    "@types/typescript": "^2.0.0",
    "dotenv": "^16.4.7",
    "express": "^4.21.2",
    "nodemon": "^3.1.9",
    "ts-node": "^10.9.2",
    "typescript": "^5.7.3"
  },
  "dependencies": {
    "@types/react": "^19.0.4",
    "@types/react-dom": "^19.0.2",
    "cors": "^2.8.5",
    "mysql2": "^3.12.0"
  }
}