Databases (.lnc)

The connection

A connection is a .lnc file (LightNote Connection) inside the workspace itself — versionable in Git and portable between machines. The folder's connections appear in the Connections panel of the sidebar (the database icon), each with a status dot (green connected, gray disconnected, red with an error) and, under it, the scripts that belong to it. Create one with New → New DB connection or with the panel's button; opening a .lnc from the file tree also leads to the panel. Below the list is the object tree of the selected connection. The connection's context menu has Connect/Disconnect, New script, SQL console and Edit connection.

Folder without a connection: the empty Connections panel offers New connection… and Open a database file…, and lists the SQLite and DuckDB database files already in the folder, each with a Connect. The database is recognized by the file's content, not by its extension (a .db can be either one); one click creates the connection (.lnc) next to the file, pointing to it by name — the two travel together — and connects right away.

Scripts: a script is a regular .sql file and opens in the code tab, like any file — with history, change bar and Save version. At the start of the tab's toolbar are the connection selector and the Execute button; the results appear below the editor on the first run (or beside it: Execute → Results beside). The script's origin connection is stored in the .lnc, and when you reopen the script it already points to it. Choosing another connection in the selector applies only to that tab; to change the origin, use Always use "…" for this script. A script without an origin opens with No connection, even if the folder has a single connection: no script opens pointed at a database you did not choose. The SQL console is a tab without a file, already linked to the connection. A result's Analysis area is saved next to the script (query.sql.analysis.json), one per query: the file keeps the charts, the pivot tables and the SQL of each one — never the data.

One session per tab: each script tab opens its own session with the database — a long query in one tab does not block the others or the object tree, and a Commit in one tab does not commit what another did. Temporary tables and SET commands apply only in the tab where they ran. If the server limits the number of connections, uncheck One session per tab in Edit connection. SQLite and DuckDB files always share the same session (in SQLite a write locks the whole file). The panel shows how many sessions each connection has open.

Drivers: SQLite and DuckDB (file) native; PostgreSQL, MySQL/MariaDB, SQL Server and generic via ODBC; native Oracle (no ODBC). SQL Server uses Microsoft's msodbcsql driver (install it on the machine) and offers Windows Authentication or user/password, plus the channel encryption options (Encrypt / trust the certificate). For Oracle, set the Oracle Instant Client folder in Settings → Database — it is loaded at runtime, with nothing to install. The Oracle connection has four modes: Service Name (host/port/service), SID (host/port/SID), TNS Name (alias from the tnsnames.ora in the client folder) and Full TNS descriptor (pasted into a text field).

Password: check Store password to encrypt it inside the .lnc itself (portable crypto, opens on another machine); unchecked, the password is not saved and is asked on every connection. Encryption uses a per-workspace Passphrase, set in Help → Configure Passphrase; keep it safe — without it the password cannot be opened anywhere else. The Passphrase is cached only on the local machine (it never goes to Git).

Check Connect on open so the connection opens by itself when you open one of its scripts. The automatic connection is silent: if the password must be typed (ask mode, or encrypted without the Passphrase cached on the machine), the script opens disconnected, without interrupting you.

Production without fear: in Edit connection, the Environment field says which database this is — Development, Staging or Production —, and its colour shows on the connection selector of the .sql tab, on a strip at the top of the tab and on a badge in the Connections panel. On a production connection, whatever changes the database (INSERT, UPDATE, DELETE, CREATE, DROP, procedures…) first shows what is about to run and asks for confirmation, with an UPDATE/DELETE without WHERE highlighted; the default button is Cancel, and saving the grid's changes asks the same way. Tick Read-only for the connection to refuse anything that changes the database — in the script, in the grid and in a table's data — before the statement leaves LightNote: the grid stops offering editing and, on SQLite, the file opens read-only. Anything LightNote cannot tell only reads (a BEGIN … END block, a DECLARE) counts as a write.

