# Set up the SQL practice lab ## Choose one route Route A is the tested self-contained local route. It needs Node.js and npm but no database server. Route B is optional for someone who already has an isolated PostgreSQL practice database. No paid hosting, AI account or cloud database signup is required. The course's offline HTML opens in a normal browser, but it does not execute SQL. No embedded browser SQL lab is claimed or supplied. ## Route A with Node and PGlite 1. Extract the entire ZIP into a folder you can write to. Keep the relative folders together. Do not run directly from an archive preview. 2. Install a supported Node.js LTS version, version 22 or newer, from https://nodejs.org/en/download if Node is not already installed. npm is included with the standard Node installer. Follow your computer's installation permission requirements. The authoring validation used Node 24.19.0; Windows, macOS and other Node versions have not been independently tested here. 3. Open Terminal on macOS/Linux or PowerShell on Windows. Change into the extracted sql-data-ai-course folder. Use your actual extraction location, for example cd followed by a quoted folder path. 4. Check the tools: ```text node --version npm --version ``` 5. Install the exact locked practice dependency from the official npm registry: ```text npm ci ``` This downloads PGlite into the course folder and does not ask for a database account. It needs internet on the first run. Do not substitute an unrelated package or a random download site. If your workplace restricts installations, ask your administrator rather than bypassing the policy. 6. Run the setup smoke check: ```text node tools/run.mjs sql/examples/01_profile.sql ``` Expect customers 8, order_items 19, orders 14, payments 16 and refunds 4. Column spacing may differ from the printed guide. 7. Run a lesson and then your own exercise: ```text node tools/run.mjs sql/examples/02_select.sql node tools/run.mjs sql/exercises/02a.sql ``` The starter contains only comments until you add your SQL in a text editor. An empty result from an untouched starter is not a failure or a completed exercise. Save your changes and rerun the command. End a statement with a semicolon. The path in the command is relative to your terminal's current folder. 8. Run the reference checks: ```text npm test ``` The validator executes reference queries and expected failures against a new in-memory fixture, then writes validation/latest-run.json. It does not grade your free-form exercise files or written capstone. A passing reference suite proves the supplied baseline is reproducible; compare your own outputs and explanations separately. ### What the runner does Each invocation creates a fresh in-memory PGlite database, loads sql/00_setup.sql, begins a READ ONLY transaction with a five-second statement timeout and executes the requested file. Nothing needs resetting between normal runs, and changes do not persist. The schema name is always aabha_sql. No database credentials are required. The runner is for reviewed course SQL. It is not a secure sandbox for hostile scripts: a script can contain transaction control, functions or other operations that a simple read-only wrapper cannot make harmless. Never point this runner or an AI tool at an existing production connection. The course does not grant permission to install software or access databases on a managed device. ## Route B with an existing isolated PostgreSQL installation Prerequisites: PostgreSQL 16 or later, the psql client or a compatible query editor, and a dedicated empty practice database you own. Installation of a native server is not automated by this package and was not tested. Use official installation guidance at https://www.postgresql.org/download/ if you choose this route. In a dedicated practice database, open and execute sql/00_setup.sql once. In psql, the command below assumes you already have a connection configured and a database named aabha_practice. Replace the database name only with your verified practice database. Let psql prompt securely if your local authentication requires it; do not paste a password into a command or chat. ```text psql -X -v ON_ERROR_STOP=1 -d aabha_practice -f sql/00_setup.sql psql -X -v ON_ERROR_STOP=1 -d aabha_practice -f sql/examples/02_select.sql ``` The setup script creates the aabha_sql schema and tables, so it requires write privileges in that practice database. It deliberately fails if the schema already exists and does not drop existing objects. If you need a reset, use a new empty practice database or ask the owner to confirm a safe reset; do not delete an unfamiliar schema. For analysis in a query editor, start a read-only transaction, set a timeout, paste the reviewed SELECT query, then end with ROLLBACK: ```sql BEGIN READ ONLY; SET LOCAL statement_timeout = '5s'; -- Paste one reviewed course SELECT query here. ROLLBACK; ``` After any error inside a transaction, run ROLLBACK before beginning a new transaction. Some editors run statements in separate sessions or manage transactions themselves; check the editor's behavior. A separate least-privilege read account is an additional production control, not something this exercise configures. ## Troubleshooting | Symptom | Check and action | | --- | --- | | node or npm not found | Finish official Node installation, then reopen the terminal and recheck versions | | Cannot find package @electric-sql/pglite | Run npm ci from the folder containing package.json | | Cannot find the SQL file | Confirm the current folder and exact relative file path; quote paths with spaces | | Schema aabha_sql already exists | You probably ran native setup twice; use a new isolated practice database, not a blind DROP command | | Relation does not exist | Use the aabha_sql prefix; on native PostgreSQL confirm setup was run in this same database | | Function pg_input_is_valid does not exist | Use the pinned PGlite route or PostgreSQL 16+; do not silently substitute an unsafe date cast | | Permission denied | Stop and check database or installation permissions with the owner; do not broaden privileges blindly | | Transaction is aborted | Run ROLLBACK in your native editor before retrying | | Query hits the timeout | Inspect the joins and conditions first; these tiny examples should not need a longer timeout | | Amounts have different trailing zeros | Compare numeric values and the query's meaning; rendering of exact numeric scale can differ | If a query differs, compare keys and values, not just the printed alignment. Expected-output CSVs use the literal marker NULL for missing values; that marker is an output convention, not the stored text 'NULL'.