We just looked at the painful 12 step process. I have 2 tools that are going to make that easier. The first is SQLite Utils And this is a Python slash command line library from Simon Willison who runs the dataset project which is data analysis using SQLite. It's very cool. I encourage you to check it out.

But we're gonna look at this SQLite utils library that he has and we're specifically gonna look at transforming tables. So we're gonna run it from the command line and we're gonna transform the first name. We're gonna make it nullable where it is currently not nullable. Now this is gonna do a lot of that work for us, but unfortunately it is not going to do the step where, we inspect it to see what the index is, the triggers, and the views are. So we still have to do that step on our own.

When we get over to Lib SQL we don't have to do that step on our own. We can just alter the table. But this is using a fork of SQLite. And so that's a pretty that's a pretty big hurdle if you're not already using that fork. So let's first look at the SQLite utils library.

So switching over here, what we're gonna do is we're gonna do SQLite utils and we're gonna say transform. And if we just run that, you'll see we need a path and a table. So we will do database dot SQLite and then the table is users. And you can just do that. It's kind of a no op.

It is gonna rewrite the create table. It doesn't really do very much. You can pass the SQL flag to do a dry run. So instead of actually running the stuff, you can just do this and you'll see here is what it's actually going to do. So it's gonna create a table, users new with a hash.

Here are the columns. Then it's gonna insert. This is this is the process we looked at in the last video. Insert into the new table. Where is the select?

Here we go. Select from the old table, drop the old table, and rename the new table. So this is the exact process. It's that chunky middle part of the process. We haven't looked at the indexes and recreated the indexes.

So what we can do here is we can say let's do, not null false, for the first name column. And this is going to put out look at that. So first name has been changed to text instead of text not null. So this, if we ran this instead of just doing the dry run, this would, accomplish the purposes of modifying a column definition. We would then need to re add the indexes, the views and the triggers.

There are a lot of things that you can do here. You can change the type of the column, you can rename it, you can change the order of the column, the not null, the primary key, the default values, the foreign keys, all of that kind of stuff. So this will get you a long way. If you are familiar with Python you can use the Python library itself. And if you go down to transforming a table and do custom transformations, this is where you could, add the indexes, the views and the triggers because here is that huge caveat.

It does not automatically upgrade index views or triggers, which is a big big problem. Now if you are using Turso's fork of sequelite which is lib SQL, then you have a lot more freedom here. You can see instead of SQLite it is lib SQL and you can come down here and, you know, remove a foreign key constraint or you can, add some check values. So this makes it a lot a lot easier. This is still not a great feature or not a great, selling point of SQLite that this alter table is such a mess.

If you know of any better tools than SQLite Utils or Lib SQL that make altering SQLite easier and less, painful, please let me know. And I will include links to them down below. But until then, I would probably write some sort of automation around it for yourself. So if you're using a web framework, I would write that 12 step process into, your code such that you can alter table and have that kind of abstracted away or frankly use libsequel and terso.