Blog App Tutorial Part 1

This tutorial series builds a blog application using the same patterns shown in the repository example. Part 1 focuses on setup and schema.

Prerequisites

Ballerina Swan Lake installed
Docker installed and running
MySQL 8 or PostgreSQL 16 available
thambaru/bal_orm.orm dependency added

Project setup

Add the dependency:

toml

[dependencies]
thambaru.bal_orm = "0.1.0"

Or run:

bash

bal add thambaru:bal_orm

Blog domain model

The tutorial uses five entities:

User
UserProfile
Post
Category
Comment

Schema definition (annotated records)

ballerina

import thambaru/bal_orm.orm;
import ballerina/time;

public enum UserStatus {
    ACTIVE,
    INACTIVE,
    SUSPENDED
}

public enum PostStatus {
    DRAFT,
    PUBLISHED,
    ARCHIVED
}

@orm:Entity {tableName: "users"}
@orm:Index {columns: ["email"], unique: true}
@orm:Index {columns: ["status", "createdAt"]}
public type User record {|
    @orm:Id @orm:AutoIncrement
    int id;

    @orm:Column {length: 255, nullable: false}
    string email;

    @orm:Column {length: 100}
    string name;

    @orm:Column {length: 20}
    UserStatus status = ACTIVE;

    @orm:CreatedAt
    time:Utc createdAt;

    @orm:UpdatedAt
    time:Utc updatedAt;

    @orm:Relation {'type: orm:ONE_TO_MANY}
    Post[]? posts;

    @orm:Relation {'type: orm:ONE_TO_ONE}
    UserProfile? profile;
|};

@orm:Entity {tableName: "user_profiles"}
public type UserProfile record {|
    @orm:Id @orm:AutoIncrement
    int id;

    @orm:Column {nullable: false}
    int userId;

    @orm:Column {'type: "TEXT"}
    string? bio;

    @orm:Column {length: 255}
    string? website;

    @orm:Column {length: 100}
    string? location;

    @orm:Relation {
        'type: orm:ONE_TO_ONE,
        references: ["id"],
        foreignKey: ["userId"]
    }
    User? user;
|};

@orm:Entity {tableName: "posts"}
@orm:Index {columns: ["authorId"]}
@orm:Index {columns: ["status", "publishedAt"]}
public type Post record {|
    @orm:Id @orm:AutoIncrement
    int id;

    @orm:Column {length: 500, nullable: false}
    string title;

    @orm:Column {length: 1000}
    string? excerpt;

    @orm:Column {'type: "TEXT", nullable: false}
    string content;

    @orm:Column {length: 20}
    PostStatus status = DRAFT;

    @orm:Column {nullable: false}
    int authorId;

    time:Utc? publishedAt;

    @orm:CreatedAt
    time:Utc createdAt;

    @orm:UpdatedAt
    time:Utc updatedAt;

    @orm:Relation {
        'type: orm:MANY_TO_ONE,
        references: ["id"],
        foreignKey: ["authorId"]
    }
    User? author;

    @orm:Relation {
        'type: orm:MANY_TO_MANY,
        joinTable: "post_categories"
    }
    Category[]? categories;

    @orm:Relation {'type: orm:ONE_TO_MANY}
    Comment[]? comments;
|};

@orm:Entity {tableName: "categories"}
@orm:Index {columns: ["slug"], unique: true}
public type Category record {|
    @orm:Id @orm:AutoIncrement
    int id;

    @orm:Column {length: 100, unique: true, nullable: false}
    string name;

    @orm:Column {length: 150, unique: true, nullable: false}
    string slug;

    @orm:Column {'type: "TEXT"}
    string? description;

    @orm:CreatedAt
    time:Utc createdAt;
|};

@orm:Entity {tableName: "comments"}
@orm:Index {columns: ["postId"]}
@orm:Index {columns: ["authorId"]}
public type Comment record {|
    @orm:Id @orm:AutoIncrement
    int id;

    @orm:Column {'type: "TEXT", nullable: false}
    string content;

    @orm:Column {nullable: false}
    int postId;

    @orm:Column {nullable: false}
    int authorId;

    @orm:CreatedAt
    time:Utc createdAt;

    @orm:UpdatedAt
    time:Utc updatedAt;
|};

Apply DDL

Use the SQL files from the repository:

examples/complete_blog_example.sql (MySQL)
examples/complete_blog_example_postgres.sql (PostgreSQL)

MySQL (excerpt)

sql

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
);

PostgreSQL (excerpt)

sql

CREATE TABLE IF NOT EXISTS "users" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL,
  "name" VARCHAR(100),
  "status" VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);

Initialize orm:Client

ballerina

orm:Client db = check new ({
    provider: orm:MYSQL,
    host: "localhost",
    port: 3306,
    user: "root",
    password: "password",
    database: "blog_app"
});

// Or URL style (PostgreSQL)
orm:Client pg = check new ({
    url: "postgresql://postgres:password@localhost:5432/blog_app"
});

Reset database for repeatable runs

ballerina

_ = check db.rawExecute(
    "TRUNCATE TABLE post_categories, comments, posts, user_profiles, categories, users RESTART IDENTITY CASCADE"
);

Next step

Proceed to Part 2: Users & Profiles.