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
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.
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)
Please visit the official documentation in the Read The Docs site
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
);