Published on: Mon Feb 14 2022
In this module, we will take a look at how to setup our data model.
Since this a series mainly focused on AWS Aurora, We will just be setting up the basics in this section.
We’ll look at setting up the model, database triggers and go through a quick overview running the local postgreSQL instance inside docker.
Let’s get started.
Here is the data modelling for our applicaton.
author(individual who can publish posts) or just null
When we do a quick overview of the application code in the next modules, we will see how it all works out.
Since we may be running our scripts a few times in our database, let’s be sure that we don’t have issues with duplicates. So, to start, we’ll create statements to drop any tables if they exist.
-- Clear table if it already exists DROP TABLE IF EXISTS social_accounts; DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS posts; DROP TABLE IF EXISTS comments;
In this case, the
is the user’s social media handle, and the
is the type of social media (ie Twitter, Github, Linkedin).
-- 01_create.sql -- Tables CREATE TABLE social_accounts( id serial PRIMARY KEY , username VARCHAR(255) , social_type VARCHAR(255) , created_at timestamptz DEFAULT current_timestamp , updated_at timestamptz DEFAULT current_timestamp );
Note: we are using "timestamptz" here but a simple "date" would also work for our purpose but just be mindful that when working with production databases that this is more suitable for cases where time zones makes a big difference like a scheduling and calendar app.
We are referencing
table to establish a relationship (1:N, one-to-many).
records belong to a
-- 01_create.sql CREATE TABLE users( id serial PRIMARY KEY , name VARCHAR(255) , email VARCHAR(255) , password VARCHAR(255) , user_type VARCHAR(255) , social_id INTEGER REFERENCES social_accounts(id) , created_at timestamptz DEFAULT current_timestamp , updated_at timestamptz DEFAULT current_timestamp );
Same with the
can have many
-- 01_create.sql CREATE TABLE posts( id serial PRIMARY KEY , title VARCHAR(255) , content TEXT , image_url VARCHAR(255) , slug VARCHAR(255) , user_id INTEGER REFERENCES users(id) , created_at timestamptz DEFAULT current_timestamp , updated_at timestamptz DEFAULT current_timestamp );
Similar as the above, a
can have many
belongs to a
(so we can identify who made the comment).
-- 01_create.sql CREATE TABLE comments( id serial PRIMARY KEY , content VARCHAR(255) , user_id INTEGER REFERENCES users(id) , post_id INTEGER REFERENCES posts(id) , created_at timestamptz DEFAULT current_timestamp , updated_at timestamptz DEFAULT current_timestamp );
In our case, we will be adding the
trigger to update our
in our tables to ensure we track when entries were last updated.
-- 01_create.sql -- Updated at trigger - refresh the timestamp during an update CREATE OR REPLACE FUNCTION updated_at_trigger() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ language 'plpgsql';
Whenever we update, we will run the trigger function defined above.
-- 01_create.sql -- Triggers CREATE TRIGGER update_social_accounts_timestamp BEFORE UPDATE ON social_accounts FOR EACH ROW EXECUTE PROCEDURE updated_at_trigger(); CREATE TRIGGER update_users_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE updated_at_trigger(); CREATE TRIGGER update_posts_timestamp BEFORE UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE updated_at_trigger(); CREATE TRIGGER update_comments_timestamp BEFORE UPDATE ON comments FOR EACH ROW EXECUTE PROCEDURE updated_at_trigger();
Here, we are just going to add some data into the database and tables we have created.
Now, that we have the database, tables and seed data ready. Let’s actually get a local postgreSQL instance running in our docker to test it out.
We will be using
image and credentials as shown below.
To avoid conflict with existing postgreSQL instances on your machine we will map default PostgreSQL port (
As part of the configuration, we will also copy over our SQL scripts to be ran on initialization inside the
Remember to create a new file in your root directory called
# docker-compose.yml version: '3.7' services: postgres: image: postgres:12 restart: always environment: - POSTGRES_USER=postgres - POSTGRES_PASSWORD=test123 logging: options: max-size: 10m max-file: "3" ports: - '5438:5432' volumes: # Copy over db create sql - ./data/sql/01_create.sql:/docker-entrypoint-initdb.d/01_create.sql # Copy over seed sql - ./data/sql/02_seed.sql:/docker-entrypoint-initdb.d/02_seed.sql
Let’s get the local docker image running.
Run command (Rebuild image and init again):
You may need to run this command if you made changes to the SQL scripts as docker will cache the image and the volume.
docker-compose up --build --force-recreate -V
export PGPASSWORD=test123 psql -p 5438 --host 0.0.0.0 --username postgres
You should see
cli display the comments in the database after running the command below.
You can also try other queries like (
select * from posts;
select * from users;
They should all work as expected (assuming the the setup was done correctly!).
\c postgres; select * from comments;
to summarize, we first took some time to understand the data model used in our application, how to implement it in using SQL and also added triggers to keep track of when it was last updated.
In addition, we also setup a local docker image which would intialize a database which ran the SQL scripts we have defined.
Now we have a working development database, the next step is to run through our applications to see how it gets connected.
Also, it would be a good time to discuss the differences between running the database locally versus in production (on AWS ECS).
The code is already available in our repository but we will do a quick walk through to highlight the main parts! See you in the next module!
Then consider signing up to get notified when new content arrives!