Generate PostgreSQL DDL Programmatically

This example shows you how to generate SQL data definition statements programmatically for PostgreSQL. You can create database tables and define foreign key relationships using CreateTable and CreateForeignKey to simplifying schema generation on-the-fly. This feature frees you from string concatenation and ensures error-free DDL query construction.

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import com.northconcepts.datapipeline.jdbc.sql.SqlPart;
import com.northconcepts.datapipeline.sql.postgresql.*;

public class GeneratePostgreSqlDdlProgrammatically {
    public static void main(String[] args) {
        SqlPart createUsersTable = new CreateTable("users")
                .addColumn(new CreateTableColumn("id", TableColumnType.SERIAL).setPrimaryKey(true))
                .addColumn(new CreateTableColumn("name", TableColumnType.VARCHAR).setNullable(false).setLength(100))
                .addColumn(new CreateTableColumn("email", TableColumnType.VARCHAR).setUnique(true).setLength(50))
                .addColumn(new CreateTableColumn("address_id", TableColumnType.INT))
                .setPretty(true);

        SqlPart createAddressTable = new CreateTable("address")
                .addColumn(new CreateTableColumn("id", TableColumnType.SERIAL).setPrimaryKey(true))
                .addColumn(new CreateTableColumn("address", TableColumnType.VARCHAR).setLength(255))
                .addColumn(new CreateTableColumn("city", TableColumnType.VARCHAR).setLength(50))
                .addColumn(new CreateTableColumn("state", TableColumnType.VARCHAR).setLength(50))
                .addColumn(new CreateTableColumn("country", TableColumnType.VARCHAR).setLength(3))
                .setPretty(true);

        SqlPart addForeignKey = new CreateForeignKey("fk_users_address", "address", "users")
                .setForeignKeyColumnNames("address_id")
                .setPrimaryKeyColumnNames("id")
                .setOnDeleteAction(ForeignKeyAction.CASCADE)
                .setPretty(true);

        SqlPart addIndex = new CreateIndex("ix_users_name", "users", "name");

        System.out.println(createUsersTable.getSqlFragment());
        System.out.println(createAddressTable.getSqlFragment());
        System.out.println(addForeignKey.getSqlFragment());
        System.out.println(addIndex.getSqlFragment());
    }
}

Code Walkthrough

1. CreateTable instance is used to create new tables users and address.
2. The columns are added via CreateTableColumn objects within addColumn() method. The corresponding constraints such as nullable, length, and unique are also specified in those objects.
3. Next, foreign key constraints are written using CreateForeignKey instance.
4. Then, an index is added to name column in the users table.
5. getSqlFragment() retrieves the SQL fragment of the SqlPart objects, which represent the generated query. Each query is printed on the console one by one.

Console Output

CREATE TABLE IF NOT EXISTS "users" (
  "id" serial,
  "name" varchar (100) NOT NULL,
  "email" varchar (50) UNIQUE,
  "address_id" int,
  PRIMARY KEY ("id")
)
CREATE TABLE IF NOT EXISTS "address" (
  "id" serial,
  "address" varchar (255),
  "city" varchar (50),
  "state" varchar (50),
  "country" varchar (3),
  PRIMARY KEY ("id")
)
ALTER TABLE IF EXISTS "users"
  ADD CONSTRAINT "fk_users_address" FOREIGN KEY ("address_id") 
  REFERENCES "address" ("id") 
  ON DELETE CASCADE
CREATE INDEX IF NOT EXISTS "ix_users_name" ON "users" ("name")