CODEWITHSUNDEEP
expressmariadb
Frederik Kliemt
Frederik Kliemt

Reputation: 58

MariaDB Node.js-Connector insert a value and use the default value

I have a MariaDB database in conjuction with an Express.js backend and therefore use the MariaDB provided Node.js Connector. I Initialized a Database with a Table that looks like this:

CREATE TABLE IF NOT EXISTS `Threads` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` TINYTEXT NOT NULL,
    `post` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`)
);

Using the Node.js-Connector in my Backend, I want to insert some data to this table and use the default values for "created_at" and "updated_at".

I was thinking of something like this:

const insertThreadQuery = 'INSERT INTO Threads VALUES (?, ?, ?, ?, ?)';
con = await pool.getConnection();
const result = await con.query(insertThreadQuery, [null, "title", "post", null, null]);

Which obivously throws an error that tells me, that I cannot insert null for these Values ((conn=4, no: 1048, SQLState: 23000) Column 'updated_at' cannot be null sql: INSERT INTO Threads VALUES (?, ?, ?, ?, ?) - parameters:[null,'title','post',null,null])

My question is: How can I insert an entry like I was showing before, but instead of inserting "null" insert something else so my columns created_at and updated_at use the default value?

Upvotes: 1

Views: 574

Answers (2)

Jared Brandt
Jared Brandt

Reputation: 213

I had this same problem and came across this question, but the answer did not work in my case, because I did not know ahead of time which columns would have values and which would have required their default values.

Using your same CREATE TABLE statement, you could prepare and execute the query like so:

const query = await con.prepare( 'INSERT INTO Threads VALUES ( ?, ?, ?, COALESCE( ?, CURRENT_TIMESTAMP ), COALESCE( ?, CURRENT_TIMESTAMP ) )' );

query.execute( [ null, 'The Title', 'The Post', new Date( '2000-01-01 00:00:00' ), null ] );

This would insert the CURRENT_TIMESTAMP into the updated_at column.

If the columns in question had scalar default values, as opposed to CURRENT_TIMESTAMP, as was the case in my problem, you can use the column name instead in the COALESCE function.

const query = await con.prepare( 'INSERT INTO Threads VALUES ( ?, ?, ?, COALESCE( ?, created_at ), COALESCE( ?, updated_at ) )' );

query.execute( [ null, 'The Title', 'The Post', null, null ] );

So if created_at and updated_at were integers, and the default values were 5 and 10, respectively, then above query would result in a row that looked something like this:

{ id: some_auto_number, title: 'The Title', post: 'The Post', created_at: 5, updated_at: 10 }

Hope this helps someone else who comes across this question!!

Upvotes: 0

Georg Richter
Georg Richter

Reputation: 7476

If you want to use DEFAULT values, then pass DEFAULT instead of NULL.

const insertThreadQuery = 'INSERT INTO Threads VALUES (NULL, ?, ?, DEFAULT, DEFAULT)';
con = await pool.getConnection();
const result = await con.query(insertThreadQuery, ["title", "post"]);

Upvotes: 1

Related Questions