Lesson 38-Express Database Integration

Express Integration with Mainstream Databases

Integrating with Relational Databases – MySQL

MySQL is a popular relational database management system, ideal for scenarios requiring transaction support and strict data consistency.

Install Dependencies

npm install mysql2

Connect to the Database

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
}).promise();

Create a Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Insert Data

async function insertUser(name, email) {
    const [result] = await pool.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email]);
    return result.insertId;
}

insertUser('John Doe', 'john@example.com')
    .then(id => console.log(`Inserted user with ID: ${id}`))
    .catch(err => console.error('Error inserting user:', err));

Query Data

async function getUsers() {
    const [rows] = await pool.query('SELECT * FROM users');
    return rows;
}

getUsers().then(users => console.log(users))
    .catch(err => console.error('Error fetching users:', err));

Integrating with Relational Databases – PostgreSQL

PostgreSQL is another robust relational database known for its stability, rich feature set, and strong open-source community support.

Install Dependencies

npm install pg

Connect to the Database

const { Pool } = require('pg');

const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'mydatabase',
    password: 'password',
    port: 5432,
});

Create a Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Insert Data

async function insertUser(name, email) {
    const client = await pool.connect();
    try {
        const result = await client.query('INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id', [name, email]);
        return result.rows[0].id;
    } finally {
        client.release();
    }
}

insertUser('Jane Doe', 'jane@example.com')
    .then(id => console.log(`Inserted user with ID: ${id}`))
    .catch(err => console.error('Error inserting user:', err));

Query Data

async function getUsers() {
    const client = await pool.connect();
    try {
        const result = await client.query('SELECT * FROM users');
        return result.rows;
    } finally {
        client.release();
    }
}

getUsers().then(users => console.log(users))
    .catch(err => console.error('Error fetching users:', err));

Integrating with Non-Relational Databases – MongoDB

MongoDB is a popular NoSQL database, particularly suited for handling large amounts of unstructured data.

Install Dependencies

npm install mongodb

Connect to the Database

const MongoClient = require('mongodb').MongoClient;
const uri = "mongodb://localhost:27017/mydatabase";

const client = new MongoClient(uri, { useNewUrlParser: true, useUnifiedTopology: true });
client.connect(err => {
    const collection = client.db("mydatabase").collection("users");
    // Perform actions on the collection object
    client.close();
});

Insert Data

async function insertUser(name, email) {
    const client = await MongoClient.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true });
    const collection = client.db("mydatabase").collection("users");
    const result = await collection.insertOne({ name, email });
    client.close();
    return result.insertedId;
}

insertUser('Alice Smith', 'alice@example.com')
    .then(id => console.log(`Inserted user with ID: ${id}`))
    .catch(err => console.error('Error inserting user:', err));

Query Data

async function getUsers() {
    const client = await MongoClient.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true });
    const collection = client.db("mydatabase").collection("users");
    const result = await collection.find({}).toArray();
    client.close();
    return result;
}

getUsers().then(users => console.log(users))
    .catch(err => console.error('Error fetching users:', err));

Database Connection Pool

A connection pool improves database operation performance by reusing existing database connections, avoiding the overhead of creating and destroying connections for each request.

Using mysql2 Connection Pool

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
}).promise();

Async/Await

The async/await syntax makes asynchronous code more readable and manageable. When handling database operations, async/await simplifies code and avoids callback hell.

async function getUserByEmail(email) {
    const [rows] = await pool.query('SELECT * FROM users WHERE email = ?', [email]);
    return rows[0];
}

async function main() {
    const user = await getUserByEmail('john@example.com');
    console.log(user);
}

main().catch(console.error);

Transaction Handling

For business logic involving multiple database operations, transactions ensure data consistency and integrity. If any operation in a transaction fails, the entire transaction is rolled back to prevent inconsistent states.

Using Sequelize

const transaction = await sequelize.transaction();
try {
    const user = await User.create({ name: 'John Doe', email: 'john@example.com' }, { transaction });
    // Additional operations...
    await transaction.commit();
} catch (error) {
    await transaction.rollback();
}

Express Integration with TypeORM

Install Required Packages

First, install TypeORM, the appropriate database driver, Express, and other dependencies. Assuming MySQL is used, the installation is as follows:

npm install express typeorm reflect-metadata mysql2

Here, reflect-metadata is a polyfill required by TypeORM to support TypeScript metadata reflection.

Create Entities

Entities in TypeORM are classes that represent database tables, defining their structure and properties.

entities/User.ts

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column()
    email: string;
}

Configure TypeORM

Configure TypeORM to connect to the database using a JSON file or TypeScript class.

ormconfig.json

{
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "username": "your_username",
    "password": "your_password",
    "database": "your_database",
    "entities": ["src/entities/*.ts"],
    "synchronize": true,
    "logging": false,
    "cli": {
        "entitiesDir": "src/entities"
    }
}

