I’d like to meet a woman with varied interests, curious about the world, comfortable expressing her likes and dislikes (I hate struggling to guess), delighting in her ability to fascinate a man and in being loved tenderly, who values joy, truth, beauty and justice more than “success”–so we can share bouts of intense, passionately kind awareness of each other, alternating with tolerant warmth while we’re absorbed in other aspects of life. - Richard M. Stallman, My former Personal Ad
Hi! I’m Jack, and I’m a nerd. I run a private channel, #bmachine, where about 200 hackers come together to chat and share ideas on machine programming and binary instructions. Nobody else knows who these hackers are, so I thought, Why not create an environment where they can get to know each other? Maybe even date, be friends, or team up for a startup if their interests align. So, I decided to create a database (a “db”) where they could search for each other and connect.
The goal of this database is simple: store basic info about each user—like their username, first name, last name, and interests—so they can easily find others. But here’s the problem: I know nothing about databases, so I called my friend Joe, who’s a web developer, to help me out.
After explaining my idea to Joe, he suggested I use SQLite, which is a database management system used by millions of developers—and it’s free! I decided to give it a go to store all the data.
I downloaded SQLite and set it up so users could access it via a specific URL. But here’s the big question: How do I actually put all the information in there, and how will others access it? So, I asked Joe:
“Hey Joe, I’ve installed SQLite and made it accessible to everyone. But I’m still not sure how to add data into it.”
Joe replied: “You need to create a database in SQLite first, then create a table. The table is where you’ll store your data.”
I said: “But why?”
Joe: “SQLite is a relational database. It stores information in tables. You can’t just create a table directly. A table exists inside a database, so you have to create the database first, then the table, and finally, you can insert the data into the table.”
I asked: “Okay, but how do I do that?”
Joe: “You’ll use SQL (Structured Query Language) to interact with the database. You’ll write queries. For example, if you want to insert data into a table, you’ll use the INSERT query. If you want to update something, you’ll use UPDATE. If you want to retrieve data, you’ll use SELECT.”
I was curious: “Wait, how many types of queries are there?”
Joe: “Technically, there are infinite ways you can write queries. But the fundamentals are simple. Once you understand those, you can work with any database—whether it’s PostgreSQL, MySQL, or MS SQL Server.”
I said: “I don’t completely get what you mean about working with any database. Let’s save that for another time, but it sounds good. By the way, I also need to explain to users how they can access the data. How should I do that?”
Joe suggested: “You could create a small guide for them. That way, they’ll know how to access the database without you having to manage it all the time. They’ll just need to write a few queries.”
I said: “Okay, I hope it’s as easy as you say!”
Joe reassured me: “Trust me, it’ll be easy for them.”
I asked: “So where do we begin?”
Joe said: “First, we need to create the database. In SQLite, creating a database is as simple as creating a file, such as db.sqlite3. It’s best practice to use the .sqlite3 extension. You can create the file with this command in the terminal:”
$ touch db.sqlite3
I said: “Okay, I ran that command, and it created a file called db.sqlite3.”
Joe replied: “Great! Now, open that file using the sqlite3 command to interact with the database. Run this command:”
$ sqlite3 db.sqlite3
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> I said: “Got it! I ran the command, and now I see the SQLite version, a help hint, and the prompt sqlite>. Looks like I’m in!”
Joe said: “Exactly! That’s the SQLite prompt. This is where you’ll write your SQL queries. Since it’s your first time, let me walk you through the setup.”
I said: “You’re very kind!”
Joe replied: “First, we need to create a table. You’ll store your users’ information in it, so we’ll create columns for username, first_name, last_name, and interests. Here’s the query you need to run:”
CREATE TABLE hackers (
username varchar(255),
first_name varchar(255),
last_name varchar(255),
interests varchar(255)
);
I said: “I ran the query, but I don’t fully understand what’s happening. I know we created a hackers table with the columns username, first_name, last_name, and interests, but what does varchar(255) mean?”
Joe replied: “Don’t worry too much about those details for now. Just focus on getting the database ready for users.”
I said: “Got it! So, we’ve created the database and the table. Are we ready to add the user information?”
Joe replied: “Absolutely! Just give me some user data, and I’ll help you insert it into the table.”
I said: “Sure, here’s some user data to get started:”
| username | first_name | last_name | interests |
|---|---|---|---|
| jack | jack | doe | cooking |
| k | kai | doe | painting |
| mi | mira | wi | scuba |
| m3 | mari | woo | cooking, singing |
| t0 | tuna | zee | painting |
Joe replied: “Now, let’s insert this data into the hackers table using the INSERT query. Here’s how to do it:”
INSERT INTO hackers (username, first_name, last_name, interests)
VALUES ("jack", "jack", "doe", "cooking");
INSERT INTO hackers (username, first_name, last_name, interests)
VALUES ("kai", "k", "doe", "painting");
INSERT INTO hackers (username, first_name, last_name, interests)
VALUES ("mira", "mi", "wi", "scuba");
INSERT INTO hackers (username, first_name, last_name, interests)
VALUES ("mari", "m3", "woo", "cooking, singing");
INSERT INTO hackers (username, first_name, last_name, interests)
VALUES ("t0", "tuna", "zee", "painting");
I said: “I ran the queries, and I understand the basic structure now! The data is entered in the same order as it appears in the parentheses, right?”
Joe smiled and replied: “Exactly. Let me explain the syntax more in-depth later. For now, you’re on the right track!”
I asked: “I have one last question before I head home—how can I see the information I just added to the hackers table?”
Joe replied: “Just run this SELECT query to see all the data in the hackers table:”
SELECT * from hackers;
I said: “I ran that query, and I can now see the data I added! Thanks so much! I need to head home now, but I’ll reach out if I run into any issues.”
Joe said: “No problem. Take care!”
Later that day, I posted the following note on #bmachine:
I’m creating a database to store details like username, first name, last name,
and interests for all the users in this channel. This will help you connect
with each other more easily. Please send me your username, first name, last name,
and interests via private message.
Thanks,
jack