skip to content

ORM & Data Access

The libraries between your objects and the database: full ORMs, query builders and thin drivers for the JVM, Python, Node, .NET, Rust and Go. Interviewers probe them because data bugs start here.

on this pageshow

explore

questions

745 · 8 sections

JPA offers four association mappings — @ManyToOne, @OneToMany, @OneToOne and @ManyToMany. Explain what each expresses, and how you would choose between them for a model of orders, customers and tags.

level: juniorimportance: must knowfreq 76%
basics
~20 s

They declare cardinality from the annotated field outward. @ManyToOne: many rows reference one (Order to Customer) and holds the foreign key. @OneToMany: the inverse collection (Customer to Orders). @OneToOne: at most one each way. @ManyToMany: many both ways, via a link table.

open as a page

In JPA, if you call EntityManager.persist() on a parent entity whose collection holds brand-new child entities, what happens to those children by default, and what does configuring cascade on that association change?

level: juniorimportance: must knowfreq 62%
basics
~20 s

By default persist applies only to the instance you pass. The new children stay transient, and at flush Hibernate typically fails with "object references an unsaved transient instance". Declaring cascade = PERSIST (or ALL) makes the provider run persist on each child too.

open as a page

In JPQL, what does the JOIN FETCH clause do, and how does it differ from writing a plain JOIN in the same query?

level: juniorimportance: must knowfreq 78%
basics
~20 s

JOIN FETCH tells the ORM to load the joined association's rows into the returned entities in the same SQL statement. A plain JOIN only lets you filter or navigate in the query; the association still loads later on access.

open as a page

In JPA, what does the @JoinColumn annotation declare, and which side of an association must carry it?

level: juniorimportance: must knowfreq 75%
basics
~20 s

@JoinColumn names the foreign-key column that stores the association. It goes on the owning side — the side whose table holds the FK, which for @ManyToOne is always the many side. The inverse side uses mappedBy instead.

open as a page

In JPA, what is the default fetch type for each of the four association annotations — @ManyToOne, @OneToOne, @OneToMany and @ManyToMany — and why does the default differ between them?

level: juniorimportance: must knowfreq 82%
basics
~20 s

To-one associations (@ManyToOne, @OneToOne) default to EAGER. To-many associations (@OneToMany, @ManyToMany) default to LAZY. The spec assumes fetching one extra row is cheap and fetching a whole collection is not. Most teams override the to-one defaults to LAZY.

open as a page

In JDBC, how should Connection, Statement and ResultSet be closed, and why?

level: juniorimportance: must knowfreq 70%
basics
~20 s

Declare each of them in a try-with-resources block so they close in reverse order even when something throws. Every one holds a scarce handle — a server-side cursor or a pooled connection — and an unclosed handle is leaked until the process ends.

open as a page

In JDBC, what does PreparedStatement do that Statement does not, and why prefer it?

level: juniorimportance: must knowfreq 85%
basics
~20 s

PreparedStatement fixes the SQL text up front with ? placeholders and supplies values afterwards through numbered typed setters, so input is never parsed as SQL. The same object can be re-executed with new values and the parsed form reused.

open as a page

In JDBC, why do applications obtain connections from a DataSource rather than DriverManager?

level: middleimportance: must knowfreq 60%
basics
~20 s

DriverManager is a static factory that opens a brand-new physical connection per call from a URL hardcoded at the call site. DataSource is an interface whose implementation is configured elsewhere, which is what allows pooled, XA-capable or instrumented connections without changing application code.

open as a page

In JDBC, how do you make several statements commit or roll back as one transaction?

level: middleimportance: must knowfreq 72%
basics
~20 s

Call setAutoCommit(false) on the Connection, run the statements, then call commit() on success and rollback() in the catch block. There is no begin() method in JDBC: turning auto-commit off is what starts the unit of work.

open as a page

How do JDBC's addBatch and executeBatch work, and what does executeBatch return?

