Ash_sqlite generated migrations

Ash Forum Ash Questions / Help
,
1

Hey team

I’m building a new ash phoenix app with a sqlite3* backend (everything up-to-date, mix hex-outdated shows all green). Because I’m early in the development process my resources are changing a lot as I “explore the domain”.

I keep running into issues where the auto-generated migrations (i.e. mix ash_sqlite.generate_migrations) try and do things which would work fine in postgres but don’t work in sqlite3 (here’s one example, but I’ve run into a few different similar scenarios).

My current workflow is to

  1. sigh heavily
  2. delete all the migrations and resource_snapshots in the app
  3. mix ecto.reset
  4. start again with a new mix ash_sqlite.generate_migrations

It’s kindof cheating, but since I’m still early in the development (nothing deployed to prod yet) I can get away with it.

I can think of a few ways I could do things differently:

  1. modify the generated migrations to use the various workarounds required to make sqlite3 actually do the things (may require executeing a bunch of raw SQL in the migrations, but I’m ok with that)
  2. modify the generate_migrations task so that it automatically applies the workarounds (happy to contribute these upstream, too - but I’m not sure how robust the detection will be and I’m not sure you wanna go down the “apply hacky workarounds” path with ash_sqlite)
  3. never make these breaking changes to the tables, and just work around things in app code (might be necessary in an existing app which can’t tolerate the downtime, etc, but that’s not my situation - so I don’t want to go down this path)
  4. switch to postgres :wink:
  5. some other option I’m missing?

Anyway thanks heaps for all your work on Ash - it’s an amazing stack, and I don’t mean to complain. Just want to know the best way forward given my situation.

*why ash_sqlite? I’m working on several small “proof of concept” apps, none of which have “precious” data, and I love the ops simplicity of using sqlite. And although I haven’t heard much from the litefs crew recently I do like that idea (since my apps all on fly anyway). So I’d rather not take option #4, and given that `ash_sqlite_ is a thing I guess you want to provide the sqlite option.

2

We basically just copied the migration generator from ash_postgres into the other data layers knowing full well that they would need to be tweaked, potentially significantly. Your options are essentially

  1. don’t use the migration generator, and handwrite migrations (not in like an aggressive way I just want to point out that it is absolutely an option to use hand-written migrations)
  2. PRs welcome on improvements to the migration generator. Even if it’s just emitting warnings saying “hey, you have to write this bit by hand because we can’t make that change automatically”, or if it’s applying various known workarounds.
  3. write a script that will at least make your life a bit easier by deleting those files and regenerating :laughing:

No magic fixes unfortunately, it’s just a truth that the sqlite/mysql data layers haven’t received even half as much love as ash_postgres. Not because we don’t care about them, they are just much newer and don’t have champions for getting their DX up to snuff w/ what ash_postgres offers. At Alembic we have tons of projects shipping using ash_postgres and its far and above our most used data layer, so it gets the most attention from me (for better or for worse :smile: )

3

Hey mate thanks for the quick response. And I completely understand your (and Alembic’s) postgres-first approach. Hi from Canberra, btw :slight_smile:

I think my plan is to do #1, but I am open to submitting PRs as per #2 - especially the “emit warning, with advice on potential workaround” thing. I’m a vaguely-competent db guy, but not a guru (my expertise lies elsewhere in the stack). So I’m reluctant to put myself forward as a go-to for tricky db workarounds, but I guess I can see what works for me and then make suggestions to see if they’ll work for others. IOW I might not be the champion you’re looking for, but I can possibly help a bit.

And I’ve actually done #3 already :wink: So I may just keep trundling along with that approach until I’ve actually shipped something.

1 Like
4

What might be a great middle ground is if you could document in issues the various behaviors that bite you and the problems the caused. That way we can at minimum catalogue them :slight_smile:

5

Roger that, will do. I remember a while ago you recommended a single-file ash app thingy which was useful for creating reproducible errors. I’ll try and track that down.

6

Hi, I just want to add that this website has guides for some scenarios including " Using SQLite ALTER TABLE for other actions" (other than renaming) here: SQLite ALTER TABLE & How To Overcome Its Limitations

1 Like