Creating a Simple Employee Database with AWS DynamoDB

DynamoDB is AWS's fully managed NoSQL database service. It provides fast, flexible, and scalable data storage without worrying about server management. This guide shows you how to create a simple employee database using DynamoDB.

What is DynamoDB?

DynamoDB is a key-value and document database that delivers single-digit millisecond performance at any scale. Key features:

• Serverless: No servers to manage
• Automatic Scaling: Handles traffic spikes automatically
• Fully Managed: AWS handles backups, patching, and monitoring
• Fast: Single-digit millisecond latency
• Scalable: Handles millions of requests per second

DynamoDB Core Concepts

Tables

A table is a collection of items (similar to rows in a relational database).

Items

An item is a collection of attributes (similar to a row in a relational database).

Attributes

An attribute is a name-value pair (similar to a column in a relational database).

Primary Key

Every table must have a primary key, which uniquely identifies each item:

• Partition Key: Simple primary key (single attribute)
• Composite Key: Partition key + Sort key (two attributes)

Secondary Indexes

Additional ways to query your data:

• Global Secondary Index (GSI): Different partition key and sort key
• Local Secondary Index (LSI): Same partition key, different sort key

Employee Database Design

For our employee database, we'll use:

Table Name: employees

Primary Key:

• Partition Key: employeeId (String) - Unique employee ID

Attributes:

• employeeId (String) - Primary key
• firstName (String)
• lastName (String)
• email (String)
• department (String)
• salary (Number)
• hireDate (String)
• status (String) - "active" or "inactive"

Step 1: Create DynamoDB Table (AWS Console)

Navigate to DynamoDB

1. Log in to AWS Console
2. Search for "DynamoDB"
3. Click "DynamoDB" to open the service

Create Table

1. Click "Create table"
2. Table name: employees
3. Partition key:
   • Attribute name: employeeId
   • Key type: Partition key
   • Data type: String
4. Keep default settings:
   • Table class: Standard
   • Capacity mode: On-demand (recommended for starters)
5. Click "Create table"

Table Creation: Takes a few seconds. Wait for status to show "Active".

Step 2: Add Items to Table (Console)

Add First Employee

1. Click on "employees" table
2. Click "Explore table items"
3. Click "Create item"

Add attributes:

{
  "employeeId": "EMP001",
  "firstName": "John",
  "lastName": "Doe",
  "email": "john.doe@company.com",
  "department": "Engineering",
  "salary": 95000,
  "hireDate": "2023-01-15",
  "status": "active"
}

4. Click "Create item"

Add More Employees

Repeat the process for additional employees:

{
  "employeeId": "EMP002",
  "firstName": "Jane",
  "lastName": "Smith",
  "email": "jane.smith@company.com",
  "department": "Marketing",
  "salary": 85000,
  "hireDate": "2023-03-20",
  "status": "active"
}

{
  "employeeId": "EMP003",
  "firstName": "Bob",
  "lastName": "Johnson",
  "email": "bob.johnson@company.com",
  "department": "Sales",
  "salary": 75000,
  "hireDate": "2022-11-10",
  "status": "active"
}

Step 3: Query and Scan Items

Get Item (by Primary Key)

1. In table items view
2. Click "Get item"
3. Enter employeeId: "EMP001"
4. Click "Get item"

This retrieves the employee with that ID instantly.

Scan Table

1. In table items view
2. Click "Scan"
3. Click "Run"

This returns all items in the table (use sparingly - it's expensive for large tables).

Query with Filter

You can add filter expressions:

• Example: Filter by status = "active"
• Example: Filter by department = "Engineering"

Step 4: Create Global Secondary Index (GSI)

To query by email (since email is not the primary key), create a GSI:

Create GSI

1. Click on "employees" table
2. Click "Indexes" tab
3. Click "Create index"

Index Configuration:

• Partition key: email (String)
• Index name: email-index
• Keep default settings

4. Click "Create index"

Note: GSI creation takes a few minutes. Wait for status to show "Active".

Now you can query employees by email!

Step 5: Spring Boot Integration

Add Dependencies

In your pom.xml (Maven):

<dependencies>
    <!-- Spring Boot Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- AWS SDK for DynamoDB -->
    <dependency>
        <groupId>software.amazon.awssdk</groupId>
        <artifactId>dynamodb</artifactId>
        <version>2.20.0</version>
    </dependency>

    <!-- Enhanced DynamoDB -->
    <dependency>
        <groupId>software.amazon.awssdk</groupId>
        <artifactId>dynamodb-enhanced</artifactId>
        <version>2.20.0</version>
    </dependency>
</dependencies>

For Gradle (build.gradle):

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'software.amazon.awssdk:dynamodb:2.20.0'
    implementation 'software.amazon.awssdk:dynamodb-enhanced:2.20.0'
}

