Conditionally add columns in SQLite

Question

I've seen enough answers to know you can't easily check for columns in SQLITE before adding. I'm trying to make a lazy person's node in Node-Red where you pass a message to SQLITE which is the query. Adding a table if it does not exist is easy.

msg.topic='create table IF NOT EXISTS fred (id PRIMARY KEY);'; node.send(msg);

it occurred to me that adding a table which had the names of the fields would be easy - and if the field name is not in the table.... then add the field. BUT you can't add multiple fields at once - so I can't do this...

msg.topic='create table IF NOT EXISTS fred (id PRIMARY KEY, myfields TEXT);'; node.send(msg);

The problem with THAT is that I can't add this in later, there's no way to check before adding a field it the table exists!

This is what I WANT

msg.topic='create table IF NOT EXISTS fred (id PRIMARY KEY, myfields TEXT);'; node.send(msg);

msg.topic='if not (select address from myfields) alter table fred add column address text';

I just cannot think of any way to do this - any ideas anyone (the idea is that the node-red node would input a table, field and value and if the table didn't exist it would be created, if the field didn't exist it would be created, all before trying to add in the value).

Answer 1

You won't be able to make the ALTER TABLE conditional in the SQL itself. You'll need to handle that from your calling script.
Alternately, simply attempt to add the column to the table and accept failure as an outcome. Assuming the table exists, the only failure reason you could encounter is that the column already exists.

If you'd like to do something more graceful, you can check if the column exists in advance, then conditionally run the SQL from your calling application.