dbSketcher

Tool to Sketch a db (plantUML ERD diagram + sqlite3 base code) out of a simple CSV file.

Test it live!:
https://matteemol.eu.pythonanywhere.com

Why? and When?

When designing a database, you probably won't get to your final scheme on the first shot (if you do, then this program may be too basic for you).

While designing a db myself, I found it quite annoying to go back and forth with some attributes & tables, so dbSketcher was born.

How?

Very, very easy. Write a simple CSV file (e.g. "my_sketch.csv") with just 3 columns (no headers), having some sqlite declarations in mind:

table_name, attribute_name, sqlite declaration

Then run in the console:

python run.py my_sketch.csv

And voilà! You'll obtain your own my_sketch.uml and my_sketch.sql with the UML and SQL code respectively, to sketch your db and visualize it easily right away! (and with the .sql script you can generate the basic database to start working)

Documentation, help and Examples

Please visit the official documentation in the Read The Docs site

Basic example

Input

CSV file:

example3.csv

recipes, recipe_id, integer primary key
recipes, name, text not null
ingredients, ingredient_id, integer primary key
ingredients, ingredient, text not null
ingredients, recipe_id, integer foreign key (recipes)

Output

Terminal:

CSV to dict - Tables:
{'ingredients': [('ingredient_id', 'pk', 'INTEGER PRIMARY KEY'), ('ingredient', 'col', 'TEXT NOT NULL'), ('recipe_id', 'fk (recipes)', 'INTEGER')], 'recipes': [('recipe_id', 'pk', 'INTEGER PRIMARY KEY'), ('name', 'col', 'TEXT NOT NULL')]}

CSV to dict - Relationships (UML):
{'recipe_id': [('recipes', 'ingredients')]}

CSV to dict - Relationships (SQL):
{'ingredients': [('recipe_id', 'recipes')]}

SQL script:

example3.sql

CREATE TABLE IF NOT EXISTS ingredients (
ingredient_id INTEGER PRIMARY KEY,
ingredient TEXT NOT NULL,
recipe_id INTEGER,
FOREIGN KEY (recipe_id)
REFERENCES recipes (recipe_id)
ON UPDATE SET NULL
ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS recipes (
recipe_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);