# Mini APP CRUD With PostgreSQL and Node.js

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

## ***About application***

an application used to process student data which includes entering student data, viewing student data, updating student data, deleting student data and this application runs on the terminal

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

## Let’s Try With Me !!

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

## Setup

1. Prepare node.js and postgreSQL software
    
2. Create Folder and file for this project with terminal
    
    * Structure file and folder
        
        db-project/
        
        ├── utils/
        
        │ ├── db.js
        
        │ └── input.js
        
        ├── 1-create.js
        
        ├── 2-read.js
        
        ├── 3-update.js
        
        ├── 4-delete.js
        
        ├── app.js
        
        ├── .env
        
        └── .gitignore
        
    * Create Folder db-project
        
        ```powershell
        mkdir db-project
        ```
        
    * Create utils folder into db-project
        
        ```powershell
        cd db-project
        mkdir utils
        ```
        
    * Create a file for database and for input into utils folder
        
        ```powershell
        touch input.js
        touch db.js
        ```
        
    * Create a file for operation CRUD
        
        ```powershell
        cd ..
        touch 1-create.js
        touch 2-read.js
        touch 3-update.js
        touch 4-delete.js
        ```
        
    * Create a file app.js for bridge or connector to access or perform CRUD operations
        
        ```powershell
        touch app.js
        ```
        
    * Create a file with the .env extension to store confidential data.
        
        ```powershell
        touch .env
        ```
        
        extension .env is A file acting as a secret diary for your code, holding sensitive information like passwords.
        
    * Create a file .gitignore
        
        ```powershell
        touch .gitignore
        ```
        
        .gitignore It's a list for Git to **ignore** certain files and folders.
        
3. Enter to Visual Studio Code with that folder
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758678851910/d089dd7c-b8c5-4ab9-9283-f3ca8cacc591.png align="center")
    
    If you are confused about where the folder you created is, you can see it in the terminal.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758679102239/542d3508-bad2-4d82-8580-76d677e05054.png align="center")
    
    That is path or address your folder
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758678856462/e5473f57-e361-400a-8f89-7bdf5ac0b9c4.png align="center")
    
    These are the files and folders that we created earlier
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758678860051/7cf9ac12-72f8-4e65-8344-0bd8416085eb.png align="center")
    
    you can open terminal in Visual Studio Code with click ctrl + \` (Grave accent above the tab key on the keyboard)
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758678865274/8c63a34b-cc22-4804-81d6-d7bf61b483fd.png align="center")
    
4. install npm, pg and git in folder, Write this in your terminal or you can copy past
    
    ```powershell
    npm init -y # install npm
    npm install pg # install pg for connect to database
    npm install dotenv # install extension env
    git init # install git
    ```
    

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

## CODE CODE !!

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

## Prepare foundation

1. Setting or configuration npm
    
    * open file package.json
        
    * write this `‘type‘: ‘module‘,` like this
        
    
    ```javascript
    {
      "name": "db-project",
      "version": "1.0.0",
      "description": "",
      "type": "module", // HERE
      "main": "index.js",
      "scripts": {
        "test": "echo \"Error: no test specified\" && exit 1"
      },
      "keywords": [],
      "author": "",
      "license": "ISC",
      "dependencies": {
        "dotenv": "^17.2.2",
        "env": "^0.0.2",
        "pg": "^8.16.3"
      }
    }
    ```
    
2. Create database and table
    
    ```pgsql
    CREATE DATABASE name_database;
    \c name_database -- for enter to database
    
    CREATE TABLE student(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL
    );
    ```
    
3. Connect to database
    
    * Actually this is not connecting the database to the file but more about preparation
        
    * Go to file with extension `.env` and write like this
        
        ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758680055202/8f4197df-0bb1-4429-9632-da7507098fb2.png align="center")
        
4. The contents of the .gitignore file are like this
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758680201885/811af12f-7725-439b-8550-e1848349c676.png align="center")
    

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

## Create Program

* Write the program for connect to database in file db.js which is in the utils folder
    

```javascript
import pg from "pg";
import "dotenv/config";

const pool = new pg.Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

export default pool;
```

* Create function for input in file input.js which is in the utils folder
    

```javascript
import readline from 'node:readline/promises';
import { stdin as input, stdout as output } from 'node:process';

const rl = readline.createInterface({ input, output });

export async function inputData(question) {
 const answer = await rl.question(`${question}`);
 return answer.trim();
}