level: middleimportance: should knowfreq 50%
basics
~20 s

On a PreparedStatement you bind parameters and call addBatch() to buffer that parameter set, then executeBatch() sends the buffered commands together. It returns an int[] of update counts, one per command, where a value may be SUCCESS_NO_INFO when the count is unknown.

open as a page

With jOOQ's code generator, which classes does it write for each table, and which outputs stay off unless you enable them?

level: juniorimportance: must knowfreq 50%
basics
~20 s

For each table, jOOQ's code generator writes a table class with typed column fields and a record class for its rows, plus schema-wide Tables and Keys classes. POJOs, DAOs and interfaces are off by default.

open as a page

In jOOQ, what is a DSLContext, and what does the SQLDialect you configure on it decide?

level: juniorimportance: must knowfreq 60%
basics
~20 s

DSLContext is jOOQ's entry point: it wraps a Configuration holding the connection source, the SQLDialect and the Settings, then builds and executes queries. The SQLDialect decides how each query renders as SQL for the target database.

open as a page

In jOOQ, how do you map fetched rows into your own POJOs, Java records or Kotlin data classes?

level: juniorimportance: must knowfreq 55%
basics
~10 s

Call fetchInto(Type.class) or Record.into(Type.class) to let jOOQ's DefaultRecordMapper map by name or constructor, or pass a RecordMapper such as Records.mapping(Type::new) to fetch() for a compiler-checked, positional mapping.

open as a page

In jOOQ, what do a Configuration and its ConnectionProvider do, and where does a DSLContext get its JDBC connection?

level: juniorimportance: must knowfreq 50%
basics
~20 s

A jOOQ Configuration bundles the SQLDialect, Settings, ConnectionProvider, TransactionProvider and listeners that a DSLContext runs on. Its ConnectionProvider hands out a JDBC Connection through acquire() once per statement execution and takes it back through release().

open as a page

Where can jOOQ's code generator read a schema from, a live database, DDLDatabase or LiquibaseDatabase, and what does each trade off?

level: middleimportance: must knowfreq 48%
basics
~20 s

jOOQ's code generator either reverse-engineers a running database over JDBC or simulates one: DDLDatabase parses SQL scripts and LiquibaseDatabase applies a changelog, both on in-memory H2. Simulation needs no server but can miss vendor-specific DDL.

open as a page

In Prisma Client, how do findUnique, findFirst and findMany differ, and what do their OrThrow variants change?

level: juniorimportance: must knowfreq 75%
basics
~20 s

findUnique reads one row by id or unique key, returning it or null; findFirst returns the first row matching any filter and sort, or null; findMany returns an array, empty if nothing matches. The OrThrow forms throw P2025 instead of null.

open as a page

In Prisma 7, why should a Node.js application share one PrismaClient instance per process, and how do you stop dev hot reload from multiplying it?

level: juniorimportance: must knowfreq 50%
basics
~20 s

Each PrismaClient gets its own connection pool from its driver adapter, so every extra instance opens extra database connections. Create one in a module and reuse it; in development, keep it on globalThis so hot reload reuses it.

open as a page

In Prisma 7, what is the difference between `prisma db push` and `prisma migrate dev`, and when should each be used?

level: juniorimportance: must knowfreq 58%
basics
~20 s

Prisma's db push makes the database match the schema directly and leaves no migration files or migrations-table rows; migrate dev records each change as a SQL migration, applies it and tracks it. Push suits local prototyping; migrations suit anything shared.

open as a page

In a Prisma 7 schema, how does a relation field differ from the relation scalar field named in @relation(fields: [...], references: [...])?

level: juniorimportance: must knowfreq 70%
basics
~20 s

A relation field such as user User exists only in the Prisma schema and client; the relation scalar field such as userId Int is the real foreign-key column, and @relation(fields: [userId], references: [id]) links them on the side holding the key.

