[Tutor] How is database creation normally handled?

Chris Warrick kwpolska at gmail.com
Sun Sep 10 14:31:51 EDT 2017


On 10 September 2017 at 02:29, boB Stepp <robertvstepp at gmail.com> wrote:
> While reading about SQL, SQLite and the Python module sqlite3, it
> appears that I could (1) have a program check for the existence of the
> program's database, and if not found, create it, make the tables,
> etc.; or, (2) create the database separately and include it with the
> program.  What are the pros and cons of each approach?  (1) to me
> looks only helpful if I wish to write a program that might want to
> allow the user to have multiple databases.  But this sounds like a lot
> of extra coding and checking to make it work well.  But if I only wish
> to have a single database, then (2) sounds like the approach to use.
> I would create the database, populate it with the needed empty tables
> with the desired fields, making it ready to use by the program's user.
>
> Not having any experience in the database arena, I'm not even sure I
> know how to properly think about this.

A separate database creation script will be better. It’s good to
separate the logic of your app and configuration/setup — just like you
shouldn’t be “helpfully” installing dependencies when someone runs
your script.

For a good approach, look at Django: every app (sub-package of a site)
has its own set of migrations. Migrations are responsible for creating
tables, and more importantly — for updating them. Because you will
need to make changes to your original DB structure throughout the
lifetime of your software. I built a small Django app over the
weekend, and I’ve created 10 migrations throughout the process¹, and
that will probably be true of your project. Now, if those migrations
would have to go to the main codebase instead of a side directory, it
would be a burden to maintain. If there were no migration frameworks,
I’d have to include something like “run this SQL to upgrade your DB
when upgrading from version 0.1.0 to 0.1.1”, which is even less fun.

So, when I make some change to my models.py (which has the database
schema), I can just run:
./manage.py makemigrations
./manage.py migrate
and my database will be magically updated, hassle-free and I don’t
even have to look at the auto-generated code.

(If you are writing a web app: do look at Django! If you aren’t:
Alembic does the same thing for SQLAlchemy. Something for plain
sqlite3 may or may not exist.)

¹ Progressive enhancement: adding more features that need extra
columns I didn’t think of first. Or removing features that weren’t
cool. Or restoring them the next day.

-- 
Chris Warrick <https://chriswarrick.com/>
PGP: 5EAAEA16


More information about the Tutor mailing list