Tooling en SQL

No es ningún secreto que la programación con SQL no dispone de un tooling a su altura. Si bien es cierto que tenemos una excelente herramienta como SSMS, por el contrario, si estás acostumbrado al desarrollo en C#, javascript o cualquier otro lenguaje, tarde o temprano querrás imitar el escenario al que estás acostumbrado, esto es, linting, guías de estilo de código, integración continua, etc.

Lógicamente, si usamos SQL sólo de forma esporádica o siempre embebido en C#, el asunto sería distinto, pero si te toca lidiar con un montón de procedimientos almacenados, funciones y demás objetos de SQL Server, en mi opinión se le empiezan a ver las costuras al flujo de desarrollo.

Con sinceridad, tampoco es que la solución propuesta aquí sea la panacea, pero es un intento de poner algo de orden y evitar que lo inevitable suceda. Además, personalmente descarto (por ahora) el uso de herramientas profesionales como redgate, ApexSQL o devart, pero por otro lado, son una incuestionable fuente de inspiración para intentar copiar o imitar (que suena mejor) algunas funcionalidades que pueden ser muy útiles.

El propósito del post será llegar a tener un linter, un formateador y un git hook, todo ello en el contexto de código T-SQL.

Para el linter (y habiendo descartado SonarQube, porque el analizador de T-SQL es de pago) nos quedan pocas opciones. Lo mejor que hemos encontrado es una extensión de VSCode llamada tsqlint. Dentro de VSCode funciona muy bien, aunque la única pega que le pongo es que el fichero de configuración donde activamos o desactivamos reglas (.tsqllintrc) tiene que estar en %USERPROFILE%, eso hace un poco más difícil el “clonar el repo y listo”, pero bueno, no habiendo más oferta, doy las gracias por esta herramienta.

Aunque tengamos el linter en VSCode, parece mejor opción instalar globalmente el paquete vía npm con npm install tsqllint -g. Ahora podemos crear el fichero .tsqllintrc con tsqllint --init. Y lo más importante, ahora podemos usar el linter desde línea de comandos (lo que abre la puerta a integración continua).

Para los ejemplos voy a usar pubs (que aunque me dicen es antigua, es sencilla y hasta un niño la entiende).

Probemos con una instrucción sencilla:

SELECT j.job_id,
    j.job_desc,
    e.emp_id,
    e.fname,
    e.lname,
    e.job_id,
    e.hire_date
FROM jobs j
INNER JOIN employee e ON j.job_id = e.job_id
ORDER BY j.job_desc,
    e.fname,
    e.lname

Y al pasar el linter

C:\Temp\test>tsqllint example.sql
example.sql(8,7): error schema-qualify : Object name not schema qualified.
example.sql(9,13): error schema-qualify : Object name not schema qualified.
example.sql(12,16): warning semicolon-termination : Statement not terminated with semicolon.

Linted 1 files in 0,2461205 seconds

2 Errors.
1 Warnings

tssqlint permite crear plugins de una forma sencilla y además usa C#, así que no hay excusa.

Nuestro plugin lo que hará es buscar la palabra clave UNION y luego ya darlo como warning, error o no reportarlo, según queramos, eso ya es configuración de cada uno.

El código del plugin (lo relevante) es este:

    public class MyTSqlLintUnionPlugin : IPlugin
    {
        public void PerformAction(IPluginContext context, IReporter reporter)
        {
            string line;
            var lineNumber = 0;

            var reader = new StreamReader(File.OpenRead(context.FilePath));

            while ((line = reader.ReadLine()) != null)
            {
                lineNumber++;
                var regex = new Regex(@"\s*UNION\s*", RegexOptions.IgnoreCase);
                var match = regex.Match(line);
                if (match.Success)
                {
                    var column = match.Index;
                    reporter.ReportViolation(new RuleViolation(
                        context.FilePath,
                        "union",
                        "UNION is forbidden",
                        lineNumber,
                        column,
                        RuleViolationSeverity.Warning));
                }
            }
        }
    }

Si ahora pasamos el linter a este SQL, ¡tenemos un UNION no permitido!

SELECT 1
UNION
SELECT 2;

C:\Temp\test>tsqllint sergio.sql
Loaded plugin: 'MyTSqlLintPlugin.MyTSqlLintUnionPlugin', Version: '1.0.0.0'
sergio.sql(2,0): warning union : UNION is forbidden.

Linted 1 files in 0,2557418 seconds

0 Errors.
1 Warnings

Visto esto, el único pero que le saco a la extensibilidad (o a mi implementación, mejor dicho) es el uso de expresiones regulares, lo mismo hoy son una solución, pero ya se sabe que mañana…

En cualquier caso, aceptamos barco, y además integrar tsqllint en SSMS también es posible como una herramienta externa.

Llegados aquí, ¡tenemos linter!, pero si queremos ir un paso más allá y en vez de trabajar con expresiones regulares, usar el parser que usa el propio SQL Server (para por ejemplo, dar un warning en un DELETE sin WHERE, que es una de las reglas de SonarQube, por cierto) podemos hacerlo con Microsoft.SqlServer.DacFx.x64.

