Author(s): Renato Sanchez
Published on Fri Aug 08 2025
Summary: Creating a speed-first app with rust.
I started on web dev a few years ago, first in the front and I founded interesting the way of User Interfaces could get build with just code, but then a crash with the idea of a full stack project with frontend and backend, so I started to use NodeJS and React as my main stack. But more recently I was learning Rust, so why not build and document a new project with all middle steps and things, and learn good practices in project development and SQL. So let’s get started!
1. Define
Simple, a basic blog posts web application, login required, AstroJS frontend, Rust on the backend and a Postgres database. A teacher once told me “if you want to learn something, use it on the more basic way and then scale”, and that’s what I like to do, take things from the basics and step by step add more and more functionalities.
2. Design
First we need to define the flow and structure of the application, all the logic, user sessions, database scheme and user interfaces. In this part well explore and document the database design and implementation for this app.
Flow
The main requirements are:
- users can publish/delete/edit posts
- users can comment posts
- users can register/login/logout
- posts can contain tags (like twitter and so)
Database
As we know, almost all the actors in the system need an ID, so my first take is the users table:
CREATE TABLE users(
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
last_login TIMESTAMP NOT NULL
);
Then I could define the post table, this entity needs a foreign key from the users table because only a user can post in this context:
CREATE TABLE blog_posts(
id SERIAL PRIMARY KEY,
user_id INT, -- the foreign key
title VARCHAR(255) NOT NULL,
content VARCHAR(255) NOT NULL,
cover_img VARCHAR(255), -- cover image (optional)
created_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) -- foreign key ref
);
Now, comments are a serious thing, but this time we’ll take the shortest implementation of it, just a table with 2 foreign keys, one from the user how write the comment and other to refer the post that we are talking about.
CREATE TABLE comments(
id SERIAL PRIMARY KEY,
user_id INT,
blog_id INT,
content VARCHAR(255) NOT NULL,
is_edited BOOL NOT NULL,
created_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (blog_id) REFERENCES blog_posts(id)
);
Finally, how must we handle the tags section?, as we know, all the tags available must be introduced by the users, as they write blogs the will decide what tags are needed. If a tag isn’t available, they must have the option to add it. So we I take the following implementation using a many-to-many relationship.
CREATE TABLE tags(
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
-- here many-to-many rel gets done
CREATE TABLE blog_tags(
tag_id INT,
blog_id INT,
FOREIGN KEY (tag_id) REFERENCES tags(id),
FOREIGN KEY (blog_id) REFERENCES blog_posts(id)
);
This design let’s us to build a strong base to a future ready to grow project.
3. Execute
Database
One of my challenges in this project was learn about SQLx, an asynchronous SQL toolkit for Rust, so here is my steps to build the database using it. Install SQLx here and I will be following this tutorial
- Create a PostgreSQL database: I’m using a docker container to get it so those are the commands:
docker run --name blogs-app \
-e POSTGRES_PASSWORD=mypassword \
-e POSTGRES_USER=renato \
-e POSTGRES_DB=blogsdb \
-p 5432:5432 \
-v postgres-data:/var/lib/postgresql/data \
-d postgres
sudo docker start blogs-app
- Once the container is up, we can create a database in there with sqlx:
sqlx database create
We could check with DBeaver or other database manager:
![[Pasted image 20251106185848.png]]
3) Create the first SQL scripts
sqlx migrate add -r init
This will generate a subdir called migrations, within this dir we’ll find 2 empty files (timestamp-init.up.sql and timestamp-init.down.sql), those files will contain the SQL scripts, in the .up.sql file we’ll write the updates and if for some reason we need to rolleback to the last state of the tables we could write that code within the .down.sql}
With all the changes in those files we can execute the following command:
sqlx migrate run
and you should get something like this: Applied 20251107012409/migrate init (35.565762ms) and if you need to rolleback run sqlx migrate revert. And we verify that all is correctly on the database:
![[Pasted image 20251106202148.png]]
Conclusion
SQL is a very powerful query language and I found really interesting dive in this field, and I decided to write it down because I’ve been using for a long time ORM’s to communicate with databases, there’s nothing bad on using those tools but I didn’t even recognize a single line of SQL so I found really useful this practice. The next step is build the backend and logic (really interesting) and finally develop the frontend, find it in [[Blazzzing-Fast-Project-Part-Two]].
Links