Code Generation: Exploring Generative AI for Database Workloads

Nature's fury, in the form of Storm Darragh, recently swept across the UK, confining many of us indoors. As the wind howled outside, I found myself with an unexpected opportunity to delve into the fascinating world of generative AI. Specifically, I was curious to explore its potential in streamlining backend database-related workloads through code generation.

This exploration felt particularly fitting given my long-standing relationship with databases. My journey in this field spans decades, from contributing to JDBC/JCA implementations for WebSphere Application Server in 2004, supporting a diverse array of commercial and open-source databases, to my current fascination with cutting-edge technologies like Amazon Aurora DSQL, unveiled at re:Invent 2024.

As the storm raged on, I embarked on a different kind of adventure – one that would bridge my database experiences with the exciting frontier of AI-assisted development. Join me as I share insights from this indoor expedition, where the constraints of a stormy weekend paved way for the integration of databases and artificial intelligence.

With just a few conversational exchanges, I was able to effortlessly conceptualize and bring to life a comprehensive database application. The process flowed seamlessly from designing a detailed PostgreSQL database schema to defining RESTful APIs, implementing a Python-based AWS Lambda function, and finally generating AWS CDK code for deployment.

What might typically require days of planning, coding, and configuration was accomplished in a matter of minutes through natural language prompts. The ability to understand context, generate code, and adapt to follow-up requests demonstrates how generative AI can significantly streamline the software development process, making it accessible even to those without deep technical expertise in every aspect of backend development and cloud infrastructure.

Prompt 1: Can you provide a sample PostgreSQL schema and tables ?

Response : Certainly! I'll provide you with a sample set of PostgreSQL schemas and tables that could represent a simplified e-commerce system. This example will include multiple schemas to demonstrate a more complex database structure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
-- Create schemas
CREATE SCHEMA inventory;
CREATE SCHEMA sales;
CREATE SCHEMA users;

-- Inventory Schema
CREATE TABLE inventory.products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL
);

CREATE TABLE inventory.categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE inventory.product_categories (
    product_id INTEGER REFERENCES inventory.products(product_id),
    category_id INTEGER REFERENCES inventory.categories(category_id),
    PRIMARY KEY (product_id, category_id)
);

-- Sales Schema
CREATE TABLE sales.orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL
);

CREATE TABLE sales.order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES sales.orders(order_id),
    product_id INTEGER REFERENCES inventory.products(product_id),
    quantity INTEGER NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE sales.payments (
    payment_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES sales.orders(order_id),
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method VARCHAR(50)
);

-- Users Schema
CREATE TABLE users.customers (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users.addresses (
    address_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users.customers(user_id),
    address_line1 VARCHAR(100) NOT NULL,
    address_line2 VARCHAR(100),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(50),
    country VARCHAR(50) NOT NULL,
    postal_code VARCHAR(20)
);

-- Create indexes for performance
CREATE INDEX idx_products_name ON inventory.products(name);
CREATE INDEX idx_orders_user_id ON sales.orders(user_id);
CREATE INDEX idx_order_items_order_id ON sales.order_items(order_id);
CREATE INDEX idx_customers_email ON users.customers(email);

-- Add some sample data
INSERT INTO inventory.categories (name) VALUES ('Electronics'), ('Books'), ('Clothing');

INSERT INTO inventory.products (name, description, price, stock_quantity) VALUES
('Smartphone', 'Latest model smartphone', 699.99, 100),
('Laptop', 'High-performance laptop', 1299.99, 50),
('T-shirt', 'Cotton T-shirt', 19.99, 200);

INSERT INTO inventory.product_categories (product_id, category_id) VALUES
(1, 1), (2, 1), (3, 3);

INSERT INTO users.customers (first_name, last_name, email, password_hash) VALUES
('John', 'Doe', 'john@example.com', 'hashed_password_here'),
('Jane', 'Smith', 'jane@example.com', 'hashed_password_here');

INSERT INTO sales.orders (user_id, total_amount) VALUES
(1, 719.98),
(2, 1299.99);

INSERT INTO sales.order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 699.99),
(1, 3, 1, 19.99),
(2, 2, 1, 1299.99);

INSERT INTO sales.payments (order_id, amount, payment_method) VALUES
(1, 719.98, 'Credit Card'),
(2, 1299.99, 'PayPal');

Prompt 2: This is a bit boring. Can you give another example ?

Response: Certainly! Here's another example of PostgreSQL schemas and tables, this time modeling a university management system. This example will include schemas for academics, staff, facilities, and research.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
-- Create schemas
CREATE SCHEMA academics;
CREATE SCHEMA staff;
CREATE SCHEMA facilities;
CREATE SCHEMA research;

-- Academics Schema
CREATE TABLE academics.departments (
    department_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) UNIQUE NOT NULL
);

CREATE TABLE academics.courses (
    course_id SERIAL PRIMARY KEY,
    department_id INTEGER REFERENCES academics.departments(department_id),
    code VARCHAR(20) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    credits INTEGER NOT NULL
);

CREATE TABLE academics.students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    enrollment_date DATE NOT NULL,
    major_department_id INTEGER REFERENCES academics.departments(department_id)
);

CREATE TABLE academics.enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES academics.students(student_id),
    course_id INTEGER REFERENCES academics.courses(course_id),
    semester VARCHAR(20) NOT NULL,
    year INTEGER NOT NULL,
    grade CHAR(2)
);

-- Staff Schema
CREATE TABLE staff.employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    position VARCHAR(100) NOT NULL
);

CREATE TABLE staff.salaries (
    salary_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES staff.employees(employee_id),
    amount DECIMAL(10, 2) NOT NULL,
    effective_date DATE NOT NULL
);

-- Facilities Schema
CREATE TABLE facilities.buildings (
    building_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200) NOT NULL,
    construction_year INTEGER
);

CREATE TABLE facilities.rooms (
    room_id SERIAL PRIMARY KEY,
    building_id INTEGER REFERENCES facilities.buildings(building_id),
    room_number VARCHAR(20) NOT NULL,
    capacity INTEGER,
    room_type VARCHAR(50) NOT NULL
);

CREATE TABLE facilities.equipment (
    equipment_id SERIAL PRIMARY KEY,
    room_id INTEGER REFERENCES facilities.rooms(room_id),
    name VARCHAR(100) NOT NULL,
    purchase_date DATE,
    last_maintenance_date DATE
);

-- Research Schema
CREATE TABLE research.projects (
    project_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    funding_amount DECIMAL(12, 2)
);

CREATE TABLE research.publications (
    publication_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    authors TEXT NOT NULL,
    publication_date DATE,
    journal VARCHAR(200),
    doi VARCHAR(100)
);

CREATE TABLE research.project_members (
    project_id INTEGER REFERENCES research.projects(project_id),
    employee_id INTEGER REFERENCES staff.employees(employee_id),
    role VARCHAR(50) NOT NULL,
    PRIMARY KEY (project_id, employee_id)
);

-- Create indexes for performance
CREATE INDEX idx_courses_department ON academics.courses(department_id);
CREATE INDEX idx_enrollments_student ON academics.enrollments(student_id);
CREATE INDEX idx_enrollments_course ON academics.enrollments(course_id);
CREATE INDEX idx_salaries_employee ON staff.salaries(employee_id);
CREATE INDEX idx_rooms_building ON facilities.rooms(building_id);
CREATE INDEX idx_equipment_room ON facilities.equipment(room_id);
CREATE INDEX idx_project_members_project ON research.project_members(project_id);
CREATE INDEX idx_project_members_employee ON research.project_members(employee_id);

