Building Web Apps with Laravel Module 5
Databases and Eloquent interview questions
Interview questions on databases and Eloquent: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.
Warm-up questions
Short questions with short answers, each answered in full.
-
Fresher
Why do bindings protect the values in a query but never the column or table names?
Because the database prepares the SQL text first and fills each "?" with a value afterwards. A value travels in its own channel: the database uses it as a value and nothing else, so no character a visitor typed is read as SQL. Column and table names are part of the statement's structure, not its values. The database needs them in the text it parses to know which columns to read, so no "?" can stand in for one. That is why where('status', $value) is safe as it is and orderBy($value) is not: the first binds a value, the second writes structure straight into the SQL. Wherever a name must vary, in orderBy, a select list or raw SQL, the code picks the real name from a hand-written allowlist.
From the lesson Databases and the query builder
-
Fresher to mid
How would you prove to a teammate that one page's query is parameterised?
Three ways, from the smallest to the largest. First, read the builder chain in the controller: a where, whereIn, insert or update call where the value is a variable, not a string that holds the variable, is already parameterised. Second, call toSql() and getBindings() on the builder before get() and print both; a correctly parameterised query shows "?" placeholders in the SQL and the actual values in the bindings array. The lesson's recorded sessions show this on every query. Third, DB::enableQueryLog() at the start of a request and DB::getQueryLog() at the end return every query the application ran, with its SQL text and its bindings; a page with ten queries is checked by looking at the array.
From the lesson Databases and the query builder
-
Fresher
migrate:rollback and migrate:fresh both take tables away. How far does each one go?
-
Fresher to mid
Why does strict mode belong in development and not on the production site?
Answered in the lesson Eloquent models, mass assignment, soft deletes
-
Fresher
Why is a fixed Faker seed useful even when you only run the seeder on your own machine?
Answered in the lesson Factories, seeders and realistic test data
-
Fresher
Which table holds the foreign key of a one-to-many relationship, and how does Eloquent guess the column's name?
Answered in the lesson Eloquent relationships, pivots and morph maps
-
Fresher
What is the N+1 query problem, and why did a lazy loop over 18 instalments of 6 students send 19 queries?
Answered in the lesson Eager loading and the N+1 query problem
-
Fresher
Why does Pathshala keep money as integer paise rather than a float number of rupees?
Answered in the lesson Casts, accessors, enums and value objects
-
Fresher
What does paginate() send that simplePaginate() does not, and what does a page lose without it?
Answered in the lesson Scopes, Eloquent collections and pagination
Questions with model answers
What each question tests, the first step of a good answer and the follow-ups to expect.
-
Fresher to mid
A code review turns up Instalment::query()->orderBy($request->input('sort'))->paginate(). Walk through the weakness this creates, the smallest safe shape you would move to, and the check that would stop the pattern coming back on the next page.
- What it tests
- Reading a column-name injection from input, moving to an allowlist with a safe default, and leaving a check behind that fires on the next page a teammate writes.
- First step
- Say what orderBy writes into the SQL: the first argument becomes the column name directly, because column names are structure and no "?" can stand in for them. Any character the visitor typed is now part of the database's parsed SQL.
- Follow-ups
-
- What does the smallest fix look like, as code?
- Where does the default column belong, and why does a ?? fallback matter?
- How would you write a test that fails on the old shape and passes on the new one?
- What rule or lint could stop a sibling controller from doing the same thing?
From the lesson Databases and the query builder
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
-
Mid to senior
A reports page needs instalments whose due date is within N days of today, with N chosen from a dropdown of 7, 30 or 90. A teammate wrote whereRaw("due_on <= date('now', '+$n days')"). Explain what is wrong, write the safe shape, and say how you would prove the change produces the same rows.
- What it tests
- Spotting an interpolated value inside a raw expression, moving the value into the bindings array, and proving the two shapes behave the same on a seeded dataset.
- First step
- Say what PHP did before the builder saw the string: $n was interpolated into the SQL, so the "prepared" statement prepares a different string each time, which is exactly the pattern a parameter is for. The fix is whereRaw with a "?" and a bindings array.
- Follow-ups
-
- What does the safe call look like, with the SQL function and the "?" in the right places?
- Would the dropdown of three values make the first shape safe?
- How would you check both calls return the same rows on a seeded database?
- When does the right answer stop being whereRaw and become a method such as whereDate or whereBetween?
From the lesson Databases and the query builder
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
-
Mid to senior
Pathshala wants to rename fee_instalments.due_on to due_date. The app runs on two servers, and each deploy replaces them one at a time over about five minutes, so old and new code serve requests together. Plan the change so that no request fails: how many deploys does it take, and what does each one change?
- What it tests
- Changing a schema while two versions of the code run against it: adding before removing, writing both columns, copying old rows, and dropping a column only once no running code uses it.
- First step
- Write down what the old code reads and writes. A renameColumn in a single deploy breaks the server that has not been replaced yet, because its queries still name due_on. So the rename becomes a sequence: add due_date beside due_on, move the code over to it, and drop due_on only in a later deploy.
- Follow-ups
-
- What does the first deploy's migration contain, and what does its code write and read?
- How and when do the existing due dates get copied into the new column?
- When is it safe to drop due_on, and what has to happen to its index first?
- What changes when the table holds two million rows on MySQL?
From the lesson Migrations and schema design
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
-
Mid to senior
A deploy adds unique(['student_id', 'number']) to a production fee_instalments table of 400,000 rows. Every test passed, but on the server the migration fails with a UNIQUE constraint error. Why did the tests miss it, what state is production in now, and how do you ship the index safely?
- What it tests
- Checking real data before adding a constraint, reading what a failed migration leaves behind, cleaning duplicates without losing payments, and closing the gap in which new duplicates can arrive.
- First step
- The tests build their tables from nothing, so they never hold a duplicate, while production does. Before touching the migration, count the duplicates with a query that groups fee_instalments by student_id and number and keeps the groups with more than one row, then find out how they were created.
- Follow-ups
-
- What did the failed migration leave behind, and does it differ between MySQL, PostgreSQL and SQLite?
- How do you remove the duplicates without losing a payment?
- How do you stop new duplicates arriving before the index exists?
- How would you add the index on PostgreSQL without blocking writes to the table?
From the lesson Migrations and schema design
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
-
Fresher to mid
A teammate sees protected $guarded = [] on a User model and a controller line User::create(request()->all()). Walk through why this pair is a mass-assignment hole, and the two small changes you would make to close it.
- What it tests
- Reading the Fillable or Guarded setting as a security boundary, naming the attack an attacker builds and writing both the model change and the controller change that stop it.
- First step
- Name the two decisions this pair makes together. $guarded = [] tells Eloquent that no column is protected, and request()->all() hands over every key the request carried. Together they let a visitor set any column of the users table.
- Follow-ups
-
- Which column would an attacker add first, and what would it do?
- What is the smallest model change that closes the hole, and what does it look like?
- Why does the controller still have to change, even after the model is fixed?
- How would you prove the hole and the fix in a test?
From the lesson Eloquent models, mass assignment, soft deletes
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A teammate replaces the primary key of an Invitation model with a ULID and closes a security ticket about an insecure direct object reference. How would you decide whether the hole is closed, and what would you add if not?
- What it tests
- Separating identifier shape from access control, writing a negative test with another signed-in user, and knowing where to put the authorization check Laravel offers.
- First step
- Reframe the question. A ULID makes the next invitation hard to guess, which is useful against a scraper and against a visitor stepping an integer up by one. It does nothing about a visitor who already has a link that was not meant for them. Decide the hole against that second case, not the first.
- Follow-ups
-
- What test proves the hole and the fix for an authenticated unrelated user?
- Where does the authorization check belong, and what does it compare?
- How would a UUID or ULID still be useful once the policy is in place?
- What does OWASP's cheat sheet name as the cure, and how is that different from a key change?
From the lesson Eloquent models, mass assignment, soft deletes
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A teammate seeds the development database by reading 500 rows of real guardian names, phone numbers and emails from a production export and inserting them with Guardian::insert. The seeder works, and the demo page shows familiar names. What would you flag in the review, and how would you redesign the seeder so the demo stays convincing?
- What it tests
- Separating synthetic test data from real personal data, and knowing where the risk lies when real values enter the development path.
- First step
- Name the harm before the fix. Real guardians did not agree to appear on a developer's laptop or in a demo URL, and a database dump from that laptop could carry those names into a crash log, a backup or a shared screenshot. Then look at the import for what it is: production data on a development path, which is the thing to remove.
- Follow-ups
-
- Which parts of the dataset are the sensitive ones, and which can stay as they are?
- How does a Faker locale make the synthetic replacement look convincing on a demo screen?
- What would you ask the teammate to do with the production export they already have?
- Which test or lint check would catch the next person adding real data back?
From the lesson Factories, seeders and realistic test data
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 6 minutes
-
Mid to senior
A CI run takes ninety seconds and most of the time is one test that writes 10,000 instalments through Instalment::create() in a loop, so a dashboard can be measured. The product owner wants the test to stay but the suite to run under thirty seconds. How would you make it faster without losing what the test is proving?
- What it tests
- Reading a slow-test report, choosing a chunked insert over create() where the model events are not needed, and knowing which parts of a test cannot be sacrificed for speed.
- First step
- Measure before you change. Count the queries the test actually runs with DB::enableQueryLog, and separate the cost of the 10,000 writes from everything else the test does. If the writes dominate, the chunked insert path is the lever; if not, swap the lever before you cut.
- Follow-ups
-
- Which model events, if any, does the test rely on for the dashboard numbers to be right?
- How do you fill created_at and updated_at in a chunked insert?
- Is there a smaller row count that still proves what the test is proving?
- What assertion would stop a next change from quietly dropping back to create()?
From the lesson Factories, seeders and realistic test data
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
-
Mid to senior
The office wants a dashboard of the batches that have at least five students with an overdue instalment, with the number of those students and the overdue total of each batch. It must stay at one query however many batches there are. Write the query and explain each part.
- What it tests
- Combining a counted whereHas with a nested existence check, adding a constrained count, reaching instalments across the enrolments pivot for the total, and proving the query count.
- First step
- Split the report into its three parts: a filter on batches, a count of the matching students and a sum of their overdue amounts. For each part, decide whether a relationship method can produce it inside the batches query, and work out which tables the sum has to cross to reach the instalments.
- Follow-ups
-
- Would withSum('students.instalments', 'amount_paise') give the total, and why?
- How would you prove in a test that the dashboard runs one query?
- A student is enrolled in two batches. Where is their overdue amount counted, and is that right?
- Which indexes does the query need once the instalments table holds a million rows?
From the lesson Eloquent relationships, pivots and morph maps
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 4 minutes
-
Mid to senior
A team adds Relation::enforceMorphMap() to an application whose comments table already holds two million rows with class names in commentable_type. Minutes after the deploy, comment pages start failing. Explain what happened, how you would recover, and the rollout you would have planned instead.
- What it tests
- Knowing that an enforced map accepts only aliases, telling morphMap from enforceMorphMap, and converting millions of rows without a window of failing reads.
- First step
- Name the mismatch first. The enforced map stores aliases and requires them, but every old row still holds a class name, which is not an alias of the map, so reading those rows' parents throws. The repair is about the stored data as much as about the new line of code.
- Follow-ups
-
- What is the fastest safe recovery while the pages are failing?
- How do Relation::morphMap() and Relation::enforceMorphMap() differ while the rows are being converted?
- How would you convert two million rows without holding long locks?
- What test would have caught the failure before the deploy?
From the lesson Eloquent relationships, pivots and morph maps
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
An API endpoint returns 50 batches as JSON resources, each with its centre's name and its number of students. It is slow, and the query log shows 101 queries for one request. Walk through how you find the cause, the fix in the controller and the resource, and how you stop it coming back.
- What it tests
- Reading 101 as 1 + 50 + 50, fixing a lazy relationship and a count in one place, making the resource safe against lazy reads, and leaving a budget test behind.
- First step
- Do the arithmetic before reading any code: 101 is one query for the list plus two more for each of the 50 batches. Two per-row reads are hiding in what the resource does with each batch; find them in the query log as two statements repeated 50 times.
- Follow-ups
-
- What changes in the controller's query, and how many queries does the request send afterwards?
- How should the resource read the centre and the count so it never lazy loads them?
- What test keeps the endpoint at its new count?
- The endpoint is paginated at 15 a page. Does that change the numbers or the fix?
From the lesson Eager loading and the N+1 query problem
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
Your team wants a query budget test for every page of Pathshala's guardian portal, about 30 pages. Design the helper and the tests so they catch a new N+1 on any page without breaking every time a page legitimately changes.
- What it tests
- Choosing between a ceiling and an exact count, catching per-row queries with data size, keeping setup out of the count, readable failures, and where strict mode fits beside the budgets.
- First step
- Separate the two failures a budget must tell apart: a page that got a little more expensive for a good reason, and a page whose count now grows with its rows. Design the data and the assertion around the second; the first should be a small, deliberate edit.
- Follow-ups
-
- Ceiling or exact count, and why?
- How do you make sure a per-row query cannot hide below the budget?
- Which queries should never be counted, and how do you keep them out?
- Where do preventLazyLoading and the budgets overlap, and why keep both?
From the lesson Eager loading and the N+1 query problem
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
Guardians' phone numbers are stored with the encrypted cast. The office now wants to look a guardian up by phone number at the counter. Explain why the obvious query cannot work, and design a lookup that keeps the numbers encrypted at rest.
- What it tests
- Knowing why encrypted values cannot be compared, designing a lookup that keeps the numbers unreadable, and planning for key rotation and normalisation.
- First step
- Start from what the session showed: the same number stored for two students became two different texts, so no value typed at the counter can ever equal the stored one. Whatever the lookup compares, it cannot be the encrypted column itself.
- Follow-ups
-
- What exactly would you store so the lookup can match, and why is it safe?
- Why must the number be normalised before it is stored or searched?
- What happens to your design when APP_KEY is rotated?
- What does the design still leak, and is that acceptable here?
From the lesson Casts, accessors, enums and value objects
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
The office wants a new instalment status, waived, and wants pending renamed to due. The status column is cast to the FeeStatus enum and the guardian API returns the status in its JSON. Plan the change so that no read fails during the deploy and no API client breaks.
- What it tests
- Ordering enum, data and API changes around the ValueError an unknown value raises, and treating stored and published enum values as contracts.
- First step
- List who reads the stored text: the enum cast on every model read, validation through Rule::enum, and every API client that sees the status in JSON. Each one fails differently when the text changes, so the steps are ordered around them.
- Follow-ups
-
- Why is adding waived safe in one step while renaming pending is not?
- In what order do the enum, the data migration and the code that writes the status change?
- How do API clients keep working while the stored value changes?
- How would you prove before the deploy that no row holds a value the enum cannot read?
From the lesson Casts, accessors, enums and value objects
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
Pathshala's attendance admin list has two million rows and uses paginate(50). The first pages open quickly, but page 30,000 takes seconds and the database is busy whenever an export script walks the list. Diagnose the cause and redesign the list, saying what the office loses and how you would keep it usable.
- What it tests
- Reading the cost of offset and count queries from their plans, moving to keyset pagination with a unique order, and keeping the screen usable without page numbers.
- First step
- Ask the database what it does for that page before changing code: capture the two queries paginate() sends and run EXPLAIN QUERY PLAN, or EXPLAIN on the production engine, on each. Then compare the plans of an early page and of page 30,000.
- Follow-ups
-
- Why are the first pages fast and page 30,000 slow, query by query?
- What exactly changes in the code and in the order of the list?
- What does the office lose with cursor pagination, and what do you offer instead?
- How should the export script read the table?
From the lesson Scopes, Eloquent collections and pagination
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A global scope limits Batch queries to the signed-in user's centre. A queued job that emails one centre's fee reminders sent them to the guardians of every centre. Explain why, fix the job, and design the scope and the checks so that a missing user can no longer widen a query.
- What it tests
- Knowing when a scope that reads the user adds nothing, passing context to background work, choosing a fail-closed design, and keeping policies as the authorization layer.
- First step
- Start from where the job runs: a queue worker has no signed-in user, so ask what the scope's apply() method does when it finds none. The leak follows from that branch, not from the job's own query.
- Follow-ups
-
- What does the scope add when nobody is signed in, and why did no test notice?
- How should the job know which centre it works for?
- How would you change the scope so a missing centre cannot return every row?
- Where does the authorization policy still have to run, and why is the scope not enough?
From the lesson Scopes, Eloquent collections and pagination
Model answer, Pro.In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
All Building Web Apps with Laravel interview questions · The Building Web Apps with Laravel track