export async function closeInput() {
 await rl.close();
}
```

* Write the program for Create data in file 1-create.js
    

```javascript
import pool from './utils/db.js';

export async function addStudent(name, address) {
 try{
  // this code for testing code 
  const name = 'fine'
  const address = 'Tokyo'

  const queryText = 'INSERT INTO student(name, address) VALUES ($1, $2) RETURNING *';
  const res = await pool.query(queryText, [name, address]);

  console.log('new student successfully added');
  console.log(res.rows[0]);
 } catch (err) {
  console.error('fail adding student:', err.stack);
 } finally { // this block finally for testing code
  pool.close()
  closeInput()
 }
}

addStudent() // for testing code
// Run this file directly from the terminal to test it.
// If there are no issues, remove this test code. 
// Otherwise, it could cause errors later.
```

* Write the program for Read data in file 2-read.js
    

```javascript
import pool from './utils/db.js';

export async function readStudent() {
 try {
  const res = await pool.query('SELECT * FROM student ORDER BY id ASC');

  if (res.rows.length === 0) {
   console.log('no student data');
   return;
  }

  console.log('List Student:');
  res.rows.forEach(s => {
   console.log(`ID: ${s.id}, Name: ${s.name}, address: ${s.address}`);
  });
 } catch (err) {
  console.error('failed to read student data:', err.stack);
 }
}
```

* Write the program for Update data in file 3-update.js
    

```javascript
import pool from './utils/db.js';

export async function editStudent(id, reName, reAddress) {
 try {

  const queryText = 'UPDATE student SET name = $1 , address = $2 WHERE id = $3';
  const res = await pool.query(queryText, [reName, reAddress, id]);

  console.log('data edited successfully');
 } catch (err) {
  console.error('data failed to edit');
 }
}
```

* Write the program for Delete data in file 4-delete.js
    

```javascript
import pool from './utils/db.js';

export async function deleteStudent(id) {
 try {

  const queryText = 'DELETE FROM student WHERE id = $1';
  const res = await pool.query(queryText, [id]);

  console.log('Student data was successfully deleted.');
 } catch (err) {
  console.error('data failed to delete');
 }
}
```

* Write the program for app.js
    

```javascript
import { addStudent } from "./1-create.js";
import { readStudent } from "./2-read.js";
import { editStudent } from "./3-update.js";
import { deleteStudent } from "./4-delete.js";

import { closeInput, inputData } from "./utils/input.js";

import pg from "pg";

const pool = new pg.Pool({
  user: "postgres",
  host: "localhost",
  database: "dbproject1",
  password: "postgres",
  port: 5432,
});

function fitur() {
  console.log("  APP Console JS  ");
  console.log("1) Add Student");
  console.log("2) Read Student");
  console.log("3) Edit Student");
  console.log("4) Delete Student");
  console.log("0) Close");
}

async function addProgram() {
  const name = await inputData("Enter Name: ");
  const address = await inputData("Enter Address");
  await addStudent(name, address);
}

async function editProgram() {
  const id = await inputData("Enter ID: ");
  const name = await inputData("Enter New Name: ");
  const address = await inputData("Enter New Address: ");
  await editStudent(id, name, address);
}

async function deleteProgram() {
  const id = await inputData("Enter ID: ");
  await deleteStudent(id);
}

async function program() {
  while (true) {
    fitur();
    const choice = await inputData("Choose: ");
    switch (choice) {
      case "1":
        await addProgram();
        break;

      case "2":
        await readStudent();
        break;

      case "3":
        await editProgram();
        break;

      case "4":
        await deleteProgram();
        break;

      case "0":
        console.log("\nApp is Closed. Thank You.\n");
        closeInput();
        pool.end();
        return;

      default:
        console.log("Unknown choice. Please try again.\n");
    }
  }
}

program();
```

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

## Test Program

1. Test Create Fitur
    
2. Test Read Fitur
    
3. Test Update Fitur
    
4. Test Delete Fitur
    

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

### **Closing**

And that's it! 🎉 You've now successfully built a simple CRUD application. You've mastered the fundamentals: creating, reading, updating, and deleting data.

From here, you can try to:

* Add more features, such as a search function.
    
* Use a web framework like **Express.js** to build a web-based application.
    
* Create a more engaging user interface.
    

Thanks for reading! See you in the next project.

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

note : picture from [https://www.atatus.com/glossary/crud/](https://www.atatus.com/glossary/crud/)