SSH tunnel: for a database behind an SSH server (bastion), pick in SSH tunnel an SSH connection (.lns) from the workspace — the same one the remote terminal uses, with user, key or encrypted password. Host and Port then become the database's as seen from the SSH server (usually localhost). On connect, LightNote opens the tunnel and reaches the database through it; disconnecting closes the tunnel. It only starts when you connect (never when the file is opened), the .lns must be inside the workspace, and ssh options that run commands (ProxyCommand, LocalCommand, -F) are refused — -J is fine. Only options on an allowlist get through (-J, -i, -4/-6, -C and -o with connection, cipher and authentication keys): -A, -R, -I and -E are refused as well. A tunnelled connection is not opened by the AI (MCP), by lnote or by Blocks, which would connect directly.

Server information: on connect, the app asks the database what it reports about itself — the version, the current database and schema, the user, the size, the encoding, the collation and the time zone, and whether the server accepts writes (a replica does not). The version shows in the connection's tooltip, and the rest in Server information…, in the connection's context menu, with a button to copy it. A detail the server does not report — older version, missing privilege — is left out, without an error. The version goes along when you ask the AI to explain an error or analyze a plan: the syntax it can suggest depends on it.

Foreign keys on SQLite: SQLite only enforces the declared foreign keys — and ON DELETE CASCADE — when the connection asks, and the app's connections do: deleting a row that others reference, or inserting a reference to a record that does not exist, is refused, and CASCADE deletes the children as the schema says. The Enforce foreign keys box, in Edit connection…, turns this off — only for a bulk load out of order. Turning it on does not check what is already in the file: Server information… counts, per table, the broken references that already exist (rows pointing to a record that does not exist), written by a tool that did not enforce them.

Running and reading the result

Run: Ctrl+Enter runs the statement under the cursor and Alt+X runs the whole script; results appear paginated, each in a Result tab with an action bar at the bottom: Fetch next block, Freeze the result, Total count, change the row Limit, Refresh (re-runs), View/Copy SQL, Export... and Generate SQL from the selected row. What counts as one statement comes from the text itself: a BEGIN … END block and a $$ … $$ body stay whole, and a line holding only / (Oracle) or GO (SQL Server) also separates.

Writing SQL with help: Ctrl+Space completes by context: after FROM, JOIN, UPDATE or INTO come the database's tables (the list opens by itself when you type the space), and after alias. come that table's columns (it opens by itself when you type the dot) — even before the FROM is written, and without expanding anything in the tree: LightNote reads the database catalog in the background, and the columns only when the text reaches them. In a script with several statements, the one Ctrl+Enter will run is highlighted. When a statement fails, it is marked in the editor, the name the database pointed at (the table or column that does not exist, the spot of the syntax error) gets a wavy underline, the cursor goes there and the message appears when you rest the mouse over it. Ctrl+click or F12 on a table name opens its data; F4, its properties. Format (Shift+Alt+F) tidies the SQL with no tool installed: one clause per line, the SELECT items one per line when there are several, AND/OR and subqueries indented. It changes only spaces and the case of keywords, never a name, a text or a comment, and leaves statements that are not queries (CREATE, BEGIN … END blocks) as they are.

Freeze the result: reads the whole result from the database in a single pass and keeps it in a local table (on disk, if it does not fit in memory). From then on the grid scrolls without querying the database, the row count becomes exact (no need for Count the total), clicking a header sorts (clicking again reverses it; a third click returns to the order the database delivered), and exporting also reads from the local copy. Sorting by the header works up to 100,000 rows; above that it is declined, with the reason in the header tooltip, because every page would have to re-sort the whole copy and walking the grid would take minutes again. A result that already arrived whole in the first page sorts without freezing: the rows are already in memory. What cannot be sorted is the first part of a result still being paged — the header would be claiming an order that only holds for what is on screen.

