In the last video, you might have noticed that I used a special command called dot tables to list all of the tables. That's called a dot command. Very clever naming. There are a whole bunch of dot commands and I'll link to the documentation below that shows all of the dot commands. There there are a lot, but I'm gonna show you a few that I think are pretty important and can be really helpful for you.

Let's go ahead and reopen that test database and right away you see dot help. That is one of the dot commands and if you run dot help you see all of the rest of the dot commands. As you can see, there are a whole lot of them and we're not gonna cover them all, but we are gonna cover just a few. The other one that you've probably seen already is dot exit and that gets you out. But we wanna go back in.

One interesting one is you can run, shell commands. We can say clear. That's a good way to clear off the terminal if you're recording videos. Alright. I think one of the first things that's interesting.

Let's run select * from users so we can see what we're working with. I'm in the users table and now Steve is in the users table and we have, I guess, different spellings of cofounder. But here we go. We've got ID, name and what appears to be a bio but I don't have any headers here. First thing we can do is we can turn headers on.

If we turn headers on, now we have ID, name and bio. That's pretty good. If we do dot mode we'll see that we are in a mode called list. There are several different modes, of displaying your information. We'll throw them all up on screen here.

There are 14 of them. They are ASCII, box, csv, column, HTML, insert, JSON, line, list, markdown, quote, table, tabs, and TCL. TCL is the favored language of the creator of SQLite, drh. I think it's drichardhip. You'll see some TCL stuff littered around.

Let's change the mode to what do you wanna change it to? Let's change it to JSON and see what happens. Now if we run this, we get fully valid JSON that we can, of course, copy and paste, but you could also use this programmatically to feed into, some other system that you're working with. If we go ahead and clear this out and we hit mode again, you see that it is preserved. But if we were to exit out and come back in and hit mode again, you'll see it switches back to list.

This only remains for the current session. You can set up a dot SQLite RC file, that will have some of your preferences in there if you wanna if you wanna have these, persist over and over. Let's keep going on this mode. If we change mode to box and then we select out, you see we get this nice looking box here. We can change mode to markdown and we get a fully legit markdown table.

The problem I'm seeing here is it's it's wrapping on, in the middle of a word, which I don't super love. If we were to do let's do, mode box again and then we can do width. Let's say we're gonna do 12, we'll do 2, 10, and 30 and see what that looks like. So if we select now, you see well, that is closed. I mean, that got smaller, but it's still pretty bad.

There is a way, there's a way to fix this. We can say mode box and we can say wrap at 30 characters. I don't think that's gonna do what we want it to do, still wrapping in the middle. But we can add, word wrap. If we add word wrap, now it's gonna wrap on a word instead of just somewhere randomly in the middle.

I'm gonna increase this to 50 and turn on word wrap and then we will see, hey, that looks that looks a lot better. There are other modes, of course, like we talked about. You have mode CSV. This is great if you need to export all of your data into CSV. You can also do mode HTML.

If you select from users then, you get a classic html table sans actual table tags, you would need to add that yourself. But here is an html table. Why you would use this anymore? Gotta be honest, I don't really know, but at least you know it's there. So you can also do, mode csv separator and then add, it's probably this is a bad separator.

You would add something like a tab if you wanted to make a TSV. But you can add a separator of whatever you want and that will control what the separator is. I think it's probably better to turn headers back on and then that gives you, let's actually change the separator back to something normal. If we were to clear that out now and run it again, that looks like a pretty well formatted c s file. There are 2 more modes I want to show you before we move on.

One is, a generally applicable pretty useful one that wraps up several of the underlying, dot commands into something that's generally pleasing. Then the second is insert mode. The one that is generally pleasing is called cue box. If you set mode to cue box, you'll see that it actually set it to box wrap 60 word wrap off quote. If we run select * from users now, you see that that's pretty good.

I don't actually like word wrap off, but that's a that's a pretty good reasonable mode. The other mode that I wanna show you is insert. If we clear this out and we've switched to we've switched to mode insert and we do select star from users, there we have actual insert statements. This is really nice because you could do select * from users where id equals 1. If you were to spell it correctly, database altogether.

But this is a nice way to transfer some data out in SQL format. Continuing to work with the results of a query, I wanna show you a way that you can write these to a file. If we were to say mode is insert and then we can put the output as somefile. SQL. Now anything, instead of being written to standard out, anything is going to be written to that somefile.

We'll do select * from users. And nothing happened, but something did happen. We see we've got somefile. File dot SQL and if we cat that file we'll see there we go.

There are the 2, insert statements. That's pretty nice. Let's do one more thing. So if we were to open SQLite again and we'll set the mode to insert and the output to some file dot SQL and then we were to select star from users. Okay.

Select * from users where ID equals 1. Okay. Well, it's just gonna continue to write to that file. That might be that might be what you want. If you check that, now there are 3 in there, but I didn't actually want the second one to be written to the file.

Let's remove that and then let's actually remove that and then come back in here and we can do something slightly different. We can set the mode to insert and then instead instead of saying output, we can say once. One time. One time write to a file. So we'll have some file dot SQL, select star from users, select star from users where ID equals 1, and we're back to standard out.

This is a nice way this is a nice way to write the results of a single query out to a file and then come back to just working right here, on the command line instead of losing all your output to that file. Throughout the rest of the course, we're gonna look at a bunch of different dot commands. I'm not gonna attempt to cover them all here, but there are a few more I wanna show you. First, dot tables shows you all the tables. Very cool.

Dot schema shows you a SQL representation of the schema of the database. Now what do you think dot expert does? Well, surprisingly, select * from users where name equals aaron. Surprisingly, SQLite has an index suggest Then we and then we would search using that index. It has this built in expert mode, which is honestly, it's honestly pretty cool.

The final dot command is dot exit. That's not the final one, but that's the final one we're gonna talk about today. As I mentioned, there are a ton of these and we'll continue to look at them as we go.