DEV Community: Sri Mahalakshmi

Idempotency in a Digital Wallet System

Sri Mahalakshmi — Sun, 29 Mar 2026 09:33:40 +0000

In digital wallet systems like PhonePe or GPay, the same request can sometimes be sent multiple times due to network issues or retries. If the system processes the same request more than once, it can lead to incorrect balances. This is where idempotency becomes important.

To understand this, I performed a simulation using the accounts table with two users, Alice and Bob. Initially, their balances were 600 and 700.

First, I performed a transfer of 200 from Alice to Bob. After executing the queries, the balances became 400 for Alice and 900 for Bob.

Then, I repeated the same transfer operation again, simulating a duplicate request. After running the same queries again, the balances changed to 200 for Alice and 1100 for Bob.

This shows that the system processed the same request multiple times, leading to repeated deductions and credits. The system did not prevent duplicate execution.

From this experiment, it is clear that without proper handling, repeated requests can cause inconsistent data. In real-world systems, techniques like unique transaction IDs or idempotency keys are used to ensure that the same request is processed only once.

This helps maintain correct balances even if a request is sent multiple times.

Managing Roles and Permissions in PostgreSQL

Sri Mahalakshmi — Sun, 29 Mar 2026 09:22:40 +0000

In a database system, controlling who can access and modify data is very important. In real-world applications like digital platforms, different users should have different levels of access based on their role. To understand this, I performed a series of tasks using the DVD Rental database in PostgreSQL.

First, I created a role called report_user with login access and gave it permission to read only from the film table. This ensured that the user could view data but not modify it.

Next, I tested access to the customer table using this role. As expected, PostgreSQL denied permission because no access had been granted. I then fixed this by giving SELECT permission on the customer table.

After that, I restricted access further by allowing report_user to view only specific columns: customer_id, first_name, and last_name. This demonstrated column-level security, where users can see only the required data instead of the entire table.

Then, I created another role called support_user. This role was given permission to read from the customer table and update only the email column. At the same time, delete access was not provided, ensuring that important data could not be removed.

In the next step, I removed the previously given SELECT permission on the film table from report_user, showing how permissions can be revoked when no longer needed.

I also created a group role called readonly_group and granted it SELECT access on all tables. Then, I created two users, analyst1 and analyst2, and added them to this group. This allowed both users to inherit read-only access without assigning permissions individually.

Through this exercise, I understood how PostgreSQL manages roles and permissions effectively. It allows fine-grained control over data access, ensuring security and proper usage of the database. This is especially important in real-world systems where different users require different levels of access.

Ensuring Atomicity in a Digital Wallet System

Sri Mahalakshmi — Sun, 29 Mar 2026 09:01:45 +0000

In digital wallet systems like PhonePe or GPay, it is important that transactions are handled correctly. When money is transferred from one user to another, both operations — deducting from the sender and adding to the receiver — must happen together. If one step fails, the entire transaction should fail. This is known as atomicity.

To understand this, I used the accounts table with two users, Alice and Bob. Initially, their balances were 800 and 700.

First, I performed a successful transaction where 200 was transferred from Alice to Bob. Inside a transaction block, I deducted 200 from Alice and added 200 to Bob, and then committed the transaction. After this, the balances were updated correctly to 600 for Alice and 900 for Bob.

Next, I tested what happens when an error occurs during the transaction. I again started a transaction and deducted 200 from Alice. However, in the next step, I intentionally introduced an error in the update query for Bob. This caused the transaction to fail.

Even though the deduction step was executed first, the database did not apply any changes permanently. After the failure, I checked the balances again, and they remained unchanged at 800 and 700.

This shows that PostgreSQL does not allow partial updates in a transaction. Either all operations are completed successfully, or none of them are applied.

From this experiment, it is clear that atomicity ensures reliability in financial systems. It prevents situations where money is deducted from one account but not added to another, maintaining correct and consistent data at all times.

Maintaining Consistency in a Digital Wallet System

