Banco de Dados

Open in GitHub Open in Codespaces

Arquivos
invest-app-db-simple
├── package-lock.json
├── package.json
├── public
│   ├── css
│   │   └── style.css
│   ├── index.html
│   └── js
│       ├── index.js
│       ├── lib
│       │   └── format.js
│       └── services
│           └── api.js
├── public-backup
│   ├── css
│   │   └── style.css
│   ├── index.html
│   └── js
│       ├── lib
│       │   └── format.js
│       ├── main.js
│       └── services
│           └── api.js
├── requests.http
└── src
    ├── database
    │   ├── database.js
    │   ├── db.sqlite
    │   ├── load.js
    │   ├── migration.js
    │   ├── seeders.js
    │   └── seeders.json
    ├── index.js
    ├── models
    │   └── Investment.js
    └── routes.js
Arquivos
invest-app-db-simple
├── package-lock.json
├── package.json
├── public
│   ├── css
│   │   └── style.css
│   ├── index.html
│   └── js
│       ├── index.js
│       ├── lib
│       │   └── format.js
│       └── services
│           └── api.js
├── public-backup
│   ├── css
│   │   └── style.css
│   ├── index.html
│   └── js
│       ├── lib
│       │   └── format.js
│       ├── main.js
│       └── services
│           └── api.js
├── requests.http
└── src
    ├── database
    │   ├── database.js
    │   ├── db.sqlite
    │   ├── load.js
    │   ├── migration.js
    │   ├── seeders.js
    │   └── seeders.json
    ├── index.js
    ├── models
    │   └── Investment.js
    └── routes.js

Migration

sqlite-async:

$ npm remove uuid
$ npm install sqlite-async
$ npm remove uuid
$ npm install sqlite-async
/codes/expressjs/invest-app-db-simple/src/database/database.js
import { resolve } from 'node:path';
import { Database } from 'sqlite-async';
 
const dbFile = resolve('src', 'database', 'db.sqlite');
 
async function connect() {
  return await Database.open(dbFile);
}
 
export default { connect };
/codes/expressjs/invest-app-db-simple/src/database/database.js
import { resolve } from 'node:path';
import { Database } from 'sqlite-async';
 
const dbFile = resolve('src', 'database', 'db.sqlite');
 
async function connect() {
  return await Database.open(dbFile);
}
 
export default { connect };

Tabela investments:

/codes/expressjs/invest-app-db-simple/src/database/migration.js
import Database from './database.js';
 
async function up() {
  const db = await Database.connect();
 
  const investmentsSql = `
    CREATE TABLE investments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name VARCHAR(50) NOT NULL,
      value INTEGER NOT NULL
    )
  `;
 
  await db.run(investmentsSql);
}
 
export default { up };
/codes/expressjs/invest-app-db-simple/src/database/migration.js
import Database from './database.js';
 
async function up() {
  const db = await Database.connect();
 
  const investmentsSql = `
    CREATE TABLE investments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name VARCHAR(50) NOT NULL,
      value INTEGER NOT NULL
    )
  `;
 
  await db.run(investmentsSql);
}
 
export default { up };
/codes/expressjs/invest-app-db-simple/src/database/load.js
import Migration from './migration.js';
import Seed from './seeders.js';
 
async function load() {
  await Migration.up();
  await Seed.up();
}
 
load();
/codes/expressjs/invest-app-db-simple/src/database/load.js
import Migration from './migration.js';
import Seed from './seeders.js';
 
async function load() {
  await Migration.up();
  await Seed.up();
}
 
load();
/codes/expressjs/invest-app-db-simple/package.json
{
  "name": "invest-app",
  "type": "module",
  "scripts": {
    "start": "node src/index.js",
    "dev": "node --watch src/index.js",
    "db:reload": "npm run db:drop && npm run db:load",
    "db:load": "node src/database/load.js",
    "db:drop": "rm src/database/db.sqlite"
  },
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.18.2",
    "express-async-errors": "^3.1.1",
    "morgan": "^1.10.0",
    "sqlite-async": "^1.2.0"
  }
}
/codes/expressjs/invest-app-db-simple/package.json
{
  "name": "invest-app",
  "type": "module",
  "scripts": {
    "start": "node src/index.js",
    "dev": "node --watch src/index.js",
    "db:reload": "npm run db:drop && npm run db:load",
    "db:load": "node src/database/load.js",
    "db:drop": "rm src/database/db.sqlite"
  },
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.18.2",
    "express-async-errors": "^3.1.1",
    "morgan": "^1.10.0",
    "sqlite-async": "^1.2.0"
  }
}
 
