Where to create the database for the first time

One of the problems we may encounter when creating software is the dependency on data storage and the evolution that it may undergo, that is, the database on day 1 will have one schema, and on day N it will have a different schema.

For example, in the image, we can see that we start with a User with basic attributes, but as the software progresses, other attributes such as salt and phone are added because additional security layers have been introduced.

When the software evolves and, consequently, the database changes, we can use tools like Alembic to manage these migrations in such a way that we transition from version 1 to version 2, and then be able to rollback, in case of an error, from version 2 to version 1.

Now, when you evolve the application and add these migration scripts, it is common to proceed in one of the following ways:

Case 1

• Create an ORM for database creation and usage in your application.
• As it evolves, create migration scripts and update the ORM.

Case 2

• Create an SQL file with the initial state of your application for database creation.
• Create an ORM for database usage in your application.
• As it evolves, create migration scripts and update the ORM.

Case 3

• Create an SQL file with the initial state of your application for database creation.
• As it evolves, create migration scripts.

And then we could mention a last case that may not be (in my humble opinion) recommended because updating an old installation would be tedious.

Case 4 (SQL only)

• Create an SQL file with the initial state of your application and update it over time.

Case 4 (ORM only)

• Create an ORM with the initial state of your application and update it over time.

Taking this into account, let's keep in mind the rule (which I may have invented myself or perhaps read somewhere):

"All code needs maintenance or understanding (which might imply documentation that, in turn, would require maintenance)"

Therefore, from the options seen earlier, in my opinion, the best one would be the first, because migrations, in my humble opinion (IMHO), should always be considered. In this case, at least, you don't have to maintain a separate creation script file; you can incorporate it into your application's migration system. Also, you wouldn't have to generate ignores for Dockerfiles, etc., meaning it's cleaner for the repository. When using Alembic or the manager you use, if you use the ORM version for table creation, it should not matter which DBMS you are using.

As you can see, when creating an SQL script for table creation, it is not updated afterward, something that does happen with the ORM because it is necessary for the application to know the data model it is working with. That's why one of the conclusions I reach is that it's not a good idea to use the ORM for the initial database creation for applications that are going to grow or need that evolution.

With this in mind, we have two options left to create our tables initially:

• Use a script to create SQL data.
• Use the migration manager, in our case, Alembic.

In the first case, the ideal would be to create a folder called "db" at the same level as "src" with the initial state of the database and a script that takes the necessary environment variables to connect to the database and execute the script. It is advisable to use environment variables instead of parameters in the CLI to prevent passwords from being stored in the command history.

In the second case, you would need to create an initial migration that indicates the entire database schema. It is important to note that Alembic does not create a database, so in this case, you would need to leave a script in the repository to do that if you want to have that code ready, or have the administrator handle it.

In my opinion, considering that Alembic can execute SQL code directly, I believe it is better to have a single entry point for database management, and that should be Alembic. The flow would be as follows:

What is your opinion on this?