4

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.

3

I can only tell you what we do now and what I have done in the past. Our shop uses MSTFS (Microsoft Team Foundation Server), so any version we have the ability to get the latest version of the script.

When we create the scripts we include the customary header - Description, Date, Author, Title, and sometimes Build number. This helps us to maintain code versioning and build numbers with database code.

We branch SQL code with functional code so as we integrate branches from developers, their SQL code comes with. When we moved to production, it all moved together.

In other positions, the data group worked with developers to create the database ERD. Then the data group came in as another branch to the project and create database objects. When the functional code was complete and the move was going to production, their branch was deployed to the data tier while the code was deployed to the app tier. The members of the DEVOPS group tied it all together.

There are many ways you can go. You just have to pick one. I suggest reading further on Application Lifecycle Management (ALM).

Typical ALM treats DB versioning like an afterthought if at all. Many companies treat the DB in the same fashion.

The answer is, it depends. I use a tool (MySQL Workbench, VS) to script my database object creation and I version the scripts based on build number. Typically it's YYYYMMDD#. Most DB tools are rudimentary so your mileage may vary. Try some different methods and find what works for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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