How to evolve database design?

Questions / Help
1

How does one generally deal with evolving database design?

say my UsersTable has only three fields as of now - name , email, phone – but at later point I need to add Country of Residence also.

Present approach (from javascript experience) - write a migration and populate a default value, then allow user to change it.

Question :

  1. Ecto allows some help in above case?
  2. Is there a general database design principle that helps evolution of database easier?

I almost always find myself changing database during development and doing mix ecto.reset – but that can’t be replicated in production.

Edit : updated after dimitarvp’s comment.

2

I don’t have much to offer on the wider strategic issue on how to approach evolving databases. It doesn’t seem that different to me from evolving a code base in general. In most applications the database will be close to the ‘core’, so like core code in general perhaps you’d aim for it to move more conservatively than the outer layers. Eventually anyway.

On the narrower point - all relational databases I’ve used implement ALTER TABLE, so is there some particular reason why you’d drop the database when adding columns? In any case, yes, Ecto can help via the alter macro.

1 Like
3

Uhmmm, how come? :open_mouth:

4

Uh. That was pooorly written. My bad.

After migration, same cluster (machine) is there. Same database is there.

5

On dev, mix ecto.reset is snappier when I don’t have real users.

I see a pain in using ALTER TABLE.

I take that ALTER TABLE is one of the ways to go.
Just that I am scared of “accidentally” wiping out everything and get fired. :rofl:

6

Understood. I have used it in production on very large databases. Admittedly that was a long time ago, and I usually had a dba approval. Assuming you’re using one of the standard databases, I think you can assume it’s the most battle-tested part of your overall system. Of course it’s not something you’d do lightly nor without some pretty serious testing.

7

Yes, Ecto supports migrations: Ecto.Migration — Ecto SQL v3.7.1

This is the standard way to go; I’m not sure there are any benefits to manually handling schema changes.

1 Like
8

what is the problem with Alter Table? If you’re scared then do everything in the test database. After writing the migration run the migration in test DB and see what it does.

MIX_ENV=test mix ecto.migrate

9

Use migrations!
Here is a very good reference to help you write good migrations: Safe Ecto Migrations · Fly

This part of the article is particularly good:

It gives you straightforward information on how to do common migration things.

5 Likes
10

This is super helpful resource.

Thank you for bringing attention to it.

Also, I recognise migrations are inevitable. So, thanks everyone.