SQL Schema Generator

The SQL generator produces the code or set of SQL statements used to define and modify the structure of the tables in a database.

Let’s generate the SQL code for the Structural model example. You should create a SQLGenerator object, provide the Structural model, and use the generate method as follows:

from besser.generators.sql import SQLGenerator

generator: SQLGenerator = SQLGenerator(model=library_model, sql_dialect="sqlite")
generator.generate()

Parameters

• model: The input B-UML structural model.

• sql_dialect: The target SQL dialect for the generated statements (default: "sqlite").

• output_dir: Optional output directory (default: output/ in the current directory).

Supported Dialects

Dialect	Description
sqlite	Default. Generates SQLite-compatible DDL statements.
postgresql	PostgreSQL DDL with CREATE TYPE for enumerations.
mysql	MySQL DDL with ENUM() column types.
mssql	Microsoft SQL Server DDL.
mariadb	MariaDB DDL (similar to MySQL).
oracle	Oracle DDL with CHECK constraints for enumeration values.

The generator handles enumeration types differently depending on the dialect: PostgreSQL uses CREATE TYPE ... AS ENUM, MySQL/MariaDB use inline ENUM() column types, and Oracle uses CHECK constraints.

Output

The generated SQL script, tables_sqlite.sql, will be saved in the output/ folder inside your current working directory. You can customize the output directory by setting the output_dir parameter in the generator (see the API docs for details). The generated output for this example is shown below.

CREATE TABLE author (
	id INTEGER NOT NULL, 
	name VARCHAR(100) NOT NULL, 
	email VARCHAR(100) NOT NULL, 
	PRIMARY KEY (id)
)

;


CREATE TABLE library (
	id INTEGER NOT NULL, 
	name VARCHAR(100) NOT NULL, 
	address VARCHAR(100) NOT NULL, 
	PRIMARY KEY (id)
)

;


CREATE TABLE book (
	id INTEGER NOT NULL, 
	title VARCHAR(100) NOT NULL, 
	pages INTEGER NOT NULL, 
	release DATE NOT NULL, 
	"locatedIn_id" INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY("locatedIn_id") REFERENCES library (id)
)

;


CREATE TABLE book_author_assoc (
	"writtenBy" INTEGER NOT NULL, 
	publishes INTEGER NOT NULL, 
	PRIMARY KEY ("writtenBy", publishes), 
	FOREIGN KEY("writtenBy") REFERENCES author (id), 
	FOREIGN KEY(publishes) REFERENCES book (id)
)

;