Create a Bookmark Organizer For Your Website Using Flask, AWS, and MySQL
Build your very own link library
I like to read a lot of articles about a lot of different topics. As is common among tech people, I want to learn about more things than I have time for. As a result, I need to save articles somewhere to go back and read them when I have time.
In the morning, I usually scan Hacker News, my Medium Daily Digest, my Twitter feed, and possibly some Substack newsletters. I’ll read some articles and bookmark a lot of articles to read later. I used to use browser bookmarks but this got out of control quickly.
My most recent solution was to use the Obsidian Clipper browser extension which saves articles to a daily note in my Obsidian Vault. This works well since I can tag them with a topic and search for them later. My issue with this is that it’s difficult to see a summary view of articles and distinguish between read and unread. This is what motivated me to implement my solution.
This article assumes that the reader has some familiarity with Python, AWS, and SQL and will explain how to save URL bookmarks to your database using a bookmarklet. Specifically, the implementation uses Flask, AWS Elastic Beanstalk, and MySQL.
Step one is deploying a Flask application on Beanstalk. The AWS documentation is pretty good if you don’t have one already. Be warned though, installing the Elastic Beanstalk CLI can be a little tricky depending on what type of machine you’re using.
Once you have a working application and you’ve changed the basic routes and templates to meet your needs, you’ll want to set up the database. The first step here is to go to the Elastic Beanstalk dashboard and find the environment where your Flask application is running. On the side panel, click Configuration. Scroll to the bottom where it says “Database”.
To ensure that you (most likely) stay within the AWS-RDS free tier, select MySQL on a db.t2.micro instance with low availability. In the next step, you’ll need the database endpoint, username, and password. So take note of these. Also, while you’re here, you should go to the security group and allow inbound traffic from your IP so that you can connect to the database while running the server locally.
Now we’ll create the database and the table that will be used to store your bookmarks. You’ll need to install MySQL on your local machine. For me, this was done with.
brew install mysql-client
export PATH=”/usr/local/opt/mysql-client/bin:$PATH”
brew install mysqlOnce this is done, you can connect to your endpoint and create the database and table. After the first command, you’ll be prompted for your password.
mysql -h <YOUR-ENDPOINT> -u <USERNAME> -pCREATE DATABASE MyDB;
USE MyDB;
CREATE TABLE bookmarks (id INT NOT NULL PRIMARY KEY, url VARCHAR(64), category VARCHAR(40), is_read BOOL, created_date DATETIME);Now we’re ready to start working in an IDE!
The very first thing to do here is the last step in setting up your database. In the same directory as application.py and the .elasticbeanstalk directory, create a new directory called .ebextensions. To make MySQL work on your elastic compute instance, create a new file called 01_packages.config and add the following:
packages:
yum:
python3-devel: []
mariadb-devel: []Okay, now we can start using Python. Activate your virtual environment and
pip install flask-mysqldbIn application.py, you’ll need to have something like this:
from flask_mysqldb import MySQL
# Create Flask Application
application = Flask(__name__)
application.secret_key = dotenv_values(‘.env’)[‘APP_SECRET’]
# initialize the db
application.config['MYSQL_HOST'] = dotenv_values('.env')['DB_ENDPOINT']
application.config['MYSQL_USER'] = dotenv_values('.env')['DB_USERNAME']
application.config['MYSQL_PASSWORD'] = dotenv_values('.env')['DB_PASSWORD']
application.config['MYSQL_DB'] = 'MyDB'
db = MySQL(application)
import source.routesI like to keep routes.py as clean as possible, so let’s start with bookmarks.py where we’ll define a Bookmark class and some functions for managing your bookmarks.
The Bookmark class takes a URL and category when initialized, assigns the current date-time to created_date, and assumes that you have not already read the article. I’m not currently using the category method. I’m planning on playing around with some machine learning models to see how well they can tag or categorize the articles that I feed it.
The Bookmark class also has a method for inserting the object into the database. When you view the bookmarks on your website, there will be a checkbox.
If you check a box and submit the form, the update_read_bookmarks() function updates the database.
The get_all_unread_bookmarks() function pulls the data that we want to display on bookmarks.html.
from application import db
import datetime
class Bookmark():
def __init__(self, url, category) -> None:
self.url = url
self.category = category
self.created_date = datetime.datetime.now()
self.is_read = False
def insert(self):
query = f"""INSERT INTO bookmarks (url, category, created_date, is_read)
VALUES ('{self.url}', '{self.category}', '{self.created_date}', {self.is_read});"""
cursor = db.connection.cursor()
cursor.execute(query)
db.connection.commit()
cursor.close()
def update_read_bookmarks(bookmarks_to_update):
query = f"""UPDATE bookmarks
SET is_read = TRUE
WHERE id IN ({bookmarks_to_update});"""
cursor = db.connection.cursor()
cursor.execute(query)
db.connection.commit()
cursor.close()
def get_all_unread_bookmarks():
query = """SELECT * FROM bookmarks
WHERE is_read = False
ORDER BY created_date DESC;"""
cursor = db.connection.cursor()
cursor.execute(query)
db.connection.commit()
bookmarks = cursor.fetchall()
cursor.close()
return bookmarksOk, so now all of the pieces are in place to create the routes and get things running!
In routes.py, you’ll have something like the one below. The bookmark route is what gets used in the bookmarklet, which is why it looks for a suffix that looks like: ?url=<URL TO BOOKMARK>. When you use the bookmarklet to bookmark a URL, it will bring you to The /bookmark/show route, which you might want to link to from index.html.
All it does is query the database for unread bookmarks and display them in a table. The /update_bookmarks route is the action associated with the form on bookmarks.html and will look for boxes that have been checked so that the database gets updated properly.
from application import db
from source.bookmarks import Bookmark, get_all_unread_bookmarks, update_read_bookmarks
@application.route("/bookmark", methods=["GET", "POST"])
def bookmark():
print(request.args)
if token := request.args.get("token"):
print("Found the token")
if token == dotenv_values(".env")["BOOKMARK_TOKEN"]:
if url := request.args.get("url"):
bookmark = Bookmark(url=url, category="self")
bookmark.insert()
return redirect(url)
else:
print("The token was wrong")
return welcome()
else:
print("Didnt find the token")
return welcome()
return bookmark_show()
@application.route("/bookmark/show", methods=["GET", "POST"])
@login_required
def bookmark_show():
bookmarks = get_all_unread_bookmarks()
return render_template("bookmarks.html", data=bookmarks)
@application.route("/update_bookmarks", methods=["GET", "POST"])
@login_required
def update_bookmarks():
new_read_bookmarks = [str(bookmark) for bookmark in request.form]
bookmarks_for_query = ",".join(new_read_bookmarks)
update_read_bookmarks(bookmarks_for_query)
bookmarks = get_all_unread_bookmarks()
return render_template("bookmarks.html", data=bookmarks)I’m using Flask-Login to ensure that I’m the only one who can interact with the database. For now, I’m the only one that can even view the bookmarks, but eventually, I’ll open that up so that other people can see my “link library”.
To be honest, I found the documentation and tutorials on using Flask-Login with MySQL pretty lacking, so I’m going to write another article on how to use Flask-Login with encrypted passwords on a MySQL database.
To make sure that I’m the only one that can add a bookmark, I have a token as an environment variable. I have that token in the request that comes from the bookmarklet; if they don’t match, the bookmark doesn’t get added.
The final step is to make bookmarks.html. I might be too utilitarian for my own good, but mine just looks like this:
<html>
<body>
<form action="/update_bookmarks" method="post">
<input type="submit">
<table border="1">
<tr border="1">
<th>URL</th>
<th>Category</th>
<th>Created Date</th>
<th>Read</th>
</tr>
{% for item in data %}
<br>
<tr border="1">
<td border="1">
<a href="{{item[1]}}">
{{item[1]}}
</a>
</td>
<td border="1">{{item[2]}}</td>
<td border="1">{{item[4]}}</td>
{% if item[3] %}
<td><input type="checkbox" name="{{ item[0] }}" checked /> </td>
{% else %}
<td><input type="checkbox" name="{{ item[0] }}" /> </td>
{% endif %}
</tr>
{% endfor %}
</table>
</form>
</body>
</html>The last thing you’ll need is the bookmarklet which is this:
javascript:location.href=’http://127.0.0.1:5000/bookmark?url='+location.href+'&token=<TOKEN>';Once you’re done testing, use your domain instead of localhost and you’ll be all done!
I have big plans for this app so if you’re interested, follow me to read soon about the next steps or connect with me on LinkedIn!
- Give the app a phone number so that I can add bookmarks via text message.
- Use Machine Learning and Natural Language Processing to extract keywords and categorize articles.
- Add search, filtering, pagination, etc.
