Direct Query and Mapper

The Authentication and Authorization course closed one question: who can make a request, and with which role they can touch which resource, is now a settled decision. A request that passes the check enters the application and meets a second question waiting there. How does the call reach down to the data?

This course builds that descent. The first step is the lowest layer: the translation between the objects in the application and the rows in the database. Writing the translation by hand is possible, and so is leaving it to a mapper. Both produce the same list; the SQL they produce and the number of queries they run are not the same. This lesson measures the difference.

Shared Task and Schema

The schema used throughout the course is the library schema from the SQL Fundamentals course: the branch, book, member, and loan relations. The block below builds the database from scratch; every example in this lesson runs against this file.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

The task is this: list the loan records that have not been returned, together with the book’s title and the member’s name. This is the loan service’s most frequently called read task; the same task will be taken up again through different layers across the course.

Direct Query

In the first approach, the query is written by hand. The database joins three relations in a single step, and the application converts the rows it gets back into its own object.

// direct.mjs — builds the open-loan list with a single query
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("library.db");
let queryCount = 0;

const sql = `SELECT o.loan_id, o.pickup_date, k.title, u.first_name, u.last_name
             FROM loan o
             JOIN book k ON k.book_id = o.book_id
             JOIN member  u ON u.member_id  = o.member_id
             WHERE o.return_date IS NULL
             ORDER BY o.loan_id`;

queryCount += 1;
const rows = db.prepare(sql).all();

// The row -> object conversion is written by hand.
const openLoans = rows.map((s) => ({
  id: s.loan_id,
  pickupDate: s.pickup_date,
  bookTitle: s.title,
  memberName: `${s.first_name} ${s.last_name}`,
}));

for (const o of openLoans) console.log(`${o.id}  ${o.pickupDate}  ${o.bookTitle}  ${o.memberName}`);
console.log("queries executed:", queryCount);

node direct.mjs

3  2025-02-11  Blindness  Ben Ortiz
7  2025-04-21  Silent House  Derek Voss
10  2025-06-03  The Book of Sand  Grace Kim
queries executed: 1

A single query. The number of requests sent to the database is one, the number of rows returned is three, the number of columns transferred is five. This approach’s gain is directly visible: the query text can be read by eye, control over the plan is complete, and the shape of the result set is in the hands of the person who wrote it.

The cost sits in the same place. The conversion inside the rows.map call is written by hand. When a new column is added to the loan record, both the SQL and the conversion are updated. If the application has fifteen variants of this task, the conversion is written fifteen times.

Declared Mapping

The second approach declares the conversion once. Which type corresponds to which table, and which field to which column, sits in a mapping table; the SQL is generated from it.

// mapper.mjs — small mapper that generates SQL from a declared mapping table
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("library.db");
export const generated = [];

const mapping = {
  Loan: {
    table: "loan", key: "loan_id",
    fields: { id: "loan_id", pickupDate: "pickup_date", returnDate: "return_date" },
    relations: { book: { target: "Book", local: "book_id" },
                 member:   { target: "Member",   local: "member_id" } },
  },
  Book: { table: "book", key: "book_id",
           fields: { id: "book_id", title: "title", author: "author" }, relations: {} },
  Member:   { table: "member", key: "member_id",
           fields: { id: "member_id", firstName: "first_name", lastName: "last_name" }, relations: {} },
};

const selectList = (type) => [
  ...Object.values(mapping[type].fields),
  ...Object.values(mapping[type].relations).map((i) => i.local),
].join(", ");

function run(sql, ...bound) {
  generated.push(sql);
  return db.prepare(sql).all(...bound);
}

function toObject(type, row) {
  const m = mapping[type];
  const obj = {};
  for (const [name, column] of Object.entries(m.fields)) obj[name] = row[column];
  // A relation loads the moment it is read: the getter runs a query each time it is called.
  for (const [name, relation] of Object.entries(m.relations)) {
    const id = row[relation.local];
    Object.defineProperty(obj, name, { get: () => find(relation.target, id) });
  }
  return obj;
}

