NodeJS Database Integration Syllabus

What is the default port for a MySQL server?

Port 3306.

Which two Node.js packages are commonly used to connect to MySQL, and what key feature does mysql2 add?

'mysql' and 'mysql2'. mysql2 is faster, supports prepared statements, and provides a built-in Promise API (mysql2/promise), making it the modern default.

What is a SQL prepared (parameterized) statement, and why use it?

A prepared statement sends the SQL structure and the parameter values separately (e.g., using ? placeholders). It prevents SQL injection and can improve performance by letting the server reuse the query plan.

What is a connection pool, and why is it important in a Node.js database app?

A connection pool is a cache of reusable open database connections. Instead of opening/closing a connection per query, the app borrows and returns connections, reducing latency and overhead under concurrent load.

What is PostgreSQL?

PostgreSQL (Postgres) is an advanced, open-source object-relational database (ORDBMS) known for standards compliance, extensibility, ACID transactions, and rich types like JSONB, arrays, and custom types.

What is the default port for a PostgreSQL server?

Port 5432.

What is the standard Node.js library for connecting to PostgreSQL?

The 'pg' package (node-postgres). It provides a Client for single connections and a Pool for pooled connections, both with a Promise-based query API.

How does the 'pg' driver denote parameter placeholders, and how does this differ from mysql2?

pg uses numbered placeholders $1, $2, $3, ... (e.g., SELECT * FROM users WHERE id = $1). mysql2 uses positional ? placeholders instead.

What is the JSONB type in PostgreSQL, and how does it differ from JSON?

JSONB stores JSON in a decomposed binary format that is faster to query and supports indexing (e.g., GIN indexes), but slightly slower to write. The JSON type stores the exact text and is faster to insert but slower to process.

What does ACID stand for in relational databases?

Atomicity, Consistency, Isolation, Durability — the four guarantees that ensure reliable database transactions.

What is Sequelize?

Sequelize is a promise-based Node.js ORM that supports PostgreSQL, MySQL, MariaDB, SQLite, and SQL Server. It maps JavaScript models/classes to SQL tables and provides methods for CRUD, associations, migrations, and transactions.

Which SQL databases (dialects) does Sequelize support?

PostgreSQL, MySQL, MariaDB, SQLite, Microsoft SQL Server (MSSQL), and Snowflake/Db2 (via dialects). It does NOT support MongoDB — that is the domain of an ODM like Mongoose.

In Sequelize, what is a Model and how do you define one?

A Model is a class representing a table, defined via sequelize.define('Name', { field: { type, ... } }) or by extending Model and calling Model.init(). Each attribute maps to a table column with a Sequelize DataType.

By default, what two timestamp columns does Sequelize automatically add to a model, and how do you disable them?

createdAt and updatedAt. Disable them by setting { timestamps: false } in the model options.

What are the four association types in Sequelize?

belongsTo (one-to-one owner side / many-to-one), hasOne (one-to-one), hasMany (one-to-many), and belongsToMany (many-to-many, using a junction table).

In Sequelize, how do you create and how do you fetch records (CRUD read/create)?

Create: Model.create({...}) or build() + save(). Read: Model.findAll(), Model.findOne({ where }), Model.findByPk(id). Update: Model.update({...}, { where }). Delete: Model.destroy({ where }).

What does sequelize.sync() do, and what does { force: true } vs { alter: true } mean?

sync() creates tables from model definitions if they don't exist. { force: true } drops and recreates each table (data loss). { alter: true } modifies existing tables to match the models without dropping them.

What is a Sequelize migration, and why is it preferred over sync() in production?

A migration is a versioned, reversible script (up/down) describing schema changes, run via the Sequelize CLI. It gives controlled, trackable schema evolution and avoids the data loss and unpredictability of sync({ force/alter }) in production.

What is a database transaction, and how do you use one in Sequelize?

A transaction groups multiple operations so they all succeed or all roll back (atomicity). In Sequelize: const t = await sequelize.transaction(); pass { transaction: t } to each query; then await t.commit() or await t.rollback().

What is the N+1 query problem, and how does Sequelize's 'eager loading' address it?

N+1 occurs when fetching N parent rows then issuing 1 extra query per parent for related data ($1 + N$ queries). Eager loading via the include option performs a JOIN to fetch parents and their associations in a single query.

In SQL, what is the difference between an INNER JOIN and a LEFT (OUTER) JOIN?

INNER JOIN returns only rows with matching keys in both tables. LEFT JOIN returns all rows from the left table plus matched rows from the right, filling unmatched right-side columns with NULL.

What is a primary key versus a foreign key in a relational database?

A primary key uniquely identifies each row in a table and cannot be NULL. A foreign key is a column referencing the primary key of another table, enforcing referential integrity between them.

What is database normalization, and what problems does it solve?

Normalization organizes tables to reduce data redundancy and improve integrity by splitting data into related tables (e.g., 1NF, 2NF, 3NF). It prevents insertion, update, and deletion anomalies.

Give the core conceptual difference between SQL and NoSQL databases relevant to choosing MongoDB vs PostgreSQL.

SQL (Postgres/MySQL): structured, fixed schema, tables with relations, strong ACID, best for complex queries/joins and structured data. NoSQL (MongoDB): flexible schema, document-based, horizontally scalable, best for unstructured/evolving data and high write throughput.

What is an index in a database, and what is its main trade-off?

An index is a data structure (often a B-tree) that speeds up read/lookup queries on indexed columns/fields. Trade-off: it consumes extra storage and slows down writes (INSERT/UPDATE/DELETE) because the index must also be maintained.

In Mongoose, how do you enforce that a field is required and unique?

In the schema definition set the field options { required: true, unique: true }. Note: 'unique' is not a validator but a directive to build a unique index on that field.

What is the aggregation pipeline in MongoDB?

A framework that processes documents through a sequence of stages (e.g., $match, $group, $sort, $project, $lookup), where each stage transforms the stream and passes results to the next — used for grouping, filtering, and computing aggregates.

What environment-variable practice is recommended for storing a database connection string in a Node.js app?

Store credentials/URIs in environment variables loaded from a .env file (e.g., via the dotenv package as process.env.DATABASE_URL), keep .env out of version control, and never hard-code secrets in source.

What is the difference between findOne() returning null and find() returning an empty array when no documents match (MongoDB/Mongoose)?

findOne() (and findById) resolves to null when no document matches. find() always resolves to an array — an empty array [] when nothing matches — so you check .length rather than for null.

In SQL, what is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes selected rows (can use WHERE, is transactional, logs each row). TRUNCATE quickly removes all rows but keeps the table structure and resets identity counters. DROP removes the entire table (structure and data).