-- Add some sample data
INSERT INTO academics.departments (name, code) VALUES 
('Computer Science', 'CS'),
('Physics', 'PHY'),
('Biology', 'BIO');

INSERT INTO academics.courses (department_id, code, title, credits) VALUES
(1, 'CS101', 'Introduction to Programming', 3),
(2, 'PHY201', 'Classical Mechanics', 4),
(3, 'BIO301', 'Genetics', 4);

INSERT INTO academics.students (first_name, last_name, date_of_birth, enrollment_date, major_department_id) VALUES
('Alice', 'Johnson', '2000-05-15', '2022-09-01', 1),
('Bob', 'Smith', '2001-03-22', '2022-09-01', 2);

INSERT INTO staff.employees (first_name, last_name, email, hire_date, position) VALUES
('John', 'Doe', 'john.doe@university.edu', '2015-08-01', 'Professor'),
('Jane', 'Doe', 'jane.doe@university.edu', '2018-01-15', 'Associate Professor');

INSERT INTO facilities.buildings (name, address, construction_year) VALUES
('Science Building', '123 University Ave', 1985),
('Library', '456 Campus Road', 1990);

INSERT INTO research.projects (title, description, start_date, end_date, funding_amount) VALUES
('AI in Education', 'Exploring AI applications in higher education', '2023-01-01', '2025-12-31', 500000.00),
('Quantum Computing Advances', 'Research on quantum algorithms', '2022-07-01', '2024-06-30', 750000.00);

While this university management schema was no different from the order management system, I liked to continue with it as it took my thoughts back to my college days and made me feel younger.

Prompt 3: Can you list all the possible APIs for the above database schema ?

Response : Certainly! Based on the university management system database schema I provided, here's a list of potential APIs that could be developed to interact with this data. I'll organize these by schema and include common CRUD (Create, Read, Update, Delete) operations as well as some more specific operations that might be useful.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
Academics API

    Departments:
        GET /api/academics/departments - List all departments
        GET /api/academics/departments/{id} - Get department details
        POST /api/academics/departments - Create a new department
        PUT /api/academics/departments/{id} - Update department information
        DELETE /api/academics/departments/{id} - Delete a department

    Courses:
        GET /api/academics/courses - List all courses
        GET /api/academics/courses/{id} - Get course details
        POST /api/academics/courses - Create a new course
        PUT /api/academics/courses/{id} - Update course information
        DELETE /api/academics/courses/{id} - Delete a course
        GET /api/academics/departments/{id}/courses - List courses in a department

    Students:
        GET /api/academics/students - List all students
        GET /api/academics/students/{id} - Get student details
        POST /api/academics/students - Register a new student
        PUT /api/academics/students/{id} - Update student information
        DELETE /api/academics/students/{id} - Remove a student
        GET /api/academics/departments/{id}/students - List students in a department

    Enrollments:
        GET /api/academics/enrollments - List all enrollments
        GET /api/academics/enrollments/{id} - Get enrollment details
        POST /api/academics/enrollments - Create a new enrollment
        PUT /api/academics/enrollments/{id} - Update enrollment (e.g., grade)
        DELETE /api/academics/enrollments/{id} - Delete an enrollment
        GET /api/academics/students/{id}/enrollments - Get a student's enrollments
        GET /api/academics/courses/{id}/enrollments - Get enrollments for a course

Staff API

    Employees:
        GET /api/staff/employees - List all employees
        GET /api/staff/employees/{id} - Get employee details
        POST /api/staff/employees - Add a new employee
        PUT /api/staff/employees/{id} - Update employee information
        DELETE /api/staff/employees/{id} - Remove an employee

    Salaries:
        GET /api/staff/salaries - List all salaries
        GET /api/staff/salaries/{id} - Get salary details
        POST /api/staff/salaries - Add a new salary record
        PUT /api/staff/salaries/{id} - Update salary information
        DELETE /api/staff/salaries/{id} - Delete a salary record
        GET /api/staff/employees/{id}/salaries - Get salary history for an employee

Facilities API

    Buildings:
        GET /api/facilities/buildings - List all buildings
        GET /api/facilities/buildings/{id} - Get building details
        POST /api/facilities/buildings - Add a new building
        PUT /api/facilities/buildings/{id} - Update building information
        DELETE /api/facilities/buildings/{id} - Remove a building

    Rooms:
        GET /api/facilities/rooms - List all rooms
        GET /api/facilities/rooms/{id} - Get room details
        POST /api/facilities/rooms - Add a new room
        PUT /api/facilities/rooms/{id} - Update room information
        DELETE /api/facilities/rooms/{id} - Remove a room
        GET /api/facilities/buildings/{id}/rooms - List rooms in a building

    Equipment:
        GET /api/facilities/equipment - List all equipment
        GET /api/facilities/equipment/{id} - Get equipment details
        POST /api/facilities/equipment - Add new equipment
        PUT /api/facilities/equipment/{id} - Update equipment information
        DELETE /api/facilities/equipment/{id} - Remove equipment
        GET /api/facilities/rooms/{id}/equipment - List equipment in a room

Research API

    Projects:
        GET /api/research/projects - List all research projects
        GET /api/research/projects/{id} - Get project details
        POST /api/research/projects - Create a new project
        PUT /api/research/projects/{id} - Update project information
        DELETE /api/research/projects/{id} - Remove a project

    Publications:
        GET /api/research/publications - List all publications
        GET /api/research/publications/{id} - Get publication details
        POST /api/research/publications - Add a new publication
        PUT /api/research/publications/{id} - Update publication information
        DELETE /api/research/publications/{id} - Remove a publication

    Project Members:
        GET /api/research/project-members - List all project members
        GET /api/research/project-members/{projectId}/{employeeId} - Get project member details
        POST /api/research/project-members - Add a member to a project
        PUT /api/research/project-members/{projectId}/{employeeId} - Update member role
        DELETE /api/research/project-members/{projectId}/{employeeId} - Remove a member from a project
        GET /api/research/projects/{id}/members - List members of a project
        GET /api/staff/employees/{id}/projects - List projects for an employee

Additional Useful APIs:

    GET /api/academics/students/{id}/transcript - Generate a student's transcript
    GET /api/academics/departments/{id}/faculty - List faculty in a department
    GET /api/facilities/rooms/available - List available rooms (for scheduling)
    GET /api/research/projects/active - List active research projects
    GET /api/staff/employees/by-position/{position} - List employees by position

These APIs cover the basic CRUD operations for each entity in the database, as well as some more specific operations that might be useful in a university management system. Depending on the specific needs of the application, you might want to add more specialized endpoints or combine some of these for efficiency.

Prompt 4: Can you generate an OpenAPI specification for these APIs in YAML format ?

Response : Certainly! Here's an OpenAPI specification in YAML format for the APIs we discussed. Note that this is a simplified version and doesn't include all the endpoints we mentioned earlier, but it covers the main entities and operations. You can expand on this as needed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
openapi: 3.0.0
info:
  title: University Management System API
  version: 1.0.0
  description: API for managing university departments, courses, students, staff, facilities, and research projects.

servers:
  - url: https://api.university.edu/v1

