3

What is good practice of settings up a database in potentially large project - creating tables and updating them - should it be done in code of the app or should it be done by external database related tools like phpmyadmin ? I mean I have two ways - create tables and set them up when the app starts, or I can do this stuff with phpmyadmin by hand independently of codebase.

2
  • 2
    They're both viable strategies, and it will depend on you or you company's culture, your technical knowledge, your deployment strategy, etc. Jan 7, 2019 at 16:26
  • I would not let any automatism in the code base to create and manage the DB or run changelogs. Overall if we are speaking of a single DB for the whole project. No to say if the project can run several instances on a distributed environment. To my experience, DB management and automatisms rarely get along well. Sooner than later you will have to operate the DB manually.
    – Laiv
    Jan 8, 2019 at 8:36

4 Answers 4

6

What you need is some form of deployment script which automaticaly sets up your database and/or updates it to the required version.

There are a number of tools that can help you do this, but the simplest way is just a collection of sql scripts and a batch file to check the version and execute them in order.

If your application uses a local db which should be deployed with the app. Its not unusual to have this script as part of the application itself and have the app create the db if it cant find it.

But for server deployments there is no need to link the deployment and the app.

2

Either approach can be fine. However, the DevOps approach would be to script database creation. Having it written down and automatically executable makes it

  • easier to understand,
  • possible to repeat (e.g. when setting up a new DB for a test environment), and
  • easier to modify.

Those modifications are crucial: what if the schema needs to change? Scripting all of these schema migrations makes the schema change testable – you can first try it in a staging environment. Deploying new versions with schema changes is now a lot less risky. You've also eliminated the possibility of manual errors: humans are not good at being perfect.

The database might be set up as part of your app, but that's not necessary. It's also perfectly fine to write separate tools as long as they are committed into the same version control system as the app.

For a battle-proven view on managing database schemas and other admin processes, see the final section of The Twelve-Factor App: https://12factor.net/admin-processes

2

This is a Separation of Duties / Least Privileges question - it's important to keep these functions separate, and much easier to do from the beginning than to try and retrofit it later on. The creation, update, and granting of objects is a DBA activity and should be done via a separate script - and done with as a separate user with different (elevated) privileges.

In the ideal case, your runtime user should not have privileges to create or modify objects at all. Those privileges can be easily abused by someone with access to the runtime account or via a SQL injection.

0

It depends how much control you want to have. If you have people who know SQL and databases well they may opt for the manual approach. If you have some people who only know PHP and have limited knowledge of databases and SQL, tooling may be the better option because the tooling will create the database tables, etc. so the team can concentrate on the coding in PHP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.