$ npm run db:load
$ npm run db:load

Para visualizar o db.sqlite use o plugin vscode-sqlite ou sqlite-viewer.

Model

/codes/expressjs/invest-app-db-simple/src/models/Investment.js
import Database from '../database/database.js';
 
async function create({ name, value }) {
  const db = await Database.connect();
 
  if (name && value) {
    const sql = `
      INSERT INTO
        investments (name, value)
      VALUES
        (?, ?)
    `;
 
    const { lastID } = await db.run(sql, [name, value]);
 
    return await readById(lastID);
  } else {
    throw new Error('Unable to create investment');
  }
}
 
async function read(field, value) {
  const db = await Database.connect();
 
  if (field && value) {
    const sql = `
      SELECT
          id, name, value
        FROM
          investments
        WHERE
          ${field} = '?'
      `;
 
    const investments = await db.all(sql, [value]);
 
    return investments;
  }
 
  const sql = `
    SELECT
      id, name, value
    FROM
      investments
  `;
 
  const investments = await db.all(sql);
 
  return investments;
}
 
async function readById(id) {
  const db = await Database.connect();
 
  if (id) {
    const sql = `
      SELECT
          id, name, value
        FROM
          investments
        WHERE
          id = ?
      `;
 
    const investment = await db.get(sql, [id]);
 
    if (investment) {
      return investment;
    } else {
      throw new Error('Investment not found');
    }
  } else {
    throw new Error('Unable to find investment');
  }
}
 
async function update({ id, name, value }) {
  const db = await Database.connect();
 
  if (name && value && id) {
    const sql = `
      UPDATE
        investments
      SET
        name = ?, value = ?
      WHERE
        id = ?
    `;
 
    const { changes } = await db.run(sql, [name, value, id]);
 
    if (changes === 1) {
      return readById(id);
    } else {
      throw new Error('Investment not found');
    }
  } else {
    throw new Error('Unable to update investment');
  }
}
 
async function remove(id) {
  const db = await Database.connect();
 
  if (id) {
    const sql = `
      DELETE FROM
        investments
      WHERE
        id = ?
    `;
 
    const { changes } = await db.run(sql, [id]);
 
    if (changes === 1) {
      return true;
    } else {
      throw new Error('Investment not found');
    }
  } else {
    throw new Error('Investment not found');
  }
}
 
export default { create, read, readById, update, remove };
/codes/expressjs/invest-app-db-simple/src/models/Investment.js
import Database from '../database/database.js';
 
async function create({ name, value }) {
  const db = await Database.connect();
 
  if (name && value) {
    const sql = `
      INSERT INTO
        investments (name, value)
      VALUES
        (?, ?)
    `;
 
    const { lastID } = await db.run(sql, [name, value]);
 
    return await readById(lastID);
  } else {
    throw new Error('Unable to create investment');
  }
}
 
async function read(field, value) {
  const db = await Database.connect();
 
  if (field && value) {
    const sql = `
      SELECT
          id, name, value
        FROM
          investments
        WHERE
          ${field} = '?'
      `;
 
    const investments = await db.all(sql, [value]);
 
    return investments;
  }
 
  const sql = `
    SELECT
      id, name, value
    FROM
      investments
  `;
 
  const investments = await db.all(sql);
 
  return investments;
}
 
async function readById(id) {
  const db = await Database.connect();
 
  if (id) {
    const sql = `
      SELECT
          id, name, value
        FROM
          investments
        WHERE
          id = ?
      `;
 
    const investment = await db.get(sql, [id]);
 
    if (investment) {
      return investment;
    } else {
      throw new Error('Investment not found');
    }
  } else {
    throw new Error('Unable to find investment');
  }
}
 
async function update({ id, name, value }) {
  const db = await Database.connect();
 
  if (name && value && id) {
    const sql = `
      UPDATE
        investments
      SET
        name = ?, value = ?
      WHERE
        id = ?
    `;
 
    const { changes } = await db.run(sql, [name, value, id]);
 
    if (changes === 1) {
      return readById(id);
    } else {
      throw new Error('Investment not found');
    }
  } else {
    throw new Error('Unable to update investment');
  }
}
 
async function remove(id) {
  const db = await Database.connect();
 
  if (id) {
    const sql = `
      DELETE FROM
        investments
      WHERE
        id = ?
    `;
 
    const { changes } = await db.run(sql, [id]);
 
    if (changes === 1) {
      return true;
    } else {
      throw new Error('Investment not found');
    }
  } else {
    throw new Error('Investment not found');
  }
}
 