open as a page

In a Prisma 7 schema, how does a model block describe a table, and what do @id, @default, @unique and the ? modifier each do?

level: juniorimportance: must knowfreq 72%
basics
~20 s

A Prisma model block declares one table; each line is a field with a type, modifiers and attributes. @id marks the primary key, @default fills an omitted value, @unique forbids duplicates, and ? makes the field nullable.

open as a page

With Mongoose 9, what does a schema add on top of a MongoDB collection, and where is that schema actually enforced?

level: juniorimportance: must knowfreq 78%
basics
~20 s

A Mongoose schema declares each field's type, validators, defaults and options, and Mongoose casts and validates documents inside your Node process before writing them. MongoDB itself never sees that schema, so any writer that bypasses Mongoose bypasses it too.

open as a page

What does Mongoose 9's populate() send to MongoDB when a list of Order documents populates its user and product references?

level: middleimportance: must knowfreq 70%
basics
~20 s

populate() runs the main query, then one extra find per populated path and referenced model, with $in over the collected ids, and assigns results in memory: a client-side join with extra round trips that cannot filter parents.

open as a page

Why does a Mongoose 9 pre('save') hook that hashes User passwords leave a password set through findOneAndUpdate() stored in plain text?

level: seniorimportance: must knowfreq 55%
basics
~20 s

Save hooks are document middleware that run for save() and create(); findOneAndUpdate() runs query middleware, where this is the Query and no document exists. Hash in a pre('findOneAndUpdate') hook through getUpdate(), or load the user, assign and save().

open as a page

In Mongoose 9, what does calling lean() on a query give up, and when is it the right choice for a read endpoint?

level: middleimportance: should knowfreq 50%
basics
~20 s

lean() tells Mongoose to skip hydrating results into documents and return plain objects: less memory and CPU, but no change tracking, save(), getters, setters, virtuals (including id), load-time defaults or schema toJSON transforms. Use it for read-only responses.

open as a page

Why can Mongoose 9's findOneAndUpdate() store a value the schema's validators would reject on save(), and what does runValidators change?

level: middleimportance: should knowfreq 60%
basics
~20 s

Update queries never load a document, so Mongoose casts the update but skips validators unless you pass runValidators: true. Even then only the updated paths are checked, this is the Query, and $inc is not validated.

open as a page

In EF Core 10, what does one SaveChangesAsync call do with the pending changes, and what happens when one of its statements fails?

level: juniorimportance: must knowfreq 72%
basics
~20 s

SaveChangesAsync detects changes, turns Added, Modified and Deleted entries into INSERT, UPDATE and DELETE commands, batches them, and runs them atomically. If any statement fails, the transaction rolls back, a DbUpdateException is thrown and the tracked states are kept.

open as a page

In EF Core 10, what are the five EntityState values, and what does SaveChanges do with an entity in each one?

level: juniorimportance: must knowfreq 72%
basics
~10 s

EntityState has five values: Detached, Unchanged, Added, Modified and Deleted. SaveChanges inserts Added entities, updates Modified ones, deletes Deleted ones and skips the rest; afterwards Added and Modified become Unchanged and Deleted become Detached.

open as a page

In EF Core 10, what do the [Key], [Required], [MaxLength], [Column] and [Index] attributes change in the model, and what do conventions decide without them?

level: juniorimportance: must knowfreq 68%
basics
~20 s

By convention EF Core makes a property named Id or <Type>Id the key, takes nullability from the C# type, maps strings without a length and names the table after the DbSet. [Key], [Required], [MaxLength], [Column] and [Index] override those choices.

open as a page

In EF Core 10, what do a DbContext and its DbSet<T> properties do, and when does a change actually reach the database?

level: juniorimportance: must knowfreq 74%
basics
~20 s

A DbContext is EF Core's session with a database: it owns the model, tracks entities and turns their changes into SQL. Each DbSet<T> is the query root for one entity type. Nothing is written until SaveChanges or SaveChangesAsync runs.

