posts-go/posts/2022-07-31-sql.md

# 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)