Configure AWS Credentials

Option 1: Using IAM Role (Recommended for EC2)

If running on EC2, attach an IAM role with DynamoDB permissions. No credentials needed in code.

Option 2: Using application.properties

aws.region=us-east-1
aws.dynamodb.table-name=employees

Create Employee Model

package com.example.dynamodb.model;

import software.amazon.awssdk.enhanced.dynamodb.mapper.annotations.DynamoDbBean;
import software.amazon.awssdk.enhanced.dynamodb.mapper.annotations.DynamoDbPartitionKey;

@DynamoDbBean
public class Employee {
    private String employeeId;
    private String firstName;
    private String lastName;
    private String email;
    private String department;
    private Integer salary;
    private String hireDate;
    private String status;

    @DynamoDbPartitionKey
    public String getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(String employeeId) {
        this.employeeId = employeeId;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }

    public String getHireDate() {
        return hireDate;
    }

    public void setHireDate(String hireDate) {
        this.hireDate = hireDate;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

Create DynamoDB Configuration

package com.example.dynamodb.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import software.amazon.awssdk.auth.credentials.DefaultCredentialsProvider;
import software.amazon.awssdk.enhanced.dynamodb.DynamoDbEnhancedClient;
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;

@Configuration
public class DynamoDBConfig {

    @Value("${aws.region:us-east-1}")
    private String region;

    @Bean
    public DynamoDbClient dynamoDbClient() {
        return DynamoDbClient.builder()
                .region(Region.of(region))
                .credentialsProvider(DefaultCredentialsProvider.create())
                .build();
    }

    @Bean
    public DynamoDbEnhancedClient dynamoDbEnhancedClient(DynamoDbClient dynamoDbClient) {
        return DynamoDbEnhancedClient.builder()
                .dynamoDbClient(dynamoDbClient)
                .build();
    }
}

Create Repository

package com.example.dynamodb.repository;

import com.example.dynamodb.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;
import software.amazon.awssdk.enhanced.dynamodb.DynamoDbEnhancedClient;
import software.amazon.awssdk.enhanced.dynamodb.DynamoDbTable;
import software.amazon.awssdk.enhanced.dynamodb.Key;
import software.amazon.awssdk.enhanced.dynamodb.TableSchema;
import software.amazon.awssdk.enhanced.dynamodb.model.QueryConditional;
import software.amazon.awssdk.services.dynamodb.model.DynamoDbException;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

@Repository
public class EmployeeRepository {

    private final DynamoDbTable<Employee> employeeTable;

    @Autowired
    public EmployeeRepository(DynamoDbEnhancedClient enhancedClient,
                              @Value("${aws.dynamodb.table-name:employees}") String tableName) {
        this.employeeTable = enhancedClient.table(tableName, TableSchema.fromBean(Employee.class));
    }

    // Create employee
    public Employee save(Employee employee) {
        try {
            employeeTable.putItem(employee);
            return employee;
        } catch (DynamoDbException e) {
            throw new RuntimeException("Error saving employee", e);
        }
    }

    // Get employee by ID
    public Employee findById(String employeeId) {
        try {
            Key key = Key.builder()
                    .partitionValue(employeeId)
                    .build();
            return employeeTable.getItem(key);
        } catch (DynamoDbException e) {
            throw new RuntimeException("Error fetching employee", e);
        }
    }

    // Update employee
    public Employee update(Employee employee) {
        try {
            employeeTable.updateItem(employee);
            return employee;
        } catch (DynamoDbException e) {
            throw new RuntimeException("Error updating employee", e);
        }
    }

    // Delete employee
    public void delete(String employeeId) {
        try {
            Key key = Key.builder()
                    .partitionValue(employeeId)
                    .build();
            employeeTable.deleteItem(key);
        } catch (DynamoDbException e) {
            throw new RuntimeException("Error deleting employee", e);
        }
    }

    // Get all employees (Scan - use sparingly)
    public List<Employee> findAll() {
        try {
            List<Employee> employees = new ArrayList<>();
            Iterator<Employee> results = employeeTable.scan().items().iterator();
            while (results.hasNext()) {
                employees.add(results.next());
            }
            return employees;
        } catch (DynamoDbException e) {
            throw new RuntimeException("Error fetching all employees", e);
        }
    }
}

Create Service Layer

package com.example.dynamodb.service;

import com.example.dynamodb.model.Employee;
import com.example.dynamodb.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.UUID;

@Service
public class EmployeeService {

    private final EmployeeRepository employeeRepository;

    @Autowired
    public EmployeeService(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    public Employee createEmployee(Employee employee) {
        if (employee.getEmployeeId() == null) {
            employee.setEmployeeId("EMP" + UUID.randomUUID().toString().substring(0, 8).toUpperCase());
        }
        return employeeRepository.save(employee);
    }

    public Employee getEmployeeById(String employeeId) {
        Employee employee = employeeRepository.findById(employeeId);
        if (employee == null) {
            throw new RuntimeException("Employee not found: " + employeeId);
        }
        return employee;
    }

    public List<Employee> getAllEmployees() {
        return employeeRepository.findAll();
    }

    public Employee updateEmployee(String employeeId, Employee employee) {
        Employee existing = getEmployeeById(employeeId);
        employee.setEmployeeId(employeeId);
        return employeeRepository.update(employee);
    }

    public void deleteEmployee(String employeeId) {
        employeeRepository.delete(employeeId);
    }
}

Create REST Controller

package com.example.dynamodb.controller;

import com.example.dynamodb.model.Employee;
import com.example.dynamodb.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/employees")
public class EmployeeController {

    private final EmployeeService employeeService;

    @Autowired
    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    @PostMapping
    public ResponseEntity<Employee> createEmployee(@RequestBody Employee employee) {
        Employee created = employeeService.createEmployee(employee);
        return new ResponseEntity<>(created, HttpStatus.CREATED);
    }

    @GetMapping("/{employeeId}")
    public ResponseEntity<Employee> getEmployee(@PathVariable String employeeId) {
        Employee employee = employeeService.getEmployeeById(employeeId);
        return ResponseEntity.ok(employee);
    }

    @GetMapping
    public ResponseEntity<List<Employee>> getAllEmployees() {
        List<Employee> employees = employeeService.getAllEmployees();
        return ResponseEntity.ok(employees);
    }

    @PutMapping("/{employeeId}")
    public ResponseEntity<Employee> updateEmployee(@PathVariable String employeeId,
                                                    @RequestBody Employee employee) {
        Employee updated = employeeService.updateEmployee(employeeId, employee);
        return ResponseEntity.ok(updated);
    }

    @DeleteMapping("/{employeeId}")
    public ResponseEntity<Void> deleteEmployee(@PathVariable String employeeId) {
        employeeService.deleteEmployee(employeeId);
        return ResponseEntity.noContent().build();
    }
}

CRUD Operations Summary

Create (PutItem)

Employee employee = new Employee();
employee.setEmployeeId("EMP001");
employee.setFirstName("John");
employee.setLastName("Doe");
employee.setEmail("john.doe@company.com");
employee.setDepartment("Engineering");
employee.setSalary(95000);
employee.setHireDate("2023-01-15");
employee.setStatus("active");

employeeService.createEmployee(employee);

Read (GetItem)

Employee employee = employeeService.getEmployeeById("EMP001");

Update (UpdateItem)

Employee employee = employeeService.getEmployeeById("EMP001");
employee.setSalary(100000);
employeeService.updateEmployee("EMP001", employee);

Delete (DeleteItem)

employeeService.deleteEmployee("EMP001");

Best Practices

1. Design for Single-Table Design (Optional)

For complex applications, consider storing related data in a single table to reduce query costs.

2. Use GSIs for Common Query Patterns

Create Global Secondary Indexes for attributes you query frequently.

3. Avoid Scans

Scans read every item in the table. Use queries with indexes instead.

4. Use Batch Operations

For multiple items:

// Batch write
employeeTable.batchWriteItem(
    WriteBatch.builder(Employee.class)
        .mappedTableResource(employeeTable)
        .addPutItem(employee1)
        .addPutItem(employee2)
        .build()
);

5. Implement Pagination

For large result sets, use pagination tokens:

PageIterable<Employee> pages = employeeTable.scan();
Iterator<Page<Employee>> pageIterator = pages.iterator();

6. Handle Errors Gracefully

DynamoDB can throttle requests. Implement retry logic with exponential backoff.

Cost Considerations

DynamoDB pricing:

• On-Demand: Pay per request (free tier: 25 GB storage, 25 RCU/WCU)
• Provisioned: Specify RCU/WCU capacity (more predictable costs)

Tips:

• Start with On-Demand for variable workloads
• Switch to Provisioned for predictable, steady workloads
• Use GSIs sparingly (they consume additional capacity)
• Monitor with CloudWatch

Common Use Cases

Query by Department

Create a GSI with department as partition key:

// After creating GSI
DynamoDbIndex<Employee> departmentIndex = employeeTable.index("department-index");
QueryConditional queryConditional = QueryConditional
    .keyEqualTo(Key.builder().partitionValue("Engineering").build());
PageIterable<Employee> results = departmentIndex.query(queryConditional);

Filter Active Employees

List<Employee> all = employeeService.getAllEmployees();
List<Employee> active = all.stream()
    .filter(e -> "active".equals(e.getStatus()))
    .collect(Collectors.toList());

DynamoDB is powerful for serverless applications. Start simple with basic CRUD operations, then explore advanced features like GSIs, streams, and transactions as your needs grow.