Ask HN: How do you iterate quickly on table schemas?

Just adding that I feel this as well and would love to know the same.

What's wrong with alter table? It's not like you have to manually re-enter rows. And this is during development, right?

Put reads behind a view, put writes behind a stored procedure.

Well first of all, I agree with a book called Databases Demystified that recommends you sort out your user interface before you make your database. To a programmer that might sound backwards, because isn't the database the foundation of your interface? The reason it works is that your user interface helps you decide what is important to store. There are so many facts you could store about your subject, and if you make your database first, you likely will choose the wrong facts or format them in the wrong way.

After that, I don't change my database much. But when I do, I prefer vi and psql:

  > vi tables.sql
  
  begin;

  drop schema stuff cascade;
  create schema stuff;
  set search_path to stuff;
  
  create table blah (
      id serial primary key,
      name text unique not null,
      kind int not null references kinds
         on update cascade,
      color int not null references colors
         on update cascade
      note text
  );

  rollback;

  > psql
  psql=> \i tables.sql  # yay, no errors
  psql=> \q
  > vi tables.sql  # change rollback to commit
  > psql
  psql=> \i tables.sql  # table created

Whenever I want to make a change, I use vi to edit tables.sql. Then I run psql and its \i command to run the stuff in the file --- which drops the schema and all its tables, and recreates everything again. I don't know if any other database treats schemas as Postgres does though, as folders for tables.

By the way, you only want to do this on your test database, while making it, before users start using it, because it drops all the tables and their data. After you've gone live, it's alter table, baby.

Even after go-live this technique is useful for idempotent commands like creating views and functions. But instead of the drop-schema-cascade command, you have to put commands at the top to drop each of the views or functions, usually in the opposite order you create them, in case there are any dependencies.

If you like GUIs or are new to SQL, this way is daunting. I think it is the happiest way long term, though.

ORM and auto migration files. This is super easy in Django for instance, just:

    python manage.py makemigrations
    (commit them)
    python manage.py migrate

https://docs.djangoproject.com/en/2.0/topics/migrations/

Never use an ORM that wants you to define your schema anywhere except the database. Never use NoSQL just to avoid writing SQL. (There are lots of valid reasons to use NoSQL, but that is not one of them).

First week or so: Change the DB as much as you like, directly, using the visual tools or create/alter statements as you see fit.

Once you're ready to do things right: Use change scripts. Save every create/alter statement, as well as data population and modification queries into a .sql file in a directory, named sensibly so that you can run them in order to exactly reproduce your schema at any point, on any machine.

Important to note: This is not hard. SQL is not hard. Avoid tooling written by people who think SQL is hard, that therefore build elaborate workflows that are in fact hard in an effort to shield you from having to touch the database.