How to Evaluate Multi-Statement SQL Queries at once & get result returned in `node:sqlite`?

[singodiyashubham87]

I'm building an educational platform where students write SQL queries in a Monaco Editor for database-related assignments. The system automatically evaluates their submissions by comparing the query results to expected outputs and assigning marks accordingly. I'm using the node:sqlite modules for SQLite operations in a node environment, but I'm hitting roadblocks in executing and evaluating multi-statement SQL queries effectively.

Background

The platform uses a Monaco Editor where students input SQL queries. Here’s an example:

• Sample Assignment: Create a table student with fields id, phone, email, and age, insert 4 rows, and query the table to fetch all student data.
• Sample Student Answer:

  -- Create the table
  CREATE TABLE student (
      id INT PRIMARY KEY,
      phone VARCHAR(15),
      email VARCHAR(100),
      age INT
  )
  
  -- Insert 4 rows into the table
  INSERT INTO student (id, phone, email, age)
  VALUES 
  (1, '123-456-7890', 'student1@example.com', 20),
  (2, '234-567-8901', 'student2@example.com', 21),
  (3, '345-678-9012', 'student3@example.com', 22),
  (4, '456-789-0123', 'student4@example.com', 23)
  
  -- Query to fetch all student data
  SELECT * FROM student
  

Note: Students aren’t required to use semicolons (;) at the end of statements.

The student’s solution is sent to the backend, where I execute it and compare the output (e.g., the result of the SELECT statement) against the expected result for grading.

Current Implementation and Limitations

I'm using the node:sqlite modules, but we’ve encountered these issues:

1. exec() Limitation: The exec() method can run multiple statements at once but doesn’t return query results. For auto-grading, I need the output (e.g., from SELECT statements), so this isn’t viable.

2. query.get() Requirement: Methods like query.get() return results but require individual statements. Since I don’t enforce semicolons, I can’t reliably split the student’s input into separate statements.

3. Parsing Attempt: I tried the node-sql-parser library to split the SQL into an array of statements. It worked for basic commands (CREATE, INSERT, SELECT), but it doesn’t support advanced commands like ALTER TABLE, making it insufficient.

Requirements

I need a solution that meets one of these needs:

1. Execute Entire Query with Results: A method or library that executes the full multi-statement SQL query and returns the results (e.g., the SELECT output).

2. Smart Statement Splitting: A tool that intelligently detects the end of each SQL statement (without relying on semicolons) and adds delimiters (;) so we can execute them sequentially.

3. Better Alternative: Any robust approach suited for auto-grading SQL queries in an educational context.

[gurgunday]

Maybe we can add a returnResults parameter to .exec (database.exec(sql, opts))? But then again it's a wrapper around sqlite3_exec() so I don't know if that would be good design

[cjihrig]

sqlite3_exec() does accept a callback for processing results. I haven't used it, but to be honest, I don't think we should pursue this. We should be pushing users towards prepared statements, which are a more secure solution. This just makes it easier to write insecure code.

[singodiyashubham87]

That will actually be the solution @gurgunday