Lesson 5: Database Integration and Data Access

Almost every integration eventually touches a database — the system of record where data actually lives. Application code connects to a database server (MySQL, PostgreSQL, SQL Server) through a driver or connector library, opens a connection, sends SQL, and reads results. Key vocabulary: a connection string holds the host, port, database name, and credentials (kept in environment variables, never in code); a connection pool reuses open connections because opening one is slow — vital when hundreds of users hit a web app at once. The database is a shared integration point: the enrollment web app, the cashier's system, and the dean's reporting dashboard may all read the same student tables. That is powerful but risky — which is why access is usually mediated through an API or a data access layer rather than letting every program write tables directly.

A data access layer (DAL) is the part of your code that isolates SQL from business logic. Instead of scattering queries everywhere, you write functions like get_student(id) or save_enrollment(record) — if the schema changes, only the DAL changes. Many teams go further and use an ORM (Object-Relational Mapper) such as Eloquent (PHP/Laravel), SQLAlchemy (Python), or Prisma (Node.js): it maps tables to classes so Student.find(id) generates the SQL for you. Trade-offs to cite in exams: ORMs speed up development and reduce SQL mistakes, but can hide inefficient queries; raw SQL gives full control but more room for error. Also know transactions: grouping several statements so they all succeed or all roll back — enrolling a student and charging their account must be atomic, or money and records drift apart.