var parser = new TSql140Parser(true);
            using (var reader = new StringReader(@"
SELECT * FROM authors;
DELETE jobs --WHERE min_lvl > 10
;"))
            {
                var result = parser.Parse(reader, out var errors) as TSqlScript;
                foreach (TSqlBatch batch in result.Batches)
                {
                    foreach (var statement in batch.Statements.OfType<DeleteStatement>())
                    {
                        if (statement.DeleteSpecification.WhereClause == null)
                        {
                            Console.WriteLine("¡DELETE sin WHERE, insensato!");
                        }
                        break;
                    }
                }
            }

Si probamos a habilitar o deshabilitar el comentario con la condición del DELETE vemos que funciona. Lógicamente, habría que hacer una herramienta de línea de comandos con un código de salida para poder usarla en la build, pero por ahora con saber que se puede hacer (y como un señor con un parser potente) parece suficiente y posibilita un futuro lleno de oportunidades.

En cuanto al formateo de SQL, es decir, una guía de estilo, tampoco hay mucho donde elegir. Si ves lo que tiene por ejemplo devart, https://sql-format.com/, es flipante, sin más, por eso viven de ello. Pero alternativas open-source no hay muchas, o al menos no hay muchas que admitan cierto grado de configuración. En nuestro caso, no hemos decantado por http://poorsql.com/, tiene muy buena pinta, pero la verdad es que el proyecto parece un poco abandonado. No obstante, es lo mejor que hemos encontrado y no parece que formatear SQL sea algo que esté cambiando todos los días.

Cabe mencionar que hemos descartado el formateo que hace el plugin oficial de SQL Server en VSCode, por eso, porque sólo lo hace en VSCode. Tiene competencia con este otro, pero ninguno es invocable por línea de comandos (o al menos yo no sé).

Con el tema del formateo tengo la sensación de que vamos a tener que contentarnos con lo que haya y no pedir peras al olmo.

Usando poorsql pasaríamos de esto (escrito por una persona con muy mala baba, la verdad sea dicha).

IF (1 =1    ) BEGIN
 SELECT j.job_id, j.job_desc,
  e.emp_id,  e.fname,
  e.lname,e.job_id,
  e.hire_date
 FROM jobs j  INNER 
 JOIN employee e ON 
 j.job_id = e.job_id
 ORDER BY j.job_desc,
    e.fname, e.lname END

a esto otro

IF (1 = 1)
BEGIN
 SELECT j.job_id,
  j.job_desc,
  e.emp_id,
  e.fname,
  e.lname,
  e.job_id,
  e.hire_date
 FROM jobs j
 INNER JOIN employee e ON j.job_id = e.job_id
 ORDER BY j.job_desc,
  e.fname,
  e.lname
END

Aunque podríamos integrarlo como comando en VSCode y en SSMS como herramienta externa, para finalmente poner todo en orden, usaremos node y los scripts de npm (que además sería equivalente a lo que sucedería en el servidor de integración continua).

Para el git hook de pre-commit vamos a usar husky.

"husky": {
    "hooks": {
      "pre-commit": "node index.js ./**/*.sql"
    }
  }

Lo que pasará ahora es que cada vez que hagamos un commit, se va a ejecutar el fichero index.js con un glob pattern para todos los ficheros .sql.

index.js es el encargado de ver en que ficheros .sql ha habido cambios y están en zona de staging, para entonces pasar el linter y el formateador a cada uno de ellos y si algo falla, abortar el commit.

const { execSync } = require("child_process");
const glob = require("glob");

// 0: C:\Program Files\nodejs\node.exe
// 1: C:\Temp\test\index.js
var args = process.argv.slice(2);
args.forEach(function(arg) {
  const files = getFiles(arg);
  files.forEach(function(file) {
    if (!formatFile(file)) {
      process.exit(1);
    }
    if (!lintFile(file)) {
      process.exit(1);
    }
    stageFile(file);
  });
});

function getFiles(pattern) {
  var files = glob.sync(pattern);
  var stagedFiles = getStagedFiles();
  return files.filter(file => stagedFiles.includes(file));
}

function formatFile(file) {
  console.log(`formatting ${file}`);
  return executeCommand(`npm run sqlformat -- -f ${file} -g ${file}`);
}

function lintFile(file) {
  console.log(`linting ${file}`);
  return executeCommand(`npm run tsqllint -- ${file}`);
}

function stageFile(file) {
  console.log(`adding ${file} to index`);
  return executeCommand(`git add ${file}`);
}

function executeCommand(command) {
  try {
    execSync(command);
    return true;
  } catch (error) {
    console.log(`stderr ${error.stdout.toString()}`);
    return false;
  }
}

function getStagedFiles() {
  var output = execSync(
    `git diff --cached --name-only --diff-filter=ADMR`
  ).toString();
  return output;
}

La verdad es que explicado por escrito parecen muchas operaciones a seguir, pero bien montado puede suponer una pequeña mejora en el flujo de trabajo con SQL (aquí tienes el código subido en github).En cualquier caso, me frustra que algo tan transversal como SQL no tenga (al menos yo no he encontrado nada) otros mecanismos para poder mirar de igual a igual a otras tecnologías en cuanto a tooling se refiere.