angular-19-tutorials: #8.4 CRUD- SQL Lite (WIP.....)

Angular [<19] CRUD application to store data in SQLite instead of localStorage, follow these steps:

🛠 Steps to Implement SQLite in Angular

Set up an SQLite database using an API (Node.js + Express + SQLite3).
Modify the Angular service to perform CRUD operations using HTTP requests.
Update the component to call the service instead of using localStorage.

📌 Step 1: Set Up SQLite API (Node.js + Express + SQLite3)

We'll create an Express API that interacts with SQLite.

1️⃣ Install Dependencies

Run this in your backend folder:

npm init -y
npm install express sqlite3 cors body-parser

2️⃣ Create `server.js` (Express API)

const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const cors = require('cors');
const bodyParser = require('body-parser');

const app = express();
const port = 3000;

app.use(cors());
app.use(bodyParser.json());

// Connect to SQLite
const db = new sqlite3.Database('./employees.db', sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
    if (err) console.error(err.message);
    console.log('Connected to SQLite database.');
});

// Create Employee Table
db.run(`CREATE TABLE IF NOT EXISTS employees (
    empid INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    contactNo TEXT,
    emailId TEXT,
    city TEXT,
    state TEXT,
    pinCode TEXT,
    address TEXT
)`);

// Get all employees
app.get('/employees', (req, res) => {
    db.all('SELECT * FROM employees', [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

// Add a new employee
app.post('/employees', (req, res) => {
    const { name, contactNo, emailId, city, state, pinCode, address } = req.body;
    db.run(`INSERT INTO employees (name, contactNo, emailId, city, state, pinCode, address) VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [name, contactNo, emailId, city, state, pinCode, address],
        function (err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ empid: this.lastID });
        }
    );
});

// Update an employee
app.put('/employees/:id', (req, res) => {
    const { name, contactNo, emailId, city, state, pinCode, address } = req.body;
    const { id } = req.params;
    db.run(`UPDATE employees SET name=?, contactNo=?, emailId=?, city=?, state=?, pinCode=?, address=? WHERE empid=?`,
        [name, contactNo, emailId, city, state, pinCode, address, id],
        function (err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ updated: this.changes });
        }
    );
});

// Delete an employee
app.delete('/employees/:id', (req, res) => {
    const { id } = req.params;
    db.run('DELETE FROM employees WHERE empid = ?', [id], function (err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ deleted: this.changes });
    });
});

// Start server
app.listen(port, () => {
    console.log(`Server running at http://localhost:${port}`);
});

Run the API:

node server.js

📌 Step 2: Modify Angular Service (`employee.service.ts`)

We will replace localStorage with HTTP API calls.

Create `employee.service.ts`

import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';

export interface Employee {
  empid?: number;
  name: string;
  contactNo: string;
  emailId: string;
  city: string;
  state: string;
  pinCode: string;
  address: string;
}

@Injectable({
  providedIn: 'root'
})
export class EmployeeService {
  private apiUrl = 'http://localhost:3000/employees';

  constructor(private http: HttpClient) {}

  getEmployees(): Observable<Employee[]> {
    return this.http.get<Employee[]>(this.apiUrl);
  }

  addEmployee(employee: Employee): Observable<any> {
    return this.http.post(this.apiUrl, employee);
  }

  updateEmployee(employee: Employee): Observable<any> {
    return this.http.put(`${this.apiUrl}/${employee.empid}`, employee);
  }

  deleteEmployee(empid: number): Observable<any> {
    return this.http.delete(`${this.apiUrl}/${empid}`);
  }
}

📌 Step 3: Modify `app.component.ts`

Replace Local Data with API Calls

import { Component, OnInit } from '@angular/core';
import { FormControl, FormGroup, Validators } from '@angular/forms';
import { EmployeeService, Employee } from './employee.service';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent implements OnInit {
  
  employeeForm: FormGroup;
  employeeList: Employee[] = [];
  selectedEmployee: Employee | null = null;

  constructor(private employeeService: EmployeeService) {
    this.createForm();
  }

  ngOnInit() {
    this.loadEmployees();
  }

  createForm() {
    this.employeeForm = new FormGroup({
      empid: new FormControl(null),
      name: new FormControl('', [Validators.required]),
      contactNo: new FormControl(''),
      emailId: new FormControl(''),
      city: new FormControl(''),
      state: new FormControl(''),
      pinCode: new FormControl('', [Validators.required, Validators.minLength(6)]),
      address: new FormControl('')
    });
  }

  loadEmployees() {
    this.employeeService.getEmployees().subscribe(data => {
      this.employeeList = data;
    });
  }

  onSave() {
    if (this.selectedEmployee) {
      this.employeeService.updateEmployee(this.employeeForm.value).subscribe(() => {
        this.loadEmployees();
        this.reset();
      });
    } else {
      this.employeeService.addEmployee(this.employeeForm.value).subscribe(() => {
        this.loadEmployees();
        this.reset();
      });
    }
  }

  onEdit(employee: Employee) {
    this.selectedEmployee = employee;
    this.employeeForm.patchValue(employee);
  }

  onDelete(empid: number) {
    if (confirm("Are you sure you want to delete?")) {
      this.employeeService.deleteEmployee(empid).subscribe(() => {
        this.loadEmployees();
      });
    }
  }

  reset() {
    this.employeeForm.reset();
    this.selectedEmployee = null;
  }
}

📌 Step 4: Update `app.module.ts`

Ensure you import HttpClientModule.

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import { ReactiveFormsModule } from '@angular/forms';
import { HttpClientModule } from '@angular/common/http';

@NgModule({
  declarations: [AppComponent],
  imports: [BrowserModule, ReactiveFormsModule, HttpClientModule],
  providers: [],
  bootstrap: [AppComponent]
})
export class AppModule { }

🎯 Final Steps

Start the SQLite API
```
node server.js
```
Run Angular App
```
ng serve
```
Test CRUD operations in the UI 🎉

🚀 What We Changed

✅ Replaced localStorage with an SQLite database
✅ Created a Node.js API with SQLite for CRUD operations
✅ Updated Angular to use HTTP services

This setup provides a real database instead of localStorage, making the app persistent and scalable. Let me know if you need more modifications! 🚀🔥

====================================================================

Since you're using Angular 19, the core structure remains the same, but there are some minor updates in best practices, TypeScript versions, and RxJS changes. I'll provide the updated Angular 19 implementation with SQLite using a Node.js backend.

🛠 Steps to Implement SQLite with Angular 19

Set up an SQLite database with an API (Node.js + Express + SQLite3).
Modify Angular Service using HttpClient to perform CRUD operations.
Update the Component and Forms using the latest Angular 19 best practices.
Ensure compatibility with RxJS changes (tap(), subscribe() syntax, etc.).

📌 Step 1: Set Up SQLite API (Node.js + Express + SQLite3)

1️⃣ Install Dependencies

Run the following command inside the backend folder:

npm init -y
npm install express sqlite3 cors body-parser dotenv

2️⃣ Create `server.js` (Express API)

require('dotenv').config();
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const cors = require('cors');
const bodyParser = require('body-parser');

const app = express();
const port = process.env.PORT || 3000;

app.use(cors());
app.use(bodyParser.json());

// Connect to SQLite
const db = new sqlite3.Database('./employees.db', sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
    if (err) console.error(err.message);
    console.log('Connected to SQLite database.');
});

// Create Employee Table
db.run(`CREATE TABLE IF NOT EXISTS employees (
    empid INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    contactNo TEXT NOT NULL,
    emailId TEXT NOT NULL,
    city TEXT,
    state TEXT,
    pinCode TEXT,
    address TEXT
)`);

// Get all employees
app.get('/employees', (req, res) => {
    db.all('SELECT * FROM employees', [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

// Add a new employee
app.post('/employees', (req, res) => {
    const { name, contactNo, emailId, city, state, pinCode, address } = req.body;
    db.run(`INSERT INTO employees (name, contactNo, emailId, city, state, pinCode, address) VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [name, contactNo, emailId, city, state, pinCode, address],
        function (err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ empid: this.lastID });
        }
    );
});

// Update an employee
app.put('/employees/:id', (req, res) => {
    const { name, contactNo, emailId, city, state, pinCode, address } = req.body;
    const { id } = req.params;
    db.run(`UPDATE employees SET name=?, contactNo=?, emailId=?, city=?, state=?, pinCode=?, address=? WHERE empid=?`,
        [name, contactNo, emailId, city, state, pinCode, address, id],
        function (err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ updated: this.changes });
        }
    );
});

// Delete an employee
app.delete('/employees/:id', (req, res) => {
    const { id } = req.params;
    db.run('DELETE FROM employees WHERE empid = ?', [id], function (err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ deleted: this.changes });
    });
});

// Start server
app.listen(port, () => {
    console.log(`Server running at http://localhost:${port}`);
});

Run the API

node server.js

📌 Step 2: Modify Angular Service (`employee.service.ts`)

We will replace localStorage with HTTP API calls.

Create `employee.service.ts`

import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';

export interface Employee {
  empid?: number;
  name: string;
  contactNo: string;
  emailId: string;
  city: string;
  state: string;
  pinCode: string;
  address: string;
}

@Injectable({
  providedIn: 'root'
})
export class EmployeeService {
  private apiUrl = 'http://localhost:3000/employees';

  constructor(private http: HttpClient) {}

  getEmployees(): Observable<Employee[]> {
    return this.http.get<Employee[]>(this.apiUrl);
  }

  addEmployee(employee: Employee): Observable<any> {
    return this.http.post(this.apiUrl, employee);
  }

  updateEmployee(employee: Employee): Observable<any> {
    return this.http.put(`${this.apiUrl}/${employee.empid}`, employee);
  }

  deleteEmployee(empid: number): Observable<any> {
    return this.http.delete(`${this.apiUrl}/${empid}`);
  }
}

📌 Step 3: Modify `app.component.ts`

Replace Local Data with API Calls

import { Component, OnInit } from '@angular/core';
import { FormControl, FormGroup, Validators } from '@angular/forms';
import { EmployeeService, Employee } from './employee.service';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent implements OnInit {
  
  employeeForm!: FormGroup;
  employeeList: Employee[] = [];
  selectedEmployee: Employee | null = null;

  constructor(private employeeService: EmployeeService) {}

  ngOnInit() {
    this.createForm();
    this.loadEmployees();
  }

  createForm() {
    this.employeeForm = new FormGroup({
      empid: new FormControl(null),
      name: new FormControl('', [Validators.required]),
      contactNo: new FormControl('', Validators.required),
      emailId: new FormControl('', Validators.required),
      city: new FormControl(''),
      state: new FormControl(''),
      pinCode: new FormControl('', [Validators.required, Validators.minLength(6)]),
      address: new FormControl('')
    });
  }

  loadEmployees() {
    this.employeeService.getEmployees().subscribe(data => {
      this.employeeList = data;
    });
  }

  onSave() {
    if (this.selectedEmployee) {
      this.employeeService.updateEmployee(this.employeeForm.value).subscribe(() => {
        this.loadEmployees();
        this.reset();
      });
    } else {
      this.employeeService.addEmployee(this.employeeForm.value).subscribe(() => {
        this.loadEmployees();
        this.reset();
      });
    }
  }

  onEdit(employee: Employee) {
    this.selectedEmployee = employee;
    this.employeeForm.patchValue(employee);
  }

  onDelete(empid: number) {
    if (confirm("Are you sure you want to delete?")) {
      this.employeeService.deleteEmployee(empid).subscribe(() => {
        this.loadEmployees();
      });
    }
  }

  reset() {
    this.employeeForm.reset();
    this.selectedEmployee = null;
  }
}

📌 Step 4: Update `app.module.ts`

Ensure you import HttpClientModule.

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import { ReactiveFormsModule } from '@angular/forms';
import { HttpClientModule } from '@angular/common/http';

@NgModule({
  declarations: [AppComponent],
  imports: [BrowserModule, ReactiveFormsModule, HttpClientModule],
  providers: [],
  bootstrap: [AppComponent]
})
export class AppModule { }

🎯 Final Steps

Start the SQLite API
```
node server.js
```
Run Angular App
```
ng serve
```
Test CRUD operations in the UI 🎉

🚀 What We Updated for Angular 19

✅ Removed any in TypeScript
✅ Ensured strict types for RxJS (tap(), subscribe())
✅ Used ! operator to avoid null issues
✅ Modernized form validation

Now your Angular 19 app works with SQLite via Node.js! 🎯🚀 Let me know if you need any changes. 🔥

angular-19-tutorials

Tuesday, March 11, 2025

#8.4 CRUD- SQL Lite (WIP.....)

🛠 Steps to Implement SQLite in Angular

📌 Step 1: Set Up SQLite API (Node.js + Express + SQLite3)

1️⃣ Install Dependencies

2️⃣ Create `server.js` (Express API)

📌 Step 2: Modify Angular Service (`employee.service.ts`)

Create `employee.service.ts`

📌 Step 3: Modify `app.component.ts`

Replace Local Data with API Calls

📌 Step 4: Update `app.module.ts`

🎯 Final Steps

🚀 What We Changed

🛠 Steps to Implement SQLite with Angular 19

📌 Step 1: Set Up SQLite API (Node.js + Express + SQLite3)

1️⃣ Install Dependencies

2️⃣ Create `server.js` (Express API)

📌 Step 2: Modify Angular Service (`employee.service.ts`)

Create `employee.service.ts`

📌 Step 3: Modify `app.component.ts`

Replace Local Data with API Calls

📌 Step 4: Update `app.module.ts`

🎯 Final Steps

🚀 What We Updated for Angular 19

No comments:

Post a Comment

#11 @Output

Report Abuse

Tuesday, March 11, 2025

#8.4 CRUD- SQL Lite (WIP.....)

🛠 Steps to Implement SQLite in Angular

📌 Step 1: Set Up SQLite API (Node.js + Express + SQLite3)

1️⃣ Install Dependencies

2️⃣ Create server.js (Express API)

📌 Step 2: Modify Angular Service (employee.service.ts)

Create employee.service.ts

📌 Step 3: Modify app.component.ts

Replace Local Data with API Calls

📌 Step 4: Update app.module.ts

🎯 Final Steps

🚀 What We Changed

🛠 Steps to Implement SQLite with Angular 19

📌 Step 1: Set Up SQLite API (Node.js + Express + SQLite3)

1️⃣ Install Dependencies

2️⃣ Create server.js (Express API)

📌 Step 2: Modify Angular Service (employee.service.ts)

Create employee.service.ts

📌 Step 3: Modify app.component.ts

Replace Local Data with API Calls

📌 Step 4: Update app.module.ts

🎯 Final Steps

🚀 What We Updated for Angular 19

No comments:

Post a Comment

#11 @Output

2️⃣ Create `server.js` (Express API)

📌 Step 2: Modify Angular Service (`employee.service.ts`)

Create `employee.service.ts`

📌 Step 3: Modify `app.component.ts`

📌 Step 4: Update `app.module.ts`

2️⃣ Create `server.js` (Express API)

📌 Step 2: Modify Angular Service (`employee.service.ts`)

Create `employee.service.ts`

📌 Step 3: Modify `app.component.ts`

📌 Step 4: Update `app.module.ts`