Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

using sqlite as database for angular v7

I am building a small SPA with angular and since I am new to angular, I am using the angular tutorial as a road map for my application. all things are working find until the retrieving data from sqlite database (due to implementation restriction, use of any other type of backend code is not possible). the tutorial (https://angular.io/tutorial/toh-pt6) uses a in-memory-database which is obviously is not fitted for me. I want to have service that retrieves data from sqlite and could be injected to component

component

   import { Component, OnInit } from '@angular/core';
   import { Areport } from '../../classess/areport';
   import { ReportService } from "../../services/report.service";
@Component({
  selector: 'app-daily-report',
  templateUrl: './daily-report.component.html',
  styleUrls: ['./daily-report.component.css']
})
export class DailyReportComponent implements OnInit {
  reports : Areport[];
  constructor(private reportService: ReportService) { }
  getReports(): void {
    this.reportService.getReports()
        .subscribe( reports => this.reports = reports)
  }
  ngOnInit() {
    this.getReports();
  }
}

reportService

import { Injectable } from '@angular/core'; 
import { Observable, of } from "rxjs";
import { Areport } from "../classess/areport";
import { MessageService } from "./message.service";
import { SqlitedbService } from "./sqlitedb.service";

@Injectable({   providedIn: 'root' })


export class ReportService {

  constructor( private messageService: MessageService) { } 
  getReports(): Observable<Areport[]>{
    this.messageService.add('reportService: fetched reports');
    return of(SqlitedbService.R()); 


} 

sqlite service

import { Injectable } from '@angular/core';
const  sqlite3 = require('sqlite3').verbose();

let db = new sqlite3.Database('../../db/my.db');
let reports : any
db.all("SELECT * FROM reports",[],(err:any,rows:any)=>{
  reports=rows;
})

@Injectable({
  providedIn: 'root'
})
export class SqlitedbService {

  constructor(){}
  R(){ return reports};
}

i couldn't find any type of tutorial for using sqlite in angular excet this (https://github.com/leota/electron-angular4-sqlite3/blob/master/src/app/app.component.ts) which i have no idea what is it doing and or neither worth trying to add more layers such as electron

the ideal for me would be some type of class function that i can use in my service for returning some query result and performing insert statement

also this piece of js code works fine in node but i don't know how to use it in angular

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('./my.db')
db.each("SELECT * FROM reports", function(err, row)  {
  console.log(err+"   "+row.id + ": " + row.txt);
    });
like image 568
morteza kiaee Avatar asked Mar 31 '19 07:03

morteza kiaee


People also ask

Is SQLite good for database?

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

Can I use SQLite instead of MySQL?

As we mentioned above, SQLite is serverless whereas MySQL is not. Therefore, if portability is of a concern, go with SQLite. This makes the transfer of all your database data much more efficient as it is stored in a single file.

Can I use SQLite with Flask?

In this tutorial, you'll build a small web application that demonstrates how to use SQLite with Flask to perform basic data manipulation covering CRUD: Create, Read, Update, and Delete. The web application will be a basic blog that displays posts on the index page. Users can create, edit, and delete individual posts.

Why SQLite is not used in production?

SQLite doesn't support any kind of concurrency, so you may have problems running it on a production website.


1 Answers

I have found the following tutorial : https://appdividend.com/2019/06/04/angular-8-tutorial-with-example-learn-angular-8-crud-from-scratch/

In this tutorial, he use mongoDb, but I have replaced mongodb with sqlite3 this way :

  1. sqlite must be in backend of your app. So create an api folder at the root of your project and do npm init -y

  2. npm install express body-parser cors --save

  3. install sqlite npm install sqlite3 --save
  4. install nodemon : npm install nodemon --save-dev and npm install -g nodemon
  5. create a server.js
const express = require('express'),
path = require('path'),
bodyParser = require('body-parser'),
cors = require('cors'),
sqlite3 = require('sqlite3').verbose();

var db = new sqlite3.Database(':memory:');

db.serialize(function () {
    db.run("CREATE TABLE lorem (info TEXT)");

    var stmt = db.prepare("INSERT INTO lorem VALUES (?)");
    for (var i = 0; i < 10; i++) {
        stmt.run("Ipsum " + i);
    }

    stmt.finalize();

    db.each("SELECT rowid AS id, info FROM lorem", function (err, row) {
        console.log(row.id + ": " + row.info);
    });
});

db.close();

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

const server = app.listen(function () {
    console.log('Listening on port ' + port);
});
  1. launch your server with nodemon ./server.js and the result is :

Listening on port 4000 1: Ipsum 0 2: Ipsum 1 3: Ipsum 2 4: Ipsum 3 5: Ipsum 4 6: Ipsum 5 7: Ipsum 6 8: Ipsum 7 9: Ipsum 8 10: Ipsum 9

like image 190
AliasBonux Avatar answered Nov 13 '22 07:11

AliasBonux