open as a page

In EF Core 10, how does an abstract Payment class with CardPayment, BankTransferPayment and VoucherPayment subclasses map to tables by default?

level: juniorimportance: must knowfreq 50%
basics
~20 s

By default EF Core uses table-per-hierarchy (TPH): every payment type goes into one Payments table, with a shadow Discriminator column holding the class name, such as CardPayment, and nullable columns for each subclass's properties. Subclasses must be added to the model explicitly.

open as a page

In a data-access layer that caches loaded rows, what makes a cached entry stale, and which writes leave one behind?

level: juniorimportance: must knowfreq 70%
basics
~20 s

A cached entry goes stale when the row it copies changes and nothing removes the copy. Any write the caching layer never sees can leave one behind: a set-based statement, a native statement, another service, or an operator at a console.

open as a page

What is a data-access cache that outlives a unit of work and is keyed by identifier, and which read does it serve?

level: juniorimportance: must knowfreq 60%
basics
~20 s

A shared cache holds mapped row state by type and identifier outside any single unit of work, so a load by key that hits it returns an object with no statement sent. Filtered queries still reach the database.

open as a page

What does it mean for a data-access layer to fill deferred links in identifier batches rather than one at a time?

level: juniorimportance: must knowfreq 52%
basics
~20 s

Rather than one statement per touched stand-in, the layer gathers the keys of other unfilled stand-ins of the same kind and fetches them together with a set-membership predicate, turning N secondary statements into roughly N divided by the batch size.

open as a page

When one statement fetches parents together with their child collection, why does the same parent come back repeatedly?

level: juniorimportance: must knowfreq 68%
basics
~20 s

The join returns one row per matching child, with the parent's columns repeated on every one of them. A layer that produces one result entry per row therefore hands back the same parent once per child row.

open as a page

In Alembic, what must env.py and alembic.ini provide before `alembic revision --autogenerate` can draft a migration from SQLAlchemy models?

level: juniorimportance: must knowfreq 70%
basics
~20 s

env.py must set target_metadata to the models' MetaData, usually Base.metadata with every model module imported, and Alembic needs a database URL. Autogenerate diffs that metadata against the live schema and writes a candidate script to review.

open as a page

In Alembic, how do `revision`, `down_revision` and the `alembic_version` table decide which scripts `alembic upgrade head` runs?

level: juniorimportance: must knowfreq 50%
basics
~20 s

Each Alembic script names itself in revision and its parent in down_revision, forming a chain. alembic upgrade head reads the stored revision from alembic_version, runs every upgrade() between it and the newest script, and records each step.

open as a page

In SQLAlchemy Core, how do MetaData, Table and Column fit together, and what does metadata.create_all() do with a table that already exists?

level: juniorimportance: must knowfreq 55%
basics
~20 s

MetaData holds Table objects, each built from Column and constraint objects. metadata.create_all(engine) checks which tables already exist, creates only the missing ones in foreign-key order, and never alters an existing table, even when its Python definition changed.

open as a page

In SQLAlchemy Core, how do you build a select() step by step with where(), group_by(), order_by() and limit(), and why reassign each call?

level: juniorimportance: must knowfreq 62%
basics
~10 s

Start from select(columns) and chain where(), join(), group_by(), having(), order_by() and limit(). Each call returns a new Select and leaves the original untouched, so a call whose result is not reassigned is silently lost.

open as a page

In SQLAlchemy, how do you pass values into a text() statement, and what does SQLAlchemy do with the :name placeholders?

level: juniorimportance: must knowfreq 55%
basics
~20 s

SQLAlchemy's text() marks values as :name placeholders, and Connection.execute() takes their values as a dict, or a list of dicts for executemany. SQLAlchemy rewrites each placeholder into the driver's parameter style and sends values separately from the SQL.

open as a page