Ecommerce Database design with AWS RDS, Flyway and Django

Ecommerce websites/apps have been a buzz these days. Actually has always been… So, I thought why not try hands building one..

I will explain the same here in this post.

So, in this post, we will explore several things. Let me give a brief of what you are going to get from this post.

• Set up flyway in your local machine
• Set up a Django project from scratch
• Configure RDS with elastic cache
• Set up user creds for RDS
• Connect RDS to Django app
• Build the DDL script for flyway
• Migrate the database to reflect tables

Set up flyway in your local machine

Which is very straight forward, you just have to follow the official website and download a copy on your system, from where you can take on the migration, and we will do the same in a couple of minutes from now.

Set up a Django project from scratch

To begin with you have to setup a virtual environment first and then you can install any number of dependencies through the environment after activating the same.

python -m venv env;
env/scripts/activate

Assuming you have pip and python installed, you can use pip to install Django.

After having that done we have to do some configuration tweaks in settings.py of the project.

Before we even make the tweaks, we have to take care of the database configuration first.

We will use postgres as database, I chose amazon RDS to host as my database, you can try localhost as well. So, we need of couple of things from RDS for the connect through after and of course dbeaver where we will access our database.

So, we will need .. database name, host, user password..

With these parameters, we can set up a dbeaver database connection and connect with RDS using the django app..

Below is the place where we have to do the tweak.

With this change, we should be ready to run the server.

Build the DDL script for flyway

Next step is to actually build the tables for which we need to a DDL script… I am presenting the DDL script which we will run in flyway desktop

CREATE TABLE public.products (
    id bigint NOT NULL,
    category_id date,
    title character varying(255),
    picture character varying(255)L,
    summary character varying(255),
    desc character varying(255),
    price character varying(255),
    discount_type character varying(255),
    discount_value character varying(255),
    tags character varying(255),
    created_at date
    updated_at date
);
ALTER TABLE public.products OWNER TO postgres;
 
CREATE TABLE public.categories (
    id bigint NOT NULL,
    parent_category date,
    slug character varying(255),
    name character varying(255),
    desc character varying(255),
    tags character varying(255),
    created_at date,
    updated_at date
);
ALTER TABLE public.categories OWNER TO postgres;
 
CREATE TABLE public.reviews (
    id bigint NOT NULL,
    user_id character varying(255),
    product_id character varying(255),
    rating character varying(255),
    comment character varying(255)
    created_at date,
);
ALTER TABLE public.reviews OWNER TO postgres;
 
CREATE TABLE public.users (
    id bigint NOT NULL,
    slug character varying(255),
    email character varying(255),
    phone character varying(255),
    role character varying(255),
    name character varying(255)
    avatar character varying(255),
    locale character varying(255)
    created_at date,
    updated_at date
    last_login character varying(255),
    email_validated character varying(255)
    phone_validated character varying(255),
    bio character varying(255),
    company character varying(255)
);
ALTER TABLE public.users OWNER TO postgres;
 
CREATE TABLE public.carts (
    id bigint NOT NULL,
    created_by character varying(255),
    status character varying(255),
    created_at date,
    updated_at date
);
ALTER TABLE public.carts OWNER TO postgres;

CREATE TABLE public.cart_items (
    cart_id character varying(255),
    product_id character varying(255),
    created_at date,
    price character varying(255)
    quantity character varying(255)
);
ALTER TABLE public.cart_items OWNER TO postgres;

CREATE TABLE public.orders (
    id bigint NOT NULL,
    created_at date,
    user_id character varying(255
);
ALTER TABLE public.orders OWNER TO postgres;


CREATE TABLE public.order_lines (
    id bigint NOT NULL,
    order_id character varying(255),
    product_id character varying(255),
    price character varying(255)
    quantity character varying(255)
);
ALTER TABLE public.order_lines OWNER TO postgres;

 
ALTER TABLE ONLY public.products
    ADD CONSTRAINT products_pkey PRIMARY KEY (id);
 
ALTER TABLE ONLY public.categories
    ADD CONSTRAINT categories_pkey PRIMARY KEY (id);
 
ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_games_pkey PRIMARY KEY (id);
 
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);
 
ALTER TABLE ONLY public.carts
    ADD CONSTRAINT carts_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.cart_items
    ADD CONSTRAINT cart_items_pkey PRIMARY KEY (id); 

ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.order_lines
    ADD CONSTRAINT order_lines_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.products
    ADD CONSTRAINT fk1kenmun90s7uhly5kkk9o6rsf FOREIGN KEY (products_id) REFERENCES public.products(id);
 
ALTER TABLE ONLY public.categories
    ADD CONSTRAINT fk5ykqwib1neqhak6wwuhsusf5w FOREIGN KEY (categories_id) REFERENCES public.categories(id);
 
ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT fkfmhm06fi40ak53r6gofvoyr44 FOREIGN KEY (reviews_id) REFERENCES public.reviews(id);
 
ALTER TABLE ONLY public.users
    ADD CONSTRAINT fkhoasvgr0mq1tkj5308chmd97v FOREIGN KEY (users_id) REFERENCES public.users(id);
 
ALTER TABLE ONLY public.carts
    ADD CONSTRAINT fkikaihvc8m29y7fqtk5brfwk48 FOREIGN KEY (carts_id) REFERENCES public.carts(id);
 
ALTER TABLE ONLY public.cart_items
    ADD CONSTRAINT fkquj6n755j3k650vwhoabw44yu FOREIGN KEY (cart_items_id) REFERENCES public.cart_items(id);
 
ALTER TABLE ONLY public.orders
    ADD CONSTRAINT fkuqqdoorh4jhfx6mqe3wsy5ni FOREIGN KEY (orders_id) REFERENCES public.orders(id);

ALTER TABLE ONLY public.order_lines
    ADD CONSTRAINT fkupqdoorh4jhfx6mqe3wsy5ny FOREIGN KEY (order_lines_id) REFERENCES public.order_lines(id);

Migrate the database to reflect tables

The next step is easy.. set up a project in red hat’s flyway desktop tool and run the migration. All the migrations are already done in the tool automatically..

Hope this helps…