Backend Data Layer Design and SQL/NoSQL Operations

When designing the data layer in the backend, the first decision to make is not what database to use, but how the data will be read and changed. Even for the same member, order, and product data, the design varies depending on whether strong consistency is required, whether the data is read together as a document, whether the search pattern is fixed, and whether it can be lowered to the storage layer over time.

If you look at the SQL, Oracle, MongoDB, Azure Storage, and Cosmos DB exercises separately, they each seem like different topics. However, in real applications, the relational model, document model, object storage, and key/value store are used together in one system. Therefore, rather than memorizing grammar, it is much more useful to connect what each repository is good at and what is problematic during operation.

RDBMS focuses on relationships and transactions

The strengths of relational databases lie in dividing data into tables, expressing relationships through primary and foreign keys, and protecting change units through transactions. In domains where consistency is important, such as members and orders, this advantage is clear.

create table members (
  id bigint generated by default as identity,
  email varchar(255) not null,
  name varchar(100) not null,
  created_at timestamp not null default current_timestamp,
  primary key (id),
  unique (email)
);

create table orders (
  id bigint generated by default as identity,
  member_id bigint not null,
  status varchar(30) not null,
  total_amount numeric(12, 2) not null,
  created_at timestamp not null default current_timestamp,
  primary key (id),
  foreign key (member_id) references members(id)
);

Here, unique (email) makes the database the last line of defense, apart from the application's verification of duplicate members. Even if the service code is first checked for duplication, application-level verification alone may not be enough if two requests come in at the same time. In this case, DB constraints and transactions must exist together.

SQL can be broadly divided into DDL, DML, DCL, and TCL. DDL creates structures such as tables and indexes, and DML searches or changes data. DCL controls permissions, and TCL handles transaction boundaries. In back-end development, SELECT, INSERT, UPDATE, and DELETE are used every day, but in an operational environment, problems are reduced by looking at constraints, permissions, indexes, and transactions together.

When it comes to SELECT, the access path is more important than the result.

When first learning SQL, the focus is on where, group by, order by, and join syntax. However, as the service grows, it becomes more important what index is used for the same SELECT, how many rows are read, and how much memory sorting and grouping uses.

select
  m.id,
  m.email,
  count(o.id) as order_count,
  sum(o.total_amount) as total_amount
from members m
left join orders o on o.member_id = m.id
where m.created_at >= timestamp '2025-01-01 00:00:00'
group by m.id, m.email
having count(o.id) >= 1
order by total_amount desc;

This query joins members and orders and counts the number and amount of orders for each member. Although it is not difficult from a grammar point of view, it does raise some questions from an operational perspective. Is there an index on members.created_at? Is orders.member_id a foreign key and a join index? Since order by total_amount desc sorts the calculated aggregate results, doesn't the cost increase as the data increases? You should also check whether the query needs to be processed in a real-time API, or whether it needs to be separated into a batch or report table.

Indexes speed up lookups but increase write costs. Creating indexes for all conditions does not last long. Make a decision by looking at the query frequency, selectivity, sort conditions, and join conditions together.

create index idx_orders_member_created_at
on orders(member_id, created_at desc);

For composite indexes, column order is important. If the pattern is to first narrow down by member_id and then look up the latest orders, the above index is helpful. Conversely, an analytical query that first scans all orders by date range may require a different index or separate aggregate table.

The local DB container is not a miniature version of the production DB

You can quickly create a development environment by running MySQL or PostgreSQL with Docker. Experience in launching a DB locally and connecting to an API is important, but container execution commands do not replace operational design.

docker run --name local-postgres \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=app-password \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

Check the connection with psql.

psql "postgresql://app:app-password@localhost:5432/appdb"

MySQL can be run similarly.

docker run --name local-mysql \
  -e MYSQL_ROOT_PASSWORD=root-password \
  -e MYSQL_DATABASE=appdb \
  -e MYSQL_USER=app \
  -e MYSQL_PASSWORD=app-password \
  -p 3306:3306 \
  -v mysqldata:/var/lib/mysql \
  -d mysql:8

