~
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.
Use UUID instead. If you can, and you should, choose UUID type which can be sortable.
Stay away from all kind of database timestamp (MySQL timestamp, 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:
[Business] time, data -> convert to unix timestamp milliseconds -> [Database] int64
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.
Use JSON_CONTAINS_PATH(col, 'one', '$.key') to check json
field exist or not
Use col->'$.key' to get value
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:
CREATE INDEX idx_user_id
ON user_upload (user_id);
If create index inside CREATE TABLE,
prefer INDEX to KEY:
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)
);
If use composite index, order is important, either both
DESC or both ASC, do not mix:
CREATE INDEX idx_user_id_created_at
ON user_upload (user_id, created_at);
-- Bad
SELECT *
FROM user_upload
ORDER BY user_id, created_at DESC;
-- Good
SELECT *
FROM user_upload
ORDER BY user_id DESC, created_at DESC;
-- Also good
SELECT *
FROM user_upload
ORDER BY user_id, created_at;
Use EXPLAIN to check if index is used or not:
TLDR with MySQL:
CREATE TABLE ekyc_approved
(
id varchar(30) NOT NULL,
PRIMARY KEY (id),
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
If compare with field which can be NULL, remember to check NULL for safety.
-- 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 happen? Idk :(
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).
Prefer LIMIT 10 OFFSET 5 to LIMIT 5, 10 to avoid
misunderstanding.
Please read docs about online ddl operations before do anything online (keep database running the same time update it, for example create index, ...)
Use SELECT 1 to check if database failed yet.
SELECT DISTINCT
table_name,
index_name
FROM information_schema.statistics;