Sri Mahalakshmi — Sun, 29 Mar 2026 08:51:05 +0000

In digital wallet applications like PhonePe or GPay, it is very important to maintain correct data at all times. Even a small mistake, such as allowing a negative balance, can lead to serious problems like incorrect transactions or money loss. This is where consistency in a database becomes important.

To understand this, I used an accounts table with two users, Alice and Bob. After previous operations, their balances were 800 and 700.

First, I tried to update Alice’s balance to a negative value directly. When I executed the query, PostgreSQL immediately gave an error and did not allow the update. This happened because of the constraint CHECK (balance >= 0) defined in the table. This constraint ensures that balance can never go below zero.

Next, I tried to deduct more money than Alice had. Since her balance was 800, I attempted to subtract 1000. Again, PostgreSQL rejected the operation because it would result in a negative balance. The database prevented invalid data from being stored.

I also tried performing this operation inside a transaction. Even in that case, the update failed and the transaction did not get committed. The database ensured that no incorrect changes were applied.

After all these failed attempts, I checked the table again. The balances remained unchanged at 800 and 700. This shows that the database maintained a consistent and valid state throughout.

From this experiment, it is clear that PostgreSQL enforces rules using constraints to prevent invalid data. At the same time, in real-world applications, the system should also check the balance before performing a transaction to avoid such errors.

Overall, this shows how consistency is maintained by ensuring that only valid data is stored in the database.

Ensuring Durability in a Digital Wallet System Using PostgreSQL

Sri Mahalakshmi — Sun, 29 Mar 2026 08:34:09 +0000

In digital wallet applications, once a transaction is completed, users expect it to remain saved even if the system crashes or restarts. This requirement is handled by the durability property in database systems.

To understand this, I performed a simple experiment using PostgreSQL.

First, I created an accounts table and inserted two users: Alice with a balance of 1000 and Bob with a balance of 500. This represented the initial state of the system.

Then, I performed a transaction to transfer 200 from Alice to Bob. Inside the transaction, 200 was deducted from Alice’s balance and added to Bob’s balance. After executing these operations, I committed the transaction. Once committed, the updated balances became 800 for Alice and 700 for Bob.

To simulate a system restart, I closed the database session and reconnected again. After reconnecting, I checked the account balances using a select query. The balances remained unchanged at 800 and 700.

This shows that once a transaction is committed, the changes are permanently stored in the database and are not lost, even after a restart. This property is known as durability.

If a failure occurs before the commit, the changes will not be saved. But if the failure happens after the commit, the data remains safe and consistent.

This experiment demonstrates how databases ensure reliability in real-world applications like digital payment systems, where maintaining correct and permanent transaction data is essential.

Alter Tables

Sri Mahalakshmi — Thu, 26 Mar 2026 15:34:00 +0000

1. Make email NOT NULL in customers

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

Ensures all future records must have an email.

2. Make username unique in users

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

Prevents duplicate usernames.

3. Add CHECK on price in products

ALTER TABLE products ADD CONSTRAINT check_price CHECK (price > 0);

Ensures price is always greater than 0.

4. Set default status in orders

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

Automatically assigns pending if no value is given.

5. Add salary column with constraints

ALTER TABLE employees ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);

Ensures salary is mandatory and above 10,000.

6. Add ON DELETE CASCADE to foreign key

ALTER TABLE employees DROP CONSTRAINT employees_department_id_fkey;

ALTER TABLE employees ADD CONSTRAINT employees_department_id_fkey FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;

Deletes employees automatically when a department is removed.

7. Remove CHECK constraint on balance

ALTER TABLE accounts DROP CONSTRAINT accounts_balance_check;

Removes restriction on balance.

8. Add composite UNIQUE in payments

ALTER TABLE payments ADD CONSTRAINT unique_user_transaction UNIQUE (user_id, transaction_id);

Ensures no duplicate transaction per user.

Create Tables

