tl;dr: I know a few SQL semantics and I know how databases work (basically) but I never created one that had a considerable size. I lack of knowledge concerning workflow, SQL-code management and very basic SQL programming practices (write by hand or use e.g. pgAdminIII). I need some "instructions" on how to manage my source code and state of my database.
Okay, this sounds like a very basic question and in fact it is a very basic question.
Five years of computer science and I never really had to develop a database by myself and now I am here and have no idea how to do that. Now, as a software developer writing Java or C or whatever the workflow is pretty clear of course. Thinking about design, creating some files, write code, use version control and commit/push. Next day repeat.
My problem is that I can't get my head around that incremental nature of creating a database. What I mean is that I don't know how my SQL script should look like if I commit it to my repository and if I use e.g. something like FlywayDB.
At the beginning I'd have something like this e.g.
DROP TABLE IF EXISTS company_employee;
CREATE TABLE company_employee (
id BIGSERIAL PRIMARY KEY
);
and I need that DROP TABLE IF EXISTS because while I am developing I might change that table all the time and therefore drop it each time before I recreate it. But do I want to commit that script?
I mean if somebody accidentally runs it on my production server it would drop all my tables and re-create them. So one question I'm asking is e.g. how do my scripts look like that I am actually committing to my e.g. git repository?
Do I have different versions of that file? One that is e.g. a "development"-version and another is a "I am confident it doesn't break anything"-version that I commit?
Another option could be that I e.g. just create the tables in the first step:
-- V1__create-company-tables.sql
CREATE TABLE company (
id BIGSERIAL PRIMARY KEY
);
CREATE TABLE company_employee (
id BIGSERIAL PRIMARY KEY
);
and in a second file I add the foreign keys:
-- V2__adding-company-tables-foreign-keys.sql
ALTER TABLE company_employee
ADD COLUMN company_id BIGSERIAL,
ADD CONSTRAINT fk_company_employee_company FOREIGN KEY (company_id) REFERENCES company(id),
But that would mean that I'll never have all in one file - just fragments of my database in different files V1 toVn.
Another thing I could do is to just use pgAdminIII and add everything I need, then reverse engineer my database, run it with FlywayDB and commit that generated file to my database. But I don't like that idea since pgAdminIII uses deprecated stuff like WITH ( OIDS=FALSE ).
So well, I would be glad if someone could bring me on track somehow and give me a scratch of how the actual development process of a database looks like.
Should I use pgAdminIII or write my whole database by hand?
How do I version my .sql files? Where do I store them on my local machine?
pgAdminIII doesn't even seem to have something like a "Database Project" - it just offers access to modify my database but there is no source code management. I am really confused about how my setup would/should/could look like.