What can be checked in the local container is SQL grammar, schema migration, application connection, and simple index effects. In an operational environment, backup, recovery, connection pool, TLS, secret management, monitoring, replication, version upgrade, and maintenance window are added to this.

local container
  - fast setup
  - disposable data
  - simple port mapping
  - manual inspection

managed production DB
  - backup and point-in-time restore
  - private network access
  - TLS and credential rotation
  - monitoring and slow query analysis
  - scaling and maintenance policy

Therefore, docker run in the development environment should be used as a starting point, and in operation, you should move to Terraform, managed database settings, and migration pipeline. What is more important than “where it is executed” is how the schema and connection settings are managed for each environment.

Transactions are designed based on failure situations

It is better to view transaction ACID as a failure scenario rather than memorizing it as a concept. If you think about the money transfer example, the two UPDATEs - deducting from one account and adding to another - should be one unit of work. If only the first UPDATE succeeds and the second fails, the data loses business meaning.

begin;

update accounts
set balance = balance - 100
where id = 1
  and balance >= 100;

update accounts
set balance = balance + 100
where id = 2;

commit;

In actual service, more concerns arise here. When the balance is insufficient, you need to check how many changes were made in the first UPDATE, and if changes are made to the same account at the same time in simultaneous requests, lock contention will occur. If the isolation level is set too low, dirty read, non-repeatable read, and phantom read problems may occur, and if the isolation level is set too high, throughput may decrease.

When using Spring/JPA, transaction boundaries are usually placed in service methods. However, habitually adding @Transactional to all methods is not a good design. You must distinguish whether it is a read-only query, whether multiple aggregates are being changed together, and whether external API calls should not be included within a transaction.

@Transactional
public Long placeOrder(Long memberId, List<OrderLineCommand> lines) {
    Member member = memberRepository.findById(memberId)
        .orElseThrow(() -> new IllegalArgumentException("member not found"));

    Order order = Order.create(member, lines);
    orderRepository.save(order);
    return order.getId();
}

If an external payment API is called within a transaction, the DB locking time will be prolonged, and inconsistencies may occur where the DB commit fails after a successful external call. This flow must be solved with structures such as outbox pattern, state-based retry, and compensation transaction.

What remains from Oracle practice is not tools but a sense of operation

Installing Oracle 19c and practicing with the scott account was more meaningful in experiencing the RDBMS operating environment than the installation procedure itself. The process of connecting to SQL*Plus, activating an account, and checking example tables such as emp, dept, and salgrade demonstrates that the database is an independent runtime outside of the application.

sqlplus sys/orcl as sysdba

show user;
alter session set "_ORACLE_SCRIPT" = true;
@C:\Database\WINDOWS.X64_193000_db_home\rdbms\admin\scott.sql

conn scott/tiger
select * from dept;
select * from emp;

Even if developers only use managed DBs, they still encounter accounts, permissions, schemas, access tools, backups, patches, and version compatibility. Experience with local Oracle installations leads to similar questions when dealing with PostgreSQL, MySQL, Azure SQL, and RDS. What kind of permissions will be given to the application account? Will DDL permissions and DML permissions be separated? Which account will the migration be performed on? Is access to the operational DB limited to the path where audit logs are stored?

grant select, insert, update, delete on members to app_user;
revoke drop any table from app_user;

It is easy to give broad authority for convenience, but in situations of failure or infringement, the scope of authority becomes the scope of damage.

MongoDB designs document boundaries first

In the MongoDB lab, collections such as emp, dept, salgrade, and book were created, JSON documents were inserted, and conditional search, projection, sorting, skip, limit, and update operators were handled. Unlike RDBMS, in MongoDB, what data a document holds together is more important than the relationship between tables.

db.book.insertOne({
  title: "Node.js",
  author: {
    name: "Tom",
  },
  price: 50000,
  categories: ["IT", "Backend"],
});

The document model is powerful when it puts data that is read together into one document. For example, embedding can be advantageous for information that is always required when searching, such as posts, author display names, and tag lists. Conversely, if there are independent life cycles and consistency requirements, such as ordering, payment, and inventory, it may be more dangerous to force them into one document.