paths:
  /academics/departments:
    get:
      summary: List all departments
      responses:
        '200':
          description: Successful response
          content:
            application/json:    
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Department'
    post:
      summary: Create a new department
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Department'
      responses:
        '201':
          description: Created

  /academics/departments/{id}:
    get:
      summary: Get department details
      parameters:
        - in: path
          name: id
          required: true
          schema:
            type: integer
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Department'

  /academics/courses:
    get:
      summary: List all courses
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Course'
    post:
      summary: Create a new course
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Course'
      responses:
        '201':
          description: Created

  /academics/students:
    get:
      summary: List all students
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Student'
    post:
      summary: Register a new student
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Student'
      responses:
        '201':
          description: Created

  /staff/employees:
    get:
      summary: List all employees
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Employee'
    post:
      summary: Add a new employee
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Employee'
      responses:
        '201':
          description: Created

  /facilities/buildings:
    get:
      summary: List all buildings
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Building'
    post:
      summary: Add a new building
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Building'
      responses:
        '201':
          description: Created

  /research/projects:
    get:
      summary: List all research projects
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Project'
    post:
      summary: Create a new project
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Project'
      responses:
        '201':
          description: Created

components:
  schemas:
    Department:
      type: object
      properties:
        id:
          type: integer
        name:
          type: string
        code:
          type: string

    Course:
      type: object
      properties:
        id:
          type: integer
        department_id:
          type: integer
        code:
          type: string
        title:
          type: string
        credits:
          type: integer

    Student:
      type: object
      properties:
        id:
          type: integer
        first_name:
          type: string
        last_name:
          type: string
        date_of_birth:
          type: string
          format: date
        enrollment_date:
          type: string
          format: date
        major_department_id:
          type: integer

    Employee:
      type: object
      properties:
        id:
          type: integer
        first_name:
          type: string
        last_name:
          type: string
        email:
          type: string
        hire_date:
          type: string
          format: date
        position:
          type: string

    Building:
      type: object
      properties:
        id:
          type: integer
        name:
          type: string
        address:
          type: string
        construction_year:
          type: integer

    Project:
      type: object
      properties:
        id:
          type: integer
        title:
          type: string
        description:
          type: string
        start_date:
          type: string
          format: date
        end_date:
          type: string
          format: date
        funding_amount:
          type: number
          format: float

Prompt 5: Now generate a Python based Lambda function to implement all the above APIs and use RDS Data APIs to interface with Aurora PostgreSQL database ?

Response : Certainly! I'll create a Python-based Lambda function that implements the APIs we discussed using the RDS Data API to interface with an Aurora PostgreSQL database. This example will provide a basic structure that you can expand upon.

First, make sure you have the necessary permissions set up for your Lambda function to access RDS Data API. You'll also need to set up the appropriate environment variables.

Here's a Python script that implements the basic structure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
import json
import os
import boto3
from botocore.exceptions import ClientError

# Initialize clients
rds_data = boto3.client("rds-data")

# Environment variables
DB_CLUSTER_ARN = os.environ["DB_CLUSTER_ARN"]
DB_SECRET_ARN = os.environ["DB_SECRET_ARN"]
DATABASE_NAME = os.environ["DB_NAME"]

def execute_statement(sql, params=[]):
    try:
        response = rds_data.execute_statement(
            resourceArn=DB_CLUSTER_ARN,
            secretArn=DB_SECRET_ARN,
            database=DATABASE_NAME,
            sql=sql,
            parameters=params,
        )
        # Check if the response has a "records" attribute
        if "records" in response:
            return response["records"]
        # If "records" is not present, check for a "numberOfRecordsUpdated" attribute
        elif "numberOfRecordsUpdated" in response:
            return response["numberOfRecordsUpdated"]
        # If neither "records" nor "numberOfRecordsUpdated" are present, return None
        else:
            return None
    except ClientError as e:
        print(f"An error occurred: {e}")
        return None

def handle_academics_request(action, entity, data=None):
    if entity == "departments":
        if action == "GET":
            records = execute_statement("SELECT * FROM academics.departments")
            return [dict(zip(["id", "name", "code"], record)) for record in records]
        elif action == "POST":
            execute_statement(
                "INSERT INTO academics.departments (name, code) VALUES (:name, :code)",
                [
                    {"name": "name", "value": {"stringValue": data["name"]}},
                    {"name": "code", "value": {"stringValue": data["code"]}},
                ],
            )
            return {"message": "Department created successfully"}
    elif entity == "courses":
        if action == "GET":
            records = execute_statement("SELECT * FROM academics.courses")
            return [
                dict(zip(["id", "department_id", "code", "title", "credits"], record))
                for record in records
            ]
        elif action == "POST":
            execute_statement(
                "INSERT INTO academics.courses (department_id, code, title, credits) VALUES (:dept_id, :code, :title, :credits)",
                [
                    {"name": "dept_id", "value": {"longValue": data["department_id"]}},
                    {"name": "code", "value": {"stringValue": data["code"]}},
                    {"name": "title", "value": {"stringValue": data["title"]}},
                    {"name": "credits", "value": {"longValue": data["credits"]}},
                ],
            )
            return {"message": "Course created successfully"}
    # Add more entities and actions as needed

def handle_staff_request(action, entity, data=None):
    if entity == "employees":
        if action == "GET":
            records = execute_statement("SELECT * FROM staff.employees")
            return [
                dict(
                    zip(
                        [
                            "id",
                            "first_name",
                            "last_name",
                            "email",
                            "hire_date",
                            "position",
                        ],
                        record,
                    )
                )
                for record in records
            ]
        elif action == "POST":
            execute_statement(
                "INSERT INTO staff.employees (first_name, last_name, email, hire_date, position) VALUES (:fname, :lname, :email, CAST(:hire_date AS DATE), :position)",
                [
                    {"name": "fname", "value": {"stringValue": data["first_name"]}},
                    {"name": "lname", "value": {"stringValue": data["last_name"]}},
                    {"name": "email", "value": {"stringValue": data["email"]}},
                    {"name": "hire_date", "value": {"stringValue": data["hire_date"]}},
                    {"name": "position", "value": {"stringValue": data["position"]}},
                ],
            )
            return {"message": "Employee added successfully"}
    # Add more entities and actions as needed

def handle_facilities_request(action, entity, data=None):
    if entity == "buildings":
        if action == "GET":
            records = execute_statement("SELECT * FROM facilities.buildings")
            return [
                dict(zip(["id", "name", "address", "construction_year"], record))
                for record in records
            ]
        elif action == "POST":
            execute_statement(
                "INSERT INTO facilities.buildings (name, address, construction_year) VALUES (:name, :address, :year)",
                [
                    {"name": "name", "value": {"stringValue": data["name"]}},
                    {"name": "address", "value": {"stringValue": data["address"]}},
                    {"name": "year", "value": {"longValue": data["construction_year"]}},
                ],
            )
            return {"message": "Building added successfully"}
    # Add more entities and actions as needed