export default { create, read, readById, update, remove };

Router

/codes/expressjs/invest-app-db-simple/src/routes.js
import express from 'express';
import Investment from './models/Investment.js';
 
class HTTPError extends Error {
  constructor(message, code) {
    super(message);
    this.code = code;
  }
}
 
const router = express.Router();
 
router.post('/investments', async (req, res) => {
  try {
    const investment = req.body;
 
    const createdInvestment = await Investment.create(investment);
 
    return res.json(createdInvestment);
  } catch (error) {
    throw new HTTPError('Unable to create investment', 400);
  }
});
 
router.get('/investments', async (req, res) => {
  try {
    const { name } = req.query;
 
    let investments;
 
    if (name) {
      investments = await Investment.read('name', name);
    } else {
      investments = await Investment.read();
    }
 
    return res.json(investments);
  } catch (error) {
    throw new HTTPError('Unable to read investments', 400);
  }
});
 
router.get('/investments/:id', async (req, res) => {
  try {
    const id = Number(req.params.id);
 
    const investment = await Investment.readById(id);
 
    return res.json(investment);
  } catch (error) {
    throw new HTTPError('Unable to find investment', 400);
  }
});
 
router.put('/investments/:id', async (req, res) => {
  try {
    const investment = req.body;
 
    const id = Number(req.params.id);
 
    const updatedInvestment = await Investment.update({ ...investment, id });
 
    return res.json(updatedInvestment);
  } catch (error) {
    throw new HTTPError('Unable to update investment', 400);
  }
});
 
router.delete('/investments/:id', async (req, res) => {
  const id = Number(req.params.id);
 
  if (await Investment.remove(id)) {
    return res.sendStatus(204);
  } else {
    throw new HTTPError('Unable to remove investment', 400);
  }
});
 
// 404 handler
router.use((req, res, next) => {
  return res.status(404).json({ message: 'Content not found!' });
});
 
// Error handler
router.use((err, req, res, next) => {
  // console.error(err.stack);
  if (err instanceof HTTPError) {
    return res.status(err.code).json({ message: err.message });
  } else {
    return res.status(500).json({ message: 'Something broke!' });
  }
});
 
export default router;
/codes/expressjs/invest-app-db-simple/src/routes.js
import express from 'express';
import Investment from './models/Investment.js';
 
class HTTPError extends Error {
  constructor(message, code) {
    super(message);
    this.code = code;
  }
}
 
const router = express.Router();
 
router.post('/investments', async (req, res) => {
  try {
    const investment = req.body;
 
    const createdInvestment = await Investment.create(investment);
 
    return res.json(createdInvestment);
  } catch (error) {
    throw new HTTPError('Unable to create investment', 400);
  }
});
 
router.get('/investments', async (req, res) => {
  try {
    const { name } = req.query;
 
    let investments;
 
    if (name) {
      investments = await Investment.read('name', name);
    } else {
      investments = await Investment.read();
    }
 
    return res.json(investments);
  } catch (error) {
    throw new HTTPError('Unable to read investments', 400);
  }
});
 
router.get('/investments/:id', async (req, res) => {
  try {
    const id = Number(req.params.id);
 
    const investment = await Investment.readById(id);
 
    return res.json(investment);
  } catch (error) {
    throw new HTTPError('Unable to find investment', 400);
  }
});
 
router.put('/investments/:id', async (req, res) => {
  try {
    const investment = req.body;
 
    const id = Number(req.params.id);
 
    const updatedInvestment = await Investment.update({ ...investment, id });
 
    return res.json(updatedInvestment);
  } catch (error) {
    throw new HTTPError('Unable to update investment', 400);
  }
});
 
router.delete('/investments/:id', async (req, res) => {
  const id = Number(req.params.id);
 
  if (await Investment.remove(id)) {
    return res.sendStatus(204);
  } else {
    throw new HTTPError('Unable to remove investment', 400);
  }
});
 
// 404 handler
router.use((req, res, next) => {
  return res.status(404).json({ message: 'Content not found!' });
});
 
// Error handler
router.use((err, req, res, next) => {
  // console.error(err.stack);
  if (err instanceof HTTPError) {
    return res.status(err.code).json({ message: err.message });
  } else {
    return res.status(500).json({ message: 'Something broke!' });
  }
});
 
export default router;

Editar esta página