As an avid Rails developer, using SQL has become something I take for granted. Thanks to the ORM ActiveRecord library, I haven’t had to physically write out a SQL statement in a while. That being said, today’s article will be a refresher on the basics of SQL: installation, databases, tables, and queries.
SQLite is a great place to start with running SQL commands. You can find the download page here. If you have a Mac running OSX version 10.4 or greater, you may already have SQLite installed by default. Open a terminal and run the command
which sqlite3 and if the return output is
/user/bin/sqlite3, then you’re good to go. If you have homebrew, just run this combined command:
brew install sqlite
Install From Binary
Create A Database
In your terminal, navigate to the working directory of your choice. For the sake of this article, we’ll call this database
medium.db. So in order to create that database, we run the command
sqlite3 medium.db. Your terminal should then output something like this:
SQLite version 3.28.0 2019-04-15 14:49:49Enter ".help" for usage hints.sqlite>
If you open up the working directory in your Finder or Files Explorer, you should now see the newly created database! I highly recommend checking out the
.help command, as it’ll show you everything SQLite is capable of. Additionally, there is a Database Browser for SQLite that allows you to see your databases instead of relying on queries for output. It can also help you build queries to find what you need, so it’s definitely worth checking out!
SQL Data Types
The next step to working with SQL is creating a table. However, since creating a table requires different data types to be entered in the command, we can cover those now. SQLite specifically uses 4 data type categories:
- TEXT: literally it is just text. If you’re familiar with the string datatype in programming, then this is the same idea. However, if you aren’t, what you are reading right now is text.
- INTEGER: literally it is just a number. Anything that you would store as a simple number should be an integer. A good way to approach is that if you think it’ll need to be involved in something mathematically, storing it as an integer is the best option.
- REAL: refers to decimal numbers. In programming terms, it is better known as a float number. SQLite specifically stores decimals up to 15 characters long. After those 15 characters, the rest gets cut off.
- BLOB: used for holding binary data. It is used in very specific cases, such as in ActiveStorage with Rails. You definitely won’t be using it in this guide.
Note: These data types are listed as categories because there are other SQL database engines that use different specifiers, like INT instead of INTEGER. However, SQLite won’t stop you from defining a number column as INT in your database.
There are two different types of commands. There are commands that relate to SQLite as a whole, such as
.help . Running the
Create Table Command
In this section, we’ll be creating a table for our
Medium database to hold our articles. In the creation command, it’s common to have the commands in all capital letters while the names that follow will be in all lowercase instead. If that doesn’t make sense, then maybe seeing the command will help clear it up:
In this example, we are creating a table called
articles that will have three columns:
- id: this is the most important column. Any time you create a new table using SQL, you always want to begin with this column. If you’ve noticed, it’s an INTEGER datatype but it’s also the PRIMARY KEY. The reason this is done is because the
idof that entry may end of being the only unique way to track that entry. In the grand scheme of Medium as a whole, there are probably articles with the same title, so the
idis used to decide which is which. It’s the same idea as your Driver’s License number on your ID. There are other people in the world with your name somewhere out there, so the government will need to be able to track you all separately somehow right?
- title: The title of the article that we are storing as plain text.
- claps: The amount of claps an article has. This is the same idea as if you had a post with a certain amount of likes. Since these are simply being counted, they are best stored as integers.
Now that the table is created, you can run
.tables (which doesn’t need a semicolon
; if you tried already) to see it in your database! You can also run
.schema to see the raw SQL command that you used to create the table.
Alter Table Command
Now we have a table and have hopefully retained the ability to create more tables. But what happens if we forgot to add some information to a certain table? Instead of dropping the entire table and recreating it (which would be quite the hassle) we can simple alter the table to suit our needs.
articles table, we can see the article’s id, title, and amount of claps. But who wrote the article? What if the article is part of a publication? Sounds like we should add a way to to track that:
These two commands (run them separately) should have added two more columns to our table. If you run
.schema, it won’t show the alter statements, but instead will modify the create statement to include the new columns as if they were there all along. Neat!
When it comes to removing a table, however, it can get a little complicated. Here’s a link to the SQLite docs on the ALTER TABLE command.
Drop Table Command
If you want to get rid of your table altogether, just run
DROP TABLE articles; and you’ll be all good to go. If you got a chance to check out the link about ALTER TABLE, it’ll show you that when you need to get rid of a column in a table, you end up needing to create a new table and copying all of the old data, then drop the old table.
As obvious as it sounds, running DROP TABLE not only deletes the table but also all of the entries in that table.
Running Commands Using a Text Editor
Using the command line for SQL is great and all, but if you’re using a text editor, it’ll make your life so much easier, especially when things start to get more complicated. Before moving on to this section, run
DROP TABLE articles; as we will re-create it.
Whenever you run the command
sqlite3 medium.db, it runs as a create OR open. Since our database already exists, let’s create a file called
01_create_articles.sql. You’ll want this file in the same directory as the database file.
Now you can run the command
sqlite3 medium.db < 01_create_articles.sql and your OS will execute that command, adding the articles table back to the database. Run
.schema to check it out.
But wait, I totally forgot the
publication column. Well then we can easily do what we just did to add it back:
And there you have it! Run
sqlite3 medium.db < 02_add_publication_column_to_articles.sql and the column will have been added. I know the names of the files may seem a little overkill, but we are naming with the future in mind. If you have a bunch of different SQL files to execute, you’ll definitely want to be specific so you know where to go to make changes if need be.
Additionally, we prefix the file names with
02 to tell ourselves and other developers which files are supposed to be added first. It is also common convention to name the files in snake_case, which just uses underscores instead of spaces.
SQL can is a very technical language that requires very specific keywords to use. I figured covering the basics today would be enough for a decent start, so next week I’ll be covering queries, which will allow us to manipulate data entries into the table and show how to retrieve data from these tables. Thanks for reading, and happy coding!