def handle_research_request(action, entity, data=None):
    if entity == "projects":
        if action == "GET":
            records = execute_statement("SELECT * FROM research.projects")
            return [
                dict(
                    zip(
                        [
                            "id",
                            "title",
                            "description",
                            "start_date",
                            "end_date",
                            "funding_amount",
                        ],
                        record,
                    )
                )
                for record in records
            ]
        elif action == "POST":
            execute_statement(
                "INSERT INTO research.projects (title, description, start_date, end_date, funding_amount) VALUES (:title, :desc, CAST(:start AS DATE), CAST(:end AS DATE), :funding)",
                [
                    {"name": "title", "value": {"stringValue": data["title"]}},
                    {"name": "desc", "value": {"stringValue": data["description"]}},
                    {"name": "start", "value": {"stringValue": data["start_date"]}},
                    {"name": "end", "value": {"stringValue": data["end_date"]}},
                    {
                        "name": "funding",
                        "value": {"doubleValue": data["funding_amount"]},
                    },
                ],
            )
            return {"message": "Project created successfully"}
    # Add more entities and actions as needed

def handler(event, context):
    try:
        # Parse the incoming request
        body = None
        if "body" in event and event["body"]:
            body = json.loads(event["body"])
        action = event["httpMethod"]
        path = event["path"].split("/")
        domain = path[1]
        entity = path[2]

        # Route the request to the appropriate handler
        if domain == "academics":
            result = handle_academics_request(
                action, entity, body if action == "POST" else None
            )
        elif domain == "staff":
            result = handle_staff_request(
                action, entity, body if action == "POST" else None
            )
        elif domain == "facilities":
            result = handle_facilities_request(
                action, entity, body if action == "POST" else None
            )
        elif domain == "research":
            result = handle_research_request(
                action, entity, body if action == "POST" else None
            )
        else:
            return {"statusCode": 400, "body": json.dumps({"error": "Invalid domain"})}

        return {"statusCode": 200, "body": json.dumps(result)}

    except Exception as e:
        print(f"An error occurred: {e}")
        return {
            "statusCode": 500,
            "body": json.dumps({"error": "Internal server error"}),
        }

Prompt 6: Great. Now can you generate a CDK code to deploy an Aurora PostgreSQL cluster with the schema and APIs generated.

Response: Certainly! I'll create a new CDK code from scratch to deploy an Aurora PostgreSQL cluster along with the schema and APIs we discussed earlier. This code will be more streamlined and focused on the core requirements. Here's the new CDK code:

Here's the CDK code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
import os
from aws_cdk import (
    Stack,
    aws_ec2 as ec2,
    aws_rds as rds,
    aws_lambda as _lambda,
    aws_iam as iam,
    aws_secretsmanager as secretsmanager,
    aws_apigateway as apigateway,
    CfnOutput,
    Duration,
)
from constructs import Construct

class UniversityManagementStack(Stack):

    def __init__(self, scope: Construct, construct_id: str, **kwargs) -> None:
        super().__init__(scope, construct_id, **kwargs)

        # Create VPC
        vpc = ec2.Vpc(self, "UniversityVPC", max_azs=2)

        # Create Aurora Serverless v2 PostgreSQL cluster
        db_cluster = rds.DatabaseCluster(self, "UniversityDatabase",
            engine=rds.DatabaseClusterEngine.aurora_postgres(version=rds.AuroraPostgresEngineVersion.VER_13_7),
            instance_props=rds.InstanceProps(
                vpc=vpc,
                vpc_subnets=ec2.SubnetSelection(subnet_type=ec2.SubnetType.PRIVATE_WITH_NAT),
                instance_type=ec2.InstanceType.of(ec2.InstanceClass.R6G, ec2.InstanceSize.LARGE)
            ),
            instances=1,
            default_database_name="university",
            serverless_v2_scaling_configuration=rds.ServerlessV2ScalingConfiguration(
                min_capacity=0.5,
                max_capacity=4
            ),
            enable_data_api=True,
        )

        # Add a Serverless v2 instance to the cluster
        serverless_instance = rds.CfnDBInstance(self, "ServerlessInstance",
            db_instance_class="db.serverless",
            engine="aurora-postgresql",
            db_cluster_identifier=db_cluster.cluster_identifier,
        )

        # Ensure the instance is created after the cluster
        serverless_instance.add_dependency(db_cluster)

        # Create a secret for database credentials
        db_secret = db_cluster.secret

        # Lambda function role for schema creation
        schema_creation_role = iam.Role(self, "SchemaCreationRole",
            assumed_by=iam.ServicePrincipal("lambda.amazonaws.com"),
            managed_policies=[
                iam.ManagedPolicy.from_aws_managed_policy_name("service-role/AWSLambdaBasicExecutionRole"),
                iam.ManagedPolicy.from_aws_managed_policy_name("AmazonRDSDataFullAccess"),
            ]
        )

        # Lambda function for schema creation
        schema_creation_lambda = _lambda.Function(self, "SchemaCreationLambda",
            runtime=_lambda.Runtime.PYTHON_3_9,
            handler="index.handler",
            code=_lambda.Code.from_asset("schema_creation_lambda"),
            environment={
                "DB_CLUSTER_ARN": db_cluster.cluster_arn,
                "DB_SECRET_ARN": db_secret.secret_arn,
                "DB_NAME": "university",
            },
            timeout=Duration.seconds(300),
            role=schema_creation_role,
        )

        # Grant permissions to the schema creation Lambda function
        db_cluster.grant_data_api_access(schema_creation_lambda)
        db_secret.grant_read(schema_creation_lambda)

        # Lambda function role for API
        api_lambda_role = iam.Role(self, "APILambdaRole",
            assumed_by=iam.ServicePrincipal("lambda.amazonaws.com"),
            managed_policies=[
                iam.ManagedPolicy.from_aws_managed_policy_name("service-role/AWSLambdaBasicExecutionRole"),
                iam.ManagedPolicy.from_aws_managed_policy_name("AmazonRDSDataFullAccess"),
            ]
        )

        # Lambda function for API
        api_lambda_function = _lambda.Function(self, "UniversityAPIFunction",
            runtime=_lambda.Runtime.PYTHON_3_9,
            handler="index.lambda_handler",
            code=_lambda.Code.from_asset("api_lambda"),
            environment={
                "DB_CLUSTER_ARN": db_cluster.cluster_arn,
                "DB_SECRET_ARN": db_secret.secret_arn,
                "DATABASE_NAME": "university"
            },
            timeout=Duration.seconds(30),
            role=api_lambda_role,
        )

        # Grant permissions to the API Lambda function
        db_cluster.grant_data_api_access(api_lambda_function)
        db_secret.grant_read(api_lambda_function)

        # API Gateway
        api = apigateway.LambdaRestApi(self, "UniversityAPI",
            handler=lambda_function,
            proxy=False
        )

        academics = api.root.add_resource("academics")
        academics.add_resource("departments").add_method("GET")
        academics.add_resource("departments").add_method("POST")
        academics.add_resource("courses").add_method("GET")
        academics.add_resource("courses").add_method("POST")

        staff = api.root.add_resource("staff")
        staff.add_resource("employees").add_method("GET")
        staff.add_resource("employees").add_method("POST")

        facilities = api.root.add_resource("facilities")
        facilities.add_resource("buildings").add_method("GET")
        facilities.add_resource("buildings").add_method("POST")

        research = api.root.add_resource("research")
        research.add_resource("projects").add_method("GET")
        research.add_resource("projects").add_method("POST")

        # Output the API URL and Database Endpoint
        CfnOutput(self, "APIUrl", value=api.url)
        CfnOutput(self, "DBEndpoint", value=db_cluster.cluster_endpoint.hostname)
        CfnOutput(self, "DBSecretName", value=db_secret.secret_name)

While the above CDK code might look syntactically correct, there are a few glaring issues:

