10 CRUD Interview Questions and Answers

CRUD, an acronym for Create, Read, Update, and Delete, represents the foundational operations of persistent storage in software applications. These operations are essential for managing data in databases and are integral to the functionality of most web and mobile applications. Understanding CRUD operations is crucial for developers as they form the backbone of data manipulation and management in any system.

This article offers a curated selection of interview questions focused on CRUD operations. By reviewing these questions and their answers, you will gain a deeper understanding of how to effectively implement and optimize CRUD functionalities, thereby enhancing your readiness for technical interviews.

CRUD Interview Questions and Answers

1. Explain the basic CRUD operations and their HTTP methods.

CRUD stands for Create, Read, Update, and Delete, which are the four basic operations for managing data in a database. Each operation corresponds to a specific HTTP method in RESTful APIs.

• Create: Adds new data to the database using the POST method. The client sends a POST request with the data to be added in the request body.
• Read: Retrieves data from the database using the GET method. A GET request fetches data without modifying it.
• Update: Modifies existing data using PUT or PATCH. PUT updates an entire resource, while PATCH updates part of it.
• Delete: Removes data using the DELETE method, which deletes the specified resource.

2. Describe the difference between PUT and PATCH methods in RESTful APIs.

In RESTful APIs, PUT and PATCH methods update resources but differ in their approach.

The PUT method replaces a resource entirely, requiring a complete representation. It can also create a resource if it doesn’t exist. PUT is idempotent, meaning multiple identical requests have the same effect as one.

The PATCH method applies partial updates, sending only the changes. PATCH is not necessarily idempotent, as outcomes can vary based on the resource’s state.

3. Explain the concept of optimistic locking and how it can be implemented in a CRUD application.

Optimistic locking handles concurrent database updates by assuming conflicts are rare and checking for them at transaction commit time. It’s useful in CRUD applications where multiple users interact with the same data.

To implement it, add a version number or timestamp to records. When reading a record, its version is also read. During an update, the version is checked to ensure it hasn’t changed. If it has, the update is aborted.

Example using SQL and Python:

import sqlite3

def update_record(id, new_value, version):
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    
    cursor.execute("SELECT version FROM records WHERE id = ?", (id,))
    current_version = cursor.fetchone()[0]
    
    if current_version != version:
        raise Exception("Record has been modified by another transaction")
    
    new_version = current_version + 1
    cursor.execute("UPDATE records SET value = ?, version = ? WHERE id = ?", (new_value, new_version, id))
    conn.commit()
    conn.close()

# Example usage
try:
    update_record(1, 'new_value', 1)
except Exception as e:
    print(e)

In this example, update_record retrieves the current version, checks for changes, and updates the record if the version is unchanged.

4. Write a SQL query to retrieve all users who registered in the last 30 days.

To retrieve users who registered in the last 30 days, use this SQL query. It assumes a users table with a registration_date column.

SELECT * 
FROM users 
WHERE registration_date >= CURDATE() - INTERVAL 30 DAY;

5. Explain how you would implement pagination in a RESTful API for retrieving large datasets.

Pagination divides large datasets into smaller chunks, improving performance in RESTful APIs. It retrieves a subset of data at a time, reducing server and client load.

Use query parameters like page and limit to specify the page number and items per page. The server uses these to determine the data subset to return.

Example:

from flask import Flask, request, jsonify

app = Flask(__name__)

# Sample data
data = list(range(1, 101))  # A list of 100 items

@app.route('/items', methods=['GET'])
def get_items():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))
    start = (page - 1) * limit
    end = start + limit
    return jsonify(data[start:end])

if __name__ == '__main__':
    app.run(debug=True)

In this example, get_items calculates indices for the data subset based on page and limit.

6. Describe the role of indexes in optimizing CRUD operations and provide an example of creating an index in SQL.

Indexes speed up row retrieval by using pointers, created on columns frequently used in WHERE clauses. They improve read performance but can slow down writes due to index updates.

Example of creating an index in SQL:

CREATE INDEX idx_employee_name ON employees (name);

This index on the name column of the employees table speeds up name-based queries.

7. Explain the ACID properties in the context of CRUD operations and why they are important.

ACID properties ensure reliable database transactions, maintaining data integrity during CRUD operations. ACID stands for Atomicity, Consistency, Isolation, and Durability.

• Atomicity: Ensures a transaction is a single unit, either fully succeeding or failing. This prevents partial updates.
• Consistency: Ensures a transaction moves the database from one valid state to another, maintaining rules and constraints.
• Isolation: Ensures concurrent transactions don’t interfere, preventing issues like dirty reads.
• Durability: Guarantees committed transactions remain so, even after system failures.

8. How do you validate data before performing CRUD operations? Provide examples.

Validating data before CRUD operations ensures data integrity and security. Validation can occur at client-side, server-side, and database-level.

Client-side validation provides immediate feedback but can be bypassed.

Server-side validation is more secure, ensuring data is validated before server processing.

Database-level validation uses constraints and triggers to enforce rules.

Example of server-side validation in Python with Flask:

from flask import Flask, request, jsonify
from wtforms import Form, StringField, IntegerField, validators

app = Flask(__name__)

class UserForm(Form):
    name = StringField('Name', [validators.Length(min=1, max=50)])
    age = IntegerField('Age', [validators.NumberRange(min=0, max=120)])

@app.route('/create_user', methods=['POST'])
def create_user():
    form = UserForm(request.form)
    if form.validate():
        return jsonify({"message": "User created successfully"}), 201
    else:
        return jsonify({"errors": form.errors}), 400

if __name__ == '__main__':
    app.run(debug=True)

In this example, UserForm defines validation rules for name and age, and create_user validates data before creation.

9. Describe how you would handle errors in a CRUD application.

Error handling in a CRUD application ensures predictable behavior and meaningful feedback. Key strategies include:

• Input Validation: Validate user inputs to prevent errors like invalid data types.
• Exception Handling: Use try-except blocks to manage unexpected errors.
• Logging: Record errors for debugging and monitoring.
• User Feedback: Provide clear error messages to users.

Example:

from flask import Flask, request, jsonify
from sqlalchemy.exc import SQLAlchemyError

app = Flask(__name__)

@app.route('/create', methods=['POST'])
def create_record():
    data = request.get_json()
    try:
        if 'name' not in data or 'age' not in data:
            return jsonify({'error': 'Invalid input'}), 400
        
        return jsonify({'message': 'Record created successfully'}), 201
    except SQLAlchemyError as e:
        app.logger.error(f'Database error: {e}')
        return jsonify({'error': 'Database error'}), 500
    except Exception as e:
        app.logger.error(f'Unexpected error: {e}')
        return jsonify({'error': 'An unexpected error occurred'}), 500

if __name__ == '__main__':
    app.run(debug=True)

10. How would you implement rate limiting in a CRUD API?

To implement rate limiting in a CRUD API, use middleware to apply rate limiting logic. A common approach is the token bucket algorithm, allowing a set number of requests per time unit.

Example using Flask and Redis:

from flask import Flask, request, jsonify
import time
import redis

app = Flask(__name__)
r = redis.Redis()

RATE_LIMIT = 5  # Number of requests
TIME_WINDOW = 60  # Time window in seconds

def is_rate_limited(ip):
    current_time = int(time.time())
    key = f"rate_limit:{ip}:{current_time // TIME_WINDOW}"
    current_count = r.get(key)
    
    if current_count and int(current_count) >= RATE_LIMIT:
        return True
    
    r.incr(key, 1)
    r.expire(key, TIME_WINDOW)
    return False

@app.route('/api/resource', methods=['GET', 'POST', 'PUT', 'DELETE'])
def resource():
    ip = request.remote_addr
    if is_rate_limited(ip):
        return jsonify({"error": "Rate limit exceeded"}), 429
    
    return jsonify({"message": "Request successful"})

if __name__ == '__main__':
    app.run()