Preparing Your SQLite Database

00:00 Preparing your SQLite Database.

00:05 Although you can reach your API endpoint to shorten the URL, nothing has happened in the back end yet. In this section, you’ll create your database, making it ready to connect to your code.

00:18 First store the code that you need for the database connection in a file named database.py.

00:27 First you import what’s needed from sqlalchemy

00:40 and get_settings from .config. SQLAlchemy uses create_engine to define your database engine. You can think of engine as the entry point to the database.

00:54 The first argument is a database URL, which you receive from db_url of the settings. You set check_same_thread to False because you’re working with a SQLite database. With this connection argument, SQLite allows more than one request at a time to communicate with the database.

01:16 You use sessionmaker to create the SessionLocal class. You’ll create a working database session when you instantiate SessionLocal later.

01:27 The declarative_base function returns a class that connects the database engine to the SQLAlchemy functionality of the models. You assign declarative_base() to Base. Base will be the class that the database model inherits from your models.py file. While database.py contains information about the database connection, the models.py file will describe the content of the database.

01:56 First, the required imports from SQLAlchemy are performed, along with the Base class you just created in database.py.

02:05 The code that you add to models.py looks similar to the code that you wrote in schemas.py. In schemas.py, you define what data your API expected from the client and the server. In models.py, you declare how your data should be stored in the database.

02:23 Here, you create a database model named URL. The URL model is a subclass of Base, imported from database.py.

02:32 It’s common to give your model a singlular name and your database tables plural names. That’s why you name your model URL and provide the special variable __tablename__. Next you define id as your database’s primary key. By setting the primary_key argument to True, you don’t need to provide a unique argument, as it defaults to True for primary keys.

02:58 The key and secret_key columns will also contain unique entries. The key field will contain the random string that will be part of the shortened URL. With secret_key, you can provide a secret key to the user to manage their shortened URL and see statistics. Here, you define the column target_url to store the URL strings for which your app provides shortened URLs.

03:25 It’s important that this column doesn’t have unique set to True. If you only accepted unique values for this database field, then you’d prevent different users from forwarding to the same URL.

03:36 Your app’s expected behavior is that any user can create a shortened URL for any target URL without knowing if such a forward already exists. So while you’ll provide a unique shortened URL, multiple shortened URLs may forward to the same address.

03:54 As you’ll see later, the is_active Boolean column will come in handy when users want to delete a shortened URL. Instead of giving the user the power to delete a database entry directly, you’ll make the entry inactive instead. That way, you’ll have a safety net for critical actions, and you can undo the deletion if it was triggered accidentally. Finally, you define the clicks column, which starts with zero. Later on in the course, you’ll increment this integer each time someone clicks the shortened link.

04:28 In the next section of the course, you’ll connect the application to the database.