GOUSH (Golang url shortener)

Routes

• GET / : show home page
• GET /links/create : Page create a link
• POST /links/create : endpoint to create a link
• GET /link/redirect/:shortlink : get long url
• DELETE /links/delete/:short_links : delete a shortlink
• GET /login : show login page
• POST /login : authenticate user
• GET /signup : get Signup page
• POST /signup : create user

Models

table short_url { Title string, url string, shortlink, expiration date, // optional }

Future

• Can create,retrieve and delete links
• Users can create,retrieve and delete their own links
• Add admin panel to manage users
• Rewrite frontend in svelte or htmx or just improve the ui. Read up on it first
• Use ouath for user creation and authentication. Read up on it first
• Read up on use of qr codes
• Allow creation of custom urls
• Database migrations
• Write tests for the application
• Use CI/CD
• Use pico css/ tailwind css

docker commands

• docker exec -it mysql mysql -D goush -u root -p -> Login to mysql as root
• docker exec -it mysql mysql -D goush -u web -p -> Login to mysql as web

-- Table to store information about shortened URLs CREATE TABLE short_links ( id INTEGER NOT NULL PRIMARY KEY, short_code VARCHAR(7) NOT NULL, original_url TEXT NOT NULL, creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expiration_date TIMESTAMP, -- You might add this if you want URLs to expire hits INTEGER DEFAULT 0 -- Number of times the short URL has been accessed );

-- Table to store user accounts (optional, if you want to implement user-specific short URLs) CREATE TABLE Users ( user_id INTEGER PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL -- Store hashed passwords for security );

-- Table to store analytics or access logs (optional) CREATE TABLE AccessLogs ( log_id INTEGER PRIMARY KEY, url_id INTEGER, access_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, user_agent TEXT, -- Store information about the accessing user agent FOREIGN KEY (url_id) REFERENCES ShortenedURLs(id) );

-- Table to store information about long URLs CREATE TABLE LongUrls ( url_id INT AUTO_INCREMENT PRIMARY KEY, original_url VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- Table to store information about short URLs and their association with long URLs CREATE TABLE ShortUrls ( short_id INT AUTO_INCREMENT PRIMARY KEY, short_key VARCHAR(10) NOT NULL, url_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (url_id) REFERENCES LongUrls(url_id) );

The LongUrls table is used to store information about the original long URLs. It includes a unique identifier (url_id), the original URL (original_url), and the timestamp when the URL was created (created_at).

The ShortUrls table is used to store information about the short URLs generated by the URL shortener service. It includes a unique identifier for the short URL (short_id), the short key used in the short URL (short_key), the corresponding url_id linking it to the original URL in the LongUrls table, and the timestamp when the short URL was created (created_at). The url_id column is a foreign key referencing the url_id column in the LongUrls table, establishing a relationship between the long and short URLs.

You can customize the table structures based on your specific requirements. Additionally, consider adding features such as user accounts, click tracking, expiration dates, and analytics based on your service's needs.

Resources

• https://www.calhoun.io/updating-and-deleting-postgresql-records-using-gos-sql-package/
• https://www.sql-easy.com/learn/how-to-delete-a-row-in-sql/
• https://www.usegolang.com/
• https://medium.com/@thomasjay200/stop-using-integer-ids-in-your-database-5e5126a25dbe
• https://medium.com/@murali.suraparaju/postgres-uuid-as-primary-keys-f54a511045b6
• https://dev.to/adnanbabakan/implement-uuid-primary-key-in-laravel-and-its-benefits-55o3
• https://www.outsystems.com/forums/discussion/76850/what-are-the-pros-cons-of-uuid-vs-auto-increment-serial-for-the-primary-key/#
• https://dev.to/frederik_vl/why-you-should-never-use-an-uuid-as-the-primary-key-in-sql-databases-147b
• https://www.baeldung.com/uuid-vs-sequential-id-as-primary-key
• https://medium.com/@selieshjksofficial/leveraging-uuids-as-primary-keys-in-your-applications-85efcd0163bb
• https://learnsql.com/cookbook/how-to-create-a-table-with-a-foreign-key-in-sql/
• https://www.quora.com/Why-can-I-not-delete-or-update-a-parent-row-A-foreign-key-constraint-fails