Always use parameterized queries or ORMs which make things easier for developers
Mention the importance of validation, both in client and server
Database Types Overview
When NOT to use relational databases?
Key-Value Stores (Redis): Caching, real-time features
Document Stores (MongoDB): Flexible schemas, JSON-like data
Graph Databases (Neo4j): Social networks, recommendation engines
Time-Series: IoT sensor data, financial metrics
Deployment Overview
Development vs Production: Understanding the Journey
Local vs Prod environment
[!NOTE] The diagram illustrates the difference between local development and production environments.
Why do we care about different environments?
We use them to test and develop our applications in a safe space without affecting real users or data.
They help us identify and fix issues before deployment.
A recap of what we mean when we say "the cloud"
A remote server (computer) running 24/7 somewhere else
Multiple users can connect simultaneously
Databases run on these servers
Your data needs to be accessible from anywhere
Why do we need to migrate to another database?
Aspect
SQLite (Dev)
PostgreSQL (Production)
Location
Local file
Remote server
Use case
Development, small apps
Production, real applications
Type of information
Testing, prototyping
Real user data, critical info
Scaling
Limited
Excellent
Demo time!
[!NOTE] Guide the trainees through the steps, explaining each part. You can choose to show it entirely and have them replicate later or to give them some time to follow along.
We're going to recreate our local SQLite database to a remote PostgreSQL database on Render.com
[!TIP] You can show the diagram again to illustrate how you are connecting to different databases based on environment.
Key Concepts to Emphasize
Environment Variables
Never hardcode database credentials
Different configs for dev/staging/production
.env file locally, Render UI for production
Always add .env to .gitignore
Migration Best Practices
Always backup before migrating
Monitor after deployment
Keep development and production schemas in sync
Alternative: Knex Migrations (Optional)
[!TIP] For production projects, you'll likely use migration tools like Knex.js to version-control your schema changes. This is beyond today's scope but worth exploring for your projects.
Questions to Explore
What happens if the remote database goes down?
How do we update the database after the first setup?
Can multiple developers work with the same production database?
Summary & Q&A
Key takeaways
SQL aggregates are your friends for calculations: reporting, dashboard stats
Security: Never trust user input, always validate.
-- Count overdue tasks
SELECT COUNT(*) AS overdue_count
FROM task
WHERE due_date < DATE('now');
-- Average tasks per user
SELECT AVG(task_count) AS average_tasks
FROM (
SELECT user_id, COUNT(*) AS task_count
FROM user_task
GROUP BY user_id
);
-- Status with most tasks
SELECT s.name, COUNT(*) AS task_count
FROM task t
JOIN status s ON t.status_id = s.id
GROUP BY s.id, s.name
ORDER BY task_count DESC
LIMIT 1;
-- User with most completed tasks (status_id = 3 for 'Done')
SELECT u.name, COUNT(*) AS completed_tasks
FROM user u
JOIN user_task ut ON u.id = ut.user_id
JOIN task t ON ut.task_id = t.id
WHERE t.status_id = 3
GROUP BY u.id, u.name
ORDER BY completed_tasks DESC
LIMIT 1;
' OR '1'='1
'; DROP TABLE tasks; --
' UNION SELECT * FROM user --
# .env file for local development
NODE_ENV=development
# For production testing (get this from Render)
DATABASE_URL=postgresql://user:password@host:5432/database