The query operation asks the same question as SQL, only the expression is different.

db.emp.find(
  { deptno: 30, job: "MANAGER" },
  { deptno: 1, ename: 1, job: 1 }
);

db.emp.find({
  $or: [{ sal: 1500 }, { sal: 1600 }],
});

db.emp.find({ ename: { $regex: /^S/ } });

Indexes are also essential in MongoDB. If there is no index on a frequently searched field, a collection scan occurs. If sorting conditions are included, the composite index order must also be considered.

db.emp.createIndex({ deptno: 1, job: 1, ename: 1 });
db.emp.find({ deptno: 30, job: "MANAGER" }).sort({ ename: 1 });

The update operator also demonstrates the characteristics of the document model.

db.dept.updateOne(
  { deptno: 55 },
  {
    $set: { dname: "Web Dev" },
    $unset: { pno: "" },
  }
);
`

```$set`, `$unset`, `$inc`, and `$rename` allow you to change only part of the document. However, a loose schema does not mean that it can be stored arbitrarily. In operation, JSON Schema validation, application-level DTO validation, and migration script are required together.

## DynamoDB requires you to first understand the difference between Query and Scan.

DynamoDB is managed NoSQL, so the server operating burden is low, but if it is designed incorrectly, cost and performance problems quickly become apparent. In particular, you must understand the difference between `Query` and `Scan`. `Query` is a method of finding necessary items based on a partition key, and `Scan` is a method of broadly scanning the table. Both work when there is little data, but as data increases, Scan increases costs and delays.

```txt
Query
  - partition key required
  - efficient access path
  - predictable cost when key design is good

Scan
  - reads broad table range
  - expensive as data grows
  - useful for admin jobs or small tables, not core request path

For example, if you are looking up the notification list for each user, you can design the partition key as userId and the sort key as createdAt.

Table: notifications
PK: userId
SK: createdAt

Query:
  userId = "usr_01"
  createdAt between "2025-12-01" and "2025-12-31"

If you need to look up notifications by status, the primary key alone is not enough. At this time, GSI (Global Secondary Index) can be added.

GSI1
  PK: status
  SK: createdAt

But GSI is not free. Write costs and storage space increase, and table design becomes complicated if all query requirements are attempted to be resolved through indexes. When using DynamoDB, you should write down the access pattern first, rather than saying, “I can just look it up later.”

Access patterns
1. Fetch the 20 most recent notifications by userId
2. Count unread notifications
3. Fetch a specific notificationId
4. Expire notifications older than 30 days

The partition key, sort key, GSI, TTL, and item shape are determined according to this access pattern. Rather than combining them later using JOIN like in RDBMS, it is closer to storing data according to the shape to be read.

Azure Storage and Cosmos DB are divided based on access patterns

In the Azure Storage exercise, we confirmed storage in the form of Blob, Files, and Tables. Blob storage is suitable for file-level objects such as images, logs, backups, and documents. Azure Files is good for when SMB/NFS-based file sharing is needed, and Table Storage is suitable for simple key/value lookups based on PartitionKey and RowKey.

{
  "id": "1",
  "productname": "Bearing Ball",
  "productnumber": "BA-8327",
  "quantityinstock": 1109,
  "productcategory": {
    "subcategory": "Parts",
    "category": "Components"
  }
}

These JSON objects can be placed in Blob or Cosmos DB, but the storage selection criteria are different. If you store the file itself and read it occasionally, a blob is better, and if you frequently search and change it based on document fields, a document DB like Cosmos DB is more natural. When using Table Storage, PartitionKey design determines performance and scalability.

Cosmos DB for NoSQL can search JSON documents using a syntax similar to SQL.

SELECT *
FROM c
WHERE CONTAINS(c.name, "Helmet")

However, what is more important in Cosmos DB is the partition key and RU. Even for the same query, the cost varies greatly depending on whether the partition key is used. If it is a user-specific dashboard, you should consider userId, if it is a tenant-based SaaS, you should consider tenantId, and if it is a time-based event, you should consider a complex design tailored to the query pattern.