Sri Mahalakshmi — Thu, 26 Mar 2026 15:30:41 +0000

1. Students table with unique id
CREATE TABLE students ( id SERIAL PRIMARY KEY, name TEXT,age INT);

PRIMARY KEY ensures each student is uniquely identified.

2. Employees table with required fields
CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL,phone_number TEXT);

NOT NULL ensures name and email cannot be empty, phone is optional.

3. Users table with unique values
CREATE TABLE users (id SERIAL PRIMARY KEY,username TEXT UNIQUE,email TEXT UNIQUE);

UNIQUE prevents duplicate usernames and emails.

4. Products table with constraints
CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,price INT CHECK (price > 0),stock INT CHECK (stock >= 0));

CHECK ensures valid price and non-negative stock.

5. Orders table with defaults
CREATE TABLE orders (id SERIAL PRIMARY KEY,status TEXT DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

DEFAULT assigns automatic values if none provided.

6. Accounts table with rules
CREATE TABLE accounts (id SERIAL PRIMARY KEY,account_number TEXT UNIQUE NOT NULL,balance INT CHECK (balance >= 0));

Ensures valid account number and no negative balance.

7. Enrollments with composite uniqueness
CREATE TABLE enrollments (student_id INT,course_id INT,UNIQUE (student_id, course_id));

Prevents duplicate enrollment for same student-course pair.

8. Foreign key relationship
CREATE TABLE departments (id SERIAL PRIMARY KEY,name TEXT);

CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT,department_id INT,FOREIGN KEY (department_id) REFERENCES departments(id));

FOREIGN KEY ensures employees belong to valid departments.

9. Cascade update and delete
CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT,department_id INT,FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE);

ON DELETE CASCADE removes employees when a department is deleted.
ON UPDATE CASCADE updates department_id automatically.

Filter in DB

Sri Mahalakshmi — Thu, 26 Mar 2026 15:07:37 +0000

1.SELECT is used to choose what data to display.
2.WHERE is used to filter records based on conditions.
3.AND and OR are used to combine multiple conditions.
4.ORDER BY is used to sort the data either ascending or descending.
5.LIMIT is used to restrict how many rows are shown.
6.OFFSET is used to skip a certain number of rows.
7.BETWEEN is used to filter values within a range.
8.IN is used to match multiple possible values.
9.LIKE and SIMILAR TO are used for pattern matching in text.
10.IS NULL is used to find missing or empty values.

1. Get movies with rental rate greater than 3

SELECT title, rental_rate FROM film WHERE rental_rate > 3;

2. Rental rate > 3 and replacement cost < 20

SELECT title, rental_rate, replacement_cost FROM film WHERE rental_rate > 3 AND replacement_cost < 20;

3. Rated PG or rental rate 0.99

SELECT title, rating, rental_rate FROM film WHERE rating = 'PG' OR rental_rate = 0.99;

4. Top 10 movies by rental rate

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10;

5. Skip 5 and get next 3

SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3;

6. Skip 5 and get next 3

SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 FETCH NEXT 3 ROWS ONLY;

7. Rental duration between 3 and 7

SELECT title, rental_duration FROM film WHERE rental_duration BETWEEN 3 AND 7;

8. Title starts with A and ends with e

SELECT title FROM film WHERE title LIKE 'A%e';

9. Customers without email

SELECT first_name, last_name FROM customer WHERE email IS NULL;

10. Movies from 2006 with conditions

SELECT title, rental_rate, release_year FROM film WHERE release_year = 2006 AND rental_rate IN (2.99, 3.99) AND title LIKE 'S%' LIMIT 5;

11. Skip 20 customers, show next 10

SELECT first_name, last_name FROM customer ORDER BY last_name LIMIT 10 OFFSET 20;

12. Highest replacement cost (skip first)

SELECT title, replacement_cost FROM film ORDER BY replacement_cost DESC OFFSET 1 FETCH NEXT 5 ROWS ONLY;