It is mixing up the Serverless cluster configurations with the provisioned configuration
The API resource creation can lead to duplicate resources errors.
It has not used all the APIs identified originally. Let us ignore it for now. But you can prompt it to include all the needed APIs.

Now to move forward, all that is required is hinting and instructing the AI by pointing out the issues and what changes are needed. Let me prompt to update the CDK code to use Aurora Provisioned Cluster version 16.2 with RDS Data API enabled and fix the API add_resource issue. (I was even able to get the Serverless V2 cluster configurations work).

Prompt 7: Can you update the CDK code to use an Aurora Provisioned cluster version 16.2 with RDS data API enabled and fix the API add_resource to prevent the existing resource error?

Response: Certainly! I'll modify the CDK code to deploy an Aurora PostgreSQL cluster version 16.2 and enable the RDS Data API. Also I understand the issue you're facing with the add_resource method potentially causing errors due to existing resources. To fix this, we can modify the API Gateway setup to ensure we don't try to add the same resource twice. Here's an updated version of the CDK code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
from aws_cdk import (
    Stack,
    aws_ec2 as ec2,
    aws_rds as rds,
    aws_lambda as lambda_,
    aws_apigateway as apigw,
    SecretValue,
    CfnOutput,
    Duration,
)
from constructs import Construct

class UniversityManagementStack(Stack):

    def __init__(self, scope: Construct, construct_id: str, **kwargs) -> None:
        super().__init__(scope, construct_id, **kwargs)

        # Create VPC
        vpc = ec2.Vpc(self, "UniversityVPC", max_azs=2)

        # Create Aurora PostgreSQL cluster
        cluster = rds.DatabaseCluster(self, "Database",
            engine=rds.DatabaseClusterEngine.aurora_postgres(version=rds.AuroraPostgresEngineVersion.VER_16_2),
            instance_props=rds.InstanceProps(
                instance_type=ec2.InstanceType.of(ec2.InstanceClass.R6G, ec2.InstanceSize.LARGE),
                vpc_subnets=ec2.SubnetSelection(subnet_type=ec2.SubnetType.PRIVATE_WITH_NAT),
                vpc=vpc
            ),
            instances=1,
            credentials=rds.Credentials.from_generated_secret("clusteradmin"),
            default_database_name="university",
            enable_data_api=True  # This enables the RDS Data API
        )

        # Create Lambda function for schema creation
        schema_lambda = lambda_.Function(self, "SchemaCreationLambda",
            runtime=lambda_.Runtime.PYTHON_3_9,
            handler="index.handler",
            code=lambda_.Code.from_asset("lambda/schema"),
            environment={
                "DB_SECRET_ARN": cluster.secret.secret_arn,
                "DB_NAME": "university",
                "CLUSTER_ENDPOINT": cluster.cluster_endpoint.hostname,
            },
            timeout=Duration.seconds(300),
            vpc=vpc,
            vpc_subnets=ec2.SubnetSelection(subnet_type=ec2.SubnetType.PRIVATE_WITH_NAT),
        )

        # Grant permissions to the schema Lambda
        cluster.secret.grant_read(schema_lambda)
        cluster.connections.allow_default_port_from(schema_lambda)

        # Create Lambda function for API
        api_lambda = lambda_.Function(self, "APILambda",
            runtime=lambda_.Runtime.PYTHON_3_9,
            handler="index.handler",
            code=lambda_.Code.from_asset("lambda/api"),
            environment={
                "DB_SECRET_ARN": cluster.secret.secret_arn,
                "DB_NAME": "university",
                "DB_CLUSTER_ARN": cluster.cluster_arn,
            },
            timeout=Duration.seconds(30),
            vpc=vpc,
            vpc_subnets=ec2.SubnetSelection(subnet_type=ec2.SubnetType.PRIVATE_WITH_NAT),
        )

        # Grant permissions to the API Lambda
        cluster.secret.grant_read(api_lambda)
        cluster.connections.allow_default_port_from(api_lambda)

        # Create API Gateway
        api = apigw.LambdaRestApi(self, "UniversityAPI",
            handler=api_lambda,
            proxy=False
        )

        # Add resources and methods to API Gateway
        academics = api.root.add_resource("academics")
        departments = academics.add_resource("departments")
        departments.add_method("GET")
        departments.add_method("POST")

        courses = academics.add_resource("courses")
        courses.add_method("GET")
        courses.add_method("POST")

        staff = api.root.add_resource("staff")
        employees = staff.add_resource("employees")
        employees.add_method("GET")
        employees.add_method("POST")

        facilities = api.root.add_resource("facilities")
        buildings = facilities.add_resource("buildings")
        buildings.add_method("GET")
        buildings.add_method("POST")

        research = api.root.add_resource("research")
        projects = research.add_resource("projects")
        projects.add_method("GET")
        projects.add_method("POST")

        # Outputs
        CfnOutput(self, "DatabaseEndpoint", value=cluster.cluster_endpoint.hostname)
        CfnOutput(self, "DatabaseSecretArn", value=cluster.secret.secret_arn)
        CfnOutput(self, "APIUrl", value=api.url)

To use this CDK code, you'll need to create two Lambda functions:

API Lambda (lambda/api/index.py): (Use the python code created in the earlier step)
Schema Creation Lambda (lambda/schema/index.py):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
import os
import json
import boto3
import cfnresponse

rds_data = boto3.client("rds-data")

def execute_statement(sql):
    response = rds_data.execute_statement(
        resourceArn=os.environ["DB_CLUSTER_ARN"],
        secretArn=os.environ["DB_SECRET_ARN"],
        database=os.environ["DB_NAME"],
        sql=sql,
    )
    return response

def create_schema():
    schemas = [
        "CREATE SCHEMA IF NOT EXISTS academics;",
        "CREATE SCHEMA IF NOT EXISTS staff;",
        "CREATE SCHEMA IF NOT EXISTS facilities;",
        "CREATE SCHEMA IF NOT EXISTS research;",
    ]

    tables = [
        """
        CREATE TABLE IF NOT EXISTS academics.departments (
            department_id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            code VARCHAR(10) UNIQUE NOT NULL
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS academics.courses (
            course_id SERIAL PRIMARY KEY,
            department_id INTEGER REFERENCES academics.departments(department_id),
            code VARCHAR(20) UNIQUE NOT NULL,
            title VARCHAR(200) NOT NULL,
            credits INTEGER NOT NULL
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS academics.students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            date_of_birth DATE NOT NULL,
            enrollment_date DATE NOT NULL,
            major_department_id INTEGER REFERENCES academics.departments(department_id)
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS staff.employees (
            employee_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            hire_date DATE NOT NULL,
            position VARCHAR(100) NOT NULL
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS facilities.buildings (
            building_id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            address VARCHAR(200) NOT NULL,
            construction_year INTEGER
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS research.projects (
            project_id SERIAL PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            description TEXT,
            start_date DATE,
            end_date DATE,
            funding_amount DECIMAL(12, 2)
        );
        """,
    ]

    for schema in schemas:
        execute_statement(schema)

    for table in tables:
        execute_statement(table)

def handler(event, context):
    try:
        if event["RequestType"] in ["Create", "Update"]:
            create_schema()
        cfnresponse.send(event, context, cfnresponse.SUCCESS, {})
    except Exception as e:
        print(f"Error: {str(e)}")
        cfnresponse.send(event, context, cfnresponse.FAILED, {})

