SQLite triggers to track table changes | Hacker News Reader

SQLite triggers to track table changes

playtechnique.io

I am just starting to explore SQLite in the context of personal projects and, more recently, Publii. So this is interesting. Didn't know about triggers.

I found one statement confusing in this post: "I can't add a primary key after the fact to my inputs table; it's not supported".

I have added PKs to existing tables before, what does the author mean by that?

I would have swore I read in the docs that you cannot add one after creating a table, but I cannot find a reference for that. Sounds like I made a mistake! Thanks for catching it!

Following up again, today I tried adding a primary key to a table in sqlite and didn't figure it out. I revisited the sqlite docs and found https://sqlite.org/faq.html#q11, showing that the alter statement is restricted to add, rename and drop.

It _does_ seem like you can add a primary key later in mysql and postgres, but I don't see any way to accomplish that in sqlite.

Oh that's very interesting. I am actually using a UI called "DB Browser for SQLite" and checking its logs shows that when I add a PK, it actually creates a new temporary table, copies over all data from the old table, deletes the old table, and then renames the new table.

So you were correct originally, it can't just add a PK.

Thanks for following up!

This is me learning sqlite basics to start a more ambitious project tracking changes in grafana.

I'm really sorry as this may sound condescending. It isn't meant like that.

I'm just old.

As in, when I started my career I worked on an application that was written 15 years earlier. It taught me a lot.

As in around 1990. Guess what? It used database triggers to keep a record of every change ever made to any table in an "archive" table. When I joined the project I was able to see every single change any user made ever since the application was built.

And yes you very probably bought a product that was planned and administered through that application.

So to try and save this post: go learn and explore! I do implore you to try and look around and away from specifics like the technology you are using being SQLite. Look beyond. Look at the concepts. Figure out that things repeat in history. Every generation re-invents some wheels. Recognize them and try to recognize if there are slight differences and improvements in the re-inventions or if it's just more layers upon layers. Or in this case is it just that SQLite is gaining/having more and more features of "real databases" even though it's mostly touted as an "embedded database"?

Learning and exploring is what I was sharing in this blog post! I’ve thought of tinkering with sqlite for the last ten years, and I finally did and shared it with you all :)

DuckDB is another embedded db worth taking a look. It has some unique features, such as, you can fetch data from an api, extract data from the json response and insert into database via sql

Cool solution. I might use it on a small multi tenant web app to generate audit logs.

It has one SQLite database per tenant.

Reply on news.ycombinator.com