Figure out migrations and persistent schema

[sgrif]

These are both blockers in my mind. As with rails, the schema file should be auto generated, but I would like to have a usage story that doesn't require a DSL for every possible database feature. In my mind this was going to be Schema.toml, but I think this might just end up being structure.sql from Rails. The main benefit of schema.rb over structure.sql in the Rails world is that the former is often easier to solve merge conflicts for. This is not an issue for 0.1, or really 0.x, as it's easy to work around, but we need to figure out our story for this

[mcasper]

Do we plan on having the user maintain the database schema manually:

struct Database;

impl Database {
  fn initialize() {
    // lots of 'CREATE TABLE IF NOT EXISTS's
  }
}
fn main() {
  Database::initialize();

  // the rest of my program
}

which seems like it would lead to some sort of one off migration system for users tracking along with the database,

Or do we plan on having a construct to ensure you'll always get to the most up to date db schema whether you're tracking along or building for the first time?

And I agree, I also would love not to have a DSL heavy migration story.

[sgrif]

Definitely a construct to ensure users are up to date. My most basic vision is basically similar to Rails, with time-stamped files that end up being SQL files. I'm unsure as of yet whether it makes sense to provide a minimal Rust DSL for common cases. The main thing for me is that any API we do provide there is immutable, as we have a huge issue in Rails with old migrations changing inadvertently.

So I'm thinking we'll probably start with time-stamped SQL files and build on top of that if we feel it's needed. In all scenarios, I want to be able to switch arbitrarily between SQL files and Rust files, with the timestamp and naming convention being the thing that ties them together.

[sgrif]

One note is that my plan for timestamps. For those unfamiliar, in Rails we generally have two semi-magic columns called created_at and updated_at, which get automatically updated by Active Record when any persistence action takes place. I don't like having them handled by the ORM, and it leads to a lot of issues (and doesn't really fit with the design path this has been taking).

created_at is actually really easy, as it should never change, and just having it default to NOW() in the database is all it takes. updated_at can be trivially done with a trigger, and we can actually re-use the same function for all the triggers. So we'll probably end up automatically performing this when we set up a database for the first time:

CREATE FUNCTION diesel_set_updated_at() RETURNS trigger AS $$
BEGIN
    IF (NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at) THEN
        NEW.updated_at := current_timestamp;
    END IF;
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

Then for each table with timestamps, you can do:

CREATE TRIGGER my_table_set_updated_at BEFORE UPDATE
ON my_table FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

However, part of why I think a Rust DSL might make sense is that we can have add_timestamps_to(table) be an easy shorthand for adding both created_at and updated_at to a table with the proper defaults, and setting that trigger.

[cyplo]

Hello ! Just a drive-by on my side here, hope that helps more than introduces noise.

In general - a thumbs up for a idea of starting small, with just SQL files and a convention there.

My experience almost always was that the DB migration solutions converge on something close to plain SQL files. With some mechanism to order them and tell which DB instance had which script ran against it already. Sometimes there are also overrides in place, e.g. something like "in general launch this script, but if running on this super special instance, also run this other script in addition.". However, my experience is mostly in largish backend systems, so may not apply to the most common use case here.

Summarizing, if diesel would support some simplistic mechanism for just running bare SQL files as migrations I would be perfectly happy. A plus for a mechanism inside the ORM that would tell me, while connecting to the DB, that "hey, it seems that the newest migration I was able to find is this, but DB has this other one as the last applied."

As for taking this further, into a DSL land, I have mixed feelings here.
I think I would rather see an ability to generate raw SQL basing on the Rust types that are my model, and then those files can be just used a basis for an actual person to review them and commit as migrations.

thank you by the way, for diesel

have a great day everyone !

[martinth]

I will also leave an (unrequested) comment on this matter just because I find diesel pretty interesting :)

I'm mainly a Python developer and I really like the way alembic is doing it's migrations. Basically each migration is a Python file with an upgrade and downgrade function. Alongside each file stores it's own version identifier (based on a file hash) and the "down" revision which is the identifier of the migration that should run before that. On runtime, alembic inspects all migration files and figures out the migration path by following the "down" pointer to the root. One nice thing you get from this is the fact that you have a builtin collision detection. If two people add two migrations in different (vcs-)branches they will both have the same "down" revision, leading to a (migration-)branch which alembic detects and asks you to correct.

I'm not so sure on how you could port this idea to Rust, since this methods relies on things like dynamic importing and introspection.

[sgrif]

This has been resolved by #79 and b062ff1. At this time, SQL files are the only supported type of migration. Some of the plumbing for other forms is in place, however. I may revisit the possibility of a Rust DSL in the future. However, at the moment the only especially compelling benefit they would provide is being able to give you down for free. While there is value in that, the value gain going from no tooling to what will ship in 0.4 is much greater, so I'm taking the 80/20 solution for now.

[sgrif]

The needs of our own test suite after adding SQLite has pretty much sold me on the need for some kind of Rust support. I don't know that I want a full rails style DSL, but there's no way to represent id INTEGER PRIMARY KEY AUTOINCREMENT/id SERIAL PRIMARY KEY in a platform agnostic way, and I don't want to maintain two sets of migrations in our test suite just for that.

I still need to think it through further, but I'm thinking we can at least support files that look like this:

// timestamp_migration_name.rs
fn up<Conn: Connection>(&Conn) -> QueryResult<()> {
    // do stuff
}

fn down<Conn: Connection>(&Conn) -> QueryResult<()> {
    // Do the opposite stuff
}

I'm still unsure if it's worth doing a rails style "change" method, where you get down for free, but I'm interested in exploring at least being able to write migrations manually in Rust. I want to continue to support the SQL file version, even though those could universally be represented in Rust using Connection#execute (there's something to be said for editor assistance and syntax highlighting).

I'm going to spend some more time thinking about this after we finish 0.5, and probably open an issue once I've had time to collect my thoughts.

/cc @mfpiccolo @mcasper @samphippen

[cyplo]

Hi ! Thanks for sharing your thoughts openly !
Do you think it would be valuable to live with the pain for a while now ?
I.e. have just SQL migrations for both postgres and mysql for a while to see what patterns emerge there and then try to DSLize them and only them first ?

[sgrif]

Yes. We've DSLized a little bit in our actual tests, though the migrations are still raw SQL. I ended up not even putting what came out of it in the main diesel crate, as it was so far away from being suitable for general use. It's actually going to be a lot more complicated to make this work for real