SQL tips for beginners; what I learned after 10+ years of occasionally writing queries and schemas as a web dev
Tags: webdev, programming, sql, postgres
Published: 2022-06-28 20:56:27

- **Index nearly everything, index early**. Not indexing results in poor performances. Indexing too late can create downtime or inconsistent data. Do not consider removing/avoiding index to preserve disk space, buy more space instead. Remember that index may have an impact on write performance though.
- **PostgreSQL is your best choice**, it has a skilled community, including StackOverflow [heroes](https://stackoverflow.com/users/6464308/laurenz-albe). There is no MySQL, MariaDB, or SQLite user group where I live.

- **ORM are great for simple CRUD operation**. Don't use it to query specific stats or any complex query, use raw queries. _BTW, for TS/JS devs, [Prisma 3](https://www.prisma.io/) is cool, Sequelize is obsolete._
- SQL _syntax_ is case insensitive, **use lower case everywhere** (and underscores), do not name your tables with camelCase. _ALSO, YOU DON'T HAVE TO YELL._ `select id from users` is the same as `SELECT id FROM users`. For string comparison, case sensitivity depends on [collation](https://www.postgresql.org/docs/11/collation.html).

- **Never miss a foreign key**. Implicit links and relations between tables should be avoided.
- **Follow a pattern when naming tables and columns**. Example: `users`, `posts`, `comments` (plural). `post_user`, `comment_post`, `comment_user` for junction tables (singular + alphabetical order). `id` for primary key, `id_user`, `id_comment`, and `id_post` (singular + id first) for foreign key. Reading a database schema is hard for newcomers. Be super consistent when naming tables.
- **1 model ≠ 1 table**. Unfortunately, when designing a database you have to think at a lower level than "models" and "objects". You will have to deal with technical tables.
- Use the **right data type**. Do not create "custom" data types defined by an implicit contract.
- **JSONB is great** but don't overuse it. Also, [learn to query it](http://raph.site/blog/postgresql.html) it's [complex yet powerful](https://dev.to/rap2hpoutre/postgresql-update-objects-deep-in-a-jsonb-structure-a-fast-and-reliable-way-25mb). When you iterate a lot on designing a schema, you can use JSONB to avoid modifying the schema over and over.
- PostgreSQL does not scale automatically. **Expect migrations and downtime.**
- **[CTE](https://www.postgresql.org/docs/current/queries-with.html) is more readable than subqueries**, you can read it from the top to the bottom. Also, you can understand the way it was designed.
- **Keep tables schema simple**, avoid patterns such as [EAV](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) that makes tables hard to understand and read; try to keep most tables as simple as vertical CSV sheets, with one line for each record.
- Don't be afraid of writing long and verbose queries to address a specific need, it's often more efficient.
- The order of the words in a query is not the order in which they are evaluated. **Know the order of execution**: from, where, group by, having, order by, select, limit.
- **120 tables are way too much**, even for big products. You can build a robust application with 25 tables.

Thank you for reading 🙏