Kittisak Ma

What is the best way to learn SQL? In 1986, SQL (Structured Query Language) became a standard. Over the next 40 years, it became the dominant language for relational database management systems. Reading the latest standard (ANSI SQL 2016) can be time-consuming. How can I learn it? There are 5 components of the SQL language: - DDL: data definition language, such as CREATE, ALTER, DROP - DQL: data query language, such as SELECT - DML: data manipulation language, such as INSERT, UPDATE, DELETE - DCL: data control language, such as GRANT, REVOKE - TCL: transaction control language, such as COMMIT, ROLLBACK For a backend engineer, you may need to know most of it. As a data analyst, you may need to have a good understanding of DQL. Select the topics that are most relevant to you. Over to you: What does this SQL statement do in PostgreSQL: “select payload->ids->0 from events”? -- Subscribe to our weekly newsletter to get a Free System Design PDF (158 pages): bit.ly/3KCnWXq

Top 12 Tips for API Security - Use HTTPS - Use OAuth2 - Use WebAuthn - Use Leveled API Keys - Authorization - Rate Limiting - API Versioning - Whitelisting - Check OWASP API Security Risks - Use API Gateway - Error Handling - Input Validation -- Subscribe to our weekly newsletter to get a Free System Design PDF (158 pages): bit.ly/3KCnWXq

Explaining JSON Web Token (JWT) to a 10 year old Kid. Imagine you have a special box called a JWT. Inside this box, there are three parts: a header, a payload, and a signature. The header is like the label on the outside of the box. It tells us what type of box it is and how it's secured. It's usually written in a format called JSON, which is just a way to organize information using curly braces { } and colons : . The payload is like the actual message or information you want to send. It could be your name, age, or any other data you want to share. It's also written in JSON format, so it's easy to understand and work with. Now, the signature is what makes the JWT secure. It's like a special seal that only the sender knows how to create. The signature is created using a secret code, kind of like a password. This signature ensures that nobody can tamper with the contents of the JWT without the sender knowing about it. When you want to send the JWT to a server, you put the header, payload, and signature inside the box. Then you send it over to the server. The server can easily read the header and payload to understand who you are and what you want to do. Over to you: When should we use JWT for authentication? What are some other authentication methods? – Subscribe to our weekly newsletter to get a Free System Design PDF (158 pages): bit.ly/3KCnWXq

How is data transmitted between applications? The diagram below shows how a server sends data to another server. Assume a chat application running in the user space sends out a chat message. The message is sent to the send buffer in the kernel space. The data then goes through the network stack and is wrapped with a TCP header, an IP header, and a MAC header. The data also goes through qdisc (Queueing Disciplines) for flow control. Then the data is sent to the NIC (Network Interface Card) via a ring buffer. The data is sent to the internet via NIC. After many hops among routers and switches, the data arrives at the NIC of the receiving server. The NIC of the receiving server puts the data in the ring buffer and sends a hard interrupt to the CPU. The CPU sends a soft interrupt so that ksoftirqd receives data from the ring buffer. Then the data is unwrapped through the data link layer, network layer and transport layer. Eventually, the data (chat message) is copied to the user space and reaches the chat application on the receiving side. Over to you: What happens when the ring buffer is full? Will it lose packets? – Subscribe to our weekly newsletter to get a Free System Design PDF (158 pages): bit.ly/3KCnWXq

Do you believe that Google, Meta, Uber, Twitter, and Airbnb put almost all of their code in one repository? This practice is called a monorepo. Monorepo vs. Microrepo. Which is the best? Why do different companies choose different options? Monorepo isn't new; Linux and Windows were both created using Monorepo. To improve scalability and build speed, Google developed its internal dedicated toolchain to scale it faster and strict coding quality standards to keep it consistent. Amazon and Netflix are major ambassadors of the Microservice philosophy. This approach naturally separates the service code into separate repositories. It scales faster but can lead to governance pain points later on. Within Monorepo, each service is a folder, and every folder has a BUILD config and OWNERS permission control. Every service member is responsible for their own folder. On the other hand, in Microrepo, each service is responsible for its repository, with the build config and permissions typically set for the entire repository. In Monorepo, dependencies are shared across the entire codebase regardless of your business, so when there's a version upgrade, every codebase upgrades their version. In Microrepo, dependencies are controlled within each repository. Businesses choose when to upgrade their versions based on their own schedules. Monorepo has a standard for check-ins. Google's code review process is famously known for setting a high bar, ensuring a coherent quality standard for Monorepo, regardless of the business. Microrepo can either set their own standard or adopt a shared standard by incorporating best practices. It can scale faster for business, but the code quality might be a bit different. Google engineers built Bazel, and Meta built Buck. There are other open-source tools available, including Nix, Lerna, and others. Over the years, Microrepo has had more supported tools, including Maven and Gradle for Java, NPM for NodeJS, and CMake for C/C++, among others. Over to you: Which option do you think is better? Which code repository strategy does your company use? -- Subscribe to our weekly newsletter to get a Free System Design PDF (158 pages): bit.ly/3KCnWXq