The chart and the pivot table start covering the whole result — including on databases where the chart did not exist. If freezing stops early (you clicked Stop or the cap was reached), the rows stay local but the chart and the pivot table remain on the server: summing over part of the result would give a wrong number — and sorting the grid sorts only the rows fetched, not the whole result.

Refresh unfreezes and runs the query again. Whether or not you freeze, the pages that already went through the grid are kept locally: scrolling back to one of them does not query the database again. Only one freeze runs at a time: with two results open, clicking Freeze the result on the other one is refused with the reason, instead of interrupting the one in progress — to stop that one, use Stop in its own tab.

Result grid: each query that returns rows gets a tab named after its table (the full SQL is in the tab's tooltip). A new run replaces those tabs, except the pinned ones — right-click the tab, Pin the result —, which stay for comparing; the × closes a result. Next to the tabs, the run summary says how many rows came back and how long it took, or the error in red; clicking it opens Messages. The footer repeats the rows and the time and, while the result is still partial, offers Fetch next block and Count the total. The bar above the grid groups the rest: Refresh, Freeze, Limit, the Analysis menu, Value, Columns and Export (with Copy SQL, View SQL and Generate SQL); in a narrow window the buttons lose their text before disappearing. The header shows each column's type (text, number, date…) and marks the table's key columns with a key; resting the mouse on it spells the type out. Dragging the header reorders the columns, and Columns (or right-clicking the header) hides, shows and fits the width to the content — the choice survives Refresh, Limit and sorting.

Cells: a NULL is shown in faded italics, distinct from an empty string, and numbers are right-aligned; when you select several cells, the footer shows the selection count and, when they are numeric, their sum and average. Value (or Show value in panel from the context menu) opens, next to the grid, the full value of the selected cell, uncut, with Format to pretty-print JSON/XML for display only; the panel's Record button swaps the value for the whole row transposed — one column per line, with the name next to the value — which follows the cursor (also via View the whole record in the cell's menu). In a cell's menu, Filter by this value / Exclude this value re-run the query with a WHERE on the cell, and Go to referenced record follows the column's foreign key and queries the parent table by the value.

Search and filter the grid: Ctrl+F with the focus on the grid goes to the Search the grid field, which shows only the rows that contain the text in any column — with the focus in the editor, Ctrl+F is still the SQL search. The funnel next to it opens the Advanced filter (per-column conditions, with AND/OR), and filtered columns get a funnel in the header; clicking a chart bar, a pivot table cell or a frequent value in Quality becomes a selection, a tag above the grid removed with its ×. The footer says how many rows are left out of how many. Searching and filtering cover the whole result, in a local copy: with a result that arrived whole it is immediate; with only the first part, the field is greyed out with the reason — filtering what is on screen would hide what is in the next part — and Freeze unlocks it. With a search or filter active, the chart, the pivot table and Quality describe the same subset as the grid. None of this changes the query: to cut on the database, use Filter by this value or write the WHERE.

Chart and pivot table: where the maths happens. The Analysis menu holds the Chart, the Pivot table, Quality and the Analysis area, and under Analyze over the choice between Local data and Whole table (on the server), checked on the one in force. When the result fitted entirely in the first page, the default is local: LightNote aggregates over the result already on screen, without going back to the database on every change of axis, aggregate or bucket — and, because the local engine is DuckDB, the chart also becomes available on Oracle, SQL Server, PostgreSQL and MySQL, where its SQL does not run on the server. When the result came back truncated, the local option is unavailable with the reason: aggregating over a sample would give a wrong number, and warning about the cut would protect nobody, because what you read in a chart is the height of the bar. The server option aggregates over the entire query, with one query per adjustment, and is unavailable while the grid has a search or filter (the subset exists only here). Numeric columns that the database delivers as text — the case of Oracle's NUMBER — are recognised as numbers; identifiers with a leading zero, such as 01310, stay text. On Oracle, SQL Server, PostgreSQL and MySQL, with the result still partial, Chart is greyed out with the reason, and lights up when you freeze the result.

