Problem running in docker

[faulander]

Hi i have a sveltekit application using better-sqlite3, which runs perfectly well on local machine. if i deploy it using docker, i get the following error in the docker-logs:

Listening on 0.0.0.0:3000
CREATE TABLE IF NOT EXISTS feedback('TIMESTAMP' DATETIME DEFAULT CURRENT_TIMESTAMP, 'kurkan' varchar, 'ticket' varchar, 'Q1' integer, 'Q2' integer, 'Q3' integer, 'info' varchar );
CREATE TABLE IF NOT EXISTS users('username' varchar, 'password' varchar, PRIMARY KEY('username'))
SELECT * FROM users WHERE username = 'xxx'
SqliteError: near ")": syntax error
    at Database.prepare (/app/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at file:///app/build/server/chunks/db-c6520b65.js:12:30
    at ModuleJob.run (node:internal/modules/esm/module_job:194:25) {
  code: 'SQLITE_ERROR'
}

if i try and create a docker image unsing github-actions, i get the following error:

#8 22.86 > vite build
#8 22.86 
#8 23.61 
#8 23.61 vite v4.3.9 building SSR bundle for production...
#8 23.67 transforming...
#8 28.43 ✓ 131 modules transformed.
#8 28.55 rendering chunks...
#8 29.31 CREATE TABLE IF NOT EXISTS feedback('TIMESTAMP' DATETIME DEFAULT CURRENT_TIMESTAMP, 'kurkan' varchar, 'ticket' varchar, 'Q1' integer, 'Q2' integer, 'Q3' integer, 'info' varchar );
#8 29.33 node:internal/event_target:1012
#8 29.33   process.nextTick(() => { throw err; });
#8 29.33                            ^
#8 29.33 
#8 29.33 Error [ERR_UNHANDLED_ERROR]: Unhandled error. ({ code: 'SQLITE_ERROR' })
#8 29.33     at new NodeError (node:internal/errors:399:5)
#8 29.33     at Worker.emit (node:events:502:17)
#8 29.33     at [kOnErrorMessage] (node:internal/worker:300:10)
#8 29.33     at [kOnMessage] (node:internal/worker:311:37)
#8 29.33     at MessagePort.<anonymous> (node:internal/worker:212:57)
#8 29.33     at [nodejs.internal.kHybridDispatch] (node:internal/event_target:737:20)
#8 29.33     at exports.emitMessage (node:internal/per_context/messageport:23:28) {
#8 29.33   code: 'ERR_UNHANDLED_ERROR',
#8 29.33   context: { code: 'SQLITE_ERROR' }
#8 29.33 }
#8 29.33 
#8 29.33 Node.js v18.16.0
#8 ERROR: executor failed running [/bin/sh -c cd /app && npm install && echo "DB_PATH=/app/data/feedback.db" > /app/.env && npm run build]: exit code: 1

My Dockerfile looks like this:

FROM node:18-alpine
RUN mkdir /app && mkdir /app/data
COPY . /app
RUN cd /app && npm install && echo "DB_PATH=/app/data/feedback.db" > /app/.env && npm run build
WORKDIR /app
CMD ["node", "build/index.js"]

[Prinzhorn]

Single quotes are for strings, double quotes for identifiers. Your SQL is invalid. Why that would make a difference between different environment is impossible to say with the limited information you've provided.

better-sqlite3 by default compiles SQLite with SQLITE_DQS https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/compilation.md#bundled-configuration

https://sqlite.org/quirks.html#dblquote

The SQL standard requires double-quotes around identifiers and single-quotes around string literals. [...] Application developers are encouraged to compile using -DSQLITE_DQS=0 in order to disable the double-quoted string literal misfeature by default.

[faulander]

thank you for your feedback @Prinzhorn , but that didn't solve the problem. I changed all the strings and the error is the same:

#9 12.14 > vite build
#9 12.14 
#9 12.78 
#9 12.78 vite v4.3.9 building SSR bundle for production...
#9 12.82 transforming...
#9 16.79 ✓ 131 modules transformed.
#9 16.90 rendering chunks...
#9 17.51 CREATE TABLE IF NOT EXISTS feedback("TIMESTAMP" DATETIME DEFAULT CURRENT_TIMESTAMP, "kurkan" varchar, "ticket" varchar, "Q1" integer, "Q2" integer, "Q3" integer, "info" varchar );
#9 17.53 node:internal/event_target:1012
#9 17.53   process.nextTick(() => { throw err; });
#9 17.53                            ^
#9 17.53 
#9 17.53 Error [ERR_UNHANDLED_ERROR]: Unhandled error. ({ code: 'SQLITE_ERROR' })
#9 17.53     at new NodeError (node:internal/errors:399:5)
#9 17.53     at Worker.emit (node:events:502:17)
#9 17.53     at [kOnErrorMessage] (node:internal/worker:300:10)
#9 17.53     at [kOnMessage] (node:internal/worker:311:37)
#9 17.53     at MessagePort.<anonymous> (node:internal/worker:212:57)
#9 17.53     at [nodejs.internal.kHybridDispatch] (node:internal/event_target:737:20)
#9 17.53     at exports.emitMessage (node:internal/per_context/messageport:23:28) {
#9 17.53   code: 'ERR_UNHANDLED_ERROR',
#9 17.53   context: { code: 'SQLITE_ERROR' }
#9 17.53 }
#9 17.53 
#9 17.53 Node.js v18.16.0
#9 ERROR: executor failed running [/bin/sh -c cd /app && npm install && echo "DB_PATH=/app/data/feedback.db" > /app/.env && npm run build]: exit code: 1

the statements that seem to make problems are the following:

const sqlCreateTableFeedback =
    'CREATE TABLE IF NOT EXISTS feedback("TIMESTAMP" DATETIME DEFAULT CURRENT_TIMESTAMP, "kurkan" varchar, "ticket" varchar, "Q1" integer, "Q2" integer, "Q3" integer, "info" varchar );';
const createTableUsers =
    'CREATE TABLE IF NOT EXISTS users("username" varchar, "password" varchar, PRIMARY KEY("username"))';

db.exec(sqlCreateTableFeedback);
db.exec(createTableUsers);

const stmt = db.prepare("SELECT * FROM users WHERE username = 'xxx'");
const row = stmt.get();

if (!row) {
    const stmtIinsertUser = db.prepare('INSERT INTO users ("username", "password") VALUES (?,?))');
    const ergebnis = stmtIinsertUser.run('xxx', 'xxx');
    console.log(ergebnis);
}

[Prinzhorn]

The Docker filesystem might just be readonly or that /app/data/ does not exist? Seems like the first writing statement fails. Does it work with a :memory: db? I doubt this is a better-sqlite3 issue. It looks like the error is also coming from a worker, which might swallow important bits of the error message. Again, this is very likely not a better-sqlite3 issue but a problem with your environment.

[Prinzhorn]

From what I can tell it also doesn't really make sense that your are doing anything with SQLite during build? I assume this is some weird SvelteKit pre-render stuff that you need to take care of.

[faulander]

yes, it seems that VITE (the bundler sveltekit is using) is doing some SSR stuff, i get the same output locally, but here it works:

> vite build

vite v4.3.9 building SSR bundle for production...
✓ 128 modules transformed.
CREATE TABLE IF NOT EXISTS feedback (t TIMESTAMP DEFAULT CURRENT_TIMESTAMP, kurkan VARCHAR, ticket VARCHAR, Q1 INTEGER, Q2 INTEGER, Q3 INTEGER, info VARCHAR)
CREATE TABLE IF NOT EXISTS users (username varchar primary key, password varchar)
SELECT * FROM users WHERE username = 'advokat'

vite v4.3.9 building for production...
✓ 127 modules transformed.

i have to dig deeper into it.

[faulander]

ok, it builds fine now, but i get following error running the program:

SELECT * FROM users WHERE username = 'xxx'
SqliteError: near ")": syntax error
    at Database.prepare (/app/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at file:///app/build/server/chunks/db-08501699.js:12:32
    at ModuleJob.run (node:internal/modules/esm/module_job:194:25) {
  code: 'SQLITE_ERROR'
}

The code is the following:```

const stmt = db.prepare('SELECT * FROM users WHERE username = ?');
const row = stmt.get('xxx');
if (!row) {
    const stmtIinsertUser = db.prepare('INSERT INTO users VALUES (?,?))');
    const ergebnis = stmtIinsertUser.run('xxx', 'xxx');
    console.log(ergebnis);
}

If the database wouldn't be there, or read only the error should come sooner and not only when selecting the user table right?

[Prinzhorn]

The error message is pretty on the spot, there is an extra ) here:

INSERT INTO users VALUES (?,?))

[faulander]

omg, sometimes you don't see the forest because of all the trees! Thank you! ;)