13. Rentals between dates

SELECT rental_id, rental_date, customer_id FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

14. Actors with "man" in last name

SELECT first_name, last_name FROM actor WHERE last_name LIKE '%man%';

15. Movies with no special features

SELECT title FROM film WHERE special_features IS NULL;

16. Rental duration more than 7

SELECT title, rental_duration FROM film WHERE rental_duration > 7;

17. Movies with multiple conditions

SELECT title, rental_rate, rating FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%L%' LIMIT 10;

18. Title starts with A or B and ends with s

SELECT title FROM film WHERE title SIMILAR TO '(A|B)%s';

19. Title contains Man, Men, or Woman

SELECT title FROM film WHERE title SIMILAR TO '%(Man|Men|Woman)%';

Basic Select SQL Queries

Sri Mahalakshmi — Thu, 26 Mar 2026 14:59:28 +0000

The SELECT statement is used to choose what data we want, either all columns (*) or specific ones.

The WHERE clause helps filter rows based on conditions like ID, country, or population.

To avoid duplicate results, DISTINCT is used, while pattern matching with LIKE helps search data based on formats. Aggregate functions like COUNT are used to perform calculations on data.

Overall idea
SELECT - what you want
FROM - where to get it
WHERE - filter data
DISTINCT - remove duplicates
COUNT - calculate values

How a request originates from cllient and reaches the server ?

Sri Mahalakshmi — Thu, 26 Mar 2026 14:44:31 +0000

When a client sends a request, it starts by entering a URL like www.example.com. The client first needs the IP address of that domain, so it performs DNS resolution. It checks local cache, then asks a DNS resolver, which eventually gets the IP from the authoritative server.

Once the IP is obtained, the client creates a connection to the server using protocols like TCP (and HTTPS if secure). This happens through a process called a handshake, where both client and server agree to communicate.

After the connection is established, the client sends an HTTP request to the server’s IP address. This request travels through multiple network devices such as routers and switches across the internet.

Finally, the request reaches the server. The server processes it, prepares a respons, and sends it back to the client, which then displays it in the browser.

How DNS resolver is happening

Sri Mahalakshmi — Thu, 26 Mar 2026 14:42:05 +0000

The user makes a request, and a DNS query starts. First, it goes to the local machine resolver, where the system checks the cache (browser/OS). If the IP is already present, it returns immediately.

If not found, the request goes through the internet (ISP) to the main resolver, usually the ISP or a public DNS. This resolver again checks its cache. If it has the IP, it returns it. If not, it starts the DNS lookup process.

The resolver contacts the root server. The root server does not know the exact IP, but it knows where the TLD servers are, so it directs the resolver to the correct TLD (like .com, .org).

Then the resolver asks the TLD server, which tells where the authoritative server for that domain is.

Next, the resolver asks the authoritative server, which gives the final IP address.

This IP is sent back to the resolver, then to the user’s system, and stored in cache for future use. Finally, the browser uses this IP to load the website.

https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hokmjfkufa9cwc7fe2a7.jpg

Database- Querying and Filtering Data

Sri Mahalakshmi — Tue, 24 Mar 2026 15:52:08 +0000

Database:
An organized collection of structured information.

Relational DB:
To have relationship between table [includes structured & unstructured] ,query alone differs.

Tasks:

Retrieve film titles and their rental rates. Use column aliases to rename title as "Movie Title" and rental_rate as "Rate".

I need only two columns which is title and rental_rate, and rename it as asked using AS.

SELECT title AS "Movie Title", rental_rate AS "Rate" FROM film;

2.List customer names and their email addresses. Alias first_name and last_name as "First Name" and "Last Name".

 We have first name, last name, and email,rename the first two using AS.

`` SELECT first_name AS "First Name", last_name AS "Last Name", email FROM customer;``

3.Get a list of films sorted by rental rate in descending order. If two films have the same rental rate, sort them alphabetically by title.

  Sort by rental rate descending,if two values are same then sort by title in ascending.

  ``SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC;``

