Database Connection

Error: remaining connection slots are reserved for non-replication superuser connections

Error: too many connections for database

const db = createKnexInstance(config);

// Perform multiple operations
await db.raw("SELECT ...");
await db.raw("UPDATE ..."); // Error occurs here

await db.destroy(); // Never reached

const db = createKnexInstance(config);

try {
  await db.raw("SELECT ...");
  await db.raw("UPDATE ...");
  return result;
} finally {
  await db.destroy(); // Executes regardless of error
}

const sourceDB = createKnexInstance(sourceConfig);
const targetDB = createKnexInstance(targetConfig);

try {
  // Perform operations
  const data = await sourceDB.select("*").from("users");
  await targetDB.insert(data).into("users");
  return data;
} finally {
  await targetDB.destroy();
  await sourceDB.destroy();
}

SELECT
  datname,
  count(*) as connections,
  max_conn
FROM pg_stat_activity
JOIN pg_database ON pg_stat_activity.datname = pg_database.datname
WHERE pg_database.datname = 'your_database_name'
GROUP BY datname, max_conn;

Error: connect ECONNREFUSED 127.0.0.1:5432

# macOS (Homebrew)
brew services list
brew services start postgresql

# Linux (systemd)
sudo systemctl status postgresql
sudo systemctl start postgresql

DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=your_database

psql -h localhost -p 5432 -U postgres -d your_database

Error: Timeout acquiring a connection. The pool is probably full.

export default {
  database: {
    miomock: {
      host: "localhost",
      port: 5432,
      user: "postgres",
      password: "password",
      database: "miomock",
      pool: {
        min: 2,
        max: 10, // Increase from default
        acquireTimeoutMillis: 30000, // Increase timeout
        idleTimeoutMillis: 30000,
      },
    },
  },
} satisfies SonamuConfig;

-- Check currently running queries
SELECT
  pid,
  now() - query_start as duration,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Kill specific query
SELECT pg_cancel_backend(pid);  -- Graceful termination
SELECT pg_terminate_backend(pid);  -- Force termination

-- Check old transactions
SELECT
  pid,
  now() - xact_start as duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC;

Error: permission denied for table users

-- Check database owner
SELECT d.datname, u.usename
FROM pg_database d
JOIN pg_user u ON d.datdba = u.usesysid
WHERE d.datname = 'your_database';

-- Grant schema permissions
GRANT ALL ON SCHEMA public TO your_user;

-- Grant table permissions
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user;

-- Grant sequence permissions
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO your_user;

Error: database "miomock_test" already exists

Error: database "miomock_test" is being accessed by other users

# Terminate all connections and delete DB
psql -U postgres -c "
  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE datname = 'miomock_test' AND pid <> pg_backend_pid();
"

psql -U postgres -c "DROP DATABASE IF EXISTS miomock_test;"

{
  "scripts": {
    "test": "vitest run --pool=forks --poolOptions.forks.singleFork=true"
  }
}

// vitest.config.ts
export default defineConfig({
  test: {
    globalSetup: "./test/setup.ts",
    globalTeardown: "./test/teardown.ts",
  },
});

// test/teardown.ts
export default async function teardown() {
  await DB.clearTestTransaction();
  await DB.closeConnection();
}