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_dialects="sqlite")
generator.generate()

The model parameter specifies the input B-UML structural model, while the sql_dialects parameter specifies the target SQL dialect for the generated statements. In this example, we use sqlite, but you can also specify postgres, mysql, mssql, or mariadb.

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)
)

;