A database script is a file of SQL commands that creates, modifies, or administers database objects and data. In the tutor service, scripts define tables before application compilation and initialize Dockerized Postgres databases with test data. section 6.3.4 section 12.3.2
Use a database script to:
- Drop existing tutor and course tables when rebuilding the schema. section 6.3.4
- Create tables with columns, types, defaults, primary keys, and relationships. section 6.3.4
- Load test data, then inspect relations and verify persistence after a container restart. section 12.3.2
/* Drop tables if they already exist*/
drop table if exists ezy_course_c6 cascade; #1
drop table if exists ezy_tutor_c6;
/* Create tables. */
create table ezy_tutor_c6 ( #2
tutor_id serial primary key,
tutor_name varchar(200) not null,
tutor_pic_url varchar(200) not null,
tutor_profile varchar(2000) not null
);
create table ezy_course_c6 #3
(
course_id serial primary key,
tutor_id INT not null,
course_name varchar(140) not null,
course_description varchar(2000),
course_format varchar(30),
course_structure varchar(200),
course_duration varchar(30),
course_price INT,
course_language varchar(30),
course_level varchar(30),
posted_time TIMESTAMP default now(),
CONSTRAINT fk_tutor #4
FOREIGN KEY(tutor_id) #4
REFERENCES ezy_tutor_c6(tutor_id) #4
ON DELETE cascade
);
grant all privileges on table ezy_tutor_c6 to <username>; #5
grant all privileges on table ezy_course_c6 to <username>;
grant all privileges on all sequences in schema public to <username>;;
/* Load seed data for testing */ #6
insert into ezy_tutor_c6(tutor_id, tutor_name, tutor_pic_url,tutor_profile)
values(1,'Merlene','http://s3.amazon.aws.com/pic1',
'Merlene is an experienced finance professional');
insert into ezy_tutor_c6(tutor_id, tutor_name, tutor_pic_url,tutor_profile)
values(2,'Frank','http://s3.amazon.aws.com/pic2',
'Frank is an expert nuclear engineer');
insert into ezy_course_c6
(course_id,tutor_id, course_name,course_level, posted_time)
values(1, 1, 'First course', 'Beginner' , '2021-04-12 05:40:00');
insert into ezy_course_c6
(course_id, tutor_id, course_name, course_format, posted_time)
values(2, 1, 'Second course', 'ebook', '2021-04-12 05:45:00');
Shows a complete Postgres schema script for tutor and course tables, including identifiers, required fields, defaults, and a tutor relationship. section 6.3.4
Initialize a Dockerized database
Run \i /docker-entrypoint-initdb.d/init-tables.sql in psql, confirm that tutor and course relations appear, query their rows, insert a tutor, restart the container, and query again to verify persistence. section 12.3.2