Now I have a fairly working code and will stop the code generation mania and seek help for deploying all of these generated code artifacts ( CDK & Lambda functions ).

Prompt 8: What are the deployment steps using the generated CDK, Lambda and the Schema code ?

Response : Certainly! Here are the step-by-step deployment instructions for your AWS CDK project with the University Management System:

1. Set up your development environment:

Ensure you have Node.js installed (v10.13.0 or later)
Install AWS CDK: npm install -g aws-cdk
Configure AWS CLI with your credentials: aws configure

2. Create and set up your CDK project:

Create a new directory: mkdir university-management-system
Navigate to the directory: cd university-management-system
Initialize a new CDK project: cdk init app --language python

3. Set up a Python virtual environment:

Create a virtual environment: python -m venv .venv
Activate the virtual environment:
- On Windows: .venv\Scripts\activate
- On macOS and Linux: source .venv/bin/activate

4. Install required dependencies:

pip install -r requirements.tx
Add the following to your requirements.tx file and reinstall:
- aws-cdk.aws-ec2
- aws-cdk.aws-rds
- aws-cdk.aws-lambda
- aws-cdk.aws-apigateway

5. Update your CDK stack:

Replace the content of university_management_system/university_management_system_stack.py with the CDK code provided earlier.

6. Create Lambda function directories and files:

Create directories:
- mkdir -p lambda/schema lambda/api
Create lambda/schema/index.py and add the schema creation Lambda code
Create lambda/api/index.py and add the API Lambda code

7. Synthesize the CloudFormation template:

Run cdk synth in your project root directory

8. Deploy the stack:

Run cdk deploy in your project root directory
Confirm the deployment when prompted

9. Monitor the deployment:

The CDK will provide updates on the deployment progress
Once complete, it will output the API Gateway URL, Database Endpoint, and Database Secret ARN

10. Test the deployment:

Use the provided API Gateway URL to test your endpoints
You can use tools like Postman or curl to send requests to your API

11. Update and iterate:

As you develop your application, you may need to update the Lambda functions or add new API routes
After making changes, redeploy using cdk deploy

