How to get JSON object instead of string?

[MarByteBeep]

I want to store JSON objects inside a SQLite database, which seem to work fine. However when trying the retrieve the objects again, better-sqlite3 returns a string, not the object. Obviously I could still JSON.parse() that string, but it would be better in my opinion if an object is returned. How can I make this happen? This is my example code:

	const db = new Database(':memory:', { verbose: console.log });

	db.exec(`CREATE TABLE test (data JSON)`);
	{
		const stmt = db.prepare('INSERT INTO test VALUES(?)');

		for (let i = 0; i < 10; i++) {
			stmt.run(JSON.stringify({ id: i, a: i * 2, b: { c: `cat${i}` } }));
		}
	}
	{
		const sql = `SELECT data ->> '$' as data FROM test`;
		const results = db.prepare(sql).all();
		console.log(results);
	}

outputs

[
  { data: '{"id":0,"a":0,"b":{"c":"cat0"}}' },
  { data: '{"id":1,"a":2,"b":{"c":"cat1"}}' },
  { data: '{"id":2,"a":4,"b":{"c":"cat2"}}' },
  { data: '{"id":3,"a":6,"b":{"c":"cat3"}}' },
  { data: '{"id":4,"a":8,"b":{"c":"cat4"}}' },
  { data: '{"id":5,"a":10,"b":{"c":"cat5"}}' },
  { data: '{"id":6,"a":12,"b":{"c":"cat6"}}' },
  { data: '{"id":7,"a":14,"b":{"c":"cat7"}}' },
  { data: '{"id":8,"a":16,"b":{"c":"cat8"}}' },
  { data: '{"id":9,"a":18,"b":{"c":"cat9"}}' }
]

[Prinzhorn]

You insert a string, you get a string back. There is nothing better-sqlite3 can do here. SQLite does not have a JSON column type. The current behavior is the correct behavior.

You can use some sort of abstraction/ORM to achieve what you want.

[MarByteBeep]

When I query for instance "id" directly, I get a number. Not a string. I find it confusing that the behavior is different.

And I wonder if you are internally parsing it to JSON as well. Because if you do, there is a performance loss because of the needless back and forth encoding/decoding of JSON.

[Prinzhorn]

When I query for instance "id" directly, I get a number. Not a string. I find it confusing that the behavior is different.

That's default SQLite behavior. SQLite does not have a strict type system https://www.sqlite.org/datatype3.html

And I wonder if you are internally parsing it to JSON as well

No, better-sqlite3 does not do any JSON handling whatsoever. It's a light wrapper around SQLite. In general when you encounter any "weird" behavior it's SQLite and not better-sqlite3.

[MarByteBeep]

Good to know. Thanks for the heads up!