abe-winter / automigrate Goto Github PK

• am taking advantage of all-or-nothing mode in postgres? (double check this)
• probably need to use a transaction in sqlite instead

example failure:

psycopg2.errors.SyntaxError: syntax error at or near "bool"
LINE 6: alter table users drop column notify_email bool;

obvious why this is invalid sql and what the change generator is doing, but I think I've also seen drops emit correct sql.

• make sure this is covered in a test case
• see if the AST is being used correctly by the diff generator

also test rm enum (both for drop order and syntax error)

running down a possible bug in our parser library

for now, you should use timestamp instead of datetime columns

datetime will break diffs

Traceback

...
  File "/home/awinter/2019/automigrate/automig/lib/wrappers.py", line 64, in name
    assert isinstance(self.tokens[0], sqlparse.sql.Identifier)
AssertionError

• column order matters in select * case and, maybe, in certain dump formats
• currently automig adds column at end (i.e. no before / after)
• need to add it in correct position

• --opaque keyword
• 'update automigrate meta' query
• automigrate_meta columns: automig_version, from_sha, opaque
• use common ancestor correctly with ... (and make sure this works with named branches) -> using git rev-list, this is probably right
• parametrize every test with capitalization on and off
• docs

precedence should be:

• command line arg if present
• then shell var if present
• finally defaults

Currently it doesn't read the env vars at all.

seems like this is triggering drop / add

- create unique index tbl_x_y on levels(x, y);
+ create unique index tbl_x_y on levels (x, y);

and it shouldn't

• sqlite users shouldn't have to install psyco
• solution: move common code to actual common so automig_sqlite doesn't import migrate_pg
• traceback:

automig_sqlite --glob schema.sql init
Traceback (most recent call last):
  File ".../.direnv/python-3.7.5/bin/automig_sqlite", line 5, in <module>
    from automig.automig_sqlite import main
  File ".../.direnv/python-3.7.5/lib/python3.7/site-packages/automig/automig_sqlite.py", line 5, in <module>
    from .migrate_pg import create_parser, init, update
  File ".../.direnv/python-3.7.5/lib/python3.7/site-packages/automig/migrate_pg.py", line 5, in <module>
    import psycopg2 # pylint: disable=import-error
ModuleNotFoundError: No module named 'psycopg2'

Thanks for writing this. I came across your blog post and this repo from the changelog weekly email, and I'm finding it a really intriguing approach. I'm heavily in Django land, even mixing it up with SQLAlchemy in the same project (translating the Django models to SQLAlchemy with Aldjemy), which is all kinda painful, and missing some features that I'm really wanting to put in place (null-coalescing unique contraints on my tables is one particular headache), so this approach makes me wonder if it may be a better way to handle things.

Schema should be versioned using the same git shas as code so the logic is easy to detect if a deploy requires a migration

This philosophical requirements is what causes the issues with rebasing, even if the schema isn't changing or conflicting. I'm not sure I'm sold on it. I do see value in versioning based on a hash of the schema, and I think that's the right choice, though. So what might be the right hash, that didn't mess up rebasing, etc?

I'll consider it by way of a similar problem, and let you poke holes in that problem, or the parallel I wish to draw with this project. I want to build docker images from my code. I want to build those docker images for each commit in my master branch, as well as for my pull requests, which are my unit of review and testing before shipping it to production. I'd like to be able to merge from the GitHub web interface, which should kick off automated build, test, and deployment. And, critically, I'd like it to not rebuild the docker image if it doesn't need to.

Unfortunately, every GitHub option for merging will change commit sha from the one at the head of the PR branch, so using the commit hash doesn't solve the final requirement to not rebuild the image if it doesn't need to. The solution that I've come up with is to not use the commit hash, but to instead use the hash of the repository tree instead. This is still calculated by Git, is easy to get to with a git command, and is based solely on the content of the repository, not the content, author, or dates on the commit, since those just don't matter to what I'm doing. The downside to that is that it's significantly more annoying to determine, from the tree hash, which commit in the repo has that tree hash. Especially since its the commit that is easily available in the GitHub UI and most git commands.

While I don't love the downsides I've just mentioned, my current thinking is that the correctness of identification is worth the trade. Applied to this project, I'd expect that it might be the hash of the schema file or files instead of the git commit hash. If that schema is in just one file, then, like my approach of using the git tree hash, you could use git to give you the blob hash of that file at the current commit. Or if its the entire contents of a folder, a tree hash of that folder at the current commit would work.

My motivation here is based on my philosophy of using Git, which is fine with rebasing. It's not for every situation, and should be done with care, but rebasing is, fundamentally, editing a line of communication with users of your code via your history. It's important to keep that looking good, IMO, when it doesn't conflict with other important concerns a person's project, like tracking deployments and keeping things safe to share, etc.

You'd get a ton of users if somehow it were possible to npm install some kind of wrapper around automigrate that node.js applications could require(), then execute the migration diffs at startup with.

Right now with knex migrations (nowhere near as good as this, I do:

const db = await initDb()
const schema = await readSchema()
await migrateDatabase(db, schema)

all from within the application (that is containerized, etc.)

having automigrate as an external tool in another language will make the Dockerfiles kind of ugly. I guess the node index.js entrypoint could be replaced with a script that runs automigrate before node index.js

might be a pain for people doing vault temporary credentials, etc.

Support subdirs

Traceback

Traceback (most recent call last):
  File "***/.direnv/python-3.6.8/bin/automig", line 11, in <module>
    load_entry_point('automig', 'console_scripts', 'automig')()
  File "***/automigrate/automig/__main__.py", line 45, in main
    changes = ref_diff.ref_range_diff(git.Repo(), *rev_tuple, args.glob)
  File "***/.direnv/python-3.6.8/lib/python3.6/site-packages/git/repo/base.py", line 168, in __init__
    raise InvalidGitRepositoryError(epath)
git.exc.InvalidGitRepositoryError: ***

• sqlparse doesn't know about bool (supported on postgres)
• which confuses wrappers.Column.parse
• which is badly written, causing an infinite loop

This is what the library is doing: ugh.

[<Identifier 'x bool' at 0x7FDA4AF097D0>, <Keyword 'default' at 0x7FDA4AEA13D0>, <Keyword 'false' at 0x7FDA4AEA14B0>]