12. Clean up (when you're done):

To avoid incurring unnecessary costs, run cdk destroy to remove all created resources

Additional notes

Ensure you have sufficient permissions in your AWS account to create and manage the required resources.
The first deployment might take some time as it creates the Aurora cluster and other resources.
Always monitor your AWS costs and resources to avoid unexpected charges.
For production use, consider implementing additional security measures, such as API key authentication for your API Gateway.
Regularly update and patch your Lambda functions and database to maintain security.

Deploy the generated code

Using all the generated code and following the deployment steps above, I was able to get the cdk deploy command working (refer screenshot below) without having to do any changes to the code. In less than 600 seconds, the deploy completed creating all the required resources in AWS. ( Aurora PostgreSQL Cluster, Lambda functions, API Gateway resources etc ).

Image not found

cdk deploy

Verifying the CloudFormation stack confirms all the resources created.

Image not found

CloudFormation Stack

Remember, this setup creates real AWS resources that may incur costs. Always review the resources being created and delete them when they're no longer needed.

Test the generated code

Let us test the schema creation lambda to check if its able to create the schema for us to get started with the database. The schema creation lambda function required a few changes :

The schema creation lambda appears to be generated as a custom resource to be invoked during the deployment of the resources through CDK/CloudFormation. For testing purposes, I simply comment off the #cfnresponse.send() calls so that I can quickly test from the new Lambda function VS Code IDE. You can also get it updated such that it is invoked during the resource deployment when you run the cdk deploy.
Next I also wanted the schema generation code to include sample data creation as well and had to run through another prompt to get it added using the next prompt.

Prompt 9: Can you update the schema generation Lambda function to include some code to ingest sample data into all the tables ?

Response: Certainly! I'll update the schema generation Lambda function to include sample data ingestion for all the tables. Here's the modified version of the Lambda function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
import os
import json
import boto3
#import cfnresponse
from datetime import datetime, timedelta

rds_data = boto3.client("rds-data")

def execute_statement(sql, parameters=[]):
    response = rds_data.execute_statement(
        resourceArn=os.environ["DB_CLUSTER_ARN"],
        secretArn=os.environ["DB_SECRET_ARN"],
        database=os.environ["DB_NAME"],
        sql=sql,
        parameters=parameters
    )
    return response

def create_schema_and_ingest_data():
    schemas = [
        "CREATE SCHEMA IF NOT EXISTS academics;",
        "CREATE SCHEMA IF NOT EXISTS staff;",
        "CREATE SCHEMA IF NOT EXISTS facilities;",
        "CREATE SCHEMA IF NOT EXISTS research;",
    ]

    tables = [
        """
        CREATE TABLE IF NOT EXISTS academics.departments (
            department_id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            code VARCHAR(10) UNIQUE NOT NULL
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS academics.courses (
            course_id SERIAL PRIMARY KEY,
            department_id INTEGER REFERENCES academics.departments(department_id),
            code VARCHAR(20) UNIQUE NOT NULL,
            title VARCHAR(200) NOT NULL,
            credits INTEGER NOT NULL
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS academics.students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            date_of_birth DATE NOT NULL,
            enrollment_date DATE NOT NULL,
            major_department_id INTEGER REFERENCES academics.departments(department_id)
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS staff.employees (
            employee_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            hire_date DATE NOT NULL,
            position VARCHAR(100) NOT NULL
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS facilities.buildings (
            building_id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            address VARCHAR(200) NOT NULL,
            construction_year INTEGER
        );
        """,
        """
        CREATE TABLE IF NOT EXISTS research.projects (
            project_id SERIAL PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            description TEXT,
            start_date DATE,
            end_date DATE,
            funding_amount DECIMAL(12, 2)
        );
        """,
    ]

    for schema in schemas:
        execute_statement(schema)

    for table in tables:
        execute_statement(table)

    # Sample data insertion
    sample_data = [
        ("INSERT INTO academics.departments (name, code) VALUES (:name, :code)",
         [
             [{'name': 'name', 'value': {'stringValue': 'Computer Science'}}, {'name': 'code', 'value': {'stringValue': 'CS'}}],
             [{'name': 'name', 'value': {'stringValue': 'Physics'}}, {'name': 'code', 'value': {'stringValue': 'PHY'}}],
             [{'name': 'name', 'value': {'stringValue': 'Mathematics'}}, {'name': 'code', 'value': {'stringValue': 'MATH'}}]
         ]),
        ("INSERT INTO academics.courses (department_id, code, title, credits) VALUES (:dept_id, :code, :title, :credits)",
         [
             [{'name': 'dept_id', 'value': {'longValue': 1}}, {'name': 'code', 'value': {'stringValue': 'CS101'}}, {'name': 'title', 'value': {'stringValue': 'Introduction to Programming'}}, {'name': 'credits', 'value': {'longValue': 3}}],
             [{'name': 'dept_id', 'value': {'longValue': 2}}, {'name': 'code', 'value': {'stringValue': 'PHY201'}}, {'name': 'title', 'value': {'stringValue': 'Classical Mechanics'}}, {'name': 'credits', 'value': {'longValue': 4}}],
             [{'name': 'dept_id', 'value': {'longValue': 3}}, {'name': 'code', 'value': {'stringValue': 'MATH301'}}, {'name': 'title', 'value': {'stringValue': 'Linear Algebra'}}, {'name': 'credits', 'value': {'longValue': 3}}]
         ]),
        ("INSERT INTO academics.students (first_name, last_name, date_of_birth, enrollment_date, major_department_id) VALUES (:fname, :lname, CAST(:dob AS DATE), CAST(:enroll_date AS DATE), :dept_id)",
         [
             [{'name': 'fname', 'value': {'stringValue': 'John'}}, {'name': 'lname', 'value': {'stringValue': 'Doe'}}, {'name': 'dob', 'value': {'stringValue': '2000-01-15'}}, {'name': 'enroll_date', 'value': {'stringValue': '2022-09-01'}}, {'name': 'dept_id', 'value': {'longValue': 1}}],
             [{'name': 'fname', 'value': {'stringValue': 'Jane'}}, {'name': 'lname', 'value': {'stringValue': 'Smith'}}, {'name': 'dob', 'value': {'stringValue': '2001-03-22'}}, {'name': 'enroll_date', 'value': {'stringValue': '2022-09-01'}}, {'name': 'dept_id', 'value': {'longValue': 2}}],
             [{'name': 'fname', 'value': {'stringValue': 'Alice'}}, {'name': 'lname', 'value': {'stringValue': 'Johnson'}}, {'name': 'dob', 'value': {'stringValue': '2000-07-30'}}, {'name': 'enroll_date', 'value': {'stringValue': '2022-09-01'}}, {'name': 'dept_id', 'value': {'longValue': 3}}]
         ]),
        ("INSERT INTO staff.employees (first_name, last_name, email, hire_date, position) VALUES (:fname, :lname, :email, CAST(:hire_date AS DATE), :position)",
         [
             [{'name': 'fname', 'value': {'stringValue': 'Robert'}}, {'name': 'lname', 'value': {'stringValue': 'Brown'}}, {'name': 'email', 'value': {'stringValue': 'robert.brown@university.edu'}}, {'name': 'hire_date', 'value': {'stringValue': '2015-08-15'}}, {'name': 'position', 'value': {'stringValue': 'Professor'}}],
             [{'name': 'fname', 'value': {'stringValue': 'Emily'}}, {'name': 'lname', 'value': {'stringValue': 'Davis'}}, {'name': 'email', 'value': {'stringValue': 'emily.davis@university.edu'}}, {'name': 'hire_date', 'value': {'stringValue': '2018-01-10'}}, {'name': 'position', 'value': {'stringValue': 'Associate Professor'}}],
             [{'name': 'fname', 'value': {'stringValue': 'Michael'}}, {'name': 'lname', 'value': {'stringValue': 'Wilson'}}, {'name': 'email', 'value': {'stringValue': 'michael.wilson@university.edu'}}, {'name': 'hire_date', 'value': {'stringValue': '2020-07-01'}}, {'name': 'position', 'value': {'stringValue': 'Assistant Professor'}}]
         ]),
        ("INSERT INTO facilities.buildings (name, address, construction_year) VALUES (:name, :address, :year)",
         [
             [{'name': 'name', 'value': {'stringValue': 'Science Building'}}, {'name': 'address', 'value': {'stringValue': '123 University Ave'}}, {'name': 'year', 'value': {'longValue': 1985}}],
             [{'name': 'name', 'value': {'stringValue': 'Library'}}, {'name': 'address', 'value': {'stringValue': '456 College St'}}, {'name': 'year', 'value': {'longValue': 1990}}],
             [{'name': 'name', 'value': {'stringValue': 'Student Center'}}, {'name': 'address', 'value': {'stringValue': '789 Campus Rd'}}, {'name': 'year', 'value': {'longValue': 2005}}]
         ]),
        ("INSERT INTO research.projects (title, description, start_date, end_date, funding_amount) VALUES (:title, :desc, CAST(:start AS DATE), CAST(:end AS DATE), :amount)",
         [
             [{'name': 'title', 'value': {'stringValue': 'AI in Education'}}, {'name': 'desc', 'value': {'stringValue': 'Exploring AI applications in higher education'}}, {'name': 'start', 'value': {'stringValue': '2023-01-01'}}, {'name': 'end', 'value': {'stringValue': '2025-12-31'}}, {'name': 'amount', 'value': {'doubleValue': 500000.00}}],
             [{'name': 'title', 'value': {'stringValue': 'Quantum Computing Advances'}}, {'name': 'desc', 'value': {'stringValue': 'Research on quantum algorithms'}}, {'name': 'start', 'value': {'stringValue': '2022-07-01'}}, {'name': 'end', 'value': {'stringValue': '2024-06-30'}}, {'name': 'amount', 'value': {'doubleValue': 750000.00}}],
             [{'name': 'title', 'value': {'stringValue': 'Climate Change Mitigation'}}, {'name': 'desc', 'value': {'stringValue': 'Studying effective climate change mitigation strategies'}}, {'name': 'start', 'value': {'stringValue': '2023-03-15'}}, {'name': 'end', 'value': {'stringValue': '2026-03-14'}}, {'name': 'amount', 'value': {'doubleValue': 1000000.00}}]
         ])
    ]

    for sql, data in sample_data:
        for item in data:
            execute_statement(sql, item)

def handler(event, context):
    try:
        if event["RequestType"] in ["Create", "Update"]:
            create_schema_and_ingest_data()
            print("Schema and tables created with sample data !! ")
        #cfnresponse.send(event, context, cfnresponse.SUCCESS, {})
    except Exception as e:
        print(f"Error: {str(e)}")
        #cfnresponse.send(event, context, cfnresponse.FAILED, {})

After these changes, invoking the lambda with a test payload, I realized that the Lambda function does not have the desired permissions to access the Aurora PostgreSQL cluster using RDS Data APIs. Now, let us do one last prompt for the day to update the IAM role into the CDK code.

Prompt 10: Can you update the CDK code to include a role for the schema creation lambda to perform rds-data:ExecuteStatement on the Aurora Cluster ?

Response: Certainly! I'll update the CDK code to include a specific IAM role for the schema creation Lambda function, granting it the necessary permissions to execute statements using the RDS Data API. Here's the updated CDK code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
from aws_cdk import (
    Stack,
    aws_ec2 as ec2,
    aws_rds as rds,
    aws_iam as iam,
    aws_lambda as lambda_,
    aws_apigateway as apigw,
    SecretValue,
    CfnOutput,
    Duration,
    CustomResource,
    aws_logs as logs,
)
from constructs import Construct

class UniversityManagementStack(Stack):

    def __init__(self, scope: Construct, construct_id: str, **kwargs) -> None:
        super().__init__(scope, construct_id, **kwargs)

        # Create VPC
        vpc = ec2.Vpc(self, "UniversityVPC", max_azs=2)

        # Create Aurora PostgreSQL cluster
        cluster = rds.DatabaseCluster(
            self,
            "Database",
            engine=rds.DatabaseClusterEngine.aurora_postgres(
                version=rds.AuroraPostgresEngineVersion.VER_16_2
            ),
            instance_props=rds.InstanceProps(
                instance_type=ec2.InstanceType.of(
                    ec2.InstanceClass.R6G, ec2.InstanceSize.LARGE
                ),
                vpc_subnets=ec2.SubnetSelection(
                    subnet_type=ec2.SubnetType.PRIVATE_WITH_NAT
                ),
                vpc=vpc,
            ),
            instances=1,
            credentials=rds.Credentials.from_generated_secret("clusteradmin"),
            default_database_name="university",
            enable_data_api=True,  # This enables the RDS Data API
        )

        # Create IAM role for schema creation Lambda
        schema_lambda_role = iam.Role(
            self,
            "SchemaLambdaRole",
            assumed_by=iam.ServicePrincipal("lambda.amazonaws.com"),
            description="Role for Schema Creation Lambda",
        )

        # Add policies to the role
        schema_lambda_role.add_managed_policy(
            iam.ManagedPolicy.from_aws_managed_policy_name(
                "service-role/AWSLambdaVPCAccessExecutionRole"
            )
        )
        schema_lambda_role.add_to_policy(
            iam.PolicyStatement(
                actions=["rds-data:ExecuteStatement"], resources=[cluster.cluster_arn]
            )
        )
        schema_lambda_role.add_to_policy(
            iam.PolicyStatement(
                actions=["secretsmanager:GetSecretValue"],
                resources=[cluster.secret.secret_arn],
            )
        )

        # Create Lambda function for schema creation
        schema_lambda = lambda_.Function(
            self,
            "SchemaCreationLambda",
            runtime=lambda_.Runtime.PYTHON_3_9,
            handler="index.handler",
            code=lambda_.Code.from_asset("lambda/schema"),
            environment={
                "DB_SECRET_ARN": cluster.secret.secret_arn,
                "DB_NAME": "university",
                "DB_CLUSTER_ARN": cluster.cluster_arn,
            },
            timeout=Duration.seconds(300),
            vpc=vpc,
            vpc_subnets=ec2.SubnetSelection(
                subnet_type=ec2.SubnetType.PRIVATE_WITH_NAT
            ),
            role=schema_lambda_role,  # Assign the role to the Lambda function
        )

        # Grant permissions to the schema Lambda
        cluster.secret.grant_read(schema_lambda)
        cluster.connections.allow_default_port_from(schema_lambda)

        # Create IAM role for schema creation Lambda
        api_lambda_role = iam.Role(
            self,
            "APILambdaRole",
            assumed_by=iam.ServicePrincipal("lambda.amazonaws.com"),
            description="Role for API Lambda",
        )

        # Add policies to the role
        api_lambda_role.add_managed_policy(
            iam.ManagedPolicy.from_aws_managed_policy_name(
                "service-role/AWSLambdaVPCAccessExecutionRole"
            )
        )
        api_lambda_role.add_to_policy(
            iam.PolicyStatement(
                actions=["rds-data:ExecuteStatement"], resources=[cluster.cluster_arn]
            )
        )
        api_lambda_role.add_to_policy(
            iam.PolicyStatement(
                actions=["secretsmanager:GetSecretValue"],
                resources=[cluster.secret.secret_arn],
            )
        )

        # Create Lambda function for API
        api_lambda = lambda_.Function(
            self,
            "APILambda",
            runtime=lambda_.Runtime.PYTHON_3_9,
            handler="index.handler",
            code=lambda_.Code.from_asset("lambda/api"),
            environment={
                "DB_SECRET_ARN": cluster.secret.secret_arn,
                "DB_NAME": "university",
                "DB_CLUSTER_ARN": cluster.cluster_arn,
            },
            timeout=Duration.seconds(30),
            vpc=vpc,
            vpc_subnets=ec2.SubnetSelection(
                subnet_type=ec2.SubnetType.PRIVATE_WITH_NAT
            ),
            role=api_lambda_role,  # Assign the role to the Lambda function
        )

        # Grant permissions to the API Lambda
        cluster.secret.grant_read(api_lambda)
        cluster.connections.allow_default_port_from(api_lambda)

        # Create API Gateway
        api = apigw.LambdaRestApi(
            self, "UniversityAPI", handler=api_lambda, proxy=False
        )

        # Add resources and methods to API Gateway
        academics = api.root.add_resource("academics")
        departments = academics.add_resource("departments")
        departments.add_method("GET")
        departments.add_method("POST")

        courses = academics.add_resource("courses")
        courses.add_method("GET")
        courses.add_method("POST")

        staff = api.root.add_resource("staff")
        employees = staff.add_resource("employees")
        employees.add_method("GET")
        employees.add_method("POST")

        facilities = api.root.add_resource("facilities")
        buildings = facilities.add_resource("buildings")
        buildings.add_method("GET")
        buildings.add_method("POST")

        research = api.root.add_resource("research")
        projects = research.add_resource("projects")
        projects.add_method("GET")
        projects.add_method("POST")

        # Outputs
        CfnOutput(self, "DatabaseEndpoint", value=cluster.cluster_endpoint.hostname)
        CfnOutput(self, "DatabaseSecretArn", value=cluster.secret.secret_arn)
        CfnOutput(self, "APIUrl", value=api.url)

Repeated the same for the API Lambda function as well and updated the code.

Now I just need to run another cdk deploy for the new changes in the CDK code (IAM role for Lambda) to be updated and now I can test the Lambda function to confirm that it can create the schema successfully.

Image not found

Schema Creation Lambda

I can also validate the sample data using the RDS Query Editor feature :

Image not found

RDS Query Editor

Let us then test the API Lambda function as well :

Image not found

Testing API Lambda Handler

The final test is to invoke the APIs and verify that it works for both GET and POST.

Image not found

Testing APIs using curl request

And validate it from the RDS Query editor as well !!

Image not found

RDS Query Editor

Conclusion

By leveraging AI-assisted code generation, I have accomplished in a matter of minutes what might traditionally take days or even weeks of manual coding and configuration. While the generated code still requires human oversight, testing, and customization to meet specific business needs, the productivity gains are substantial. Developers can now start from a much more advanced baseline, allowing them to deliver more value in less time.

This approach doesn't replace the need for skilled developers but rather augments their capabilities. It shifts the developer's role towards higher-level system design, business logic implementation, and fine-tuning performance and security – areas where human expertise and creativity are most valuable.

As generative AI continues to evolve, I can expect even more sophisticated code generation capabilities, further revolutionizing the software development lifecycle and enabling teams to build complex, scalable systems with unprecedented speed and efficiency.

Happy Code Generation :-)

Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.

