Quick Start

BareSQL is one program, baresql. BareSQL Audit is its audit and diff commands, and BareSQL Portable is lint, compare and rules. Get it from the download page and check that it runs:

baresql version

Audit a PostgreSQL Database

Give baresql audit a connection string, the same as for psql. Leave the password out of it: BareSQL reads PGPASSWORD and ~/.pgpass like every PostgreSQL tool.

export PGPASSWORD=...
baresql audit "postgres://auditor@db.example.com/shop?sslmode=verify-full"

It prints a short summary. This one is for the sample shop database BareSQL is tested on:

BareSQL Audit of baresql_fx_shop (PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)) as postgres
7 logins, 7 of them can read personal data; 10 columns hold personal data.
Findings: 2 high, 4 medium, 0 low
  high   public-reads-personal        shop.newsletter
  high   secdef-no-search-path        shop.export_customers()
  medium default-grants-to-public     shop_owner
  medium read-all-data                shop_reporting
  medium secdef-executable            shop.count_orders()
  medium secdef-executable            shop.export_customers()
Wrote baresql-report/report.html and baresql-report/audit.json

Open baresql-report/report.html in a browser. It starts with the findings. Then come the logins that can read personal data and the columns that hold it, and after those a section for each login, with every column it can read and the route that gets it there. The sample reports show what that looks like for four databases.

Any login can run the audit, since the catalogs it reads are visible to every role. To check values for personal data, it needs read access to the tables, so a superuser or a member of pg_read_all_data sees the most. Whatever the audit could not see is listed under “Not seen”. It works in a read-only transaction and never calls a function you wrote. Values it samples are never written anywhere; --no-sample turns sampling off.

Watch for Access That Grows

Keep audit.json. The next audit can be compared with it:

baresql diff last-month.json baresql-report/audit.json

baresql diff lists what changed between the two, starting with the reach that grew. To fail a scheduled job when any login reaches more than before, run the audit with a baseline:

baresql audit --baseline last-month.json "postgres://auditor@db.example.com/shop"

It exits 1 when access grew. The quarterly review case study walks through one.

Check SQL for Other Engines

baresql lint reads SQL files and points out what fails or answers differently on SQLite, PostgreSQL, MySQL and MariaDB. Give it the schema as well, so it knows the column types:

baresql lint schema.sql queries.sql

This is lint on the shop’s queries:

shop.sql:6:8: warning: || joins text on SQLite and PostgreSQL. On MySQL and MariaDB it means OR, so 'a' || 'b' is 0. [pipes-concat]
    name || ' (' || stock || ')'
    Use CONCAT(a, b), which all four engines have (SQLite from version 3.44). CONCAT treats NULL differently on different engines; see concat-null.
shop.sql:7:8: warning: Dividing an integer by an integer drops the fraction on SQLite and PostgreSQL; MySQL and MariaDB keep it. [integer-division]
    SUM(stock) / COUNT(*)
    Multiply one side by 1.0 for the fraction and round it to the places you need (see decimal-division): ROUND(7 * 1.0 / 2, 2) is 3.5 on every engine. For a whole number on every engine, take the remainder off first: (7 - 7 % 2) / 2.
shop.sql:9:37: error: PostgreSQL has no = between a boolean and a number. [boolean-as-number]
    active = 1
    Compare and insert TRUE and FALSE (active = TRUE, or simply WHERE active). All four engines accept them.
shop.sql:10:8: error: A selected column that is neither grouped nor aggregated takes its value from any row of the group on SQLite and MariaDB; PostgreSQL and MySQL refuse the query. [ungrouped-column]
    name
    Add the column to GROUP BY, or say which value you want with an aggregate such as MAX(b).
shop.sql:10:14: error: A selected column that is neither grouped nor aggregated takes its value from any row of the group on SQLite and MariaDB; PostgreSQL and MySQL refuse the query. [ungrouped-column]
    price
    Add the column to GROUP BY, or say which value you want with an aggregate such as MAX(b).
shop.sql:12:8: error: IFNULL() works on SQLite, MySQL and MariaDB; PostgreSQL refuses it. [function-not-everywhere]
    IFNULL(stock, 0)
    Use COALESCE(a, b).

6 problems: 4 errors, 2 warnings. 4 notes not shown (differences that depend on the data); --all shows them.

Each problem says what to write instead. If only two engines matter, say so, and the differences between the others drop out:

baresql lint --engines sqlite,postgres schema.sql queries.sql

A comment -- baresql:ignore integer-division on the line of a problem, or the line before it, silences that rule there.

Run the Same Script on Real Engines

baresql compare runs a script on two or more engines, statement by statement, and shows every answer that differs:

export PGPASSWORD=... MYSQL_PWD=...
baresql compare --sqlite --postgres postgres://dev@localhost/scratch \
    --mysql mysql://dev@localhost:3306/ shop.sql

Each engine works in a throwaway space: a transaction rolled back at the end on PostgreSQL, a scratch database dropped at the end on MySQL and MariaDB, and a temporary file on SQLite. Point it at servers meant for testing, with logins that may do no more than the script needs. The BareSQL Portable page has an example of its output.

Look Up a Difference

baresql rules lists the 58 differences BareSQL knows. baresql rules integer-division shows one, with the answer each engine gave:

integer-division (warning): Integer division

Dividing an integer by an integer drops the fraction on SQLite and PostgreSQL;
MySQL and MariaDB keep it.

SQLite and PostgreSQL give an integer result when both sides of / are
integers, so 7 / 2 is 3. MySQL and MariaDB always divide as decimals and
answer 3.5000. Averages written as SUM(x) / COUNT(*) are where this usually
bites: the sum of an integer column is an integer on SQLite and PostgreSQL, so
the average loses its fraction there.

Example:
    SELECT 7 / 2

    SQLite 3.45.1        3
    PostgreSQL 16.15     3
    MySQL 8.0.46         3.5000
    MariaDB 10.11.14     3.5000

The same on every engine:
    SELECT ROUND(7 * 1.0 / 2, 2)

    SQLite 3.45.1        3.5
    PostgreSQL 16.15     3.50
    MySQL 8.0.46         3.50
    MariaDB 10.11.14     3.50

Multiply one side by 1.0 for the fraction and round it to the places you need
(see decimal-division): ROUND(7 * 1.0 / 2, 2) is 3.5 on every engine. For a
whole number on every engine, take the remainder off first: (7 - 7 % 2) / 2.

Each difference also has its own post here, listed on the BareSQL Portable page.

Use It in CI

The exit code says what happened. Lint exits 1 when it reports a problem, and compare when answers differ. baresql diff and an audit with --baseline exit 1 when access grew. Every command exits 2 when it could not run, and 0 otherwise. A CI step can fail on portability problems like this:

- name: Check the SQL
  run: baresql lint --engines sqlite,postgres db/schema.sql db/queries/*.sql

The reference lists every command and flag.