155 lines
4.9 KiB
Markdown
155 lines
4.9 KiB
Markdown
# SQL
|
|
|
|
Previously in my fresher software developer time, I rarely write SQL, I always
|
|
use ORM to wrap SQL. But time past and too much abstraction bites me. So I
|
|
decide to only write SQL from now as much as possible, no more ORM for me. But
|
|
if there is any cool ORM for Go, I guess I try.
|
|
|
|
This guide is not kind of guide which cover all cases. Just my little tricks
|
|
when I work with SQL.
|
|
|
|
## Stay away from database unique id
|
|
|
|
Use UUID instead. If you can, and you should, choose UUID type which can be
|
|
sortable.
|
|
|
|
## Stay away from database timestamp
|
|
|
|
Stay away from all kind of database timestamp (MySQL timestmap, SQLite
|
|
timestamp, ...) Just use int64 then pass the timestamp in service layer not
|
|
database layer.
|
|
|
|
Why? Because time and date and location are too much complex to handle. In my
|
|
business, I use timestamp in milliseconds. Then I save timestamp as int64 value
|
|
to database. Each time I get timestamp from database, I parse to time struct in
|
|
Go with location or format I want. No more hassle!
|
|
|
|
It looks like this:
|
|
|
|
```txt
|
|
[Business] time, data -> convert to unix timestamp milliseconds -> [Database] int64
|
|
```
|
|
|
|
## Extra field for extra things
|
|
|
|
Create new column in database is scary, so I suggest avoid it if you can. How to
|
|
avoid, first design table with extra field. It is black hole, put everything in
|
|
there if you want.
|
|
|
|
I always use MySQL json data type for extra field.
|
|
|
|
JSON data type is also useful for dumping request, response data.
|
|
|
|
- [For MySQL 5.7](https://dev.mysql.com/doc/refman/5.7/en/json.html)
|
|
- [For MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/json.html)
|
|
|
|
Use `JSON_EXTRACT(col, '$.key') IS NULL` to check json field exist or not.
|
|
|
|
## Use index!!!
|
|
|
|
You should use index for faster query, but not too much. Don't create index for
|
|
every fields in table. Choose wisely!
|
|
|
|
For example, create index in MySQL:
|
|
|
|
```sql
|
|
CREATE INDEX idx_user_id
|
|
ON user_upload (user_id);
|
|
```
|
|
|
|
If create index inside `CREATE TABLE`,
|
|
[prefer `INDEX` to `KEY`](https://stackoverflow.com/a/1401615):
|
|
|
|
```sql
|
|
CREATE TABLE user_upload
|
|
(
|
|
id int(11) NOT NULL,
|
|
user_id int(11) NULL DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
INDEX idx_user_id (user_id)
|
|
);
|
|
```
|
|
|
|
Use `EXPLAIN` to check if index is used or not:
|
|
|
|
- [For MySQL 5.7](https://dev.mysql.com/doc/refman/5.7/en/explain-output.html)
|
|
- [For MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html)
|
|
|
|
## Be careful with UTF-8
|
|
|
|
TLDR with MySQL:
|
|
|
|
```sql
|
|
CREATE TABLE ekyc_approved
|
|
(
|
|
id varchar(30) NOT NULL,
|
|
PRIMARY KEY (id),
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
```
|
|
|
|
## Be careful with NULL
|
|
|
|
If compare with field which can be NULL, remember to check NULL for safety.
|
|
|
|
```sql
|
|
-- field_something can be NULL
|
|
|
|
-- Bad
|
|
SELECT *
|
|
FROM table
|
|
WHERE field_something != 1
|
|
|
|
-- Good
|
|
SELECT *
|
|
FROM table
|
|
WHERE (field_something IS NULL OR field_something != 1)
|
|
```
|
|
|
|
Need clarify why this happpen? Idk :(
|
|
|
|
## `VARCHAR` or `TEXT`
|
|
|
|
Prefer `VARCHAR` if you need to query and of course use index, and make sure
|
|
size of value will never hit the limit. Prefer `TEXT` if you don't care, just
|
|
want to store something.
|
|
|
|
If you need to store UUID, use `VARCHAR(255)`.
|
|
|
|
## `LIMIT`
|
|
|
|
Prefer `LIMIT 10 OFFSET 5` to `LIMIT 5, 10` to avoid misunderstanding.
|
|
|
|
## Be super careful when migrate, update database on production and online!!!
|
|
|
|
Plase read docs about online ddl operations before do anything online (keep
|
|
database running the same time update it, for example create index, ...)
|
|
|
|
- [For MySQL 5.7](https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html),
|
|
[Limitations](https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html)
|
|
- [For MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html),
|
|
[Limitations](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html)
|
|
|
|
## Heathcheck
|
|
|
|
Use `SELECT 1` to check if database failed yet.
|
|
|
|
## Tools
|
|
|
|
- Use [sqlfluff/sqlfluff](https://github.com/sqlfluff/sqlfluff) to check your
|
|
SQL.
|
|
- Use [k1LoW/tbls](https://github.com/k1LoW/tbls) to grasp your database reality
|
|
:)
|
|
|
|
## Thanks
|
|
|
|
- [Use The Index, Luke](https://use-the-index-luke.com/)
|
|
- [Essential elements of high performance applications: SQL indexes](https://www.foxhound.systems/blog/essential-elements-of-high-performance-sql-indexes/)
|
|
- [Things You Should Know About Databases](https://architecturenotes.co/things-you-should-know-about-databases/)
|
|
- [When to use JSON data type in database schema design?](https://shekhargulati.com/2022/01/08/when-to-use-json-data-type-in-database-schema-design/)
|
|
- [My Notes on GitLab Postgres Schema Design](https://shekhargulati.com/2022/07/08/my-notes-on-gitlabs-postgres-schema-design/)
|
|
- [How to read MySQL EXPLAINs](https://planetscale.com/blog/how-read-mysql-explains)
|
|
- [Honest health checks that hit the database](https://brandur.org/fragments/database-health-check)
|
|
- [Why are database columns 191 characters?](https://www.grouparoo.com/blog/varchar-191)
|
|
- [Store UUID v4 in MySQL](https://stackoverflow.com/a/43056611)
|