Select your cookie preferences

Site Terms, Privacy, and more.

Code Generation: Exploring Generative AI for Database Workloads

My code generation experience using a "Productive AI" tool built on Amazon Bedrock leveraging its foundation models.

Prompt 1: Can you provide a sample PostgreSQL schema and tables ?

Prompt 2: This is a bit boring. Can you give another example ?

Prompt 3: Can you list all the possible APIs for the above database schema ?

Prompt 4: Can you generate an OpenAPI specification for these APIs in YAML format ?

Prompt 5: Now generate a Python based Lambda function to implement all the above APIs and use RDS Data APIs to interface with Aurora PostgreSQL database ?

Prompt 6: Great. Now can you generate a CDK code to deploy an Aurora PostgreSQL cluster with the schema and APIs generated.

Prompt 7: Can you update the CDK code to use an Aurora Provisioned cluster version 16.2 with RDS data API enabled and fix the API add_resource to prevent the existing resource error?

Prompt 8: What are the deployment steps using the generated CDK, Lambda and the Schema code ?

Deploy the generated code

Test the generated code

Prompt 9: Can you update the schema generation Lambda function to include some code to ingest sample data into all the tables ?

Prompt 10: Can you update the CDK code to include a role for the schema creation lambda to perform rds-data:ExecuteStatement on the Aurora Cluster ?

Conclusion

Comments