Note: Setting synchronize to true automatically syncs entity classes with the database schema, but in production, disable this to avoid accidental data loss.

Initialize TypeORM

In your main application file, initialize TypeORM using createConnection or getConnectionOptions to establish a database connection.

index.ts

import 'reflect-metadata';
import express from 'express';
import { createConnection } from 'typeorm';
import { User } from './entities/User';

const app = express();

createConnection().then(async connection => {
    console.log('Connected to database.');

    // Create a user
    const user = new User();
    user.name = 'John Doe';
    user.email = 'john.doe@example.com';
    await connection.manager.save(user);

    // Query users
    const users = await connection.manager.find(User);
    console.log(users);

    // Start Express server
    app.listen(3000, () => {
        console.log('Server is running on port 3000.');
    });
}).catch(error => console.log(error));

Create Routes and Controllers

Create routes and controllers to handle HTTP requests.

routes/users.ts

import express from 'express';
import { getRepository } from 'typeorm';
import { User } from '../entities/User';

const router = express.Router();

router.get('/', async (req, res) => {
    const userRepository = getRepository(User);
    const users = await userRepository.find();
    res.json(users);
});

router.post('/', async (req, res) => {
    const userRepository = getRepository(User);
    const user = userRepository.create(req.body);
    await userRepository.save(user);
    res.status(201).send('User created');
});

export default router;

index.ts

import express from 'express';
import usersRouter from './routes/users';

const app = express();
app.use(express.json());
app.use('/api/users', usersRouter);

// ... Other code ...

Run the Application

Run your Express application, ensuring the database service is active:

node index.js

Express Integration with Sequelize

Install Required Packages

Install Sequelize and the appropriate database driver. For MySQL, the installation is as follows:

npm install express sequelize mysql2

Initialize Sequelize

Initialize Sequelize and configure the database connection in your Express application. Create a file, e.g., db.js, for configuration.

db.js

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql',
    logging: false, // Set to console.log to view logs
});

module.exports = sequelize;

Define Models

Define models, which are Sequelize objects representing database tables.

models/user.js

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = require('./db');

const User = sequelize.define('User', {
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    email: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: false
    }
}, {
    timestamps: true,
    underscored: false
});

module.exports = User;

Sync Models

In your main application file, sync models to create or update database tables using sequelize.sync().

index.js

const express = require('express');
const app = express();
const sequelize = require('./db');
const User = require('./models/user');

sequelize.sync().then(() => {
    console.log('Database & tables created!');
});

app.get('/users', async (req, res) => {
    const users = await User.findAll();
    res.json(users);
});

app.post('/users', async (req, res) => {
    const user = await User.create(req.body);
    res.status(201).json(user);
});

app.listen(3000, () => {
    console.log('Server started on http://localhost:3000');
});

Perform CRUD Operations with Models

Sequelize provides methods for CRUD (Create, Read, Update, Delete) operations. Examples include:

Create a Record

const newUser = await User.create({ name: 'John Doe', email: 'john.doe@example.com' });

Read Records

const users = await User.findAll();
const user = await User.findByPk(1);

Update a Record

const user = await User.findByPk(1);
user.name = 'Jane Doe';
await user.save();

Delete a Record

const user = await User.findByPk(1);
await user.destroy();

Associations

Sequelize supports various associations, such as one-to-one, one-to-many, and many-to-many. For example, define a Post model associated with the User model:

models/post.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('./db');
const User = require('./user');

class Post extends Model {}
Post.init({
    title: DataTypes.STRING,
    content: DataTypes.TEXT
}, { sequelize, modelName: 'post' });

User.hasMany(Post);
Post.belongsTo(User);

module.exports = Post;

Handle Transactions

Sequelize supports transaction handling, useful for scenarios requiring atomic operations.

const transaction = await sequelize.transaction();
try {
    const user = await User.create({ name: 'John Doe', email: 'john.doe@example.com' }, { transaction });
    const post = await Post.create({ title: 'My First Post', content: 'Hello World!', userId: user.id }, { transaction });
    await transaction.commit();
} catch (error) {
    await transaction.rollback();
}

Express Integration with Mongoose

Install Required Packages

Install Express and Mongoose. For TypeScript, install the corresponding type definitions.

npm install express mongoose
# For TypeScript
npm install @types/express @types/mongoose

Connect to MongoDB

Connect to MongoDB using Mongoose in your application, typically during startup.

index.js or app.js

const mongoose = require('mongoose');
const express = require('express');
const app = express();

mongoose.connect('mongodb://localhost:27017/mydatabase', {
    useNewUrlParser: true,
    useUnifiedTopology: true,
    useCreateIndex: true,
    useFindAndModify: false
})
    .then(() => console.log('MongoDB connected'))
    .catch(err => console.error('MongoDB connection error:', err));