Data quality of the result. The Quality item in the Analysis menu opens, next to the grid, the same per-column profile as the file tables. It is computed by DuckDB: on the local copy when the result came in full (or was frozen), or in the database itself when the database is DuckDB. On Oracle, SQL Server, PostgreSQL and the others, with the result still partial, the panel says why it cannot describe it and offers Freeze the result right there; if freezing stops at the cap, it recommends narrowing the query — a profile of one part would describe a different table. Clicking a frequent value filters the grid (it is a selection, like the search); to cut on the database, use Filter by this value in the cell menu.

How much data the analysis used. Above the chart, the pivot table, the quality panel and the Analysis area, a line says whether the calculation used all the data — with the row count, counted automatically on the local copy — or was computed in the database, over the whole query; there the count is the Count rows link, because counting runs the query again. When freezing stopped before the end and the grid has a search or filter, the analysis can only cover what was fetched, and the line says Part of the data, in the warning tone. The same sentence goes into the caption of the Analysis area report.

Data snapshot: the analysis of a saved script keeps the cards next to it, not the data — seeing it again needs the database. The Data snapshot button in the Analysis area saves the whole result to a Parquet file next to the script, along with the cards; opening that file shows the same analysis without a connection, over the data of that moment. It is an explicit gesture and the window warns first: the file is a copy of the database data, and it goes along if the folder goes to Git or is shared. It is only available with the whole result here — with only the first part, freeze it first (the button's tooltip says so).

Execution plan: the Execution plan item of the Execute menu asks for the plan of the statement under the cursor (SQLite, DuckDB, PostgreSQL and MySQL) and shows it in the Plan tab: the tree of steps, with the heaviest one highlighted and a sentence saying what it means — in PostgreSQL and MySQL, the step with the highest own cost (its cost minus that of the steps inside it), with its share of the total; in DuckDB, the one that produces the most rows; in SQLite, which gives no cost, reading a whole table. When the step reads the whole table in a database that has indexes, the sentence reminds you that an index on the filtered column usually avoids it (in DuckDB, which is columnar, reading the whole table is normal and the advice does not appear). The query's alias (FROM orders o) is replaced by the table name. View as text shows the same tree indented, and Copy the plan copies it. The numbers are the planner's estimate: the query is not run. A typed EXPLAIN also opens in the Plan tab when LightNote recognizes the answer; what it does not recognize stays in the grid. In databases that do not expose the plan in a single statement (SQL Server, Oracle) the item is disabled, with the reason in the tooltip.

Analyze with AI: the highlight says which step is heavy; the Analyze with AI button on the Plan tab asks why the planner chose it and what to change. The query, the plan and the schema of the tables it reads are sent — with the indexes that already exist, so the AI does not suggest creating one that is already there. The values are removed before sending (quoted text and numbers become ?; comments are dropped), and See what will be sent shows exactly what goes. The suggestion opens in the editor as a new script, and nothing is run.

The schema

A table's data: double-click a table in the schema tree to open its tab on the Data sub-tab. Above the grid there are two fields, WHERE and ORDER BY, applied with Enter — and both go to the database, so they apply to the whole table, not just to the rows on screen. Clicking a column header also sorts in the database (ascending, descending, unsorted) and fills in the ORDER BY; Filter by this value in a cell's menu adds the condition to the WHERE, visible and editable. The tab has its own session with the database, like a script tab. Opening the table through Properties does not query the data: the query only runs when the Data sub-tab is shown. With ORDER BY empty, rows come in primary key order (the field shows which): with no order at all, the database does not guarantee the same sequence from one page to the next, and scrolling could repeat or skip rows. When you sort by a header, the key breaks ties between equal values.

Editing the data: in the grid of a table that has a primary key (or a unique key on NOT NULL columns), double-click a cell, press F2 or start typing. The editor matches the column's type: a date with a calendar, true/false in a list, a number field that only takes numbers, and JSON checked when you confirm. Deleted text becomes empty text, not NULL: NULL is a gesture of its own (Set to NULL in the cell's menu, or the NULL button in the value panel, which also edits long texts). Insert adds a new row — whatever stays as default gets the database's default value —, Duplicate row copies the selected one except for the key, and Delete marks rows for deletion (again, unmarks them). None of this goes to the database right away: the changes stay pending, painted in the grid (amber for changed, green for new, struck-through red for deleted), and a bar above it counts them. Review… shows the SQL that will run and copies it as a script; Save writes everything in a single transaction, checking that each UPDATE and DELETE reaches exactly one row — if a row was changed or deleted by another session after being read, or if any statement fails, nothing is written and the changes stay in the grid; Discard undoes them all. Reloading the grid, changing the filter or closing the tab with pending changes asks first.