export function find(type, id) {
  const m = mapping[type];
  const row = run(`SELECT ${selectList(type)} FROM ${m.table} WHERE ${m.key} = ?`, id)[0];
  return row ? toObject(type, row) : null;
}

export function search(type, condition) {
  const m = mapping[type];
  const sql = `SELECT ${selectList(type)} FROM ${m.table} WHERE ${condition} ORDER BY ${m.key}`;
  return run(sql).map((row) => toObject(type, row));
}

The mapper’s work comes down to three points: it builds the select list from the mapping, converts the result into an object, and loads relations the moment they are read. The last point is the critical one. The book property is not a real value; it is a reader that runs a query when it is called. This behavior is called lazy loading.

The caller no longer sees SQL.

// mapper-use.mjs — builds the same list with the mapper and counts the generated SQL
import { search, generated } from "./mapper.mjs";

for (const o of search("Loan", "return_date IS NULL")) {
  console.log(`${o.id}  ${o.pickupDate}  ${o.book.title}  ${o.member.firstName} ${o.member.lastName}`);
}

console.log("queries executed:", generated.length);
for (const sql of generated) console.log("  " + sql.replace(/\s+/g, " "));

node mapper-use.mjs

3  2025-02-11  Blindness  Ben Ortiz
7  2025-04-21  Silent House  Derek Voss
10  2025-06-03  The Book of Sand  Grace Kim
queries executed: 10
  SELECT loan_id, pickup_date, return_date, book_id, member_id FROM loan WHERE return_date IS NULL ORDER BY loan_id
  SELECT book_id, title, author FROM book WHERE book_id = ?
  SELECT member_id, first_name, last_name FROM member WHERE member_id = ?
  SELECT member_id, first_name, last_name FROM member WHERE member_id = ?
  SELECT book_id, title, author FROM book WHERE book_id = ?
  SELECT member_id, first_name, last_name FROM member WHERE member_id = ?
  SELECT member_id, first_name, last_name FROM member WHERE member_id = ?
  SELECT book_id, title, author FROM book WHERE book_id = ?
  SELECT member_id, first_name, last_name FROM member WHERE member_id = ?
  SELECT member_id, first_name, last_name FROM member WHERE member_id = ?

The output is the same, but the query count is ten instead of one. The number comes from two sources. The first is the nature of lazy loading: the book and the member are read separately for each of the three loan records. The second is sneakier. In the print line, o.member.firstName and o.member.lastName do two separate reads; each read runs the reader again, so the member query comes back twice per loan. Together the three make 1 + 3 + 6 = 10.

This is the central risk of using a mapper. The query is invisible while reading the code. The difference between a property access and a database round trip has disappeared from the writer’s view.

Identity Map

Part of the number can be closed inside the mapper itself. If a row with the same identity and the same type is read once and kept, a second access produces no query. The structure that keeps loaded objects by their identity is called an identity map.

// mapper-identity.mjs — version that stores loaded objects by their identity
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("library.db");
export const generated = [];
const identityMap = new Map(); // type:id -> object

const mapping = {
  Loan: {
    table: "loan", key: "loan_id",
    fields: { id: "loan_id", pickupDate: "pickup_date", returnDate: "return_date" },
    relations: { book: { target: "Book", local: "book_id" },
                 member:   { target: "Member",   local: "member_id" } },
  },
  Book: { table: "book", key: "book_id",
           fields: { id: "book_id", title: "title", author: "author" }, relations: {} },
  Member:   { table: "member", key: "member_id",
           fields: { id: "member_id", firstName: "first_name", lastName: "last_name" }, relations: {} },
};

const selectList = (type) => [
  ...Object.values(mapping[type].fields),
  ...Object.values(mapping[type].relations).map((i) => i.local),
].join(", ");

function run(sql, ...bound) {
  generated.push(sql);
  return db.prepare(sql).all(...bound);
}