// Other Express configurations and routes...

Define Schema and Models

Mongoose’s core is the Schema, which defines your data structure. Models, created from Schemas, encapsulate MongoDB collections and provide CRUD operations.

models/user.js

const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
    name: {
        type: String,
        required: true
    },
    email: {
        type: String,
        required: true,
        unique: true
    },
    createdAt: {
        type: Date,
        default: Date.now
    }
});

module.exports = mongoose.model('User', userSchema);

Use Models in Routes

Use Models in Express routes to perform CRUD operations.

routes/users.js

const express = require('express');
const router = express.Router();
const User = require('../models/user');

router.get('/', async (req, res) => {
    const users = await User.find();
    res.json(users);
});

router.post('/', async (req, res) => {
    const newUser = new User(req.body);
    const savedUser = await newUser.save();
    res.status(201).json(savedUser);
});

// Other routes...

module.exports = router;

index.js or app.js

const express = require('express');
const app = express();
const usersRouter = require('./routes/users');

app.use('/api/users', usersRouter);

app.listen(3000, () => {
    console.log('Server running on port 3000');
});

Use Mongoose Middleware

Mongoose supports middleware, which are functions executed before or after saving or deleting documents, useful for data preprocessing or postprocessing.

models/user.js

userSchema.pre('save', function(next) {
    console.log('About to save a user');
    next();
});

userSchema.post('save', function(doc) {
    console.log('A user has been saved');
});

Use Virtual Properties

Virtual properties allow you to add fields to a Schema that are not stored in the database but can be used in queries.

models/user.js

userSchema.virtual('fullName').get(function() {
    return `${this.name.first} ${this.name.last}`;
});

Use Plugins

Mongoose plugins extend functionality. For example, use mongoose-autopopulate to automatically populate referenced fields.

models/user.js

const autopopulate = require('mongoose-autopopulate');

userSchema.plugin(autopopulate);

Express Integration with Prisma

Install Prisma CLI and Prisma Client

Install Prisma CLI globally and Prisma Client in your project.

# Install Prisma CLI globally
npm install -g prisma

# Install Prisma Client in the project
npx prisma generate

Configure Prisma Schema

Create a prisma/schema.prisma file in your project root and define your database models.

schema.prisma

datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
}

generator client {
    provider = "prisma-client-js"
}

model User {
    id        Int      @id @default(autoincrement())
    email     String   @unique
    name      String?
    posts     Post[]
}

model Post {
    id         Int      @id @default(autoincrement())
    title      String
    content    String?
    published  Boolean  @default(false)
    author     User     @relation(fields: [authorId], references: [id])
    authorId   Int
}

Generate Prisma Client

Use Prisma CLI to generate the client, which includes all methods needed to interact with the database.

npx prisma generate

Migrate the Database

Use Prisma CLI to create and apply database migrations.

# Create a new migration
npx prisma migrate dev --name init

# Apply migrations
npx prisma migrate up

Integrate with Express

Import Prisma Client into your Express application and use it for database operations.

index.js or app.js

const express = require('express');
const app = express();
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

app.get('/users', async (req, res) => {
    const users = await prisma.user.findMany();
    res.json(users);
});

app.post('/users', async (req, res) => {
    const newUser = await prisma.user.create({
        data: req.body
    });
    res.status(201).json(newUser);
});

// Error handling and Prisma client shutdown
process.on('SIGINT', async () => {
    await prisma.$disconnect();
    process.exit(0);
});

app.listen(3000, () => {
    console.log('Server running on port 3000');
});

Use Prisma’s Advanced Features

Prisma offers advanced features like transactions, relational queries, filtering, and sorting.

Query Relational Data

app.get('/users/:id/posts', async (req, res) => {
    const user = await prisma.user.findUnique({
        where: { id: parseInt(req.params.id) },
        include: { posts: true }
    });
    res.json(user.posts);
});

Transaction Handling

app.put('/users/:id', async (req, res) => {
    const { id } = req.params;
    const { name, email } = req.body;

    try {
        await prisma.$transaction([
            prisma.user.update({
                where: { id: parseInt(id) },
                data: { name, email },
            }),
            prisma.post.deleteMany({ where: { authorId: parseInt(id) } })
        ]);
        res.status(200).send('User updated and posts deleted');
    } catch (e) {
        res.status(500).send(e.message);
    }
});
Course: Node.js Asynchronous Patterns, Architecture & Frameworks
Share your love

Trending now

Exploring Web 3D Core Technologies Development
Assembly Language Programming From Low-Level Logic to System Kernel
WebRTC Advanced Guide：Media, Transport & Architecture
WebSocket Deep Guide：Building High-Performance Realtime Systems