Advanced SQL for Application Development

Build scalable and resilient databases to support any application. Learn how to write optimized SQL, use indexes, partition data, and use established design patterns.

Introduction

• Getting started with SQL for application developers

1. Advanced Querying with Select

• Designing an ecommerce application database
• Creating tables and loading data
• Using environment variables for connection parameters
• Connecting to a database
• Parameterizing SELECT statements
• Avoiding N+1 queries
• Challenge: Write a parameterized query in Python
• Solution: How to write a parameterized query in Python

2. Indexing Tables

• Overview of index types
• Using B-tree indexes and range scans
• Using hash indexes and equality operations
• Covering indexes
• Indexes and bulk data loading
• Avoiding index locks
• Challenge: Define a B-tree and a hash index
• Solution: Defining B-tree and hash indexes

3. Object-Relational Mapping (ORM)

• Overview of object-relational mapping
• Introduction to SQLAlchemy
• Installing SQLAlchemy
• Query with SQLAlchemy
• Limitations of ORM and cautionary advice
• Challenge: Create an ORM model
• Solution: Create an ORM model

4. Designing for Scalability

• Overview of partitioning
• Range partitioning
• List partitioning
• Hash partitioning
• Querying partitioned tables
• Using read-only replicas
• Challenge: Define a partitioned table
• Solution: Defining a partitioned table

5. Designing for Reliability

• Database errors
• Error trapping
• Database not responding, timeouts, and exponential backoff
• Connection pooling and bulk heads
• Monitoring and logging best practices
• Challenge: When your Python application fails
• Solution: When your Python application fails

6. Designing for Maintainability

• Understanding schema changes
• Ad hoc scripts for schema changes
• Automated schema migration with Flyway
• Automated schema migration with Liquibase
• Challenge
• Solution

Conclusion

• Key considerations