SQL DSL Generator with Xtext/Xtend

📜 Overview:

The SQL DSL Generator is a Domain-Specific Language (DSL) implemented using Xtext and Xtend. It provides developers with a declarative way to define database structures and automatically generates platform-specific SQL code for MySQL, PostgreSQL, and Oracle.

With this tool, database schema design becomes easier, faster, and platform-independent.

✨ Features :

• Declarative Database Schema Definition ( Tables, Columns , Sequences, Triggers, Stored Procedures ...).
• Flexible Constraints and Data Types (primary keys, foreign kyes and unique constraints) .
• SQL Code Generation specific to the chosen database platform.
• Validation: Built-in validation to detect schema definition errors.

📂 Example Usage :

<- Input DSL: :

 	schema TestPostgreSQLDB
	config {
	    target PostgreSQL
	    charset 'UTF8'
	    version '5.7'
	    database {
	        dbname 'test_db'
	        host 'localhost'
	        port 3306
	        dbusername 'root'
	        dbpassword 'root'
	    	}
	}


table Users {
    id: INTEGER not_null auto_increment
    username: VARCHAR(50) not_null
    email: VARCHAR(100) not_null
    full_name: VARCHAR(200)
    age: INTEGER default '18'
    registration_date: DATE not_null
    is_active: BOOL default 'true'
    profile_json: TEXT
    
    primary_key(id)
    unique(email)
    unique(username)
}

-> Generated SQL Output (PostgreSQL):

DROP SCHEMA IF EXISTS "TestPostgreSQLDB" CASCADE;
CREATE SCHEMA "TestPostgreSQLDB";
SET search_path TO "TestPostgreSQLDB";

DROP TABLE IF EXISTS "Users" CASCADE;
CREATE TABLE "Users" (
    "id" SERIAL NOT NULL,
            "username" VARCHAR(50) NOT NULL,
            "email" VARCHAR(100) NOT NULL,
            "full_name" VARCHAR(200), 
            "age" INTEGER DEFAULT '18' ,
            "registration_date" DATE NOT NULL,
            "is_active" BOOLEAN DEFAULT 'true' ,
            "profile_json" TEXT 
        ,
        CONSTRAINT "PK_Users" PRIMARY KEY ("id"),
        CONSTRAINT "UQ_Users_email" 
        UNIQUE ("email"),
        CONSTRAINT "UQ_Users_username" 
        UNIQUE ("username")
);

🚀 Bonus extension :

To simplify common operations like adding auditing columns (e.g., created_at, created_by, updated_at, updated_by) while creating database tables, we introduced the pattern command. This feature reduces boilerplate code by automatically generating these audit columns in your table definitions.

📂 Example Usage :

<- Input DSL: :

schema TestMySQLDB
config {
    target MySQL
    charset 'UTF8'
    version '5.7'
    database {
        dbname 'test_db'
        host 'localhost'
        port 3306
        dbusername 'root'
        dbpassword 'root'
    }
}



table Books {
    id: INTEGER not_null auto_increment
    title: VARCHAR(255) not_null
    isbn: VARCHAR(20) not_null
    publication_date: DATE
    price: DECIMAL(10,2) not_null
    publisher: VARCHAR(100)
    total_pages: INTEGER
    is_available: BOOL default 'true'
    description: TEXT
    
    primary_key(id)
    unique(isbn)
}

table Orders {
    id: INTEGER not_null 		auto_increment
    book_id: INTEGER not_null
    quantity: INTEGER not_null default '1'
    total_price: DECIMAL(10,2) not_null
    status: VARCHAR(20) not_null
    order_date: TIMESTAMP not_null
    
primary_key(id)
foreign_key(book_id) references Books(id) on_delete CASCADE on_update RESTRICT

}

// pattern command to audit table Orders  

pattern OrderAudit {
    type audit
    apply to Orders
}

-> Generated SQL Output (MySQL):

	CREATE DATABASE IF NOT EXISTS TestMySQLDB 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;
USE TestMySQLDB;

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

DROP TABLE IF EXISTS Books;
        CREATE TABLE Books (
            id INT NOT NULL AUTO_INCREMENT,
            title VARCHAR(255) NOT NULL,
            isbn VARCHAR(20) NOT NULL,
            publication_date DATE, 
            price DECIMAL(10,2) NOT NULL,
            publisher VARCHAR(100), 
            total_pages INT, 
            is_available TINYINT(1) DEFAULT 'true' ,
            description LONGTEXT 
            ,
                PRIMARY KEY (id),
                CONSTRAINT UQ_Books_isbn 
                UNIQUE (isbn)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS Orders;
        CREATE TABLE Orders (
            id INT NOT NULL AUTO_INCREMENT,
            book_id INT NOT NULL,
            quantity INT NOT NULL DEFAULT '1' ,
            total_price DECIMAL(10,2) NOT NULL,
            status VARCHAR(20) NOT NULL,
            order_date TIMESTAMP NOT NULL,
            created_at TIMESTAMP, 
            created_by VARCHAR(100) DEFAULT ,
            updated_at TIMESTAMP, 
            updated_by VARCHAR(100) DEFAULT 
            ,
                PRIMARY KEY (id),
                CONSTRAINT FK_Orders_book_id 
                FOREIGN KEY (book_id) 
                REFERENCES Books (id)
                ON DELETE CASCADE
                ON UPDATE RESTRICT
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



SET FOREIGN_KEY_CHECKS=1;