function toObject(type, row) {
  const m = mapping[type];
  const obj = {};
  for (const [name, column] of Object.entries(m.fields)) obj[name] = row[column];
  for (const [name, relation] of Object.entries(m.relations)) {
    const id = row[relation.local];
    Object.defineProperty(obj, name, { get: () => find(relation.target, id) });
  }
  return obj;
}

export function find(type, id) {
  const mapKey = `${type}:${id}`;
  if (identityMap.has(mapKey)) return identityMap.get(mapKey);
  const m = mapping[type];
  const row = run(`SELECT ${selectList(type)} FROM ${m.table} WHERE ${m.key} = ?`, id)[0];
  const obj = row ? toObject(type, row) : null;
  identityMap.set(mapKey, obj);
  return obj;
}

export function search(type, condition) {
  const m = mapping[type];
  const sql = `SELECT ${selectList(type)} FROM ${m.table} WHERE ${condition} ORDER BY ${m.key}`;
  return run(sql).map((row) => toObject(type, row));
}

// identity-use.mjs — builds the same list with the identity-map version
import { search, generated } from "./mapper-identity.mjs";

for (const o of search("Loan", "return_date IS NULL")) {
  console.log(`${o.id}  ${o.pickupDate}  ${o.book.title}  ${o.member.firstName} ${o.member.lastName}`);
}

console.log("queries executed:", generated.length);

node identity-use.mjs

3  2025-02-11  Blindness  Ben Ortiz
7  2025-04-21  Silent House  Derek Voss
10  2025-06-03  The Book of Sand  Grace Kim
queries executed: 7

Ten queries dropped to seven: touching the same member twice now reads once. The identity map gives a second guarantee too. Because it returns a single object for the same row, a change made in one place also shows up in a copy read somewhere else; the chance of two different copies overwriting each other disappears.

The number seven is still greater than one, and it grows together with the number of records in the list. Seven for three records means hundreds of queries for three hundred. This pattern is called the N+1 query problem; it will be measured and resolved in the Performance Problems topic.

Where the Trade-off Stands

The choice between the two approaches does not reduce to which one is better; what matters is which kind of cost gets lowered.

A direct query maximizes control. The SQL that runs is exactly as written; which columns come back, how the join is built, and what the plan will look like are all visible. Complex reports, aggregate functions, and hand-tuned joins stay on this side. Its cost is repetition: the conversion code is rewritten for every read task.

A mapper lowers repetition. The mapping is declared once, and dozens of read tasks are generated from it; adding a column to the schema asks for a change in a single place. Its cost is the loss of visibility: how many queries ran can only be learned by counting them.

In practice the two approaches sit side by side in the same application. The split is made by the type of work: record lifecycle and single-object reads go to the mapper, multi-table reports and bulk operations are left to the direct query. The condition for the decision is measurability. Whichever approach is chosen, there must be a hook that lets you see the generated SQL and the query count; the generated array in this lesson is the smallest form of that hook.

Summary

• The same read task can be built with hand-written SQL or with a mapper that generates SQL from a declared mapping; both paths give the same output.
• The measurement showed the difference: the direct query ran 1 query, the mapper with lazy loading ran 10, and the mapper with the identity map added ran 7.
• Lazy loading turns a property access into an invisible database round trip; touching the same relation twice produces two queries.
• The identity map makes sure the same identity is represented by a single object, cuts repeated reads, and prevents copies from overwriting one another.
• A direct query maximizes control, a mapper lowers repetition; the choice is made by the type of work, and either way the generated SQL must be countable.

Next Step

The SQL the mapper produced ran more queries than expected, but that is not the real problem. In the mapping table, the Loan type and the loan relation lined up one to one: every field matched a column, every relation matched a foreign key. Real domain models do not form this alignment: an inheritance hierarchy has no single-table equivalent, object identity is not the same thing as a primary key, and a two-way relation has only one owner in the relational model. The next lesson examines where these mismatches come from and which mapping decision closes each one.