Sqlite db init and migrations

[Algebra8]

Hello @benbjohnson

I had a few questions regarding how you initialized your sqlite database and handle migrations.

Regarding the wtf/sqlite/migration/ directory, did you create this manually or was it auto-generated? How about the file(s) inside it? If you wanted to add a new migration, would you manually create a new file called 00000001.sql and include the new schema?

I would also like to verify with you that I've got the correct idea about how the migrations are being performed. It seems like you create a table called migrations (if it doesn't already exist), extract an embedded .sql file from the migrations directory if it doesn't exist in the migrations table, execute it, and then push it into the migrations table.

Would you consider this as sufficient for large projects as well?

I've been searching for db migrations (particularly in sqlite) for go and it's incredibly difficult to find anything that doesn't use golang-migrate. So, thank you very much for this resource :)

PS, what is your opinion regarding using golang-migrate? Are migrations in sqlite so easy/concise that it would be overkill to use it?

Best regards

[benbjohnson]

Regarding the wtf/sqlite/migration/ directory, did you create this manually or was it auto-generated? How about the file(s) inside it? If you wanted to add a new migration, would you manually create a new file called 00000001.sql and include the new schema?

Yes, I just manually write the SQL to do migrations. I would just add a new .sql file with the next sequence number.

I would also like to verify with you that I've got the correct idea about how the migrations are being performed. It seems like you create a table called migrations (if it doesn't already exist), extract an embedded .sql file from the migrations directory if it doesn't exist in the migrations table, execute it, and then push it into the migrations table.

Yep. That's correct. You also need to make sure you're executing the migrations in sorted order. One thing I would change is that I would use the PRAGMA user_version to set and retrieve the version instead of creating a migrations table.

https://www.sqlite.org/pragma.html#pragma_user_version

Would you consider this as sufficient for large projects as well?

It depends. You need to coordinate a little bit to make sure you don't have different people trying to edit the same version number. Another option that I think Rails does is to name the file based on a timestamp and track that in the migrations table. That way you minimize collisions.

PS, what is your opinion regarding using golang-migrate? Are migrations in sqlite so easy/concise that it would be overkill to use it?

I haven't used it but I generally don't like having additional tooling for migrations. I think migrations should be straightforward. I also don't do reverse migrations as I find them brittle.

[Algebra8]

That is all great to know. Thank you, again, so much for this resource! It's so useful and I really appreciate your feedback!