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 of our Structural model example 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)
generator.generate()

The tables.sql file with the SQL statements will be generated in the <<current_directory>>/output folder and it will look as follows.

CREATE TABLE IF NOT EXISTS Book (
    Book_id SERIAL PRIMARY KEY,
    pages int,      
    release timestamp,      
    title varchar(100)
);

CREATE TABLE IF NOT EXISTS Author (
    Author_id SERIAL PRIMARY KEY,
    name varchar(100),      
    email varchar(100)
);

CREATE TABLE IF NOT EXISTS Library (
    Library_id SERIAL PRIMARY KEY,
    address varchar(100),      
    name varchar(100)
);

CREATE TABLE IF NOT EXISTS Author_Book (
    Author_id INT REFERENCES Author(Author_id),
    Book_id INT REFERENCES Book(Book_id),
    PRIMARY KEY (Author_id, Book_id)
);

ALTER TABLE Book
ADD COLUMN Library_id INT REFERENCES Library(Library_id);

If you want to use this generator for technology-specific SQL commands such as PostgreSQL, you can use the sql_dialects parameter in the SQLGenerator. Currently only postgres and mysql are valid values. For example:

from besser.generators.sql import SQLGenerator

generator: SQLGenerator = SQLGenerator(model=library_model, sql_dialects="postgres")
generator.generate()