What tools or technologies do you use for working with databases?

For working with databases in Node.js, I use the following tools and technologies:

• ORM/ODM:
  • Sequelize: For relational databases (PostgreSQL, MySQL, SQLite, SQL Server). Provides a powerful set of features for object-relational mapping, transactions, migrations, and validation.
  • Mongoose: For MongoDB databases. Object-document mapping, data schemas, validation, queries.
• Database clients:
  • pg: Native client for PostgreSQL.
  • mysql2: A more modern and faster client for MySQL compared to mysql.
• Migration tools:
  • sequelize-cli: Used with Sequelize for managing migrations of relational databases.
  • migrate-mongo: Tool for managing migrations in MongoDB.
• Database management tools (GUI):
  • DBeaver: Universal tool for various types of databases.
  • MongoDB Compass: Official GUI for MongoDB.
• Caching:
  • Redis: Frequently used for caching data from the database, reducing load on the main database and speeding up responses.

Example of using Sequelize:

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

// Connecting to the database
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres' // Or 'mysql', 'sqlite', 'mssql'
});

// Defining a model
const User = sequelize.define('User', {
  firstName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  lastName: {
    type: DataTypes.STRING
    // allowNull defaults to true
  }
}, {
  // Other model options go here
});

// Synchronizing model with the database
async function syncDatabase() {
  await User.sync({ force: true }); // 'force: true' will drop and recreate the table
  console.log("Database synchronized!");
}

// Example query
async function findUser() {
  const users = await User.findAll();
  console.log(users.map(user => user.toJSON()));
}

// syncDatabase();
// findUser();

Example of using Mongoose:

const mongoose = require('mongoose');

// Connecting to MongoDB
mongoose.connect('mongodb://localhost:27017/mydatabase', { useNewUrlParser: true, useUnifiedTopology: true });

// Defining schema
const userSchema = new mongoose.Schema({
  name: String,
  age: Number
});

// Creating model
const User = mongoose.model('User', userSchema);

// Example of creating a document
async function createUser() {
  const user = new User({ name: 'Alice', age: 30 });
  await user.save();
  console.log('User saved!');
}

// Example of query
async function findUsers() {
  const users = await User.find({ age: { $gte: 25 } });
  console.log(users);
}

// createUser();
// findUsers();

The choice of specific tool depends on the type of database (relational or NoSQL), project requirements regarding abstraction and query complexity, as well as team preferences.