When the grid doesn't edit, it says why — in the buttons' tooltips and in a bar when you try to edit. A table with no primary key and no unique key on NOT NULL columns stays read-only: without the key there's no way to know which row each edit would change. In a script tab, the result can also be edited when the query is a SELECT from a single table that lists its columns (or uses SELECT *) and includes the key; a join, a subquery, a computed or renamed column, DISTINCT, GROUP BY and UNION leave the result read-only. Binary columns aren't edited in the grid. And with a transaction open in the tab (autocommit off) holding pending statements, commit or roll back before saving: saving the grid would take them along.

Object properties: press F4 on a table in the schema tree (or choose Properties from the context menu) to open its tab straight on the structure; next to Data there are four sub-tabs: Columns (#, name, type, nullability, default, and a key icon on primary-key columns), Keys (primary, foreign, unique and check constraints; double-clicking a foreign key opens the referenced table), Indexes and DDL — the object's CREATE TABLE, ready to copy, save as .sql or open in an editable tab. When the database does not provide ready-made DDL (PostgreSQL and SQL Server have no such command), LightNote reconstructs it from the metadata — the same happens when the DDL command is denied for lack of permission — and says so clearly in the tab. If not even the column catalog can be read (no permission, or the catalog is unavailable), there is nothing to reconstruct from: the tab says so instead of sitting silently empty. The table's context menu also offers Copy DDL without opening the tab.

Object tree: in the Connections panel, below the list, the tree of the selected connection separates Tables from Views (each with its icon) and marks with a key icon the columns that are part of the primary key. The tree also shows Procedures and functions, Triggers and Sequences (where the database has them); double-clicking a procedure, function or trigger opens its source code in a SQL console of the connection. Next to each table is its approximate row count, and its comment is in the tooltip — it is the estimate the database keeps, not a COUNT(*). System schemas (SYS, pg_catalog…) are hidden; the item at the end of the list shows them. The filter box sifts what is already loaded right away and, from 2 letters on, also searches the database: what nobody expanded shows up in the In the database folder at the top. Inside a script, Ctrl+Alt+O (Go to table…, in the connection selector) opens the search from the keyboard, and F12 on a table name opens its tab.

Refresh metadata. After a CREATE, ALTER, DROP or RENAME runs in a script, the tree, completion and the table properties re-read tables, columns and types on their own, a few seconds later — without collapsing what was open in the tree. With autocommit off, the re-read waits for the Commit (or the Rollback), because until then the change does not exist for the other sessions. When the structure changed elsewhere (another program, a migration), use the Refresh metadata button on the script toolbar, in the connection menu, next to the tree filter, or the Refresh of the table properties.

Exploring the schema: Right-click a table to Insert SELECT into the editor, Preview data, Count rows, Copy qualified name or open the Generate SQL submenu → INSERT/UPDATE/MERGE: a screen lists the table's columns so you can check Include and Key, shows a live preview and drops the command into the editor (MERGE comes out in ANSI; if the driver does not support it, a warning appears). Right on the results grid, the Generate SQL button on the bottom bar builds an INSERT/UPDATE/DELETE pre-filled with the values of the selected row (available when the query is a single-table SELECT). The table and column names you browse in the tree also start showing up in the autocomplete (Ctrl+Space) of the connection's .sql editors, with no extra database queries.

ER diagram: in the context menu of the Tables folder, ER diagram of the tables draws all of them; in a table's menu, ER diagram (this table and the ones it references) draws it and the tables its foreign keys point to. The result is a note in Data Dictionary with a mermaid erDiagram fence — columns with type, PK/FK, and each foreign key as a relationship (optional when the column accepts NULL, one-to-one when the key is the primary key itself). It is text in the note: it is versioned in Git and edited like any diagram. Above 40 tables the rest are left out, and the note says how many.

Import a file into a table: Import file into a table… (in the connection's menu and in the Tables folder's) and Import file into this table… (in a table's) bring a CSV, TSV or Excel spreadsheet into the database — the way back from Export result. The window shows a preview with the type read for each column: a type only holds if all cells match (integer without leading zero — 01310 is a code and stays text —, decimal with a dot or with a comma, ISO date, true/false); a dd/mm/yyyy date stays text, because the month may come first. An empty cell becomes NULL. The separator is sniffed as in the table view. Create the table uses the file name; Append to table first checks that the file's columns exist in it. Everything goes in one transaction: if a row fails, nothing stays — and the table created by this import is dropped. The guards apply here too: a read-only connection refuses, and a production one asks first. On a DuckDB connection the CSV is read directly by the database, with no row limit; on the others the limit is 100,000 rows.

Export, variables and transactions

Export results: right-click the results grid and choose Export results... to write CSV (delimiter, quote character, always quote, header row, NULL text), JSON Lines or SQL (INSERT statements with a table name), with an optional row limit. The export walks the whole query in streaming mode (paginated re-fetch), not just what is visible on screen.

Result in today's note: in the grid's Export menu, Add to today's note takes the table as it is on screen — the visible columns, in grid order, with the search and filter applied — and, right below, the query in a sql fence: the note keeps where the number came from. NULL shows as NULL, numbers are right-aligned, and an italic line says when the table is not the whole result (the first 200 rows, or only the ones already loaded).

Script variables: use @set name = value on a line of the script to define a variable — the @ prefix keeps it away from the database (it does not collide with the real SET of Postgres/DuckDB/SQL Server). :name references are replaced before running (outside strings and comments; the ::type cast and binds such as :1 stay intact); a :name without a value opens a prompt before running. The Variables… item of the Execute menu opens the screen of the variables already defined (edit/add/remove).

Parameters: when you run a statement with :name — or with ?, ?1, ?2, except on PostgreSQL, where ? is a JSON operator —, the Query parameters window shows the statement and all its parameters already filled with the last value used, and each one's list keeps the previous values: running again with another value is changing one field. The value goes into the SQL as is, so text goes in single quotes ('abc'). What the script defines with @set is not asked. Uncheck Ask on every run for the tab to use the last values without asking; to change them later, use Variables….

Query history: the History… item of the Execute menu opens a window with the last statements run successfully on this connection, in the columns SQL / When / Duration / Rows. Select one to Re-run, Insert in editor or Copy (double-click inserts), or use Clear history. It is kept only on the local machine (it does not go to Git or to the .lnc).

History of every connection: in the history window, tick All connections to see the queries of every database, newest first, with the connection column, and search them all with the filter field. Re-run only applies to this connection's queries; another's are inserted into the editor, so you run them on the right database.

Transactions: the Autocommit option (on by default) is in the Execute menu. With it off, changes stay pending until you commit them (Commit) or discard them (Rollback), and the tab's toolbar shows Transaction · N pending, with both commands. Closing the tab, switching connections or disconnecting with an open transaction asks what to do.