{
  "id": "daily-2025-12-23",
  "userId": "user-123",
  "type": "dailySummary",
  "createdAt": "2025-12-23T09:00:00Z",
  "items": [
    {
      "category": "cloud",
      "title": "AKS release note"
    }
  ]
}

If the search for this document is always based on userId, the natural partition key is userId. Conversely, if you frequently aggregate data from all users on a specific date, you may need a separate analysis storage or materialized view collection.

Azure SQL and Flexible Server need to look at the boundary behind the word managed

Azure SQL Database, Azure Database for MySQL Flexible Server, and Azure Database for PostgreSQL Flexible Server reduce operational burden compared to installing the DB directly on a VM. However, managed DB does not mean that data models, connection boundaries, backups, and performance tuning disappear. Server patching and some high availability are handled by the cloud, but schema design, query quality, indexes, connection pools, permissions, and network perimeter are still the responsibility of the application team.

Even within the same RDBMS family, the selection criteria are different. Azure SQL fits well with the Microsoft SQL Server ecosystem, T-SQL, SSMS/Azure Data Studio, and Managed Instance migration. PostgreSQL has a strong standard SQL, extensions, JSONB, and open source ecosystem, while MySQL is familiar with web applications and CMS. The important thing is not “which DB is better,” but what kind of transactions and queries the application has.

relational database selection notes

Azure SQL
  - SQL Server compatibility
  - migration from Windows / MS SQL workloads
  - T-SQL, SSMS, Azure Data Studio workflow

PostgreSQL Flexible Server
  - strong relational modeling
  - JSONB and extension ecosystem
  - good fit for product backend services

MySQL Flexible Server
  - familiar web application stack
  - simple operational model
  - broad framework support

Azure Data Studio's migration extension can help you check this boundary. Migration may seem like a task of moving data, but in reality, it is a task of assessing compatibility, checking unsupported features, and deciding on downtime plans, network connections, authentication methods, and rollback strategies.

migration review

1. source engine and version
2. unsupported features and compatibility issues
3. schema size and data volume
4. online or offline migration
5. network path: public, VPN, ExpressRoute, private endpoint
6. application connection string cutover
7. rollback and validation plan

In small labs, it is easy to check the results by connecting to the DB through a public endpoint. In operation, this configuration should not be taken as is. At a minimum, narrow down the allowed IPs and, if possible, review private endpoints and VNet integration. The access account should also have read/write permissions and schema permissions rather than using the administrator account as is in the application.

-- example: separate application role
CREATE USER app_writer WITH PASSWORD = 'use-secret-store';
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_writer;

CREATE USER app_reader WITH PASSWORD = 'use-secret-store';
GRANT SELECT ON SCHEMA::dbo TO app_reader;

The password example is for structure explanation only, and in an actual environment, it must be stored in Key Vault or Secret Manager. DB security does not end with “do not open the firewall.” You need to check which accounts can read and write which tables, with what permissions the migration script is run, and whether audit logs are left.

Analysis repository does not replace operational DB

Services such as Synapse Analytics, Databricks, Data Lake Storage, and Stream Analytics are closer to moving data into analysis and processing flows rather than replacing the transactional DB of the operational API. The operational DB focuses on short requests and consistency, and the analysis storage is designed for bulk reading, aggregation, machine learning, and reporting.

The important boundary here is the time difference between operational data and analytical data. The OLTP DB is responsible for order status, payment status, and authorization status that must be displayed immediately on the operation screen. On the other hand, it is better to transfer daily sales aggregation, user behavior analysis, and log-based failure pattern analysis to a data lake or analysis engine. Attaching analytical queries directly to the operational DB can cause transaction delays, locking, and cost issues.

OLTP path
  - small request
  - transaction consistency
  - indexed lookup
  - low latency

analytics path
  - batch or streaming ingestion
  - large scan and aggregation
  - schema evolution
  - report and model training

Just because JSON is stored in Cosmos DB or Blob Storage, it does not immediately become an analysis platform. If data needs to be re-read, joined, and aggregated, file formats, partitions, schema evolution, and reprocessing strategies are needed. For example, if you store daily events, dividing the routes by date and tenant will affect cost and performance later.

