📄Matar DB tables

This page will describe how Matar facilitates communication between the app and its databases.

---

Matar makes use of Alembic which is a database version management system using SQL Alchemy as the underlying engine. SQL Alchemy is a library that manages communication between Python programs and databases.

With Alembic you can define a schema via a sqlalchemy model, and have the database reflect those changes. Alembic can also migrate schemas via SQLalchemy models. no work has to be done manually which reduces chances of error.

Let's take a sample sqlalchemy model from matar code repo ,alembic will create a table reflecting this schema in our database:

SQLAlchemy Model

In a file db.py , we have the following SQLAlchemy model for an organization in matar app :

import json
import requests
from functools import cached_property
from db import db
from sqlalchemy.dialects.postgresql import JSON
from datetime import datetime, timedelta
from utils.common_functions import get_random_string,\
    get_response_from_chatgpt, cur_ms, normalize_value
from utils.constants import DEFAULT_CHATGPT_USER
from .base import Base, Language
from .user import User

MIN_TIMESTAMP = 1609439400000
MAX_TIMESTAMP = 1893436200000

class Organization(Base):
    __tablename__ = 'organization'

    _id = db.Column(db.String, primary_key=True)
    title = db.Column(db.String)
    language = db.Column(db.String)
    is_chatgpt_enabled = db.Column(db.Boolean, default=True)
    max_post_duration = db.Column(db.Integer, default=15)  # in seconds
    org_type = db.Column(db.Integer, default=0)  # 0 - public, 1 - private
    is_active = db.Column(db.Boolean, default=True)
    description = db.Column(db.String)
    code = db.Column(db.String)
    

    sort_order = db.Column(db.Integer)

    def __init__(
        self, title, _id=None, language=Language.HINDI, is_chatgpt_enabled=True,
        max_post_duration=15, org_type=0, is_active=True, description=None, code=None
    ):
        self._id = _id or get_random_string()
        self.title = title
        self.language = language
        self.is_chatgpt_enabled = is_chatgpt_enabled,
        self.max_post_duration = max_post_duration
        self.org_type = org_type
        self.is_active = is_active
        self.description = description
        self.code = code

In the code above `class organization(Base)` uses a SQLalchemy base class that comes with a number of features including metadata that contains information about all schemas.

Alembic uses the metadata information from Base to create database tables. The alembic components with database configuration are located inside the migrations folder of the Matar repo.

---

Migrations directory

Here is a preview of migrations folder of the matar repo:

--migrations(directory)
 --versions(directory)
 --alembic.ini(file)
 --env.py(file)
 --script.py.mako(file)
 --README(file) 

versions - Directory for storage of migration files.

alembic.ini - Alembic configuration file.

README - Instructions on how to use alembic.

env.py - Commands and configurations for alembic migrations.

script.py.mako - This is a Mako template file which is used to generate new migration scripts. Whatever is here is used to generate new files within versions/.

Here is an alembic migration file related to organization SQLalchemy model defined on the starting of the page:

"""create organization table

Revision ID: 4640e9af0234
Revises: 29edbf20051e
Create Date: 2023-04-28 01:59:42.015293

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '4640e9af0234'
down_revision = '29edbf20051e'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto-generated by Alembic - please adjust! ###
    op.create_table('organization',
    sa.Column('_id', sa.String(), nullable=False),
    sa.Column('title', sa.String(), nullable=True),
    sa.Column('sort_order', sa.Integer(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('_id')
    )
    with op.batch_alter_table('category', schema=None) as batch_op:
        batch_op.add_column(sa.Column('organization_id', sa.String(), nullable=True))

    with op.batch_alter_table('post', schema=None) as batch_op:
        batch_op.add_column(sa.Column('organization_id', sa.String(), nullable=True))

    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.add_column(sa.Column('roles', sa.JSON(), nullable=True))

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.drop_column('roles')

    with op.batch_alter_table('post', schema=None) as batch_op:
        batch_op.drop_column('organization_id')

    with op.batch_alter_table('category', schema=None) as batch_op:
        batch_op.drop_column('organization_id')

    op.drop_table('organization')
    # ### end Alembic commands ###

---

Describing Alembic Code

An alembic migration file contains two functions: upgrade and downgrade. These functions contain the necessary operations to implement the changes that you made to your SQLAlchemy models (upgrade) and to undo the changes once they've been implemented (downgrade).

Another thing to notice is the down_revision variable. This is how Alembic knows the correct order in which to apply migrations. When we create the next revision, the new file’s down_revision identifier would point to this one:

# revision identifiers, used by Alembic.

revision = '4640e9af0234'

down_revision = '29edbf20051e'

create_table() and drop_table() are Alembic commands for creating and modifying database models. An overview of all Alembic commands is at Operation Reference.

Now if we want to modify or add another column to the organization table. We need to perform another migration where alembicrevise '4640e9af0234' and create a new revision.

"""Add an organization ID column to Organization Database Table. 

Revision ID: 01ea0e75088a
Revises: 4640e9af0234
Create Date: 2023-04-28 02:27:59.044441

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '01ea0e75088a'
down_revision = '4640e9af0234'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('post_activity', schema=None) as batch_op:
        batch_op.add_column(sa.Column('organization_id', sa.String(), nullable=True))

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('post_activity', schema=None) as batch_op:
        batch_op.drop_column('organization_id')

    # ### end Alembic commands ###

---

Listing SQLAlchemy Models

A detailed list of all relevant SQLAlchemy models is provided below:

• Organization SQLAlchemy Model

• Post SQLAlchemy Model

• Category SQLAlchemy Model

• PostActivity SQLAlchemy Model

• UserPostHistory SQLAlchemy Model

• PostActivityType SQLAlchemy Model

---

Listing Alemic Scripts

• Create post_activity_type database table with PostActivity SQLAlchemy model.

  • Add category_id column

• Create organization database table with Organization SQLAlchemy model.

• Create user_post_history database table with SQLAlchemy model

  • Update id column

• Create category database table with category SQLAlchemy model

  • Add sort order column

• Create user database table with user SQLAlchemy model

  • Add image_url column

  • Add source column

  • Add language column

  • Modify Update and Creation details.

• Populate post database table with post SQLAlchemy model.

  • Add parent_post_id column

  • Add is_chatgpt_answered column

• Populate Post Activity database table with post activity SQLAlchemy model