Why would a JavaScript developer need to know about databases? Well, unless you are just creating static websites, you need to be able to access, manipulate, and edit data. So knowing a little about databases will come in real handy.

I’ll just go ahead and say right now, I’m focusing on SQL (structured query language). SQL and similar languages are used to interact with databases, which is why you’ll hear terms like “SQL-database.” SQL itself, however, is not a database.

I chose to focus on this language because it is very common and the concepts of it will carry over to different databases like MySQL or Postgres. If you’re interested in different types of databases, check out Seven Databases in Seven Weeks.

But, if you’re just interested in enough to get you started on an SQL-like language, you’ve come to the right place.

OK, so what will I use this for?

Let’s say you’ve been tasked with creating a computer system to track who all is onboard the new Death Star. Darth Vader has tasked you with doing this because he force-choked your boss – the guy who miscalculated the shields and left a small exhaust port vulnerable. You’ve been given a list of everyone who is allowed on the Death Star, and you need to devise a system that will check if someone is allowed on board or not. If they are allowed, they’ll be able to come on to the ship. If not – they’ll be blasted.

You want to do a good job for Vader so he doesn’t force-choke you. So you decide the first thing you need to do is put this whole list into a database. This allows you to easily update who is and isn’t allowed on board.

So, let’s create a database for this.

This can be done a few ways. If you are using a GUI tool, like Sequel Pro, there will be options in the menu that allow you to add a database. Most of what is covered here can be done through the GUI tool, but the commands listed below can be used in the command line once you’ve connected to your database server.

There will be times you’ll need to use these types of queries in your JavaScript code to test against items in the database, as well as edit and add items to the database, so it’s good to remember these commands for later.

create database death_star_personnel

Once you’ve created your database, you’ll need to connect to it. Once you’ve connected to your database, you’ll want to start with a table. Let’s build a table for all of the stormtroopers based onboard the Death Star.

create table stormtrooper(id integer, name text,
can_aim boolean default(false), is_alive boolean default(true));

Let’s look at a few things a little bit more closely. Notice the can_aim and is_alive columns are booleans, and to make things easier, I set defaults on them. Initially all stormtroopers are terrible aims, and they all start out alive. This way, I don’t have to type that every time.

But our database doesn’t do us much good without some data in it. Let’s go ahead and add some.

insert into stormtrooper (id, name)
values (1, 'bobby trooper'), (2, 'sally trooper'),
(3, 'tommy trooper'), (4, 'anita trooper'),
(5, 'edgar trooper'), (6, 'cathy trooper');

Oh no! You forgot to add the is_allowed column, to make sure people on the list are allowed on board. Better do that before Vader notices and force-chokes you.

alter table stormtrooper add column
is_allowed boolean default(true);

Now let’s take a look at what all you’ve done.

SELECT * from stormtrooper;

Look what all you’ve accomplished so far! You tell Vader how it’s going, and I’ll start on the next blog post. Good luck!

Note: Many thanks to Rob Sullivan, database extraordinare! Rob was so helpful with checking over this and helping me make sure these queries were exactly right. Thank you, sir!

Questions? Tweet me!