mgsql

library0.2.21jsnpmunverified

A lightweight SQL utility library for Node.js that normalizes PostgreSQL and MySQL query building and execution. Current version 0.2.21. Provides a unified API with schema-aware insert/update validation, query builders (SELECT, INSERT, UPDATE), auto-parameter conversion (? -> $1 for Postgres), and result formatting. Differentiators: automatic column validation against database schema, logging support, and a simple builder pattern. Released under the MIT license by MacLaurin Group.

database

npm install mgsql

github ↗package ↗homepage ↗

INSTALL

IMPORT

SIG · MGSQL

mgsql

databasejavascriptv0.2.21

harness data pending

Install & Compatibility

Where this runs

No compatibility data collected yet for this library.

Code

Verified usage

Verified import paths — ran on the pinned version, not inferred.

default

✓ const mgsql = require('mgsql')

✗ import mgsql from 'mgsql'

Currently CommonJS only; no ESM support.

getPostgresWrap

✓ const dbConn = mgsql.getPostgresWrap(pgConnect, 'schema1,schema2')

✗ const { getPostgresWrap } = require('mgsql'); const dbConn = getPostgresWrap(pgConnect, 'schema1,schema2')

Named export not directly available; use via default import.

assert

✓ mgsql.assert.forMissing(data, 'field1,field2')

✗ const { assert } = require('mgsql'); assert.forMissing(data, 'field1,field2')

assert is a nested object, not a named export.

Connects to Postgres, performs schema-validated insert, runs a parameterized query, and uses the builder pattern.

const mgsql = require('mgsql'); const pg = require('pg'); const pgConnect = new pg.Client({ connectionString: process.env.DATABASE_URL ?? '' }); await pgConnect.connect(); const dbConn = mgsql.getPostgresWrap(pgConnect, 'public'); // Insert with schema validation const id = await dbConn.insert('public.users', { name: 'Alice', email: 'alice@example.com' }); console.log('Inserted ID:', id); // Query with auto parameter conversion const rows = await dbConn.select('SELECT * FROM public.users WHERE name = ?', ['Alice']); console.log('Rows:', rows); // Builder pattern const insertBuilder = dbConn.buildInsert() .table('public.users') .column('name', 'Bob') .column('email', 'bob@example.com'); await dbConn.run(insertBuilder); await pgConnect.end();

Debug

Known issues

breakingIn version 0.2.x, the query builder's .run() method changed from returning a promise to being async-only. Code using .run() without await may fail silently.

fix

Use await on all .run() calls: await dbConn.run(builder);

affects: <0.2

deprecatedThe .insertIgnoreDuplicate() method is deprecated and will be removed in a future version. Use .insert() with .ignoreDuplicate() modifier in the builder instead.

fix

Replace dbConn.insertIgnoreDuplicate('table', data) with builder pattern: dbConn.buildInsert().table('table').column(...).ignoreDuplicate()

affects: >=0.2

gotchaSchema validation caches table metadata; if the schema changes after the first insert/update, the cached metadata will be stale until the process restarts.

fix

Restart the Node.js process after any DDL changes, or call a hypothetical reset (not provided).

affects: >=0.0

gotchaThe .clean.forOnlyAZaz09() method strips non-alphanumeric characters, which may be unexpected when fields contain underscores or hyphens.

fix

Ensure field names only contain a-z, A-Z, 0-9 before calling, or use an alternative cleaning method.

affects: >=0.0

gotchaWhen using .query() with Postgres, the library auto-converts ? to $1, but if your SQL contains $1 already, it will be double-converted and break.

fix

Use .queryR() (raw) when your SQL already uses Postgres-style $N placeholders.

affects: >=0.0

Errors

Common errors & fixes

Cannot find module 'mgsql'

Package not installed or import path incorrect in ESM project.

fix

Run `npm install mgsql` and use `const mgsql = require('mgsql')` (CommonJS) or use dynamic import: `const mgsql = await import('mgsql')` in ESM.

TypeError: dbConn.insert is not a function

dbConn not properly initialized with getPostgresWrap or getMySQLWrap.

fix

Ensure you call getPostgresWrap(pgClient, schemas) or getMySQLWrap(mysqlPool) and await the connection if needed.

Error: Column 'id' is missing in data

Insert/update missing a required column according to database schema.

fix

Check the table schema and include all required columns in the data object, or alter the table to make the column nullable.

Error: Relation 'public.users' does not exist

Table name or schema is incorrect in the database.

fix

Verify the table name and schema exist. If using schemas, pass them as a comma-separated string to getPostgresWrap.

TypeError: Cannot read properties of undefined (reading 'run')

Builder method .run() called without awaiting, or builder not fully constructed.

fix

Use `await dbConn.run(builderObject)` and ensure the builder has .table() and appropriate clauses set.

Upgrade

Version history

0.2.21latest on npm

Audit

Dependencies

No dependency data recorded yet.

Agent activity

4 hits · last 30 days

node

Resources

githubhttps://github.com/MacLaurinGroup/mgsql ↗

packagemgsql ↗

homepagehttps://github.com/MacLaurinGroup/mgsql#readme ↗