Hacker News Reader

Comment by da_chicken | Hacker News Reader

The one that you are currently using is the best one to learn. They're all different and they all work slightly differently. You need to learn generalities, not specifics, until you're working with a specific RDBMS.

SQLite is fine. The biggest issue to be aware of with SQLite is that SQLite's type affinity system is completely different from how other SQL RDBMSs function. The norm is for columns to have much more rigid data typing.

View on HN

SQLite also ignores size restrictions on text columns; trying to get CHAR(2) to prevent the insertion of ILLINOIS instead of IL requires a trigger. Most other databases do not behave like this.

Other databases all had wonky left join syntax before the SQL92 standard - every attempt should be made to avoid archaic syntax if possible. SQLite itself lacked right join until recently.

Procedural SQL comes in two common varieties - ANSI SQL/PSM (strongly influenced by Oracle), and Transact-SQL (that is only found on Sybase and Microsoft SQL Server). Choose your investment here carefully.

> SQLite also ignores size restrictions on text columns; [...] requires a trigger.

I'd go for CHECK constraints first: https://www.sqlite.org/lang_createtable.html#ckconst

> Procedural SQL [...] Choose your investment here carefully.

I think that the demand for procedual code has dropped drastically in the past decades as the "normal" SQL can solve so many more things with window functions, recursion, and so forth. So I'd say: Yes, choose your investment wisely and stay away from procedural SQL as long as possible.

There is still a case to embed business logic at the database layer, not the application layer, as databases tend not to change as much.

I have thousands of lines of PL/SQL that originated in the days of PowerBuilder that are now serviced by .NET; a decade from now could be totally different.

SQLite appears to use a (very small) subset of PSM for triggers.

Reply on news.ycombinator.com