Now that you're an expert SQLite user and an expert in SQL, hopefully, we gotta figure out, well, all the rest of it. How do we do migrations and schema changes and multi tenancy? And that is what we're gonna cover in this module. The first thing we're gonna look at is altering a library at a library called SQLite utils that can help. But then I'm going to point you again to Terso because their fork of SQLite Lib SQL has better alter table support.

But this is a course about SQLite, and so I'm gonna teach you how to do it the vanilla way. And if you want the easy way out, check out terso because they make it a lot easier. Alright, let's get started with altering the table. We are gonna start here on the docs. So I'm at SQLite dotorg and I'm on the alter table docs.

And we're gonna look at this railroad diagram because frankly we just don't look at them enough and they're super cool. This is the extent of what the alter table command supports. You can see, boy, there's just not very much here. So alter table, optionally you can add the schema name, otherwise you can reroute around it and go straight to table name. So alter table, let's say users, and then there are four things that you can do.

You can rename the entire table to something new. You can rename a single column to something new. You can add a column and you can drop a column. That's it. Now, if you're coming from literally any other database in the world, you're thinking well, where do I change my column definition?

Where do I add and drop foreign keys? Bummer. You just can't. So this is one of the drawbacks of SQLite. I'm gonna be honest.

There are ways to work around it, but this is a drawback. This is a pain. Now, to their great credit, they're smart people. The creators of this are smart and there is a reason that you can't do that. Why alter table is such a problem for SQLite?

They know it's a problem. We know it's a problem. It is a trade off. So whenever you hear somebody say SQLite sucks, I don't know if that's true or not. SQLite has made trade offs and I can't fault them for enumerating what those trade offs are.

The reason that it is such a problem for sequel lite is the way that the, the schema is stored in the actual database. They actually store that create table statement and then it's parsed every time the database is opened and that helps preserve, say it with me, backwards compatibility. Everything they do is for backwards compatibility. So let's take a look at how we can rename columns, although that's not very interesting. And then I'll show you how to do the stuff you really want to do, which is change column definitions, add foreign keys and that sort of thing.

We're just gonna follow this railroad diagram straight through. We're gonna say alter table users and then we're gonna rename first name to something else. So let's do alter table users rename first name and then let's check the grammar. So this is this is why railroad diagrams are so helpful because it says clearly you have to put the to word there, which is helpful. Rename first name to first name 2.

So if we run that, it's going to run and then we can do select star from users and we get back first name 2. That's it. That's all you can really do. You can just rename it. So I'm gonna go ahead and rename it it back.

So instead of first name to, we're gonna rename it to first name. And let's drop this limit down to 5. There you go. So now we're back in a good spot. I'm unimpressed, frankly, that you can rename.

I kind of expected that. One caveat you have to watch out for with adding a column is it's always going to go to the end. That may bother you. There's a way to put it in the middle with this next tool that we're gonna look at, but when you add a column by default it's always going to go to the end. And that is so that SQLite doesn't have to go through and update the format of every single row.

So that's a good thing, frankly. That means it's going to be a lot faster because all it has to do is update the schema and not go and touch every single row. So many of these operations don't have to touch the rows at all and that makes it incredibly fast. If you are adding a new column that has, some sort of check constraint or is a generated column that references, other columns or or a generated column that that generates any sort of data. In fact, it has to touch every every row.

So, it's gonna have to go in and put a value in every row and so those are gonna be a lot slower. Now, how do we actually make the changes that we care about? Because this ain't it. Just renaming columns, not good enough. Well, the SQLite docs lay out a 12 step process.

Yes, 12 steps to alter a column definition or do anything else that you wanna do, like add a foreign key. It's insane, but I am going to teach you vanilla SQLite. And then on top of that I'm gonna show you a library and then on top of that maybe just use terso or their forklib SQL. But hey, you're here for the good stuff. I'm going to teach you the good stuff as painful as it might be.

We're gonna look at all 12 steps. The basic premise of this 12 step process is create a new table that's kinda like a shadow table of the one that you're trying to modify. Create a new table with all the changes you want to see in it. Then copy all of the data from the old table into that new table, then drop the old table and rename the new table. So that is the basic 4 step process, which is new table, copy data in, drop the old table, rename the new table.

Now, that does get a little bit expanded, when you actually start looking at the implementation, So let's talk about that. The real first step for the expanded twelve step process is to turn off foreign keys. So we're gonna set foreign keys equal to off. And if they're not on in the 1st place, this isn't a problem. But if they're not on in the 1st place, don't turn them on in step 12 because they were never on in the 1st place.

So this is for people who have foreign keys enabled. And remember, we're going to be dropping the existing first, we just turn them off, and then we begin a transaction. It's all or nothing, baby. We don't want this half committed because we're gonna be doing a bunch of silly things, including we gotta figure out we gotta figure out the indexes and the foreign keys and everything that's associated with this table. So we're gonna select star.

Nope. We're gonna select type and sequel from sequelite schema where tbl name equals the name of the table that we're modifying. And this will show you everything that you're going to need to recreate it as it currently exists, which is which is pretty important. This is where it gets a little bit silly. You just have to, like, you just have to remember.

You just have to write it down or remember or come up with your own system and we'll fix this later. So that is step 3 or 4. I think that is step 3. So step 4 is to create the new table. So you're gonna create table users 2 or users new or users some, you know, some hash to make sure you don't have any collisions.

So you're gonna create the new table and in here, importantly, you have to put the structure of the new table. So whatever you're trying to alter, whether that's adding, adding a constraint or changing a column definition to not null or nullable, this is where you would do it. This is, this is step 4 and we're not actually gonna do that right now because there's just there's just so much there's a much better way. After that, you're going to insert the content from the old table into the new table and you can do that pretty easily. You can do insert into users 2, select star from users.

Now you might have to monkey around with this part right here depending on the definition of users 2. So if you change something from, being nullable to not nullable, you're gonna have problems here if you have nulls. Right? If you added a bunch of columns, you need to make sure that you're providing the right data. So normally this will work just fine.

This is just a straight copy, one for 1 from the old table to the new table. But I don't know what your new table looks like and so you need to make sure that this is going to work. The good news is we're we are inside a transaction and we're only messing around with users 2 at this point. We haven't really even done anything to users. So we're kind of double protected here.

We can just abort the transaction. Or if we if we do this with users 2 and we don't like it, we can just kind of drop users 2 and keep going. Like I said, I'm not going to do this right now because there's a much better way. Step 6, we're halfway there, is drop table users. So this is the old table.

You've you've already copied all the data out of it. This is the old table with the old schema. So you're gonna drop that table and then after that you're going to alter table, rename alter table users to rename to users. And so whatever you named that shadow table, that goes here. This is your new table.

And then you're putting it in place of where that table previously existed. And so now you have that users table back and we need to go ahead and start rebuilding everything. Step 8 is you have to recreate everything that we gleaned from that earlier step. So you're gonna recreate the indexes and you're gonna recreate the triggers. Step 9 is re create any views.

And now the view definition might need to be updated because your table definition has been updated. So step 8, indexes, triggers. Step 9, views. Step 10 is you can run pragma foreign key check. So if you had foreign keys on in the first place, now you've inserted all this new data into this new table and you can run a foreign key check before we close out the transaction just to make sure that all those foreign keys are still valid.

So that is step 10. Step 11 is commit the transaction. Everything is done now. And then finally, thank god, step 12 is you can turn on foreign keys again if you had them in the first place. What a terrible process.

This this is not a very good process, I will admit. We're gonna end it here because this is getting a little bit long, and we're gonna look at the library in the next video, and that makes this all a whole lot easier.