Inside your server folder, create a new Python file named create_database.py and add the following code:

import sqlite3

database = sqlite3.connect("chat.db")
cursor = database.cursor()

create_users_sql = "CREATE TABLE users (username TEXT, real_name TEXT)"
cursor.execute(create_users_sql)

database.commit()
database.close()

After importing the sqlite3 module, we connect to a database named chat.db. This database will become a file inside our server folder once the code has run.

We use this connection to receive a cursor, which is able to perform SQL queries over a database.

The SQL query to create a table inside our database comes next. It is customary to write the keywords of SQL in capital letters to easily distinguish your databases, tables, and columns from the rest of the query. We create a table named users, which will hold two columns—username and real_name.

We can then pass this query to our cursor and use its execute method to carry out the query. This will create our table inside the database.

SQL relies on things called transactions, which allow for a user to roll back any updates which they may not want to keep. This is useful if a database is updated at one point in a script but, later on, an error occurs, which means we no longer want to keep that update.

In order to tell our transaction that we do indeed want to make it persist, we call the commit method. This makes our users table permanent.

Once we are done with a database connection, we can then close it to free up resources. This is achieved with the close method.

Run this file and your database should be created! You will see a file named chat.db has appeared in your server folder which holds the database and its users table.

Now that we have this prepared and ready, it's time to begin adding data to it.