4.Retrieve actor names sorted by last name, then first name.
Sort by last_name first, then first_name.
SELECT first_name, last_name FROM actor ORDER BY last_name, first_name;

5.List all unique replacement costs from the film table.
If Duplicates exist, then use DISTINCT for unique values.
SELECT DISTINCT replacement_cost FROM film;

6.List all films' title and length in minutes. Alias length as "Duration (min)".

Rename length to Duration(min) and list out the titles from film

SELECT title, length AS "Duration (min)" FROM film;

7.Retrieve customer first and last names along with their active status. Alias active as "Is Active".

Retrieve first and last name and also rename active using AS.

SELECT first_name, last_name, active AS "Is Active" FROM customer;

8.Retrieve the list of film categories sorted alphabetically.

  Categories are in category table, sort it by name.

SELECT name FROM category ORDER BY name;

9.List films by length, sorted in descending order. Include only the title and length.

Longest(lengthy) movies first,use desc,retrieve title also from film.

SELECT title, length FROM film ORDER BY length DESC;

10.Retrieve all actor names, sorted by their first name in descending order.
Reverse alphabetical order in first name from actor.

SELECT first_name, last_name FROM actor ORDER BY first_name DESC;

11.List all unique ratings available in the film table.

Using distinct we can retrieve unique values.

SELECT DISTINCT rating FROM film;

12.Find all unique rental durations from the film table.
Use distinct for unique values.
SELECT DISTINCT rental_duration FROM film;

13.Retrieve the first unique customer ID based on active status. Include the customer_id and active columns, and order by customer_id.

SELECT DISTINCT customer_id, active FROM customer ORDER BY customer_id;

14.List the earliest rental date for each customer. Include customer_id and rental_date, and order by customer_id.

SELECT customer_id, MIN(rental_date) AS rental_date FROM rental GROUP BY customer_id ORDER BY customer_id;

15.List the 10 shortest films by length. Include the title and length.
For short size files listing we can use limit .
SELECT title, length FROM film ORDER BY length ASC LIMIT 10;

16.Get the top 5 customers with the highest customer_id. Include the first and last name.
By using limit we get top specific elements.
SELECT first_name, last_name FROM customer ORDER BY customer_id DESC LIMIT 5;

17.Retrieve all unique values of store_id from the inventory table.

SELECT DISTINCT store_id FROM inventory;

18.Find all unique replacement_cost values in the film table. Sort the results in ascending order.

SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;

19.List the first rental date for each store. Include store_id and rental_date, and sort by store_id.

SELECT store_id, MIN(rental_date) AS rental_date FROM rental GROUP BY store_id ORDER BY store_id;

20.Retrieve a list of film ratings sorted alphabetically and include only unique values.

SELECT DISTINCT rating FROM film ORDER BY rating;

21.List films by rating in ascending order and length in descending order.

SELECT title, rating, length FROM film ORDER BY rating ASC, length DESC;

22.Retrieve actor names sorted by last_name in ascending order and first_name in descending order.
We can use asc,desc for sorting.
SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;

23.List films ordered by replacement_cost in ascending order and rental_rate in descending order.
list out all films ordered by replacement cost in asc and rental rate using desc

SELECT title, replacement_cost, rental_rate FROM film ORDER BY replacement_cost ASC, rental_rate DESC;

24.Retrieve customer names sorted by last_name ascending and first_name descending.
list out first name and last name from customer ordered by asc for last name and desc in first name.

SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;

25.List all rentals sorted by customer_id ascending and rental_date descending.
using asc and desc sort the customer id and rental date from rental.
SELECT customer_id, rental_date FROM rental ORDER BY customer_id ASC, rental_date DESC;

26.Retrieve a list of films ordered by rental_duration ascending and title descending.
using asc ,desc order the rental_duration and title from film.
SELECT title FROM film ORDER BY rental_duration ASC, title DESC;