No Time Dad

A blog about web development written by a busy dad

SQL Basics for Web Developers

The data I requested in my component isn’t coming out of thin air. It’s from a database somewhere. And lately I’ve been feeling like it’s more than a little helpful to know a bit more about that database and how it works. It’s more than likely an SQL database, so that’s where I’ll start.

Early in my web development career I didn’t think SQL was valuable for me to learn, especially since I relied heavily on ORMs (object relation mapper). This might still actually be the case, but I do think there’s value in knowing some SQL basics as a web developer.

For practicing SQL, I think sqlite is the best place to start. It’s easy to install and very forgiving. I can literally just delete the .db file it creates and start over if I make a mistake.

Installing sqlite

There are lots of tutorials on installing sqlite online, so I’m not going to dive too deep into it here. One in particular that I like is Digital Ocean’s ubuntu sqlite installation guide. The main goal is to get your system to a point where you can the following command from the command line without error:

sqlite3 --version

Which should return something like:

3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e1067

Creating a database

We’ll need to create a database before we can run a few basic commands. Creating a database with the sqlite3 cli is a breeze. For this, I’ll create and cd into a new directory for the database and some files that I’ll be making shortly.

mkdir db_stuff
cd db_stuff

Then I can run the sqlite3 cli command to create the new database, which also drops me into the sqlite3 shell. Pressing CTRL-D will exit the sqlite3 shell.

sqlite3 test.db

An important thing to note here is that the database file has not been created yet. And it won’t be created until I execute a command against the database, which I’ll do next.

Creating a table

I’ll be creating a simple table called User that contains a user_id, and name rows. The userId row will be of type integer, not allow null values, and also server as the primary key for the record. The name row will be of type varchar (a string) and have a length limit of 255 characters.

I’ll create a new file called create_table.sql with the following contents:

CREATE TABLE User (
  user_id INTEGER NOT NULL,
  name VARCHAR(255),
  PRIMARY KEY (user_id)
);

And create the table with the command below:

sqlite3 test.db < create_table.sql

Inserting a record

To create a new record, I’ll use the sql insert command. I’ll specify that I want the record added to the User table, and I’ll also specify the values to be added.

In a new file called insert.sql, I’ll add the statement shown below. Note that I’m not specifying a user_id value. Sqlite will generate this for me.

INSERT INTO User (name) VALUES ("notimedad");

And run the file via the command below:

sqlite3 test.db < insert.sql

Selecting a record

Now that I have a record in the database I can use the select command to query it. There are many different ways to do this, but I’ll focus on the simplest and least specific way here. Which is to select all of the records in the Users table.

In a new file called select.sql I’ll add the following:

SELECT * FROM User;

Running the command:

sqlite3 test.db < select.sql

Which should return something that looks like this:

1|notimedad

Updating a record

After inserting and selecting a record, the next logical thing to do is update a record. This can be done with an update statement that supplies the table name, the row to update, and the value to update it with.

I’ll create a new file called update.sql with the following contents:

UPDATE User SET name = 'nottimedad_updated' WHERE name = 'notimedad';

Running the command:

sqlite3 test.db < update.sql

I can then check my update by re-running the select.sql file I created previously:

sqlite3 test.db < select.sq

Which should return:

1|nottimedad_updated

Deleting a record

So, the last thing to do is to try deleting the record. This is done via a delete statement. Again, lots of a different ways to delete a record or many records, but in this case I just want to delete the record with a user_id of 1.

I’ll create a new file called delete.sql with the following contents:

DELETE FROM User WHERE user_id = 1;

Running the command:

sqlite3 test.db < delete.sql

And verifying the results with:

sqlite3 test.db < select.sql

Which should not return any values or return values not including the record with user_id = 1.

Conclusion

Now I’ve created a table and performed all of the basic create, read, update, and delete (CRUD) operations. Which is a great starting point for getting familiar with SQL basics.

Why use .sql files, though? Well, I think they’re easier to work with than the sqlite3 command line and less to type out and remember. They can also work for most other database systems like postgres or mariadb without too much trouble in most cases.