> If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...)
I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.
Sure, though the two obvious problems are solved easily: make developers "reserve" a number ahead of time, and keep a list of applied revisions in the DB table.
I was on a team doing this 15 years ago, and we did some pretty hardcore DB migrations on a large, complex database without any fuss with >30 developers working on it at the same time.
One thought I had is to put the schema in a flat file and put the migration on the commits (with mechanical checking that the two agree). I may've prototyped it at some point, I don't recall - I'm pretty sure I never used it in anger.
I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.