raw/events/year=2026/month=07/day=05/tenant=a/*.json
curated/events/year=2026/month=07/day=05/tenant=a/*.parquet

You don't need to turn all your data into a perfect lakehouse from the beginning. However, when analysis requests begin to repeat in the operational DB, it is time to separate the API data model and analysis data model.

Choosing a data access technique depends on the shape of the problem

The way to access DB in Spring is divided into several steps. Pure JDBC, JdbcTemplate, MyBatis, JPA, and Spring Data JPA are all the same in that they “connect to the DB,” but the complexity they hide and the control points they reveal are different.

Pure JDBC must directly handle connection acquisition, Statement creation, ResultSet processing, exception handling, and resource return. This method is good for understanding the order in which database access actually occurs, but it involves a lot of repetitive code and leaves a lot of room for mistakes.

public Member save(Member member) {
    String sql = "insert into member(name) values (?)";

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        conn = dataSource.getConnection();
        pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, member.getName());
        pstmt.executeUpdate();

        rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            member.setId(rs.getLong(1));
        }
        return member;
    } catch (SQLException e) {
        throw new IllegalStateException(e);
    } finally {
        close(conn, pstmt, rs);
    }
}

JdbcTemplate reduces this repetition. SQL is written directly, but the framework takes care of connection and resource organization. It is suitable when you want to reduce noise in Java code while maintaining clear SQL form.

public Optional<Member> findById(Long id) {
    List<Member> result = jdbcTemplate.query(
        "select id, name from member where id = ?",
        memberRowMapper(),
        id
    );
    return result.stream().findAny();
}

private RowMapper<Member> memberRowMapper() {
    return (rs, rowNum) -> {
        Member member = new Member();
        member.setId(rs.getLong("id"));
        member.setName(rs.getString("name"));
        return member;
    };
}

MyBatis puts SQL more at the forefront. MyBatis is often a realistic choice in SI environments that require dynamic search conditions, complex filters on the administrator screen, report-based queries, and existing DB schema. Because SQL is not hidden, tuning and cause analysis are fast, but as the number of query fragments increases, XML or mapper management rules are needed.

<select id="findNotifications" resultType="Notification">
  select id, title, target_group, status, created_at
  from notifications
  where deleted = false
  <if test="targetGroup != null">
    and target_group = #{targetGroup}
  </if>
  <if test="status != null">
    and status = #{status}
  </if>
  order by created_at desc
  limit #{limit} offset #{offset}
</select>

JPA focuses on object model and change detection. In simple CRUD, productivity is high and it is easy to express state changes at the aggregate level in code. However, the fact that JPA creates SQL instead is both an advantage and a risk. If you do not understand N+1, wider-than-expected update, persistence context scope, and lazy loading issues, it becomes difficult to track queries during operation.

@Transactional
public void approveRefund(Long refundId) {
    Refund refund = refundRepository.findById(refundId)
        .orElseThrow(() -> new IllegalArgumentException("refund not found"));

    refund.approve();
}

Although this code is short, it actually searches for entities within a transaction, changes its state, and sends an update SQL at the time of commit. Understanding this flow allows you to use JPA as a tool, not as “magic.”

The selection criterion should be the shape of the problem, not technology preference.

JdbcTemplate
- When the SQL is simple and direct control is useful
- When repetitive code should be reduced without hiding the query shape

MyBatis
- When there are many dynamic search conditions
- When existing schemas and complex joins or reporting queries matter
- When SQL tuning and traceability matter in operations

JPA / Spring Data JPA
- When state transitions should be expressed around the domain model
- When aggregate changes and transaction boundaries should be managed in code
- When simple CRUD productivity matters

In practice, there are cases where roles are divided rather than sticking to just one. Core domain status changes can be handled with JPA, and complex administrator searches and report queries can be separated using MyBatis or JdbcTemplate. The important thing is to have clear boundaries so that transaction, cache, and change responsibilities do not conflict when the same table is touched in multiple ways.

Standards for sharing storage within one system

The backend data layer becomes complex when trying to solve all problems with one DB. By narrowing down the role of each repository, the design becomes clearer.

RDBMS focuses on core transaction data where consistency is important. Redis places short-lived states such as caches, rate limits, and sessions. Object storage is placed on files and large objects. Document DB is used for data that is read together in document units or data with many flexible properties. Search engines are responsible for full-text searches and complex filtering.

The important thing is that as storage increases, synchronization and failure scenarios also increase. Although the order is stored in the RDB, reflection in the search index may fail. Blob upload may be successful, but saving DB metadata may fail. Considering this situation, idempotency key, outbox table, retry, dead letter queue, and cleanup job must be designed.

create table outbox_events (
  id bigint generated by default as identity,
  aggregate_type varchar(100) not null,
  aggregate_id varchar(100) not null,
  event_type varchar(100) not null,
  payload text not null,
  created_at timestamp not null default current_timestamp,
  processed_at timestamp null,
  primary key (id)
);

The application stores business data and outbox events together within a transaction, and a separate worker reads the events and reflects them in the search index or document DB. This way, external system call failures do not directly break the core DB transaction.

In tasks with state transitions, this structure becomes more important. For example, functions such as payment and refund, parking ticket allocation, and notification sending are not simple CRUD, but the key is “how to move from which state to which state.”

create table payment_requests (
  id bigint generated by default as identity,
  member_id bigint not null,
  amount numeric(12, 2) not null,
  status varchar(30) not null,
  requested_at timestamp not null default current_timestamp,
  approved_at timestamp null,
  canceled_at timestamp null,
  primary key (id)
);

create table payment_status_history (
  id bigint generated by default as identity,
  payment_id bigint not null,
  from_status varchar(30),
  to_status varchar(30) not null,
  reason varchar(255),
  changed_by varchar(100) not null,
  changed_at timestamp not null default current_timestamp,
  primary key (id),
  foreign key (payment_id) references payment_requests(id)
);

You can see the current status by just looking at payment_requests.status, but it is difficult to know why it has changed. What operators need to check is not only “currently approved,” but also “who approved it, when it was approved, and for what reason.” Therefore, it is advantageous for tracking to leave the current state in the core table and state transitions in the history table.

Service code should not allow state changes anywhere.

@Transactional
public void approvePayment(Long paymentId, String operatorId) {
    Payment payment = paymentRepository.findByIdForUpdate(paymentId)
        .orElseThrow(() -> new IllegalArgumentException("payment not found"));

    payment.approve();

    paymentHistoryRepository.save(
        PaymentStatusHistory.of(paymentId, "REQUESTED", "APPROVED", operatorId)
    );

    outboxRepository.save(
        OutboxEvent.paymentApproved(paymentId)
    );
}

Here, a locking method like findByIdForUpdate is necessary depending on the situation. This is because two operators may process the same request at the same time, or API retries may repeat the same state change. You don't need to put pessimistic locks on every function, but areas where redundant processing costs are high, such as money, inventory, assignments, and permissions, require a clear concurrency model.

State transitions also affect API response design.

{
  "id": 101,
  "status": "APPROVED",
  "availableActions": ["cancel", "refund"],
  "lastChangedAt": "2025-12-23T10:15:00Z"
}

Rather than having the front-end arbitrarily interpret the state value and create a button, if the back-end provides the possible actions in the current state, the possibility of the screen and policy being misaligned is reduced. Ultimately, data layer design does not end with table design, but also extends to service methods, transactions, API responses, and operation screens.

Cleanup

SQL grammar, Oracle installation, MongoDB CRUD, Azure Storage, and Cosmos DB exercises are closer to tool usage when viewed separately. But when you tie it all down to the backend data layer, common questions emerge. What data requires strong consistency? Which views occur most frequently? How does an index balance read and write costs? Does the partition key divide traffic evenly? How to recover from a state where only some repositories succeeded when a failure occurs.

Database selection is a matter of boundary setting, not technology preference. RDBMS handles relationships and transactions, MongoDB and Cosmos DB handle documents and access patterns, Object Storage handles files and large objects, and Redis handles short-lived, fast states. Backend design is more about ensuring that these boundaries don't mix, and placing transactions, events, retries, and observability where they do.