dont discard tls

- Fix precedence logic so explicit options (hostname, port, username, password, database)
  now correctly override URL parameters instead of the other way around
- Add 10 comprehensive tests verifying explicit options take precedence over URL parameters
- Test both PostgreSQL and MySQL adapters with various combinations of explicit options
- Test alternative option names (host, user, pass, db)
- Ensure explicit options still take precedence even when environment variables are present
- All 43 adapter tests and 29 main SQL tests continue to pass

🤖 Generated with [Claude Code](https://claude.ai/code)

Co-Authored-By: Claude <noreply@anthropic.com>

src/js/internal/sql/shared.ts

@@ -212,8 +212,10 @@ function hasProtocol(url: string) {
     "mysql",
     "mysql2",
     "mariadb",
+    "mysqls",
     "file",
     "sqlite",
+    "unix",
   ];
   for (const protocol of protocols) {
     if (url.startsWith(protocol + "://")) {
@@ -223,82 +225,416 @@ function hasProtocol(url: string) {
   return false;
 }
 
-function defaultToPostgresIfNoProtocol(url: string | URL | null): URL {
-  if (url instanceof URL) {
-    return url;
+function getAdapterFromProtocol(protocol: string): Bun.SQL.__internal.Adapter | null {
+  switch (protocol) {
+    case "postgres":
+    case "postgresql":
+      return "postgres";
+    case "mysql":
+    case "mysql2":
+    case "mariadb":
+    case "mysqls":
+      return "mysql";
+    case "file":
+    case "sqlite":
+      return "sqlite";
+    case "unix":
+      return null; // Unix sockets require explicit adapter
+    default:
+      return null;
   }
-  if (hasProtocol(url as string)) {
-    return new URL(url as string);
-  }
-  return new URL("postgres://" + url);
 }
+
+function determineAdapter(
+  options: Bun.SQL.Options,
+  urlString: string | URL | null,
+  env?: Record<string, string | undefined>,
+): Bun.SQL.__internal.Adapter {
+  // 1. Use explicit adapter if provided
+  if (options.adapter) {
+    const adapter = options.adapter;
+    switch (adapter) {
+      case "postgres":
+      case "postgresql":
+        return "postgres";
+      case "mysql":
+      case "mysql2":
+      case "mariadb":
+        return "mysql";
+      case "sqlite":
+        return "sqlite";
+      default:
+        throw new Error(`Unsupported adapter: ${adapter}. Supported adapters: "postgres", "sqlite", "mysql"`);
+    }
+  }
+
+  // 2. Infer from URL protocol if present
+  if (urlString) {
+    const urlStr = urlString instanceof URL ? urlString.href : urlString;
+
+    // Check for SQLite URLs first
+    if (parseDefinitelySqliteUrl(urlStr) !== null) {
+      return "sqlite";
+    }
+
+    // Extract protocol
+    const colonIndex = urlStr.indexOf(":");
+    if (colonIndex !== -1) {
+      const protocol = urlStr.substring(0, colonIndex);
+      const adapterFromProtocol = getAdapterFromProtocol(protocol);
+      if (adapterFromProtocol) {
+        return adapterFromProtocol;
+      }
+    }
+  }
+
+  // 3. If no URL provided, check environment variables to infer adapter
+  // Respect precedence: POSTGRES_URL > DATABASE_URL > PGURL > PG_URL > MYSQL_URL
+  if (!urlString && env) {
+    // Check in order of precedence (including TLS variants)
+    const envVars = [
+      { name: "POSTGRES_URL", url: env.POSTGRES_URL },
+      { name: "TLS_POSTGRES_DATABASE_URL", url: env.TLS_POSTGRES_DATABASE_URL },
+      { name: "DATABASE_URL", url: env.DATABASE_URL },
+      { name: "TLS_DATABASE_URL", url: env.TLS_DATABASE_URL },
+      { name: "PGURL", url: env.PGURL },
+      { name: "PG_URL", url: env.PG_URL },
+      { name: "MYSQL_URL", url: env.MYSQL_URL },
+      { name: "TLS_MYSQL_DATABASE_URL", url: env.TLS_MYSQL_DATABASE_URL },
+    ];
+
+    for (const { name, url: envUrl } of envVars) {
+      if (envUrl) {
+        // Check for SQLite URLs first (special case)
+        if (parseDefinitelySqliteUrl(envUrl) !== null) {
+          return "sqlite";
+        }
+
+        // Environment variable name takes precedence over protocol
+        if (name === "MYSQL_URL" || name === "TLS_MYSQL_DATABASE_URL") {
+          return "mysql";
+        } else if (
+          name === "POSTGRES_URL" ||
+          name === "TLS_POSTGRES_DATABASE_URL" ||
+          name === "PGURL" ||
+          name === "PG_URL"
+        ) {
+          return "postgres";
+        }
+
+        // For generic DATABASE_URL and TLS_DATABASE_URL, use protocol detection as fallback
+        if (name === "DATABASE_URL" || name === "TLS_DATABASE_URL") {
+          const colonIndex = envUrl.indexOf(":");
+          if (colonIndex !== -1) {
+            const protocol = envUrl.substring(0, colonIndex);
+            const adapterFromProtocol = getAdapterFromProtocol(protocol);
+            if (adapterFromProtocol) {
+              return adapterFromProtocol;
+            }
+          }
+        }
+
+        //  No adapter inferred from this env var; continue to lower-precedence vars
+        continue;
+      }
+    }
+  }
+
+  // 4. Default to postgres if no explicit adapter or protocol
+  return "postgres";
+}
+
+function getEnvironmentUrlsForAdapter(adapter: Bun.SQL.__internal.Adapter, env: Record<string, string | undefined>) {
+  const urls: (string | undefined)[] = [];
+
+  if (adapter === "postgres") {
+    urls.push(env.POSTGRES_URL, env.DATABASE_URL, env.PGURL, env.PG_URL);
+    // Also check TLS variants
+    urls.push(env.TLS_POSTGRES_DATABASE_URL, env.TLS_DATABASE_URL);
+  } else if (adapter === "mysql") {
+    urls.push(env.MYSQL_URL, env.DATABASE_URL);
+    // Also check TLS variants
+    urls.push(env.TLS_MYSQL_DATABASE_URL, env.TLS_DATABASE_URL);
+  } else if (adapter === "sqlite") {
+    urls.push(env.DATABASE_URL);
+  }
+
+  return urls.filter((url): url is string => typeof url === "string" && url.length > 0);
+}
+
+function getAdapterSpecificDefaults(adapter: Bun.SQL.__internal.Adapter, env: Record<string, string | undefined>) {
+  const defaults: {
+    hostname?: string;
+    port?: number;
+    username?: string;
+    password?: string;
+    database?: string;
+  } = {};
+
+  if (adapter === "postgres") {
+    defaults.hostname = env.PGHOST;
+    defaults.port = env.PGPORT ? Number(env.PGPORT) : undefined;
+    defaults.username = env.PGUSERNAME || env.PGUSER || env.USER || env.USERNAME;
+    defaults.password = env.PGPASSWORD;
+    defaults.database = env.PGDATABASE;
+  } else if (adapter === "mysql") {
+    defaults.hostname = env.MYSQL_HOST;
+    defaults.port = env.MYSQL_PORT ? Number(env.MYSQL_PORT) : undefined;
+    defaults.username = env.MYSQL_USER || env.USER || env.USERNAME;
+    defaults.password = env.MYSQL_PASSWORD;
+    defaults.database = env.MYSQL_DATABASE;
+  } else if (adapter === "sqlite") {
+    // SQLite doesn't use these connection parameters
+  }
+
+  return defaults;
+}
+
 function parseOptions(
   stringOrUrlOrOptions: Bun.SQL.Options | string | URL | undefined,
   definitelyOptionsButMaybeEmpty: Bun.SQL.Options,
 ): Bun.SQL.__internal.DefinedOptions {
   const env = Bun.env;
 
-  let [
-    stringOrUrl = env.POSTGRES_URL || env.DATABASE_URL || env.PGURL || env.PG_URL || env.MYSQL_URL || null,
-    options,
-  ]: [string | URL | null, Bun.SQL.Options] =
-    typeof stringOrUrlOrOptions === "string" || stringOrUrlOrOptions instanceof URL
-      ? [stringOrUrlOrOptions, definitelyOptionsButMaybeEmpty]
-      : stringOrUrlOrOptions
-        ? [null, { ...stringOrUrlOrOptions, ...definitelyOptionsButMaybeEmpty }]
-        : [null, definitelyOptionsButMaybeEmpty];
+  // Step 1: Determine input string/URL and options
+  let inputUrl: string | URL | null = null;
+  let options: Bun.SQL.Options;
 
-  if (options.adapter === undefined && stringOrUrl !== null) {
-    const sqliteUrl = parseDefinitelySqliteUrl(stringOrUrl);
+  if (typeof stringOrUrlOrOptions === "string" || stringOrUrlOrOptions instanceof URL) {
+    inputUrl = stringOrUrlOrOptions;
+    options = definitelyOptionsButMaybeEmpty;
+  } else if (stringOrUrlOrOptions) {
+    options = { ...stringOrUrlOrOptions, ...definitelyOptionsButMaybeEmpty };
+    inputUrl = options.url || null;
+  } else {
+    options = definitelyOptionsButMaybeEmpty;
+  }
 
-    if (sqliteUrl !== null) {
-      const sqliteOptions: Bun.SQL.__internal.DefinedSQLiteOptions = {
-        ...options,
-        adapter: "sqlite",
-        filename: sqliteUrl,
-      };
+  // Step 2: Determine the adapter (without reading environment variables yet)
+  const adapter = determineAdapter(options, inputUrl, env);
 
-      return parseSQLiteOptionsWithQueryParams(sqliteOptions, stringOrUrl);
+  // Step 2.5: Validate adapter matches protocol if URL is provided
+  if (inputUrl) {
+    let urlToValidate: URL | null;
+    if (typeof inputUrl === "string") {
+      // Parse the URL for validation - handle SQLite URLs specially
+      if (parseDefinitelySqliteUrl(inputUrl) !== null) {
+        // Create a fake URL for SQLite validation
+        urlToValidate = new URL("sqlite:///" + encodeURIComponent(inputUrl));
+      } else if (hasProtocol(inputUrl)) {
+        // Only validate URLs that have protocols
+        urlToValidate = parseUrlForAdapter(inputUrl, adapter);
+      } else {
+        // For URLs without protocols, skip validation (could be filenames)
+        urlToValidate = null;
+      }
+    } else {
+      urlToValidate = inputUrl;
+    }
+
+    if (urlToValidate) {
+      validateAdapterProtocolMatch(adapter, urlToValidate, inputUrl);
     }
   }
 
-  if (options.adapter === "sqlite") {
-    let filenameFromOptions = options.filename || stringOrUrl;
+  // Handle SQLite early since it has different logic
+  if (adapter === "sqlite") {
+    return handleSQLiteOptions(options, inputUrl, env);
+  }
 
-    // Parse sqlite:// URLs when adapter is explicitly sqlite
-    if (typeof filenameFromOptions === "string" || filenameFromOptions instanceof URL) {
-      const parsed = parseDefinitelySqliteUrl(filenameFromOptions);
-      if (parsed !== null) {
-        filenameFromOptions = parsed;
+  // Step 3: Get the appropriate URL for this adapter
+  let finalUrl: URL | null = null;
+  let sslMode: SSLMode = SSLMode.disable;
+
+  if (inputUrl) {
+    // User provided a URL directly
+    finalUrl = inputUrl instanceof URL ? inputUrl : parseUrlForAdapter(inputUrl, adapter);
+  } else {
+    // Look for environment URLs appropriate for this adapter
+    // Only use environment URLs if no explicit connection options are provided
+    const hasExplicitConnectionOptions = !!(
+      options.hostname ||
+      options.host ||
+      options.port ||
+      options.username ||
+      options.user ||
+      options.password ||
+      options.pass ||
+      options.database ||
+      options.db
+    );
+
+    if (!hasExplicitConnectionOptions) {
+      const envUrls = getEnvironmentUrlsForAdapter(adapter, env);
+      const envUrl = envUrls[0]; // Get first available URL
+
+      if (envUrl) {
+        // Check if it's a TLS URL that sets SSL mode
+        if (
+          envUrl === env.TLS_POSTGRES_DATABASE_URL ||
+          envUrl === env.TLS_DATABASE_URL ||
+          envUrl === env.TLS_MYSQL_DATABASE_URL
+        ) {
+          sslMode = SSLMode.require;
+        }
+        finalUrl = parseUrlForAdapter(envUrl, adapter);
       }
     }
-
-    const sqliteOptions: Bun.SQL.__internal.DefinedSQLiteOptions = {
-      ...options,
-      adapter: "sqlite",
-      filename: filenameFromOptions || ":memory:",
-    };
-
-    return parseSQLiteOptionsWithQueryParams(sqliteOptions, stringOrUrl);
   }
 
-  if (!stringOrUrl) {
-    const url = options?.url;
-    if (typeof url === "string") {
-      stringOrUrl = defaultToPostgresIfNoProtocol(url);
-    } else if (url instanceof URL) {
-      stringOrUrl = url;
+  // Step 4: Normalize and validate options for the specific adapter
+  return normalizeOptionsForAdapter(adapter, options, finalUrl, env, sslMode);
+}
+
+function handleSQLiteOptions(
+  options: Bun.SQL.Options,
+  inputUrl: string | URL | null,
+  env: Record<string, string | undefined>,
+): Bun.SQL.__internal.DefinedSQLiteOptions {
+  let filename: string | URL | null = options.filename || inputUrl;
+
+  // If no filename provided, check environment
+  if (!filename) {
+    const envUrl = env.DATABASE_URL;
+    if (envUrl) {
+      const parsed = parseDefinitelySqliteUrl(envUrl);
+      if (parsed !== null) {
+        filename = parsed;
+      }
     }
   }
 
+  // Parse SQLite URLs
+  if (typeof filename === "string" || filename instanceof URL) {
+    const parsed = parseDefinitelySqliteUrl(filename);
+    if (parsed !== null) {
+      filename = parsed;
+    }
+  }
+
+  // Special handling for empty strings: should default to :memory:
+  let finalFilename: string;
+  if (filename === null || filename === undefined) {
+    finalFilename = ":memory:";
+  } else if (filename === "") {
+    // Empty string when explicitly passed (like new SQL("", {adapter: "sqlite"})) should be :memory:
+    // This should only be set to ":memory:" if the inputUrl is also an empty string
+    finalFilename = inputUrl === "" ? ":memory:" : "";
+  } else {
+    finalFilename = filename as string;
+  }
+
+  const sqliteOptions: Bun.SQL.__internal.DefinedSQLiteOptions = {
+    ...options,
+    adapter: "sqlite",
+    filename: finalFilename,
+  };
+
+  return parseSQLiteOptionsWithQueryParams(sqliteOptions, inputUrl);
+}
+
+function defaultProtocolForAdapter(adapter: Bun.SQL.__internal.Adapter) {
+  switch (adapter) {
+    case "mariadb":
+    case "mysql2":
+    case "mysql":
+      return "mysql://";
+    case "sqlite":
+      return "sqlite://";
+    case "postgres":
+    case "postgresql":
+    default:
+      return "postgres://";
+  }
+}
+
+function parseUrlForAdapter(urlString: string, adapter: Bun.SQL.__internal.Adapter): URL {
+  if (urlString.startsWith("unix://")) {
+    // Handle unix:// URLs specially
+    return new URL(urlString);
+  }
+
+  // Check if it's a SQLite URL that can't be parsed as a standard URL
+  if (parseDefinitelySqliteUrl(urlString) !== null) {
+    // Create a fake URL for SQLite that won't fail URL parsing
+    return new URL("sqlite:///" + encodeURIComponent(urlString));
+  }
+
+  if (hasProtocol(urlString)) {
+    return new URL(urlString);
+  }
+
+  // Add default protocol for the adapter
+  const defaultProtocol = defaultProtocolForAdapter(adapter);
+  try {
+    return new URL(defaultProtocol + urlString);
+  } catch (error) {
+    try {
+      // can be a "sqlite://file with empty spaces.db"
+      return new URL(encodeURI(defaultProtocol + urlString));
+    } catch {
+      // throw the original error if the URL is invalid
+      throw error;
+    }
+  }
+}
+
+function validateAdapterProtocolMatch(
+  adapter: Bun.SQL.__internal.Adapter,
+  url: URL,
+  originalUrl: string | URL | null = null,
+) {
+  let protocol = url.protocol.replace(":", "");
+
+  if (protocol === "unix") {
+    // Unix sockets are valid for any adapter
+    return;
+  }
+
+  const expectedAdapter = getAdapterFromProtocol(protocol);
+  if (!expectedAdapter) {
+    // Unknown protocol, let it through
+    return;
+  }
+  protocol = getAdapterFromProtocol(protocol) as string;
+
+  // Special handling for SQLite
+  if (protocol === "sqlite" && adapter !== "sqlite") {
+    const urlString = originalUrl ? originalUrl.toString() : url.href;
+    throw new Error(`Invalid URL '${urlString}' for ${adapter}. Did you mean to specify \`{ adapter: "sqlite" }\`?`);
+  }
+
+  // Special handling: postgres:// protocol with sqlite adapter is allowed
+  // (explicit adapter wins over protocol for backward compatibility)
+  if (protocol === "postgres" && adapter === "sqlite") {
+    return;
+  }
+
+  // For network databases (postgres/mysql), validate the match
+  if ((protocol === "postgres" || protocol === "mysql") && expectedAdapter !== adapter) {
+    throw new Error(
+      `Protocol '${protocol}' is not compatible with adapter '${adapter}'. Expected adapter '${expectedAdapter}'.`,
+    );
+  }
+}
+
+function normalizeOptionsForAdapter(
+  adapter: Bun.SQL.__internal.Adapter,
+  options: Bun.SQL.Options,
+  url: URL | null,
+  env: Record<string, string | undefined>,
+  sslMode: SSLMode,
+): Bun.SQL.__internal.DefinedOptions {
+  // Get adapter-specific defaults from environment
+  const envDefaults = getAdapterSpecificDefaults(adapter, env);
+
   let hostname: string | undefined,
     port: number | string | undefined,
     username: string | null | undefined,
     password: string | (() => Bun.MaybePromise<string>) | undefined | null,
     database: string | undefined,
     tls: Bun.TLSOptions | boolean | undefined,
-    url: URL | undefined,
-    query: string,
+    query = "",
     idleTimeout: number | null | undefined,
     connectionTimeout: number | null | undefined,
     maxLifetime: number | null | undefined,
@@ -306,152 +642,71 @@ function parseOptions(
     onclose: ((client: Bun.SQL) => void) | undefined,
     max: number | null | undefined,
     bigint: boolean | undefined,
-    path: string,
-    adapter: Bun.SQL.__internal.Adapter;
+    path = "";
 
   let prepare = true;
-  let sslMode: SSLMode = SSLMode.disable;
 
-  if (!stringOrUrl || (typeof stringOrUrl === "string" && stringOrUrl.length === 0)) {
-    let urlString = env.POSTGRES_URL || env.DATABASE_URL || env.PGURL || env.PG_URL;
-
-    if (!urlString) {
-      urlString = env.TLS_POSTGRES_DATABASE_URL || env.TLS_DATABASE_URL;
-      if (urlString) {
-        sslMode = SSLMode.require;
-      }
-    }
-
-    if (urlString) {
-      // Check if it's a SQLite URL before trying to parse as regular URL
-      const sqliteUrl = parseDefinitelySqliteUrl(urlString);
-      if (sqliteUrl !== null) {
-        const sqliteOptions: Bun.SQL.__internal.DefinedSQLiteOptions = {
-          ...options,
-          adapter: "sqlite",
-          filename: sqliteUrl,
-        };
-        return parseSQLiteOptionsWithQueryParams(sqliteOptions, urlString);
-      }
-
-      url = new URL(urlString);
-    }
-  } else if (stringOrUrl && typeof stringOrUrl === "object") {
-    if (stringOrUrl instanceof URL) {
-      url = stringOrUrl;
-    } else if (options?.url) {
-      const _url = options.url;
-      if (typeof _url === "string") {
-        url = defaultToPostgresIfNoProtocol(_url);
-      } else if (_url && typeof _url === "object" && _url instanceof URL) {
-        url = _url;
-      }
-    }
-    if (options?.tls) {
-      sslMode = SSLMode.require;
-      tls = options.tls;
-    }
-  } else if (typeof stringOrUrl === "string") {
-    try {
-      url = defaultToPostgresIfNoProtocol(stringOrUrl);
-    } catch (e) {
-      throw new Error(`Invalid URL '${stringOrUrl}' for postgres. Did you mean to specify \`{ adapter: "sqlite" }\`?`, {
-        cause: e,
-      });
-    }
-  }
-  query = "";
-  adapter = options.adapter;
+  // Parse URL if provided
   if (url) {
-    ({ hostname, port, username, password, adapter } = options);
-    // object overrides url
-    hostname ||= url.hostname;
-    port ||= url.port;
-    username ||= decodeIfValid(url.username);
-    password ||= decodeIfValid(url.password);
-    adapter ||= url.protocol as Bun.SQL.__internal.Adapter;
-    if (adapter && adapter[adapter.length - 1] === ":") {
-      adapter = adapter.slice(0, -1) as Bun.SQL.__internal.Adapter;
-    }
+    if (url.protocol === "unix:") {
+      // Handle unix domain socket
+      path = url.pathname;
+    } else {
+      hostname = url.hostname;
+      port = url.port;
+      username = decodeIfValid(url.username);
+      password = decodeIfValid(url.password);
+      database = decodeIfValid(url.pathname.slice(1)); // Remove leading /
 
-    const queryObject = url.searchParams.toJSON();
-    for (const key in queryObject) {
-      if (key.toLowerCase() === "sslmode") {
-        sslMode = normalizeSSLMode(queryObject[key]);
-      } else if (key.toLowerCase() === "path") {
-        path = queryObject[key];
-      } else {
-        // this is valid for postgres for other databases it might not be valid
-        // check adapter then implement for other databases
-        // encode string with \0 as finalizer
-        // must be key\0value\0
-        query += `${key}\0${queryObject[key]}\0`;
+      const queryObject = url.searchParams.toJSON();
+      for (const key in queryObject) {
+        if (key.toLowerCase() === "sslmode") {
+          sslMode = normalizeSSLMode(queryObject[key]);
+        } else if (key.toLowerCase() === "path") {
+          path = queryObject[key];
+        } else {
+          query += `${key}\0${queryObject[key]}\0`;
+        }
       }
+      query = query.trim();
     }
-    query = query.trim();
   }
-  if (adapter) {
-    switch (adapter) {
-      case "http":
-      case "https":
-      case "ftp":
-      case "postgres":
-      case "postgresql":
-        adapter = "postgres";
-        break;
-      case "mysql":
-      case "mysql2":
-      case "mariadb":
-        adapter = "mysql";
-        break;
-      case "file":
-      case "sqlite":
-        adapter = "sqlite";
-        break;
-      default:
-        options.adapter satisfies never; // This will type error if we support a new adapter in the future, which will let us know to update this check
-        throw new Error(`Unsupported adapter: ${options.adapter}. Supported adapters: "postgres", "sqlite", "mysql"`);
-    }
+
+  // Apply explicit options (highest precedence) - they override URL parameters
+  hostname = options.hostname || options.host || hostname;
+  port = options.port || port;
+  username = options.username || options.user || username;
+  password = options.password || options.pass || password;
+  database = options.database || options.db || database;
+  path = (options as { path?: string }).path || path;
+
+  // Apply adapter-specific environment defaults (medium precedence)
+  hostname ||= envDefaults.hostname;
+  port ||= envDefaults.port;
+  username ||= envDefaults.username;
+  password ||= envDefaults.password;
+  database ||= envDefaults.database;
+
+  // Apply final defaults (lowest precedence)
+  hostname ||= "localhost";
+  if (port === undefined || port === "") {
+    port = adapter === "mysql" ? 3306 : 5432;
   } else {
-    adapter = "postgres";
+    port = Number(port);
   }
-  options.adapter = adapter;
-  assertIsOptionsOfAdapter(options, adapter);
-  hostname ||= options.hostname || options.host || env.PGHOST || "localhost";
+  username ||= adapter === "mysql" ? "root" : "postgres";
+  database ||= adapter === "mysql" ? "mysql" : username;
+  password ||= "";
 
-  port ||= Number(options.port || env.PGPORT || (adapter === "mysql" ? 3306 : 5432));
-
-  path ||= (options as { path?: string }).path || "";
-
-  if (adapter === "postgres") {
-    // add /.s.PGSQL.${port} if the unix domain socket is listening on that path
-    if (path && Number.isSafeInteger(port) && path?.indexOf("/.s.PGSQL.") === -1) {
-      const pathWithSocket = `${path}/.s.PGSQL.${port}`;
-
-      // Only add the path if it actually exists. It would be better to just
-      // always respect whatever the user passes in, but that would technically
-      // be a breakpoint change at this point.
-      if (require("node:fs").existsSync(pathWithSocket)) {
-        path = pathWithSocket;
-      }
+  // Handle PostgreSQL unix domain socket special case
+  if (adapter === "postgres" && path && Number.isSafeInteger(port) && path.indexOf("/.s.PGSQL.") === -1) {
+    const pathWithSocket = `${path}/.s.PGSQL.${port}`;
+    if (require("node:fs").existsSync(pathWithSocket)) {
+      path = pathWithSocket;
     }
   }
 
-  username ||=
-    options.username ||
-    options.user ||
-    env.PGUSERNAME ||
-    env.PGUSER ||
-    env.USER ||
-    env.USERNAME ||
-    (adapter === "mysql" ? "root" : "postgres"); // default username for mysql is root and for postgres is postgres;
-  database ||=
-    options.database ||
-    options.db ||
-    decodeIfValid((url?.pathname ?? "").slice(1)) ||
-    env.PGDATABASE ||
-    (adapter === "mysql" ? "mysql" : username); // default database;
-  password ||= options.password || options.pass || env.PGPASSWORD || "";
+  // Handle connection parameters
   const connection = options.connection;
   if (connection && $isObject(connection)) {
     for (const key in connection) {
@@ -461,9 +716,15 @@ function parseOptions(
     }
   }
 
+  // Handle TLS
   tls ||= options.tls || options.ssl;
-  max = options.max;
+  if (options?.tls) {
+    sslMode = SSLMode.require;
+    tls = options.tls;
+  }
 
+  // Handle other options
+  max = options.max;
   idleTimeout ??= options.idleTimeout;
   idleTimeout ??= options.idle_timeout;
   connectionTimeout ??= options.connectionTimeout;
@@ -473,7 +734,8 @@ function parseOptions(
   maxLifetime ??= options.maxLifetime;
   maxLifetime ??= options.max_lifetime;
   bigint ??= options.bigint;
-  // we need to explicitly set prepare to false if it is false
+
+  // Handle prepare option
   if (options.prepare === false) {
     if (adapter === "mysql") {
       throw $ERR_INVALID_ARG_VALUE("options.prepare", false, "prepared: false is not supported in MySQL");
@@ -495,6 +757,7 @@ function parseOptions(
     }
   }
 
+  // Validate numeric options
   if (idleTimeout != null) {
     idleTimeout = Number(idleTimeout);
     if (idleTimeout > 2 ** 31 || idleTimeout < 0 || idleTimeout !== idleTimeout) {
@@ -538,19 +801,18 @@ function parseOptions(
     }
   }
 
+  // Handle TLS configuration
   if (sslMode !== SSLMode.disable && !tls?.serverName) {
     if (hostname) {
       tls = { ...tls, serverName: hostname };
-    } else if (tls) {
-      tls = true;
     }
   }
 
   if (tls && sslMode === SSLMode.disable) {
     sslMode = SSLMode.prefer;
   }
-  port = Number(port);
 
+  port = Number(port);
   if (!Number.isSafeInteger(port) || port < 1 || port > 65535) {
     throw $ERR_INVALID_ARG_VALUE("port", port, "must be a non-negative integer between 1 and 65535");
   }
@@ -591,7 +853,11 @@ function parseOptions(
   }
 
   if (path) {
-    if (require("node:fs").existsSync(path)) {
+    // For unix sockets or when explicitly set, always use the path
+    // Don't require existence check for unix sockets since they might not exist yet
+    if (url?.protocol === "unix:" || (options as { path?: string }).path) {
+      ret.path = path;
+    } else if (require("node:fs").existsSync(path)) {
       ret.path = path;
     }
   }

test/js/sql/adapter-env-var-precedence.test.ts

@@ -0,0 +1,567 @@
+import { SQL } from "bun";
+import { describe, expect, test } from "bun:test";
+
+describe("SQL adapter environment variable precedence", () => {
+  const originalEnv = { ...process.env };
+
+  function cleanEnv() {
+    // Clean all SQL-related env vars
+    delete process.env.DATABASE_URL;
+    delete process.env.POSTGRES_URL;
+    delete process.env.PGURL;
+    delete process.env.PG_URL;
+    delete process.env.MYSQL_URL;
+    delete process.env.TLS_DATABASE_URL;
+    delete process.env.TLS_POSTGRES_DATABASE_URL;
+    delete process.env.TLS_MYSQL_DATABASE_URL;
+    delete process.env.PGHOST;
+    delete process.env.PGPORT;
+    delete process.env.PGUSER;
+    delete process.env.PGUSERNAME;
+    delete process.env.PGPASSWORD;
+    delete process.env.PGDATABASE;
+    delete process.env.MYSQL_HOST;
+    delete process.env.MYSQL_PORT;
+    delete process.env.MYSQL_USER;
+    delete process.env.MYSQL_PASSWORD;
+    delete process.env.MYSQL_DATABASE;
+    delete process.env.USER;
+    delete process.env.USERNAME;
+  }
+
+  function restoreEnv() {
+    // Restore original env
+    Object.assign(process.env, originalEnv);
+  }
+
+  test("should not prioritize DATABASE_URL over explicit options (issue #22147)", () => {
+    cleanEnv();
+    process.env.DATABASE_URL = "foo_url";
+
+    const options = new SQL({
+      hostname: "bar_url",
+      username: "postgres",
+      password: "postgres",
+      port: 5432,
+    });
+
+    expect(options.options.hostname).toBe("bar_url");
+    expect(options.options.port).toBe(5432);
+    expect(options.options.username).toBe("postgres");
+    restoreEnv();
+  });
+
+  test("should only read PostgreSQL env vars when adapter is postgres", () => {
+    cleanEnv();
+    process.env.PGHOST = "pg-host";
+    process.env.PGUSER = "pg-user";
+    process.env.PGPASSWORD = "pg-pass";
+    process.env.MYSQL_URL = "mysql://mysql-host/db";
+
+    const options = new SQL({
+      adapter: "postgres",
+    });
+
+    expect(options.options.hostname).toBe("pg-host");
+    expect(options.options.username).toBe("pg-user");
+    expect(options.options.password).toBe("pg-pass");
+    // Should not use MYSQL_URL
+    expect(options.options.hostname).not.toBe("mysql-host");
+    restoreEnv();
+  });
+
+  test("should only read MySQL env vars when adapter is mysql", () => {
+    cleanEnv();
+    process.env.PGHOST = "pg-host";
+    process.env.PGUSER = "pg-user";
+    process.env.MYSQL_URL = "mysql://mysql-host/db";
+
+    const options = new SQL({
+      adapter: "mysql",
+    });
+
+    // Should use MYSQL_URL and not read PostgreSQL env vars
+    expect(options.options.hostname).toBe("mysql-host");
+    expect(options.options.username).not.toBe("pg-user");
+    restoreEnv();
+  });
+
+  test("should infer postgres adapter from postgres:// protocol", () => {
+    cleanEnv();
+    const options = new SQL("postgres://user:pass@host:5432/db");
+    expect(options.options.adapter).toBe("postgres");
+    restoreEnv();
+  });
+
+  test("should infer mysql adapter from mysql:// protocol", () => {
+    cleanEnv();
+    const options = new SQL("mysql://user:pass@host:3306/db");
+    expect(options.options.adapter).toBe("mysql");
+    restoreEnv();
+  });
+
+  test("should default to postgres when no protocol specified", () => {
+    cleanEnv();
+    const options = new SQL("user:pass@host/db");
+    expect(options.options.adapter).toBe("postgres");
+    restoreEnv();
+  });
+
+  test("should support unix:// with explicit adapter", () => {
+    cleanEnv();
+    const options = new SQL("unix:///tmp/mysql.sock", {
+      adapter: "mysql",
+    });
+
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.path).toBe("/tmp/mysql.sock");
+    restoreEnv();
+  });
+
+  test("should validate adapter matches protocol", () => {
+    cleanEnv();
+    expect(() => {
+      new SQL("mysql://host/db", { adapter: "postgres" });
+    }).toThrow(/mysql.*postgres/i);
+    restoreEnv();
+  });
+
+  test("adapter-specific env vars should take precedence over generic ones", () => {
+    cleanEnv();
+    process.env.USER = "generic-user";
+    process.env.PGUSER = "postgres-user";
+
+    const options = new SQL({
+      adapter: "postgres",
+    });
+
+    expect(options.options.username).toBe("postgres-user");
+    restoreEnv();
+  });
+
+  test("should infer mysql adapter from MYSQL_URL env var", () => {
+    cleanEnv();
+    process.env.MYSQL_URL = "mysql://user:pass@host:3306/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(3306);
+    restoreEnv();
+  });
+
+  test("should default to port 3306 for MySQL when no port specified", () => {
+    cleanEnv();
+    process.env.MYSQL_URL = "mysql://user:pass@host/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(3306); // Should default to MySQL port
+    restoreEnv();
+  });
+
+  test("should default to port 3306 for explicit MySQL adapter", () => {
+    cleanEnv();
+    const options = new SQL({
+      adapter: "mysql",
+      hostname: "localhost",
+    });
+
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.port).toBe(3306); // Should default to MySQL port
+    restoreEnv();
+  });
+
+  test("should infer postgres adapter from POSTGRES_URL env var", () => {
+    cleanEnv();
+    process.env.POSTGRES_URL = "postgres://user:pass@host:5432/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("postgres");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(5432);
+    restoreEnv();
+  });
+
+  test("POSTGRES_URL should take precedence over MYSQL_URL", () => {
+    cleanEnv();
+    process.env.POSTGRES_URL = "postgres://pg-host:5432/pgdb";
+    process.env.MYSQL_URL = "mysql://mysql-host:3306/mysqldb";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("postgres");
+    expect(options.options.hostname).toBe("pg-host");
+    expect(options.options.port).toBe(5432);
+    restoreEnv();
+  });
+
+  test("should infer mysql from MYSQL_URL even without protocol", () => {
+    cleanEnv();
+    process.env.MYSQL_URL = "root@localhost:3306/test";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.hostname).toBe("localhost");
+    expect(options.options.port).toBe(3306);
+    expect(options.options.username).toBe("root");
+    restoreEnv();
+  });
+
+  test("should infer postgres from POSTGRES_URL even without protocol", () => {
+    cleanEnv();
+    process.env.POSTGRES_URL = "user@localhost:5432/test";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("postgres");
+    expect(options.options.hostname).toBe("localhost");
+    expect(options.options.port).toBe(5432);
+    expect(options.options.username).toBe("user");
+    restoreEnv();
+  });
+
+  test("environment variable name should override protocol (PGURL with mysql protocol should be postgres)", () => {
+    cleanEnv();
+    process.env.PGURL = "mysql://host:3306/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("postgres");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(3306);
+    restoreEnv();
+  });
+
+  test("environment variable name should override protocol (MYSQL_URL with postgres protocol should be mysql)", () => {
+    cleanEnv();
+    process.env.MYSQL_URL = "postgres://host:5432/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(5432);
+    restoreEnv();
+  });
+  test("should use MySQL-specific environment variables", () => {
+    cleanEnv();
+    process.env.MYSQL_HOST = "mysql-server";
+    process.env.MYSQL_PORT = "3307";
+    process.env.MYSQL_USER = "admin";
+    process.env.MYSQL_PASSWORD = "secret";
+    process.env.MYSQL_DATABASE = "production";
+
+    const options = new SQL({ adapter: "mysql" });
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.hostname).toBe("mysql-server");
+    expect(options.options.port).toBe(3307);
+    expect(options.options.username).toBe("admin");
+    expect(options.options.password).toBe("secret");
+    expect(options.options.database).toBe("production");
+    restoreEnv();
+  });
+
+  test("MySQL-specific env vars should take precedence over generic ones", () => {
+    cleanEnv();
+    process.env.USER = "generic-user";
+    process.env.MYSQL_USER = "mysql-user";
+
+    const options = new SQL({ adapter: "mysql" });
+    expect(options.options.username).toBe("mysql-user");
+    restoreEnv();
+  });
+
+  test("should default to database name 'mysql' for MySQL adapter", () => {
+    cleanEnv();
+
+    const options = new SQL({ adapter: "mysql", hostname: "localhost" });
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.database).toBe("mysql");
+    restoreEnv();
+  });
+
+  test("should default to username as database name for PostgreSQL adapter", () => {
+    cleanEnv();
+
+    const options = new SQL({ adapter: "postgres", hostname: "localhost", username: "testuser" });
+    expect(options.options.adapter).toBe("postgres");
+    expect(options.options.database).toBe("testuser");
+    restoreEnv();
+  });
+
+  test("should infer mysql adapter from TLS_MYSQL_DATABASE_URL", () => {
+    cleanEnv();
+    process.env.TLS_MYSQL_DATABASE_URL = "mysql://user:pass@host:3306/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(3306);
+    expect(options.options.sslMode).toBe(2); // SSLMode.require
+    restoreEnv();
+  });
+
+  test("should infer postgres adapter from TLS_POSTGRES_DATABASE_URL", () => {
+    cleanEnv();
+    process.env.TLS_POSTGRES_DATABASE_URL = "postgres://user:pass@host:5432/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("postgres");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(5432);
+    expect(options.options.sslMode).toBe(2); // SSLMode.require
+    restoreEnv();
+  });
+
+  test("should infer adapter from TLS_DATABASE_URL using protocol", () => {
+    cleanEnv();
+    process.env.TLS_DATABASE_URL = "mysql://user:pass@host:3306/db";
+
+    const options = new SQL();
+    expect(options.options.adapter).toBe("mysql");
+    expect(options.options.hostname).toBe("host");
+    expect(options.options.port).toBe(3306);
+    expect(options.options.sslMode).toBe(2); // SSLMode.require
+    restoreEnv();
+  });
+
+  describe("Adapter-Protocol Validation", () => {
+    test("should work with explicit adapter and URL without protocol", () => {
+      cleanEnv();
+
+      const options = new SQL("user:pass@host:3306/db", { adapter: "mysql" });
+      expect(options.options.adapter).toBe("mysql");
+      expect(options.options.hostname).toBe("host");
+      expect(options.options.port).toBe(3306);
+      restoreEnv();
+    });
+
+    test("should work with explicit adapter and matching protocol", () => {
+      cleanEnv();
+
+      const options = new SQL("mysql://user:pass@host:3306/db", { adapter: "mysql" });
+      expect(options.options.adapter).toBe("mysql");
+      expect(options.options.hostname).toBe("host");
+      expect(options.options.port).toBe(3306);
+      restoreEnv();
+    });
+
+    test("should throw error when adapter conflicts with protocol (mysql adapter with postgres protocol)", () => {
+      cleanEnv();
+
+      expect(() => {
+        new SQL("postgres://user:pass@host:5432/db", { adapter: "mysql" });
+      }).toThrow(/Protocol 'postgres' is not compatible with adapter 'mysql'/);
+      restoreEnv();
+    });
+
+    test("should throw error when adapter conflicts with protocol (postgres adapter with mysql protocol)", () => {
+      cleanEnv();
+
+      expect(() => {
+        new SQL("mysql://user:pass@host:3306/db", { adapter: "postgres" });
+      }).toThrow(/Protocol 'mysql' is not compatible with adapter 'postgres'/);
+      restoreEnv();
+    });
+
+    test("should throw error when sqlite adapter used with mysql protocol", () => {
+      cleanEnv();
+
+      expect(() => {
+        new SQL("mysql://user:pass@host:3306/db", { adapter: "sqlite" });
+      }).toThrow(/Protocol 'mysql' is not compatible with adapter 'sqlite'/);
+      restoreEnv();
+    });
+
+    test("should throw error when mysql adapter used with postgres protocol", () => {
+      cleanEnv();
+
+      expect(() => {
+        new SQL("postgres://user:pass@host:5432/db", { adapter: "mysql" });
+      }).toThrow(/Protocol 'postgres' is not compatible with adapter 'mysql'/);
+      restoreEnv();
+    });
+
+    test("should work with unix:// protocol and explicit adapter", () => {
+      cleanEnv();
+
+      const options = new SQL("unix:///tmp/mysql.sock", { adapter: "mysql" });
+      expect(options.options.adapter).toBe("mysql");
+      expect(options.options.path).toBe("/tmp/mysql.sock");
+      restoreEnv();
+    });
+
+    test("should work with sqlite:// protocol and sqlite adapter", () => {
+      cleanEnv();
+
+      const options = new SQL("sqlite:///tmp/test.db", { adapter: "sqlite" });
+      expect(options.options.adapter).toBe("sqlite");
+      expect(options.options.filename).toBe("/tmp/test.db");
+      restoreEnv();
+    });
+
+    describe("Explicit options override URL parameters", () => {
+      test("explicit hostname should override URL hostname", () => {
+        cleanEnv();
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          hostname: "explicithost",
+        });
+
+        expect(options.options.hostname).toBe("explicithost");
+        expect(options.options.port).toBe(1234); // URL port should remain
+        expect(options.options.username).toBe("urluser"); // URL username should remain
+        expect(options.options.database).toBe("urldb"); // URL database should remain
+        restoreEnv();
+      });
+
+      test("explicit port should override URL port", () => {
+        cleanEnv();
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          port: 5432,
+        });
+
+        expect(options.options.hostname).toBe("urlhost"); // URL hostname should remain
+        expect(options.options.port).toBe(5432);
+        expect(options.options.username).toBe("urluser"); // URL username should remain
+        expect(options.options.database).toBe("urldb"); // URL database should remain
+        restoreEnv();
+      });
+
+      test("explicit username should override URL username", () => {
+        cleanEnv();
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          username: "explicituser",
+        });
+
+        expect(options.options.hostname).toBe("urlhost"); // URL hostname should remain
+        expect(options.options.port).toBe(1234); // URL port should remain
+        expect(options.options.username).toBe("explicituser");
+        expect(options.options.database).toBe("urldb"); // URL database should remain
+        restoreEnv();
+      });
+
+      test("explicit password should override URL password", () => {
+        cleanEnv();
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          password: "explicitpass",
+        });
+
+        expect(options.options.hostname).toBe("urlhost"); // URL hostname should remain
+        expect(options.options.port).toBe(1234); // URL port should remain
+        expect(options.options.username).toBe("urluser"); // URL username should remain
+        expect(options.options.password).toBe("explicitpass");
+        expect(options.options.database).toBe("urldb"); // URL database should remain
+        restoreEnv();
+      });
+
+      test("explicit database should override URL database", () => {
+        cleanEnv();
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          database: "explicitdb",
+        });
+
+        expect(options.options.hostname).toBe("urlhost"); // URL hostname should remain
+        expect(options.options.port).toBe(1234); // URL port should remain
+        expect(options.options.username).toBe("urluser"); // URL username should remain
+        expect(options.options.database).toBe("explicitdb");
+        restoreEnv();
+      });
+
+      test("multiple explicit options should override corresponding URL parameters", () => {
+        cleanEnv();
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          hostname: "explicithost",
+          port: 5432,
+          username: "explicituser",
+          password: "explicitpass",
+          database: "explicitdb",
+        });
+
+        expect(options.options.hostname).toBe("explicithost");
+        expect(options.options.port).toBe(5432);
+        expect(options.options.username).toBe("explicituser");
+        expect(options.options.password).toBe("explicitpass");
+        expect(options.options.database).toBe("explicitdb");
+        restoreEnv();
+      });
+
+      test("should work with MySQL URLs and explicit options", () => {
+        cleanEnv();
+
+        const options = new SQL("mysql://urluser:urlpass@urlhost:3306/urldb", {
+          hostname: "explicithost",
+          port: 3307,
+          username: "explicituser",
+        });
+
+        expect(options.options.adapter).toBe("mysql");
+        expect(options.options.hostname).toBe("explicithost");
+        expect(options.options.port).toBe(3307);
+        expect(options.options.username).toBe("explicituser");
+        expect(options.options.password).toBe("urlpass"); // URL password should remain
+        expect(options.options.database).toBe("urldb"); // URL database should remain
+        restoreEnv();
+      });
+
+      test("should work with alternative option names (user, pass, db, host)", () => {
+        cleanEnv();
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          host: "explicithost",
+          user: "explicituser",
+          pass: "explicitpass",
+          db: "explicitdb",
+        });
+
+        expect(options.options.hostname).toBe("explicithost");
+        expect(options.options.username).toBe("explicituser");
+        expect(options.options.password).toBe("explicitpass");
+        expect(options.options.database).toBe("explicitdb");
+        restoreEnv();
+      });
+
+      test("explicit options should override URL even when environment variables are present", () => {
+        cleanEnv();
+        process.env.PGHOST = "envhost";
+        process.env.PGPORT = "9999";
+        process.env.PGUSER = "envuser";
+
+        const options = new SQL("postgres://urluser:urlpass@urlhost:1234/urldb", {
+          hostname: "explicithost",
+          port: 5432,
+          username: "explicituser",
+        });
+
+        expect(options.options.hostname).toBe("explicithost");
+        expect(options.options.port).toBe(5432);
+        expect(options.options.username).toBe("explicituser");
+        expect(options.options.password).toBe("urlpass"); // URL password should remain since no explicit password
+        expect(options.options.database).toBe("urldb"); // URL database should remain
+        restoreEnv();
+      });
+
+      test("explicit options should have higher precedence than environment-specific variables", () => {
+        cleanEnv();
+        process.env.MYSQL_HOST = "mysqlhost";
+        process.env.MYSQL_USER = "mysqluser";
+        process.env.MYSQL_PASSWORD = "mysqlpass";
+
+        const options = new SQL("mysql://urluser:urlpass@urlhost:3306/urldb", {
+          hostname: "explicithost",
+          username: "explicituser",
+        });
+
+        expect(options.options.adapter).toBe("mysql");
+        expect(options.options.hostname).toBe("explicithost");
+        expect(options.options.username).toBe("explicituser");
+        expect(options.options.password).toBe("urlpass"); // URL password (not env)
+        expect(options.options.database).toBe("urldb"); // URL database should remain
+        restoreEnv();
+      });
+    });
+  });
+});