A database layer should make it easy to see what the application stores, how it retrieves it, and which rules protect the data. In his essay about building a finance app called FinLedger, Devanshu Patil argues for explicit queries and modest abstractions—not because every app needs the same design, but because predictable persistence code is easier to understand and maintain.
Start with the data the application actually stores
A transaction is not necessarily just an amount. In a finance application, it may include a date, type, category or tag, person, and additional metadata. Thinking through those fields and their relationships first helps make the persistence model reflect the application rather than an arbitrary set of generic operations.
That model also gives the code a concrete vocabulary. Instead of beginning with a universal repository API, ask what data the feature needs and what operations users perform on it. Patil’s examples include getTransactionsForMonth() and getTransactionsForPerson(): names that reveal the purpose of a query to someone reading the calling code.
Use validation and database constraints for different jobs
Application validation can tell a person what needs fixing and provide useful feedback before a save. Database constraints serve as a final integrity safeguard when data is written. In Patil’s framing, those layers complement each other: a friendly message does not replace a rule that protects stored data.
#1 Best Overall
SQLite documents constraints including UNIQUE, NOT NULL, CHECK, and FOREIGN KEY. Its documentation says constraint checks occur on writes. The right constraints depend on the data model; SQLite’s documentation is specifically about SQLite, so check the relevant documentation before assuming another database engine behaves identically. See SQLite’s CREATE TABLE documentation.
Make the query’s purpose visible
Generic methods such as save(), update(), delete(), find(), and query() can be useful building blocks. But when a straightforward operation is hidden behind several interfaces, the caller may have to trace layers of code just to understand what data it accesses.
Patil’s test for an abstraction is whether it removes meaningful complexity. As he puts it, “Abstraction is useful when it removes meaningful complexity.” He also cautions, “If it only hides a simple query behind five interfaces, it may be making the code harder to understand.” Those are design judgments from Patil’s essay, not universal rules: an abstraction that earns its place in one application may be unnecessary in another.
Fetch what the screen needs
When a screen displays one month of transactions, prefer a query scoped to that month over loading a large collection and filtering it in application code. A person-specific view can likewise use an operation such as getTransactionsForPerson(). This is Patil’s qualitative design advice; his essay does not report a benchmark or quantify a performance gain.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Rank #3
The practical question is whether the data access operation matches the caller’s need. Keeping that scope explicit can make it easier to inspect what a screen requests and avoid returning records it does not use.
Keep writes and operational behavior understandable
Persistence code should make it possible to follow what happens when data changes, including whether related changes belong together. SQLite documents ACID transactions and explains that a transaction’s changes occur completely or not at all, even if a write is interrupted by a crash or power failure. That description applies to SQLite; transaction guarantees and details should be verified for the database in use. See SQLite’s transaction documentation.
Patil also points to Room with Kotlin as an example of observable data flowing from database changes to UI state. That is an example from his essay, rather than a claim that every persistence layer should use the same library or pattern.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choose abstraction by the complexity it removes
Keeping a database layer boring does not mean avoiding every abstraction. Centralizing data access can help an application and its schema change more independently. Redgate’s guide describes that encapsulation benefit while emphasizing that using an ORM does not eliminate the need to understand the database and schema. Read Redgate’s guide to the repository pattern.
A useful review can focus on the trade-offs rather than a blanket preference:
- Clarity: Can a reader tell which data access operation is happening from its name and call site?
- Integrity: Which rules are user-facing validation, and which are enforced when data is stored?
- Complexity: Does an abstraction remove meaningful repetition or complexity, or merely place a simple query behind extra layers?
- Scope: Does the query return the records the caller needs, rather than fetching a broader set to filter elsewhere?
- Change boundaries: Does centralizing access make application code and schema easier to change independently, without obscuring how the database works?
The point is not to reject repositories, ORMs, or reactive data flow. It is to keep the path from an application need to the database operation legible, and to add structure where it makes that path easier to work with.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
