**JavaScript Database Integration: 8 Essential Methods for Seamless Data Management and Performance**

Source: Dev.to

Managing Database Connections with a Pool

Creating a new connection for every request is slow and wasteful. A connection pool maintains a set of ready‑to‑use connections that can be checked out, used, and then released back to the pool.

import mysql from 'mysql2/promise';

class DatabasePool {
  constructor() {
    this.pool = mysql.createPool({
      host: 'localhost',
      user: 'app_user',
      password: 'secure_password',
      database: 'my_app_db',
      waitForConnections: true,
      connectionLimit: 20,
      queueLimit: 0
    });
  }

  async query(sql, values) {
    let connection;
    try {
      connection = await this.pool.getConnection();
      const [results] = await connection.execute(sql, values);
      return results;
    } catch (err) {
      console.error('Database query failed:', err.message);
      // Add retry logic for specific errors if needed
      throw err;
    } finally {
      if (connection) connection.release();
    }
  }
}

export default new DatabasePool();

Using an ORM for Object‑Oriented Data Access

An Object‑Relational Mapper (ORM) lets you work with database rows as JavaScript objects and classes, reducing the need to write raw SQL strings.

// user.model.js
import { Model, DataTypes } from 'sequelize';
import sequelize from '../config/database.js';
import Post from './post.model.js';

class User extends Model {}

User.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: { isEmail: true }
  },
  fullName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  isActive: {
    type: DataTypes.BOOLEAN,
    defaultValue: true
  }
}, {
  sequelize,
  modelName: 'User',
  tableName: 'users'
});

// Relationships
User.hasMany(Post, { foreignKey: 'authorId' });
Post.belongsTo(User, { foreignKey: 'authorId' });

export default User;

Example Operations

// Create a new user
const newUser = await User.create({
  email: 'hello@example.com',
  fullName: 'Jane Developer'
});
console.log(newUser.id);

// Find a user with their posts
const user = await User.findOne({
  where: { email: 'hello@example.com' },
  include: Post
});
console.log(user.fullName);
user.Posts.forEach(post => console.log(post.title));

// Update a user
await user.update({ isActive: false });

Query Builder for Flexible, Safe SQL Construction

When an ORM feels too heavyweight, a query builder offers a chainable API that generates parameterized SQL, protecting against injection attacks.

import knex from 'knex';

const db = knex({
  client: 'mysql2',
  connection: {
    host: 'localhost',
    user: 'app_user',
    password: 'secure_password',
    database: 'my_app_db'
  }
});

// Dynamic user search
async function searchUsers(filters = {}) {
  let query = db('users')
    .select('id', 'fullName', 'email', 'created_at');

  if (filters.name) {
    query = query.where('fullName', 'like', `%${filters.name}%`);
  }
  if (filters.active !== undefined) {
    query = query.where('isActive', filters.active);
  }
  if (filters.minDate) {
    query = query.where('created_at', '>=', filters.minDate);
  }

  return await query
    .orderBy('created_at', 'desc')
    .limit(50);
}

// Usage
const activeUsers = await searchUsers({ active: true, name: 'Jane' });

Managing Schema Changes with Migrations

Migrations provide version‑controlled scripts to evolve the database schema safely.

// migrations/20230915_add_bio_to_users.js
export async function up(knex) {
  await knex.schema.table('users', (table) => {
    table.text('bio').nullable().after('fullName'); // Add column
    table.index(['isActive'], 'idx_users_active');   // Add index
  });
}

export async function down(knex) {
  await knex.schema.table('users', (table) => {
    table.dropIndex('idx_users_active');
    table.dropColumn('bio');
  });
}

Running the migration applies the new column and index; rolling back reverts the changes.

Validating Data Before Persistence

Validate input in JavaScript before it reaches the database to provide clearer error messages and enforce business rules early.

import Joi from 'joi';

const userSchema = Joi.object({
  email: Joi.string().email().required(),
  fullName: Joi.string().min(2).max(100).required(),
  age: Joi.number().integer().min(13).max(120).optional(),
  website: Joi.string().uri().allow('').optional()
});

Use userSchema.validate(data) (or validateAsync) before creating or updating records.