New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

@linked-db/linked-ql

Package Overview
Dependencies
Maintainers
0
Versions
125
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@linked-db/linked-ql - npm Package Compare versions

Comparing version 0.7.9 to 0.8.0

resources/linked-ql-gh-mobile.png
resources/linked-ql-gh-mobile2.png
resources/linked-ql-gh1.png
resources/linked-ql.gif
resources/linked-ql.png
resources/linked-ql2.png
resources/linked-ql3.png
resources/linked-ql4.png
resources/linked-ql4b.png
resources/linked-ql5.png
src/lang/schema/tbl/constraints/AutoIncrementConstraint.js
src/lang/schema/tbl/constraints/DefaultConstraint.js
src/lang/schema/tbl/constraints/ExpressionConstraint.js
src/lang/schema/tbl/constraints/ForeignKey.js
src/lang/schema/tbl/constraints/IdentityConstraint.js
src/lang/schema/tbl/constraints/NotNullConstraint.js
src/lang/schema/tbl/constraints/NullConstraint.js
src/lang/schema/tbl/constraints/OnUpdateClause.js
src/lang/schema/tbl/constraints/PrimaryKey.js
src/lang/schema/tbl/constraints/UniqueKey.js

2

package.json

@@ -14,3 +14,3 @@ {

"icon": "https://webqit.io/icon.svg",
"version": "0.7.9",
"version": "0.8.0",
"license": "MIT",

@@ -17,0 +17,0 @@ "repository": {

1636

README.md

@@ -7,6 +7,20 @@ # Linked QL

❄️ **_Save the overhead working with SQL and structured data - from the time and effort spent figuring out relational queries to the labour managing schemas!_** Try a modern, simplistic take on SQL and databases in general!
<picture>
<source media="(max-width: 799px)" srcset="https://github.com/linked-db/linked-ql/blob/master/resources/linked-ql-gh-mobile.png?raw=true">
<source media="(min-width: 800px)" srcset="https://github.com/linked-db/linked-ql/blob/master/resources/linked-ql-gh1.png?raw=true">
<img src="https://github.com/linked-db/linked-ql/blob/master/resources/linked-ql-gh1.png?raw=true" alt="Linked QL Banner" width="100%">
</picture>
Linked QL is a DB query client that simplfies how you interact with your database and manage your schemas.
<br>
> **_Save the overhead working with SQL and structured data - from the time and effort spent figuring out relational queries to the labour managing schemas!_** Try a modern, simplistic take on SQL and databases in general!
<div align="center">
[Follow](https://x.com/LinkedQL) • [Sponsor](https://github.com/sponsors/ox-harris)
</div>
Linked QL is a database query client that simplfies how you interact with your database and manage your schemas.
💥 Takes the ORM and friends out of the way and let's you just write SQL, but SQL that you will actually enjoy. (Linked QL extends standard SQL with [new syntax sugars](#introducing-magic-paths) that let you write relational queries in less than half the code and without a single JOIN clause in most cases.)

@@ -22,8 +36,8 @@

+ [Getting Started](#getting-started)
+ [Magic Paths](#introducing-magic-paths)
+ [Auto-Versioning](#introducing-auto-versioning)
+ [Schema-as-Code](#re-introducing-schema-as-code-with-schemajson)
+ [API](#linked-ql-api)
+ [CLI](#linked-ql-cli)
+ 🎲 [Getting Started](#getting-started)
+ 🎯 [Magic Paths](#introducing-magic-paths)
+ ⛱ [Auto-Versioning](#introducing-auto-versioning)
+ 🧩 [Schema-as-Code](#re-introducing-schema-as-code-with-schemajson)
+ [The Linked QL API](https://github.com/linked-db/linked-ql/wiki/API) ✈️
+ [The Linked QL CLI](https://github.com/linked-db/linked-ql/wiki/CLI) ✈️

@@ -99,3 +113,3 @@ ## Getting Started

Other APIs are covered just ahead in the [API](#linked-ql-api) section.
Other APIs are covered right in [The Linked QL API](#linked-ql-api) section.

@@ -176,3 +190,3 @@ ## Introducing Magic Paths

*(Now pivot tables get a new syntax sugar!)*
*(Now pivot/junction/link tables get an easier way!)*

@@ -285,9 +299,22 @@ ## Introducing Auto-Versioning

<details><summary>Explore the structure</summary>
> <details><summary>See the database schema spec</summary>
>
> ```ts
> interface DatabaseSchemaSpec {
> name: string;
> tables: TableSchemaSpec[];
> }
> ```
>
> </details>
An example table object:
<details><summary>Explore the structure further</summary>
-------------
└ *Table schema example:*
```js
{
"name": "users",
"name": "users", // or something like ['db1', 'tbl1'] which would translate to db1.tbl1
"columns": [], // Column objects (minimum of 1)

@@ -299,4 +326,19 @@ "constraints": [], // Constraint objects

An example column object:
> <details><summary>See the table schema spec</summary>
>
> ```ts
> interface TableSchemaSpec {
> name: string | string[];
> columns: ColumnSchemaSpec[];
> constraints: TableConstraintSchemaType[];
> indexes: IndexSchemaSpec[];
> }
> ```
>
> </details>
-------------
└ *Column schema examples:*
```js

@@ -311,5 +353,2 @@ {

<details>
<summary>More column examples</summary>
```js

@@ -347,17 +386,37 @@ {

```
</details>
An example constraint object:
> <details><summary>See the column schema spec</summary>
>
> ```ts
> interface ColumnSchemaSpec {
> name: string;
> type: string | Array;
> primaryKey?: boolean | PrimaryKeySchemaSpec;
> [ foreignKey | references ]?: ForeignKeySchemaSpec;
> uniqueKey?: boolean | UniqueKeySchemaSpec;
> check?: string | CheckConstraintSchemaSpec;
> default?: string | DefaultConstraintSchemaSpec;
> expression?: string | ExpressionConstraintSchemaSpec;
> identity: boolean | IdentityConstraintSchemaSpec;
> onUpdate?: string | OnUpdateConstraintSchemaSpec; // (MySQL-specific attributes)
> autoIncrement?: boolean; // (MySQL-specific attributes)
> notNull?: boolean;
> null?: boolean;
> }
> ```
>
> </details>
---------------
└ *Table constraint examples:*
```js
{
"type": "PRIMARY_KEY",
"name": "constraint_name",
"columns": ["id"],
"name": "constraint_name"
}
```
<details>
<summary>More constraint examples</summary>
```js

@@ -374,7 +433,7 @@ {

"columns": ["parent"],
"targetTable": "users",
"targetTable": "users", // or something like ['db1', 'tbl1'] which would translate to db1.tbl1
"targetColumns": ["id"],
"matchRull": "full",
"updateRule": "cascade",
"deleteRule": "restrict"
"deleteRule": { rule: "restrict", "columns": ["col1", "col2"] }
}

@@ -389,6 +448,85 @@ ```

```
</details>
An example index object:
> <details><summary>See the table constraint schema spec</summary>
>
> ```ts
> type TableConstraintSchemaType = TablePrimaryKeySchemaSpec | TableForeignKeySchemaSpec | TableUniqueKeySchemaSpec | TableCheckConstraintSchemaSpec;
> ```
>
> ```ts
> interface TablePrimaryKeySchemaSpec extends PrimaryKeySchemaSpec {
> type: 'PRIMARY_KEY';
> columns: string[];
> }
>
> interface TableForeignKeySchemaSpec extends ForeignKeySchemaSpec {
> type: 'FOREIGN_KEY';
> columns: string[];
> }
>
> interface TableUniqueKeySchemaSpec extends UniqueKeySchemaSpec {
> type: 'UNIQUE_KEY';
> columns: string[];
> }
>
> interface TableCheckConstraintSchemaSpec extends CheckConstraintSchemaSpec {
> type: 'CHECK';
> }
> ```
>
> </details>
> <details><summary>See the column constraint schema spec</summary>
>
> ```ts
> type ColumnConstraintSchemaType = PrimaryKeySchemaSpec | ForeignKeySchemaSpec | UniqueKeySchemaSpec | CheckConstraintSchemaSpec | DefaultConstraintSchemaSpec | ExpressionConstraintSchemaSpec | IdentityConstraintSchemaSpec | OnUpdateConstraintSchemaSpec;
> ```
>
> ```ts
> interface PrimaryKeySchemaSpec {
> name: string;
> }
>
> interface ForeignKeySchemaSpec {
> name?: string;
> targetTable: string | string[];
> targetColumns: string[];
> matchRule?: string;
> updateRule?: string | { rule: string, columns: string[] };
> deleteRule?: string | { rule: string, columns: string[] };
> }
>
> interface UniqueKeySchemaSpec {
> name: string;
> }
>
> interface CheckConstraintSchemaSpec {
> name?: string;
> expr: string;
> }
>
> interface DefaultConstraintSchemaSpec {
> expr: string;
> }
>
> interface ExpressionConstraintSchemaSpec {
> expr: string;
> stored: boolean;
> }
>
> interface IdentityConstraintSchemaSpec {
> always: boolean;
> }
>
> interface OnUpdateConstraintSchemaSpec {
> expr: string;
> }
> ```
>
> </details>
-------------
└ *Index schema examples:*
```js

@@ -401,5 +539,2 @@ {

<details>
<summary>More index examples</summary>
```js

@@ -411,4 +546,15 @@ {

```
</details>
> <details><summary>See the index schema spec</summary>
>
> ```ts
> interface IndexSchemaSpec {
> name?: string;
> type: string;
> columns: string[];
> }
> ```
>
> </details>
</details>

@@ -427,3 +573,3 @@

1. Make a directory within your application for database concerns. Linked QL will look in `./database`, but you will be able to point to your preferred location when running Linked QL commands.
1. Make a directory within your application for database concerns. Linked QL will want to look in `./database`, but you will be able to point to your preferred location when running Linked QL commands.

@@ -448,3 +594,3 @@ 2. Have a `driver.js` file in that directory that has a *default export* function that returns a Linked QL instance. This will be imported and used by Linked QL to interact with your database. This could look something like:

3. Have your DB structure defined in a `schema.json` file in that directory. (See [`schema.json`](#schemajson) above for a guide.)
3. Have your DB structure defined in a `schema.json` (or `schema.yml`) file in that directory. (See [`schema.json`](#schemajson) above for a guide.)

@@ -464,1425 +610,23 @@ You can always extend your schema with new objects, and you can always drop objects or edit them in-place. For an existing database, table, column, constraint, or index, **names may be changed, but not in-place!** A "rename" operation is done with the addition of a temporary `$name` attribute:

+ Use `linkedql migrate` to walk through your staged local changes and interactively perform a migration on your database.
+ Use `linkedql migrate` to walk through your staged local changes and interactively perform a migration against your database.
+ Use `linkedql rollback` to walk through the latest savepoint at each database and interactively perform a rollback.
+ Use `linkedql leaderboard` to just view the latest savepoint at each database.
*(More details in the [Linked QL CLI](#linked-ql-cli) section.)*
*(More details in the [Linked QL CLI](https://github.com/linked-db/linked-ql/wiki/CLI#linked-ql-cli) section.)*
## Linked QL API
🐣 *And that's a wrap!*
Here's for a quick overview of the Linked QL API:
## DOCS
Here we talk about the `client.query()` method in more detail along with other Linked QL APIs that essentially let you do the same things possible with `client.query()`, but this time, programmatically.
If you've made it this far, you may want to go here next:
As an example of one of these APIs, a `CREATE DATABASE` operation...
+ The Linked QL API: [in the wiki](https://github.com/linked-db/linked-ql/wiki/API) ✈️
+ The Linked QL CLI: [in the wiki](https://github.com/linked-db/linked-ql/wiki/CLI) ✈️
```js
const savepoint = await client.query('CREATE DATABASE IF NOT EXISTS database_1');
```
could be programmatically achieved as:
```js
const savepoint = await client.createDatabase('database_1', { ifNotExists: true });
```
That said, while the `createDatabase()` method is associated with the base `Client` object, the different programmatic query APIs in Linked QL are actually organized into three hierarchical scopes:
+ the top-level scope (represented by the [`Client`](#the-client-api) interface), featuring methods such as: `createDatabase()`, `alterDatabase()`, `dropDatabase()`, `hasDatabase()`, `describeDatabase()`
+ the database-level scope (represented by a certain [`Database`](#the-database-api) interface), featuring methods such as: `createTable()`, `alterTable()`, `dropTable()`, `hasTable()`, `describeTable()`
+ the table-level scope (represented by a certain [`Table`](#the-table-api) interface), featuring methods such as: `select()`, `insert()`, `upsert()`, `update()`, `delete()`
Each object provides a way to narrow in to the next; e.g. from the top-level scope to a database scope...
```js
const database_1 = client.database('database_1');
```
and from there to a table scope:
```js
const table_1 = database.table('table_1');
```
These APIs at play would look something like:
```js
// Create database "database_1"
await client.createDatabase('database_1', { ifNotExists: true });
```
```js
// Enter "database_1" and create a table
await client.database('database_1').createTable({
name: 'table_1', columns: [
{ name: 'column_1', type: 'int', identity: true, primaryKey: true },
{ name: 'column_2', type: 'varchar' },
{ name: 'column_3', type: 'varchar' },
]
});
```
```js
// Enter "table_1" and insert data
await client.database('database_1').table('table_1').insert({
column_2: 'Column 2 test content',
column_3: 'Column 3 test content',
});
```
These APIs and more are what's covered in this section.
Click on a definition to expand.
------------
### The `Client` API
*Client* is the top-level object for the individual database kinds in Linked QL. Each instance implements the following interface:
<details><summary>See content</summary>
+ [`client.query()`](#clientquery)
+ [`client.createDatabase()`](#clientcreatedatabase)
+ [`client.alterDatabase()`](#clientalterdatabase)
+ [`client.dropDatabase()`](#clientdropdatabase)
+ [`client.hasDatabase()`](#clienthasdatabase)
+ [`client.describeDatabase()`](#clientdescribedatabase)
+ [`client.databases()`](#clientdatabases)
+ [`client.database()`](#clientdatabase)
</details>
#### `client.query()`:
<details><summary>
Run any SQL query.
<pre><code>client.query(sql: string, options?: Options): Promise&lt;Savepoint | Array&lt;object&gt;&gt;</code></pre></summary>
⚙️ Spec:
+ `sql` (string): an SQL query.
+ `options` (Options, *optional*): extra parameters for the query.
+ Return value: a [`Savepoint`](#the-savepoint-api) instance when it's a `CREATE`, `ALTER`, or `DROP` operation, an array (the result set) when it's a `SELECT` query or when it's an `INSERT`, `UPDATE`, or `DELETE` operation that has a `RETURNING` clause, but a number (indicating number of rows processed by the query) when not having a `RETURNING` clause. Null in all other cases.
⚽️ Usage:
Run a `CREATE`, `ALTER`, or `DROP` operation and get back a reference to the savepoint associated with it:
```js
const savepoint = await client.query('ALTER TABLE users RENAME TO accounts');
console.log(savepoint.versionTag); // number
await savepoint.rollback(); // true
```
or a SELECT query, and get back a result set:
```js
const rows = await client.query('SELECT * FROM users WHERE id = 4');
console.log(rows.length); // 1
```
or an `INSERT`, `UPDATE`, or `DELETE` operation with a `RETURNING` clause, and get back a result set:
```js
const rows = await client.query('INSERT INTO users SET name = \'John Doe\' RETURNING id');
console.log(rows.length); // 1
```
or an `INSERT`, `UPDATE`, or `DELETE` operation without a `RETURNING` clause, and ge back a number indicating the number of rows processed by the query:
```js
const rowCount = await client.query('INSERT INTO users SET name = \'John Doe\'');
console.log(rowCount); // 1
```
Some additional parameters via `options`:
+ `dialect` (string, *optional*): the SQL dialect in use: `postgres` (the default) or `mysql`. (Details soon as to how this is treated by Linked QL.)
```js
// Unlock certain dialect-specific clauses or conventions
const rows = await client.query('ALTER TABLE users MODIFY COLUMN id int', { dialect: 'mysql' });
```
+ `values` ((string | number | boolean | null | Date | object | any[])[], *optional*): the values for parameters in the query.
```js
const rows = await client.query('SELECT * FROM users WHERE id = $1', { values: [4] });
```
+ `description` (string, *optional*): the description for a `CREATE`, `ALTER`, `DROP` operation and for the underlying savepoint they create.
```js
const savepoint = await client.query('DROP DATABASE test', { description: 'No longer needed' });
```
+ `noCreateSavepoint` (boolean, *optional*): a flag to disable savepoint creation on a `CREATE`, `ALTER`, `DROP` operation.
```js
await client.query('DROP DATABASE test', { noCreateSavepoint: true });
```
</details>
#### `client.createDatabase()`:
<details><summary>
Dynamically run a <code>CREATE DATABASE</code> operation.
<pre><code>client.createDatabase(createSpec: string | { name: string, tables?: Array }, options?: Options): Promise&lt;Savepoint&gt;</code></pre></summary>
⚙️ Spec:
+ `createSpec` (string | { name: string, tables?: Array }): the database name, or an object corresponding to the *database* object in [schema.json](#schemajson).
+ `options` (Options, *optional*): as described in [`query()`](#clientquery).
+ Return value: a [`Savepoint`](#the-savepoint-api) instance.
⚽️ Usage:
Specify database by name:
```js
const savepoint = await client.createDatabase('database_1', { description: 'Just testing database creation' });
```
or by a schema object, with an optional list of tables to be created along with it. (Each listed table corresponding to the *table* object in [schema.json](#schemajson).):
```js
const savepoint = await client.createDatabase({
name: 'database_1',
tables: [{
name: 'table_1'
columns: [{ name: 'column_1', type: 'int' }, { name: 'column_2', type: 'time' }]
}]
}, { description: 'Just testing database creation' });
```
Some additional parameters via `options`:
+ `ifNotExists` (boolean, *optional*): a flag to conditionally create the database.
```js
const savepoint = await client.createDatabase('database_1', { ifNotExists: true, description: 'Just testing database creation' });
```
</details>
#### `client.alterDatabase()`:
<details><summary>
Dynamically run an <code>ALTER DATABASE</code> operation.
<pre><code>client.alterDatabase(alterSpec: string | { name: string, tables?: string[] }, callback: (schema: DatabaseSchema) => void, options?: Options): Promise&lt;Savepoint&gt;</code></pre></summary>
⚙️ Spec:
+ `alterSpec` (string | { name: string, tables?: string[] }): the database name, or an object with the name and, optionally, a list of tables to be altered along with it.
+ `callback` ((schema: DatabaseSchema) => void): a function that is called with the requested schema. This can be async. Received object is a [`DatabaseSchema`](#the-database-apischema) instance.
+ `options` (Options, *optional*): as described in [`query()`](#clientquery).
+ Return value: a [`Savepoint`](#the-savepoint-api) instance.
⚽️ Usage:
Specify database by name:
```js
const savepoint = await client.alterDatabase('database_1', schema => {
schema.name('database_1_new');
}, { description: 'Renaming for testing purposes' });
```
or by an object, with an optional list of tables to be altered along with it:
```js
const savepoint = await client.alterDatabase({ name: 'database_1', tables: ['table_1'] }, schema => {
schema.name('database_1_new');
schema.table('table_1').column('column_1').name('column_1_new');
schema.table('table_1').column('column_2').type('varchar');
}, { description: 'Renaming for testing purposes' });
```
</details>
#### `client.dropDatabase()`:
<details><summary>
Dynamically run a <code>DROP DATABASE</code> operation.
<pre><code>client.dropDatabase(dbName: string, options?: Options): Promise&lt;Savepoint&gt;</code></pre></summary>
⚙️ Spec:
+ `dbName` (string): the database name.
+ `options` (Options, *optional*): as described in [`query()`](#clientquery).
+ Return value: a [`Savepoint`](#the-savepoint-api) instance.
⚽️ Usage:
```js
const savepoint = await client.dropDatabase('database_1', { description: 'Dropping for testing purposes' });
```
Some additional parameters via `options`:
+ `ifExists` (boolean, *optional*): a flag to conditionally drop the database.
```js
const savepoint = await client.dropDatabase('database_1', { ifExists: true, description: 'Dropping for testing purposes' });
```
+ `cascade` (boolean, *optional*): a flag to force-drop the database along with its dependents.
```js
const savepoint = await client.dropDatabase('database_1', { cascade: true, description: 'Dropping for testing purposes' });
```
</details>
#### `client.hasDatabase()`:
<details><summary>
Check if a database exists.
<pre><code>client.hasDatabase(dbName: string): Promise&lt;Boolean&gt;</code></pre></summary>
⚙️ Spec:
+ `dbName` (string): the database name.
+ Return value: Boolean.
⚽️ Usage:
```js
const exists = await client.hasDatabase('database_1');
```
</details>
#### `client.describeDatabase()`:
<details><summary>
Get the schema structure for a database.
<pre><code>client.describeDatabase(dbName: string): Promise&lt;{ name: string, tables: Array }&gt;</code></pre></summary>
⚙️ Spec:
+ `dbName` (string): the database name.
+ Return value: an object corresponding to the *database* object in [schema.json](#schemajson).
⚽️ Usage:
```js
const schema = await client.describeDatabase('database_1');
console.log(schema.name);
console.log(schema.tables);
```
</details>
#### `client.databases()`:
<details><summary>
Get a list of available databases.
<pre><code>client.databases(): Promise&lt;Array&lt;string&gt;&gt;</code></pre></summary>
⚙️ Spec:
+ Return value: an array of database names.
⚽️ Usage:
```js
const databases = await client.databases();
console.log(databases); // ['public', 'database_1', ...]
```
</details>
#### `client.database()`:
<details><summary>
Obtain a <code>Database</code> instance.
<pre><code>client.database(dbName: string): Database</code></pre></summary>
⚙️ Spec:
+ `dbName` (string): the database name.
+ Return value: a [`Database`](#the-database-api) instance.
⚽️ Usage:
```js
const database = client.database('database_1');
```
</details>
------------
### The `Database` API
*Database* is the API for database-level operations. This object is obtained via [`client.database()`](#clientdatabase)
<details><summary>See content</summary>
+ [`database.name`](#databasename)
+ [`database.createTable()`](#databasecreatetable)
+ [`database.alterTable()`](#databasealtertable)
+ [`database.dropTable()`](#databasedroptable)
+ [`database.hasTable()`](#databasehastable)
+ [`database.describeTable()`](#databasedescribetable)
+ [`database.tables()`](#databasetables)
+ [`database.table()`](#databasetable)
+ [`database.savepoint()`](#databasesavepoint)
</details>
#### `database.name`:
<details><summary>
The name associated with the <i>Database</i> instance.
<pre><code>database.name: (string, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const database = client.database('test_db');
console.log(database.name); // test_db
```
</details>
#### `database.createTable()`:
<details><summary>
Dynamically run a <code>CREATE TABLE</code> operation.
<pre><code>database.createTable(createSpec: { name: string, columns: Array, constraints?: Array, indexes?: Array }, options?: Options): Promise&lt;Savepoint&gt;</code></pre></summary>
⚙️ Spec:
+ `createSpec` ({ name: string, columns: Array, constraints?: Array, indexes?: Array }): an object corresponding to the *table* object in [schema.json](#schemajson).
+ `options` (Options, *optional*): as described in [`query()`](#clientquery).
+ Return value: a [`Savepoint`](#the-savepoint-api) instance.
⚽️ Usage:
```js
const savepoint = await database.createTable({
name: 'table_1'
columns: [
{ name: 'column_1', type: 'int' },
{ name: 'column_2', type: 'time' }
]
}, { description: 'Just testing table creation' });
```
Some additional parameters via `options`:
+ `ifNotExists` (boolean, *optional*): a flag to conditionally create the table.
```js
const savepoint = await database.createTable({
name: 'table_1'
columns: [ ... ]
}, { ifNotExists: true, description: 'Just testing table creation' });
```
</details>
#### `database.alterTable()`:
<details><summary>
Dynamically run an <code>ALTER TABLE</code> operation.
<pre><code>database.alterTable(tblName: string, callback: (schema: TableSchema) => void, options?: Options): Promise&lt;Savepoint&gt;</code></pre></summary>
⚙️ Spec:
+ `tblName` (string): the table name.
+ `callback` ((schema: TableSchema) => void): a function that is called with the requested table schema. This can be async. Received object is a [`TableSchema`](#the-table-apischema) instance.
+ `options` (Options, *optional*): as described in [`query()`](#clientquery).
+ Return value: a [`Savepoint`](#the-savepoint-api) instance.
⚽️ Usage:
```js
const savepoint = await database.alterTable('table_1', schema => {
schema.name('table_1_new');
schema.column('column_1').type('int');
schema.column('column_2').drop();
}, { description: 'Renaming for testing purposes' });
```
</details>
#### `database.dropTable()`:
<details><summary>
Dynamically run a <code>DROP TABLE</code> operation.
<pre><code>database.dropTable(tblName: string, options?: Options): Promise&lt;Savepoint&gt;</code></pre></summary>
⚙️ Spec:
+ `tblName` (string): the table name.
+ `options` (Options, *optional*): as described in [`query()`](#clientquery).
+ Return value: a [`Savepoint`](#the-savepoint-api) instance.
⚽️ Usage:
```js
const savepoint = await database.dropTable('table_1', { description: 'Dropping for testing purposes' });
```
Some additional parameters via `options`:
+ `ifExists` (boolean, *optional*): a flag to conditionally drop the table.
```js
const savepoint = await database.dropTable('table_1', { ifExists: true, description: 'Dropping for testing purposes' });
```
+ `cascade` (boolean, *optional*): a flag to force-drop the table along with its dependents.
```js
const savepoint = await database.dropTable('table_1', { cascade: true, description: 'Dropping for testing purposes' });
```
</details>
#### `database.hasTable()`:
<details><summary>
Check if a table exists.
<pre><code>database.hasTable(tblName: string): Promise&lt;Boolean&gt;</code></pre></summary>
⚙️ Spec:
+ `tblName` (string): the table name.
+ Return value: Boolean.
⚽️ Usage:
```js
const exists = await database.hasTable('table_1');
```
</details>
#### `database.describeTable()`:
<details><summary>
Get the schema structure for a table.
<pre><code>database.describeTable(tblName: string): Promise&lt;{ name: string, columns: Array, constraints: Array, indexes: Array }&gt;</code></pre></summary>
⚙️ Spec:
+ `tblName` (string): the table name.
+ Return value: an object corresponding to the *table* object in [schema.json](#schemajson).
⚽️ Usage:
```js
const schema = await database.describeTable('table_1');
console.log(schema.name);
console.log(schema.columns);
```
</details>
#### `database.tables()`:
<details><summary>
Get a list of available tables.
<pre><code>database.tables(): Promise&lt;Array&lt;string&gt;&gt;</code></pre></summary>
⚙️ Spec:
+ Return value: an array of table names.
⚽️ Usage:
```js
const tables = await database.tables();
console.log(tables); // ['table_1', 'table_2', ...]
```
</details>
#### `database.table()`:
<details><summary>
Obtain a <code>Table</code> instance.
<pre><code>database.table(tblName: string): Table</code></pre></summary>
⚙️ Spec:
+ `tblName` (string): the table name.
+ Return value: a [`Table`](#the-table-api) instance.
⚽️ Usage:
```js
const table = database.table('table_1');
```
</details>
#### `database.savepoint()`:
<details><summary>
Obtain the next available <i>savepoint</i> for given database.
<pre><code>database.savepoint(options?: { direction: string }): Savepoint</code></pre></summary>
⚙️ Spec:
+ `options` ({ direction: string }, *optional*): extra paramters for the method.
+ Return value: a [`Savepoint`](#the-savepoint-api) instance.
⚽️ Usage:
```js
const savepoint = await database.savepoint();
console.log(savepoint.versionTag); // number
await savepoint.rollback(); // true
```
Some additional parameters via `options`:
+ `direction` (string, *optional*): the direction of lookup - either back in time: `backward` (the default), or forward in time: `forward`.
```js
const savepoint = await database.savepoint({ direction: 'forward' });
console.log(savepoint.versionTag); // number
await savepoint.rollback(); // true
```
</details>
------------
### The `Table` API
*Table* is the API for table-level operations. This object is obtained via [`database.table()`](#databasetable)
<details><summary>See content</summary>
+ [`table.name`](#tablename)
+ [`table.count()`](#tablecount)
+ [`table.select()`](#tableselect)
+ [`table.insert()`](#tableinsert)
+ [`table.upsert()`](#tableupsert)
+ [`table.update()`](#tableupdate)
+ [`table.delete()`](#tabledelete)
</details>
#### `table.name`:
<details><summary>
The name associated with the <i>Table</i> instance.
<pre><code>table.name: (string, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const table = client.database('test_db').table('table_1');
console.log(table.name); // table_1
```
</details>
#### `table.count()`:
<details><summary>
Count total entries in table.
<pre><code>table.count(expr?: string | Function = *): Promise&lt;number&gt;</code></pre></summary>
⚙️ Spec:
+ `expr` (string | Function = *, *optional*): a string denoting column name, or a function that recieves a *Field* object with which to build an expression. Defaults to `*`.
+ Return value: number.
⚽️ Usage:
```js
const rowCount = await table.count();
```
```js
// Number of rows where column_1 isn't null
const rowCount = await table.count('column_1');
```
</details>
#### `table.select()`:
<details><summary>
Dynamically run a <code>SELECT</code> query.
<pre><code>table.select(fields?: (string | Function)[] = *, where?: number | object | Function | true): Promise&lt;Array&lt;object&gt;&gt;</code></pre>
<pre><code>table.select(where?: number | object | Function): Promise&lt;Array&lt;object&gt;&gt;</code></pre></summary>
⚙️ Spec:
+ `fields` ((string | Function)[] = *, *optional*): an array of fields to select. (A field being either a column name string, or a function that recieves a *Field* object with which to build an expression.)
+ `where` (number | object | Function | true, *optional*): a number targeting the primary key value of the target row, or an object specifying some column name/column value conditions, or a function that recieves an *Assertion* object with which to build the conditions, or the value `true` denoting all records. Defaults to `true`.
+ Return value: an array (the result set).
⚽️ Usage:
```js
// Select all fields (*) from all records
const result = await table.select();
```
```js
// Select specified fields from the record having primary key value of 4
const result = await table.select(['first_name', 'last_name', 'email'], 4);
```
```js
// Select record by primary key value, ommiting fields (implying all fields)
const result = await table.select(4);
```
```js
// Select record by some column name/column value conditions, ommiting fields (implying all fields)
const result = await table.select({ first_name: 'John', last_name: 'Doe' });
```
</details>
#### `table.insert()`:
<details><summary>
Dynamically run an <code>INSERT</code> operation. (With automatic parameter binding.)
<pre><code>table.insert(payload: object | object[], returnList?: (string | Function)[]): Promise&lt;Array&lt;object&gt; | number&gt;</code></pre>
<pre><code>table.insert(columns: string[], values: any[][], returnList?: (string | Function)[]): Promise&lt;Array&lt;object&gt; | number&gt;</code></pre></summary>
⚙️ Spec:
+ `payload` (object | object[]): an object denoting a single entry, or an array of said objects denoting multiple entries. (An entry having the general form: `{ [key: string]: string | number | boolean | null | Date | object | any[] }` where arrays and objects as values are automatically JSON-stringified.)
+ `columns` (string[]): just column names (as against the key/value `payload` in the first call pattern).
+ `values` (any[][]): a two-dimensional array of just values (as against the key/value `payload` in the first call pattern), denoting multiple entries.
+ `returnList` (((string | Function)[] | false), *optional*): a list of fields, corresponding to a [select list](#tableselect), specifying data to be returned from the just inserted row. (Equivalent to Postgres' [RETURNING clause](https://www.postgresql.org/docs/current/dml-returning.html), but supported for other DB kinds in Linked QL.)
+ Return value: a number indicating number of rows processed by the query, or where `returnList` was provided, an array of the processed row(s).
⚽️ Usage:
```js
// Insert single entry
await table.insert({ first_name: 'John', last_name: 'Doe', email: 'johndoe@example.com'});
```
```js
// Insert multiple entries
await table.insert([
{ first_name: 'John', last_name: 'Doe', email: 'johndoe@example.com'},
{ first_name: 'James', last_name: 'Clerk', email: 'jamesclerk@example.com'},
]);
```
```js
// Insert multiple entries another way
await table.insert(['first_name', 'last_name', 'email'], [
['John', 'Doe', 'johndoe@example.com'],
['James', 'Clerk', 'jamesclerk@example.com'],
]);
```
```js
// Insert single entry, obtaining inserted rows - which is itself streamlined to just the "id" column
const insertedRows = await table.insert({ first_name: 'John', last_name: 'Doe', email: 'johndoe@example.com'}, ['id']);
```
</details>
#### `table.upsert()`:
<details><summary>
Dynamically run an <code>UPSERT</code> operation. (With automatic parameter binding.)
<pre><code>table.upsert(payload: object | object[], returnList?: (string | Function)[]): Promise&lt;Array&lt;object&gt; | number&gt;</code></pre>
<pre><code>table.upsert(columns: string[], values: any[][], returnList?: (string | Function)[]): Promise&lt;Array&lt;object&gt; | number&gt;</code></pre></summary>
⚙️ Spec:
+ `payload` (object | object[]): as described in [`insert()`](#tableinsert).
+ `columns` (string[]): as described in [`insert()`](#tableinsert).
+ `values` (any[][]): as described in [`insert()`](#tableinsert).
+ `returnList` ((string | Function)[], *optional*): as described in [`insert()`](#tableinsert).
+ Return value: as described in [`insert()`](#tableinsert).
⚽️ Usage:
An `UPSERT` operation is an `INSERT` operation that automatically converts to an `UPDATE` operation where given record already exists. API usage is same as [`insert()`](#tableinsert) but as `upsert()`.
</details>
#### `table.update()`:
<details><summary>
Dynamically run an <code>UPDATE</code> operation. (With automatic parameter binding.)
<pre><code>table.update(where: number | object | Function | true, payload: object, returnList?: (string | Function)[]): Promise&lt;Array&lt;object&gt; | number&gt;</code></pre></summary>
⚙️ Spec:
+ `where` (number | object | Function | true): as described in [`select()`](#tableselect).
+ `payload` (object): an object having the general form: `{ [key: string]: string | number | boolean | null | Date | object | any[] }` where arrays and objects as values are automatically JSON-stringified.
+ `returnList` ((string | Function)[], *optional*): as described in [`insert()`](#tableinsert).
+ Return value: as described in [`insert()`](#tableinsert).
⚽️ Usage:
```js
// Update the record having primary key value of 4
await table.update(4, { first_name: 'John', last_name: 'Doe' });
```
```js
// Update the record having specified email value, obtaining the updated rows
const updatedRows = await table.update({ email: 'johndoe@example.com' }, { first_name: 'John', last_name: 'Doe' }, ['*']);
```
```js
// Update all records
await table.update(true, { updated_at: new Date });
```
</details>
#### `table.delete()`:
<details><summary>
Dynamically run a <code>DELETE</code> operation. (With automatic parameter binding.)
<pre><code>table.delete(where: number | object | Function | true, returnList?: (string | Function)[]): Promise&lt;Array&lt;object&gt; | number&gt;</code></pre></summary>
⚙️ Spec:
+ `where` (number | object | Function | true): as described in [`select()`](#tableselect).
+ `returnList` ((string | Function)[], *optional*): as described in [`insert()`](#tableinsert).
+ Return value: as described in [`insert()`](#tableinsert).
⚽️ Usage:
```js
// Delete the record having primary key value of 4
await table.delete(4);
```
```js
// Delete the record having specified email, obtaining the deleted row
const deletedRow = await table.delete({ email: 'johndoe@example.com' });
```
```js
// Delete all records
await table.delete(true);
```
</details>
------------
### The `Savepoint` API
*Savepoint* is an object representation of a database's savepoint. This object is obtained either via [`database.savepoint()`](#databasesavepoint) or via a `CREATE`, `ALTER`, or `DROP` operation.
<details><summary>See content</summary>
+ [`savepoint.id`](#savepointid)
+ [`savepoint.databaseTag`](#savepointdatabasetag)
+ [`savepoint.versionTag`](#savepointversiontag)
+ [`savepoint.versionMax`](#savepointversionmax)
+ [`savepoint.cursor`](#savepointcursor)
+ [`savepoint.description`](#savepointdescription)
+ [`savepoint.savepointDate`](#savepointsavepointdate)
+ [`savepoint.rollbackDate`](#savepointrollbackdate)
+ [`savepoint.rollbackEffect`](#savepointrollbackoutcome)
+ [`savepoint.isNextPointInTime()`](#savepointisnextpointintime)
+ [`savepoint.rollback()`](#savepointrollback)
+ [`savepoint.toJson()`](#savepointtojson)
+ [`savepoint.schema()`](#savepointschema)
+ [`savepoint.name()`](#savepointname)
</details>
#### `savepoint.id`:
<details><summary>
The UUID associated with the savepoint.
<pre><code>savepoint.id: (UUID, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.id); // f740d66a-df5f-4a34-a281-8ef3ba6fe754
```
</details>
#### `savepoint.databaseTag`:
<details><summary>
The subject database's generic identifier that transcends name changes.
<pre><code>savepoint.databaseTag: (string, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
Consider a database's generic identifier before and after a name change:
```js
// Before name change
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.databaseTag); // db:18m6z
```
```js
// Name change
await client.alterDatabase('test_db', schema => schema.name('test_db_new'));
```
```js
// Now even after name change
const savepoint = await client.database('test_db_new').savepoint();
console.log(savepoint.databaseTag); // db:18m6z
```
</details>
#### `savepoint.versionTag`:
<details><summary>
The savepoint's version tag.
<pre><code>savepoint.versionTag: (number, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
// Version 1
const savepoint = await client.createDatabase({
name: 'test_db',
tables: [{
name: 'test_tbl1',
columns: [],
}]
});
console.log(savepoint.versionTag); // 1
```
```js
// Version 2
const savepoint = await client.database('test_db').createTable({
name: 'test_tbl2',
columns: [],
});
console.log(savepoint.versionTag); // 2
```
```js
// Version 2 currently
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.versionTag); // 2
```
</details>
#### `savepoint.versionMax`:
<details><summary>
The database's peak version regardless of its current rollback level.
<pre><code>savepoint.versionMax: (number, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.versionTag); // 2
console.log(savepoint.versionMax); // 2
```
```js
await savepoint.rollback();
```
```js
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.versionTag); // 1
console.log(savepoint.versionMax); // 2
```
</details>
#### `savepoint.cursor`:
<details><summary>
The savepoint's current level in the database's list of available savepoints.
<pre><code>savepoint.cursor: (string, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.cursor); // 1/2
```
</details>
#### `savepoint.description`:
<details><summary>
The description for the changes associated with the savepoint.
<pre><code>savepoint.description: (string, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const savepoint = await client.database('test_db').createTable({
name: 'test_tbl2',
columns: [],
}, { description: 'Create test_tbl2' });
console.log(savepoint.description); // Create test_tbl2
```
```js
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.description); // Create test_tbl2
```
</details>
#### `savepoint.savepointDate`:
<details><summary>
The savepoint's creation date.
<pre><code>savepoint.savepointDate: (Date, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const savepoint = await client.database('test_db').savepoint();
console.log(savepoint.savepointDate); // 2024-07-20T15:31:06.096Z
```
</details>
#### `savepoint.rollbackDate`:
<details><summary>
The savepoint's rollback date.
<pre><code>savepoint.rollbackDate: (Date, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
```js
const savepoint = await client.database('test_db').createTable({
name: 'test_tbl2',
columns: [],
}, { description: 'Create test_tbl2' });
console.log(savepoint.rollbackDate); // null
```
```js
await savepoint.rollback();
console.log(savepoint.rollbackDate); // 2024-07-20T15:31:06.096Z
```
```js
// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
console.log(savepoint.rollbackDate); // 2024-07-20T15:31:06.096Z
```
</details>
#### `savepoint.rollbackEffect`:
<details><summary>
A single-word summary of the effect that rolling back to this savepoint will have on subject DB.
<pre><code>savepoint.rollbackEffect: (string, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
Will rolling back to given savepoint mean dropping or re-creating the subject database?:
For a create operation...
```js
const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });
```
Rolling back will mean dropping the DB:
```js
console.log(savepoint.descripton); // Create db
console.log(savepoint.rollbackEffect); // DROP
```
```js
// Drop DB
console.log(savepoint.rollbackEffect); // DROP
await savepoint.rollback();
```
Having rolled back, rolling forward will mean a re-creation of the DB:
```js
// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
// Now rolling back will mean re-creating the DB
console.log(savepoint.descripton); // Create db
console.log(savepoint.rollbackEffect); // CREATE
```
But note that table-level create/drop operations always only have an `ALTER` effect on parent DB:
```js
// Create table - which translates to a DB "alter" operation
const savepoint = await client.database('test_db').createTable({
name: 'test_tbl2',
columns: [],
}, { description: 'Create test_tbl2' });
// Rolling back will mean dropping the table - which will still translate to a DB "alter" operation
console.log(savepoint.descripton); // Create test_tbl2
console.log(savepoint.rollbackEffect); // ALTER
```
```js
// Drop DB
await savepoint.rollback();
console.log(savepoint.rollbackEffect); // ALTER
```
```js
// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
// Now rolling back will mean re-creating the table - which will still translate to a DB "alter" operation
console.log(savepoint.descripton); // Create test_tbl2
console.log(savepoint.rollbackEffect); // ALTER
```
</details>
#### `savepoint.rollbackQuery`:
<details><summary>
A query preview of the rollback.
<pre><code>savepoint.rollbackQuery: ({ toString(): string }, <i>readonly</i>)</code></pre></summary>
⚽️ Usage:
You get a query instance that is *toString()able*:
For a create operation...
```js
const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });
```
Rolling back will mean dropping the DB:
```js
console.log(savepoint.rollbackQuery.toString()); // DROP SCHEMA test_db CASCADE
```
</details>
#### `savepoint.isNextPointInTime()`:
<details><summary>
Check if the savepoint is the next actual <i>point in time</i> for the database.
<pre><code>savepoint.isNextPointInTime(): Promise&lt;boolean&gt;</code></pre></summary>
⚙️ Spec:
+ Return value: boolean.
⚽️ Usage:
For a new operation, that would be true:
```js
const dbCreationSavepoint = await client.createDatabase('test_db');
console.log(await dbCreationSavepoint.isNextPointInTime()); // true
```
But after having performed more operations, that wouldn't be:
```js
const tblCreationSavepoint = await client.database('test_db').createTable({
name: 'test_tbl',
columns: [{
name: 'id',
type: 'int'
}]
});
console.log(await tblCreationSavepoint.isNextPointInTime()); // true
console.log(await dbCreationSavepoint.isNextPointInTime()); // false
```
Rollback table creation and test `dbCreationSavepoint`'s position again:
```js
await tblCreationSavepoint.rollback();
console.log(await tblCreationSavepoint.isNextPointInTime()); // false
console.log(await dbCreationSavepoint.isNextPointInTime()); // true
```
</details>
#### `savepoint.rollback()`:
<details><summary>
Rollback all changes associated with given savepoint.
<pre><code>savepoint.rollback(): Promise&lt;boolean&gt;</code></pre></summary>
⚙️ Spec:
+ Return value: boolean.
⚽️ Usage:
Create database and rollback:
```js
// Create DB
const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });
// Roll back - which means drop the DB
await savepoint.rollback();
```
Undo the rollback; i.e. roll forward:
```js
// Find the same savepoint with a forward lookup
const savepoint = await client.database('test_db').savepoint({ direction: 'forward' });
// Roll back - which means re-create the DB
await savepoint.rollback();
```
</details>
#### `savepoint.toJson()`:
<details><summary>
Get a plain object representation of the savepoint.
<pre><code>savepoint.toJson(): object</code></pre></summary>
⚙️ Spec:
+ Return value: an object of the form `{ id: string, name: string, databaseTag: string, versionTag: number, versionMax: number, cursor: string, description: string, savepointDate: Date, rollbackDate: Date | null }`.
⚽️ Usage:
```js
const savepoint = await client.createDatabase('test_db', { descripton: 'Create db' });
console.log(savepoint.toJson());
```
</details>
#### `savepoint.schema()`:
<details><summary>
Get the subject DB's schema snapshot at this point in time.
<pre><code>savepoint.schema(): object</code></pre></summary>
⚙️ Spec:
+ Return value: an object corresponding to the *database* object in [schema.json](#schemajson).
⚽️ Usage:
```js
const savepoint = await client.database('test_db').createTable({
name: 'test_tbl',
columns: [{
name: 'id',
type: 'int'
}]
});
console.log(savepoint.schema());
```
```js
const savepoint = await client.database('test_db').savepoint();
await savepoint.schema();
```
</details>
#### `savepoint.name()`:
<details><summary>
Get the subject database's name.
<pre><code>savepoint.name(postRollback?: boolean): string</code></pre></summary>
⚙️ Spec:
+ `postRollback` (boolean, *optional*): in case a name change was captured in the savepoint, whether to return the database's post-rollback name. Otherwise the database's active, pre-rollback name is returned.
+ Return value: the database name.
⚽️ Usage:
```js
// Name change
const savepoint = await client.alterDatabase('test_db', schema => schema.name('test_db_new'));
// The database's active, pre-rollback name
console.log(savepoint.name()); // test_db_new
// The database's post-rollback name
console.log(savepoint.name(true)); // test_db
```
</details>
------------
## Linked QL CLI
Linked QL migrations are a **small** addition to Linked QL. And it comes ready-to-use, via the `linkedql` command, upon Linked QL's installation. (No extra setup is required.)
### Overview
The `linkedql` command comes as part of your local Linked QL installation and not as a global package, and that means you'll need the `npx` prefix to run the commands below. E.g.
```cmd
npx linkedql migrate
```
On each command, you can use the `--dir` flag to point Linked QL to your "database" directory (where you have your `schema.json` and `driver.js` files), that's if you have chosen a different location other than `./database`:
```cmd
npx linkedql migrate --dir="./src/database-stuff"
```
*(Relative paths will resolve against your current working directory (CWD).)*
To run a command for a specific database out of your list of databases, use the `--db` flag:
```cmd
npx linkedql migrate --db=database_1
```
To turn off prompts and get Linked QL to just take the "sensible-default" action, use the flag `--auto`:
```cmd
npx linkedql migrate --auto
```
### Commands
#### `linkedql migrate`
*Interactively run new migrations.* Linked QL looks through your local schema and compares with your active DB structure to see what's new. It works interactively by default and you're able to preview each SQL query to be run.
<details><summary>🐹 Usage:</summary>
```cmd
npx linkedql migrate
```
```cmd
npx linkedql migrate --db=database_1
```
Use the `--desc` flag to provide the description for your new changes:
```cmd
npx linkedql migrate --desc="Initial DB creation"
```
Use the flag `--quiet` to turn off SQL previews:
```cmd
npx linkedql migrate --quiet
```
</details>
#### `linkedql rollback`
*Interactively perform a rollback.* Linked QL looks for the next savepoint at each database and initiates a rollback. It works interactively by default and you're able to preview each SQL query to be run.
<details><summary>🐹 Usage:</summary>
```cmd
npx linkedql rollback
```
```cmd
npx linkedql rollback --db=database_1
```
Use the `--direction` flag to specify either a "backward" rollback (the default) or a "forward" rollback if already at a certain rollback state:
```cmd
npx linkedql rollback --direction=forward
```
Use the flag `--quiet` to turn off SQL previews:
```cmd
npx linkedql migrate --quiet
```
</details>
#### `linkedql leaderboard`
*View the latest savepoint at each database.* Linked QL displays details about the next savepoint at each database.
<details><summary>🐹 Usage:</summary>
```cmd
npx linkedql leaderboard
```
```cmd
npx linkedql leaderboard --db=database_1
```
Use the flag `--direction` to specify either a "back in time" lookup (the default) or "forward in time" lookup if already at a certain rollback state:
```cmd
npx linkedql leaderboard --direction=forward
```
</details>
#### `linkedql refresh`
*Refresh local schema file.* Linked QL regenerates the schema from current DB structure for each database it has managed; refreshes local copy.
<details><summary>🐹 Usage:</summary>
```cmd
npx linkedql refresh
```
```cmd
npx linkedql refresh --db=database_1
```
</details>
#### `linkedql forget`
*Permanently erase savepoint histories.* Linked QL deletes the savepoint history of all databases, or a specific database from the `--db` flag. This is irreversible.
<details><summary>🐹 Usage:</summary>
```cmd
npx linkedql forget
```
```cmd
npx linkedql forget --db=database_1
```
</details>
🐣 *And that's a wrap!*
## Roadmap
+ [ONGOING] Improve support for MySQL.
+ [DONE] Implement support for a `schema.yml` alternative to `schema.json` file.
+ Implement support for IndexedDB.
+ Implement the in-memory database.
+ [`ONGOING`] Improve support for MySQL.
+ [`DONE`] Implement support for a `schema.yml` alternative to `schema.json` file.
+ [`PENDING`] Implement support for IndexedDB.
+ [`PENDING`] Implement the in-memory database.

@@ -1889,0 +633,0 @@ > Much of that could happen sooner with your support! If you'd like to help out, please consider a [sponsorship](https://github.com/sponsors/ox-harris). PRs are also always welcome.

29

src/api/AbstractClient.js
import Parser from '../lang/Parser.js';
import AbstractNode from '../lang/AbstractNode.js';
import Identifier from '../lang/components/Identifier.js';
import TableSchema from '../lang/schema/tbl/TableSchema.js';

@@ -274,10 +275,10 @@ import DatabaseSchema from '../lang/schema/db/DatabaseSchema.js';

const result = await this.query(`
SELECT id, database_tag, name, "$name", keep, version_tag, version_max, rank_for_cursor || '/' || total AS cursor, savepoint_description, tables, savepoint_date, rollback_date FROM (
SELECT
SELECT id, database_tag, name, ${ Identifier.fromJson(this, '$name') }, keep, version_tag, version_max, CONCAT(rank_for_cursor, '/', total) AS ${ Identifier.fromJson(this, '$cursor') }, savepoint_description, tables, savepoint_date, rollback_date FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY database_tag ORDER BY rollback_date IS NOT NULL ${ params.direction === 'forward' ? 'DESC' : 'ASC' }, version_tag ${ params.direction === 'forward' ? 'ASC' : 'DESC' }) AS rank_for_target,
ROW_NUMBER() OVER (PARTITION BY database_tag ORDER BY version_tag ASC) AS rank_for_cursor,
MAX(version_tag) OVER (PARTITION BY database_tag) AS version_max,
COUNT(version_tag) OVER (PARTITION BY database_tag) AS total,
* FROM ${ tblName }
) AS savepoint WHERE rollback_date IS ${ params.direction === 'forward' ? 'NOT NULL' : 'NULL' } AND rank_for_target = 1${ params.name ? (params.direction === 'forward' ? ` AND name = '${ params.name }'` : ` AND COALESCE("$name", name) = '${ params.name }'`) : '' }
COUNT(version_tag) OVER (PARTITION BY database_tag) AS total
FROM ${ tblName }
) AS savepoint WHERE rollback_date IS ${ params.direction === 'forward' ? 'NOT NULL' : 'NULL' } AND rank_for_target = 1${ params.name ? (params.direction === 'forward' ? ` AND name = '${ params.name }'` : ` AND COALESCE(${ Identifier.fromJson(this, '$name') }, name) = '${ params.name }'`) : '' }
`);

@@ -304,14 +305,14 @@ return result.map(savepoint => new Savepoint(this, savepoint, params.direction))

columns: [
{ name: 'id', type: 'uuid', primaryKey: true, default: { expr: 'gen_random_uuid()' } },
{ name: 'id', ...(this.params.dialect === 'mysql' ? { type: 'char(36)', default: { expr: 'uuid()' } } : { type: 'uuid', default: { expr: 'gen_random_uuid()' } }), primaryKey: true },
// Actual snapshot
{ name: 'name', type: 'varchar', notNull: true },
{ name: '$name', type: 'varchar' },
{ name: 'name', type: ['varchar',255], notNull: true },
{ name: '$name', type: ['varchar',255] },
{ name: 'tables', type: 'json' },
{ name: 'keep', type: 'boolean' },
{ name: 'keep', type: this.params.dialect === 'mysql' ? ['bit',1] : 'boolean' },
// Meta data
{ name: 'savepoint_description', type: 'varchar' },
{ name: 'database_tag', type: 'varchar', notNull: true },
{ name: 'savepoint_description', type: ['varchar', 255] },
{ name: 'database_tag', type: ['varchar', 12], notNull: true },
{ name: 'version_tag', type: 'int', notNull: true },
{ name: 'savepoint_date', type: 'timestamp', notNull: true },
{ name: 'rollback_date', type: 'timestamp' },
{ name: 'savepoint_date', type: ['timestamp',3], notNull: true },
{ name: 'rollback_date', type: ['timestamp',3] },
],

@@ -350,4 +351,4 @@ }],

const insertResult = await this.database(OBJ_INFOSCHEMA_DB).table('database_savepoints').insert(savepointJson, '*');
return new Savepoint(this, { ...insertResult[0], version_max: savepointJson.version_tag, cursor: null });
return new Savepoint(this, { ...insertResult[0], version_max: savepointJson.version_tag, $cursor: null });
}
}

4

src/api/AbstractTable.js

@@ -126,3 +126,3 @@

// On-conflict
query.onConflict({ entries: columns.map((col, i) => [col, values[i]])});
query.onConflict({ entries: columns.map((col, i) => [col, values[0][i]]) });
if (returnList) query.returning(returnList);

@@ -225,5 +225,5 @@ // Handle

const toVal = (v, autoBindings) => {
if (v instanceof Date) return q => q.value(v.toISOString().split('.')[0]);
if (autoBindings !== false) return q => q.$bind(0, v);
if ([true,false,null].includes(v)) return q => q.literal(v);
if (v instanceof Date) return q => q.value(v.toISOString().split('.')[0]);
if (Array.isArray(v)) return q => q.array(v);

@@ -230,0 +230,0 @@ if (_isObject(v)) return q => q.object(v);

12

src/api/Savepoint.js
import CreateStatement from "../lang/ddl/create/CreateStatement.js";
import DropStatement from "../lang/ddl/drop/DropStatement.js";
import DatabaseSchema from "../lang/schema/db/DatabaseSchema.js";
import CreateStatement from '../lang/ddl/create/CreateStatement.js';
import DropStatement from '../lang/ddl/drop/DropStatement.js';
import DatabaseSchema from '../lang/schema/db/DatabaseSchema.js';

@@ -54,3 +54,3 @@ export default class Savepoint {

*/
get cursor() { return this.$.json.cursor; }
get cursor() { return this.$.json.$cursor; }

@@ -115,4 +115,4 @@ /**

toJson() {
const { id, database_tag: databaseTag, version_tag: versionTag, version_max: versionMax, cursor, savepoint_description: description, savepoint_date: savepointDate, rollback_date: rollbackDate } = this.$.json;
return { id, name: this.name(), databaseTag, versionTag, versionMax, cursor, description, savepointDate, rollbackDate, rollbackEffect: this.rollbackEffect };
const { id, database_tag: databaseTag, version_tag: versionTag, version_max: versionMax, $cursor, savepoint_description: description, savepoint_date: savepointDate, rollback_date: rollbackDate } = this.$.json;
return { id, name: this.name(), databaseTag, versionTag, versionMax, cursor: $cursor, description, savepointDate, rollbackDate, rollbackEffect: this.rollbackEffect };
}

@@ -119,0 +119,0 @@

67

src/api/sql/SQLClient.js

@@ -63,11 +63,13 @@

const supportsReturnList = [InsertStatement,UpdateStatement,DeleteStatement].some(x => query instanceof x);
let myReturningList;
let myReturningCallback;
if (supportsReturnList && this.params.dialect === 'mysql' && query.RETURNING_LIST.length) {
query = query.clone();
myReturningList = query.RETURNING_LIST.splice(0);
// TODO: myReturningList
if (this.params.returningClause === false) throw new Error(`Support for the "RETURNING" clause has been disabled.`);
[query, myReturningCallback] = await this.$myReturningMagic(query);
}
const bindings = (query.BINDINGS || []).concat(params.values || []).map(value => Array.isArray(value) || typeof value === 'object' && value ? JSON.stringify(value) : value);
const result = await this.driver.query(query.toString(), bindings);
if (query instanceof SelectStatement || (supportsReturnList && query.RETURNING_LIST.length)) return result.rows || result;
// _____________________
let result = await this.driver.query(query.toString(), bindings);
if (myReturningCallback) result = await myReturningCallback();
// _____________________
if (query instanceof SelectStatement || (supportsReturnList && query.RETURNING_LIST.length) || myReturningCallback) return result.rows || result;
return 'rowCount' in result ? result.rowCount : result.affectedRows;

@@ -77,2 +79,55 @@ }, ...arguments);

/**
* Initialise the logic for supporting the "RETURNING" clause in MySQL
*/
async $myReturningMagic(query) {
query = query.clone();
// ----------------------------------
const selectList = query.RETURNING_LIST.splice(0);
const tblName = query.$trace('get:name:table');
const dbName = query.$trace('get:name:database');
const tableIdent = Identifier.fromJson(this, [dbName,tblName]);
// ----------------------------------
// Delete statements are handled ahead of the query
if (query instanceof DeleteStatement) {
const result = await this.driver.query(`SELECT ${ selectList.join(', ' ) } FROM ${ tableIdent }${ this.WHERE_CLAUSE ? ` WHERE ${ this.WHERE_CLAUSE }` : '' }`);
return [query, () => result];
}
// Insert and update statements are post-handled
// ----------------------------------
const colName = 'obj_column_for_returning_clause_support';
const columnIdent = Identifier.fromJson(this, colName );
await this.driver.query(`ALTER TABLE ${ tableIdent } ADD COLUMN ${ columnIdent } char(36) INVISIBLE`);
const insertUuid = ( 0 | Math.random() * 9e6 ).toString( 36 );
// ----------------------------------
if (!query.SET_CLAUSE && query instanceof InsertStatement) {
// Columns must be explicitly named
if (!query.COLUMNS_CLAUSE && (query.SELECT_CLAUSE || query.VALUES_LIST.length)) {
//query.columns(...columns);
throw new Error(`Support for the RETURNING clause currently requires explicit column list in INSERT statements.`);
}
query.columns(colName);
// Add to values list, or select list if that's what's being used
if (query.SELECT_CLAUSE) {
query.SELECT_CLAUSE.select(q => q.value(insertUuid));
} else if (query.VALUES_LIST.length) {
for (const values of query.VALUES_LIST) values.list(q => q.value(insertUuid));
} else query.values(insertUuid);
} else {
query.set(colName, q => q.value(insertUuid));
}
if (query instanceof InsertStatement && query.ON_CONFLICT_CLAUSE) {
query.ON_CONFLICT_CLAUSE.set(colName, q => q.value(insertUuid));
}
console.log('.......////\n\n\n\n\n', query+'');
return [query, async () => {
// ----------------------------------
const result = await this.driver.query(`SELECT ${ selectList.join(', ' ) } FROM ${ tableIdent } WHERE ${ columnIdent } = '${ insertUuid }'`);
await this.driver.query(`ALTER TABLE ${ tableIdent } DROP COLUMN ${ columnIdent }`);
// ----------------------------------
return result;
}];
}
/**

@@ -79,0 +134,0 @@ * Sets or returns the search path for resolving unqualified table references.

2

src/api/sql/SQLTable.js
import Expr from '../../lang/components/Expr.js';
import Identifier from '../../lang/components/Identifier.js';
import AbstractTable from '../AbstractTable.js';

@@ -3,0 +5,0 @@ import SQLCursor from './SQLCursor.js';

2

src/lang/components/Assertion.js

@@ -325,3 +325,3 @@

const rightHandSide = operands;
if (this.OPERATOR === 'IN') sql.push(`(${ rightHandSide.join(',') })`);
if (this.OPERATOR === 'IN') sql.push(`(${ rightHandSide.join(', ') })`);
else if (/BETWEEN/i.test(this.OPERATOR)) sql.push(`(${ rightHandSide.join(' AND ') })`);

@@ -328,0 +328,0 @@ else sql.push(`${ rightHandSide.join(' ') }`);

15

src/lang/components/Path.js

@@ -68,5 +68,5 @@

if (!clientApi) throw new Error(`No client API in context.`);
const getPrimaryKey = schema => schema.constraint('PRIMARY_KEY')?.columns[0];
const getPrimaryKey = schema => schema.primaryKey()?.columns()[0];
const getTargetTable = async (schema, foreignKey) => {
const targetTable = schema.NODES.find(node => node.TYPE === 'FOREIGN_KEY' && node.columns.includes(foreignKey.NAME)).targetTable;
const targetTable = [...schema.NODES].find(node => node.TYPE === 'FOREIGN_KEY' && node.columns().includes(foreignKey.NAME))?.targetTable();
if (targetTable && !targetTable.BASENAME) return Identifier.fromJson(this, [await clientApi.basenameGet(targetTable.NAME),targetTable.NAME]);

@@ -93,2 +93,3 @@ return targetTable;

({ LHS: foreignKey_rhs/*Identifier*/, RHS/*Path*/: { LHS: table_rhs/*Identifier*/, RHS: path/*Identifier|Path*/ } } = this);
if (!table_rhs.BASENAME) { table_rhs = Identifier.fromJson(this, [await clientApi.basenameGet(table_rhs.NAME), table_rhs.NAME]); }
schema_rhs = await getSchema(table_rhs.NAME, table_rhs.BASENAME);

@@ -119,3 +120,3 @@ if (!schema_rhs) throw new Error(`[${ this }]: The implied table ${ table_rhs } does not exist.`);

// Get lhs schema
const schema_lhs = await getSchema(table_lhs);
const schema_lhs = await getSchema(table_lhs.NAME, table_lhs.BASENAME);
if (!schema_lhs) throw new Error(`[${ this }]: The implied table ${ table_lhs } does not exist.`);

@@ -129,3 +130,3 @@ const { LHS: foreignKey_lhs/*Identifier*/, RHS: path/*Identifier|Path*/ } = this;

// Get schema_rhs from keyDef!
const schema_rhs = await getSchema(table_rhs);
const schema_rhs = await getSchema(table_rhs.NAME, table_rhs.BASENAME);
if (!schema_rhs) throw new Error(`[${ this }]: The implied table ${ table_rhs } does not exist.`);

@@ -156,4 +157,4 @@ // Get shcema_lhs's acting key (primary key) and validate

const { lhs, rhs } = await this.eval();
const baseKey = lhs.foreignKey || lhs.primaryKey;
const joinKey = rhs.primaryKey || rhs.foreignKey;
const baseKey = lhs.foreignKey?.NAME || lhs.primaryKey;
const joinKey = rhs.primaryKey || rhs.foreignKey.NAME;
if (lhs.primaryKey/*then incoming reference*/ && (lhs.table.NAME.toLowerCase() !== baseTable.NAME.toLowerCase() || lhs.table.BASENAME.toLowerCase() !== baseTable.BASENAME.toLowerCase())) throw new Error(`[${ this }]: Cannot resolve incoming path to base table ${ baseTable.EXPR }.`);

@@ -164,3 +165,3 @@ const joinAlias = `_view:${ [baseKey, rhs.table.BASENAME, rhs.table.NAME, joinKey].join(':') }`;

// Implement the join for the first time
const baseAlias = ['ALIAS','EXPR'].reduce((prev, key) => prev || baseTable[key]?.NAME, null);
const baseAlias = this.$trace('get:node:table').ALIAS?.NAME || baseTable.NAME;
const joinKeyAlias = `${ joinKey }:${ ( 0 | Math.random() * 9e6 ).toString( 36 ) }`;

@@ -167,0 +168,0 @@ stmt.leftJoin( j => j.query( q => q.select( field => field.name( joinKey ).as( joinKeyAlias ) ), q => q.from([rhs.table.BASENAME,rhs.table.NAME].filter(s => s)) ) )

9

src/lang/ddl/alter/Set.js
import AbstractNode from '../AbstractNode.js';
import DataType from '../../schema/tbl/DataType.js';
import Identifier from '../../components/Identifier.js';

@@ -19,2 +20,4 @@ export default class Set extends AbstractNode {

this.build('ARGUMENT', [value], DataType);
} else if (this.KIND === 'SCHEMA') {
this.build('ARGUMENT', [value], Identifier);
} else { this.ARGUMENT = value; }

@@ -53,4 +56,6 @@ return this;

const instance = new this(context, isIdentity ? 'IDENTITY' : kind.replace(/\s+/g, '_').toUpperCase());
if (/^(DATA\s+)?TYPE\s+/i.test(kind)) {
if (/^(DATA\s+)?TYPE$/i.test(kind)) {
instance.argument(parseCallback(instance, argument, [DataType]));
} else if (/^SCHEMA$/i.test(kind)) {
instance.argument(parseCallback(instance, argument, [Identifier]));
} else instance.argument(isIdentity ? (/^AS\s+ALWAYS$/i.test(argument) ? 'always' : true) : argument);

@@ -61,3 +66,3 @@ return instance;

static get CLAUSE() { return 'SET'; }
static KINDS = ['SCHEMA', 'DATA_TYPE', 'TYPE', 'IDENTITY', 'DEFAULT', 'NOT_NULL'];
static KINDS = ['SCHEMA', 'DATA_TYPE', 'TYPE', 'IDENTITY', 'DEFAULT', 'NOT_NULL', 'NULL', 'AUTO_INCREMENT', 'ON_UPDATE'];
}

4

src/lang/ddl/create/Add.js
import AbstractNode from '../AbstractNode.js';
import Identity from '../../schema/tbl/constraints/Identity.js';
import IdentityConstraint from '../../schema/tbl/constraints/IdentityConstraint.js';
import TablePrimaryKey from '../../schema/tbl/constraints/TablePrimaryKey.js';

@@ -75,4 +75,4 @@ import TableForeignKey from '../../schema/tbl/constraints/TableForeignKey.js';

static get CLAUSE() { return 'ADD'; }
static NODE_TYPES = [Identity,TablePrimaryKey,TableForeignKey,TableUniqueKey,CheckConstraint,Index,Column];
static NODE_TYPES = [IdentityConstraint,TablePrimaryKey,TableForeignKey,TableUniqueKey,CheckConstraint,Index,Column];
static KINDS = ['COLUMN','CONSTRAINT', 'PRIMARY_KEY', 'FOREIGN_KEY', 'UNIQUE_KEY', 'CHECK', 'FULLTEXT_INDEX', 'SPATIAL_INDEX', 'INDEX', 'KEY'];
}

2

src/lang/ddl/drop/Drop.js

@@ -61,3 +61,3 @@

static get CLAUSE() { return 'DROP'; }
static KINDS = ['COLUMN', 'CONSTRAINT', 'PRIMARY_KEY', 'FOREIGN_KEY', 'UNIQUE_KEY', 'CHECK', 'INDEX', 'KEY', 'IDENTITY', 'EXPRESSION', 'DEFAULT', 'NOT_NULL'];
static KINDS = ['COLUMN', 'CONSTRAINT', 'PRIMARY_KEY', 'FOREIGN_KEY', 'UNIQUE_KEY', 'CHECK', 'INDEX', 'KEY', 'IDENTITY', 'EXPRESSION', 'DEFAULT', 'NOT_NULL', 'NULL', 'AUTO_INCREMENT', 'ON_UPDATE'];
}

2

src/lang/ddl/rename/Rename.js

@@ -56,3 +56,3 @@

static parse(context, expr) {
const [ match, kind = '', name_unescaped, /*esc*/, name_escaped, argument_unescaped, /*esc*/, argument_escaped ] = (new RegExp(`^${ this.CLAUSE }\\s+(?:(${ this.KINDS.map(s => s).join('|') })\\s+)?(?:(?:(\\w+)|([\`"])((?:\\3\\3|[^\\3])+)\\3)\\s+)?(?:TO|AS)\\s+(?:(\\w+)|([\`"])((?:\\5\\5|[^\\5])+)\\5)$`, 'i')).exec(expr.trim()) || [];
const [ match, kind = '', name_unescaped, /*esc*/, name_escaped, argument_unescaped, /*esc*/, argument_escaped ] = (new RegExp(`^${ this.CLAUSE }\\s+(?:(${ this.KINDS.map(s => s).join('|') })\\s+)?(?:(?:(\\w+)|([\`"])((?:\\3\\3|[^\\3])+)\\3)\\s+)?(?:TO|AS)\\s+(?:(\\w+)|([\`"])((?:\\6\\6|[^\\6])+)\\6)$`, 'i')).exec(expr.trim()) || [];
if (!match) return;

@@ -59,0 +59,0 @@ const instance = new this(context, kind.replace(/\s+/g, '_').toUpperCase());

6

src/lang/dml/insert/AssignmentList.js

@@ -29,5 +29,5 @@

else value_s = Expr.cast(this, value_s);
} else if (!(target_s instanceof AbstractNode)) {
target_s = Identifier.fromJson(this, target_s);
value_s = Expr.cast(this, value_s);
} else {
target_s = target_s instanceof AbstractNode ? target_s : Identifier.fromJson(this, target_s);
value_s = value_s instanceof AbstractNode ? value_s : Expr.cast(this, value_s);
}

@@ -34,0 +34,0 @@ this.ENTRIES.push([target_s, value_s]);

6

src/lang/dml/insert/ColumnsList.js

@@ -21,3 +21,3 @@

*/
list(...args) { return this.build('LIST', args, Identifier); }
list(...args) { return (this.build('LIST', args, Identifier), this); }

@@ -27,3 +27,3 @@ /**

*/
toJson() { return { list: this.LIST.slice(0) }; }
toJson() { return { list: this.LIST.map(col => col.toJson()) }; }

@@ -35,3 +35,3 @@ /**

if (!Array.isArray(json?.list)) return;
return (new this(context)).col(...json.list);
return (new this(context)).list(...json.list);
}

@@ -38,0 +38,0 @@

16

src/lang/dml/insert/InsertStatement.js

@@ -19,3 +19,3 @@

TABLE = null;
COLUMNS_LIST = [];
COLUMNS_CLAUSE = null;
VALUES_LIST = [];

@@ -47,3 +47,3 @@ SET_CLAUSE = null;

/**
* Builds the statement's COLUMNS_LIST
* Builds the statement's COLUMNS_CLAUSE
*

@@ -54,3 +54,3 @@ * .columns('col1', 'col2');

*/
columns(...columns) { return this.build('COLUMNS_LIST', columns, ColumnsList, 'list'); }
columns(...columns) { return this.build('COLUMNS_CLAUSE', columns, ColumnsList, 'list'); }

@@ -113,8 +113,8 @@ /**

table: this.TABLE.toJson(),
columns_list: this.COLUMNS_LIST.toJson(),
values_list: this.VALUES_LIST.toJson(),
columns_clause: this.COLUMNS_CLAUSE?.toJson(),
values_list: this.VALUES_LIST.map(valuesList => valuesList.toJson()),
set_clause: this.SET_CLAUSE?.toJson(),
select_clause: this.SELECT_CLAUSE?.toJson(),
on_conflict_clause: this.ON_CONFLICT_CLAUSE?.toJson(),
returning_list: this.RETURNING_LIST,
returning_list: this.RETURNING_LIST.slice(0),
flags: this.FLAGS,

@@ -131,3 +131,3 @@ };

instance.into(json.table);
if (json.columns_list?.length) instance.columns(...json.columns_list);
if (json.columns_clause) instance.columns(json.columns_clause);
if (json.values_list?.length) instance.values(...json.values_list);

@@ -150,3 +150,3 @@ if (json.set_clause) instance.set(json.set_clause);

else {
if (this.COLUMNS_LIST) sql.push(this.COLUMNS_LIST);
if (this.COLUMNS_CLAUSE) sql.push(this.COLUMNS_CLAUSE);
if (this.SELECT_CLAUSE) sql.push(this.SELECT_CLAUSE);

@@ -153,0 +153,0 @@ else sql.push('VALUES', this.VALUES_LIST);

2

src/lang/dml/insert/ValuesList.js

@@ -21,3 +21,3 @@

*/
list(...args) { return this.build('LIST', args, Expr.Types); }
list(...args) { return (this.build('LIST', args, Expr.Types), this); }

@@ -24,0 +24,0 @@ /**

2

src/lang/schema/db/DatabaseSchema.js

@@ -39,3 +39,3 @@

if (typeof table === 'string') return this.TABLES.find(tbl => this.isSame(tbl.name().NAME, table, 'ci'));
return (this.build('TABLES', [table], TableSchema), this);
return (this.build('TABLES', [table], TableSchema), this.TABLES[this.TABLES.length - 1]);
}

@@ -42,0 +42,0 @@

24

src/lang/schema/tbl/Column.js

@@ -5,13 +5,13 @@

import AbstractNode from '../AbstractNode.js';
import AutoIncrement from './constraints/AutoIncrement.js';
import Expression from './constraints/Expression.js';
import Identity from './constraints/Identity.js';
import Default from './constraints/Default.js';
import NotNull from './constraints/NotNull.js';
import ColumnPrimaryKey from './constraints/ColumnPrimaryKey.js';
import ColumnForeignKey from './constraints/ColumnForeignKey.js';
import ColumnUniqueKey from './constraints/ColumnUniqueKey.js';
import AutoIncrementConstraint from './constraints/AutoIncrementConstraint.js';
import ExpressionConstraint from './constraints/ExpressionConstraint.js';
import IdentityConstraint from './constraints/IdentityConstraint.js';
import DefaultConstraint from './constraints/DefaultConstraint.js';
import NotNullConstraint from './constraints/NotNullConstraint.js';
import PrimaryKey from './constraints/PrimaryKey.js';
import ForeignKey from './constraints/ForeignKey.js';
import UniqueKey from './constraints/UniqueKey.js';
import CheckConstraint from './constraints/CheckConstraint.js';
import OnUpdate from './constraints/OnUpdate.js';
import Null from './constraints/Null.js';
import OnUpdateClause from './constraints/OnUpdateClause.js';
import NullConstraint from './constraints/NullConstraint.js';
import DataType from './DataType.js';

@@ -37,3 +37,3 @@

*/
static CONSTRAINT_TYPES = [AutoIncrement,Identity,Expression,Default,NotNull,Null,OnUpdate,ColumnPrimaryKey,ColumnForeignKey,ColumnUniqueKey,CheckConstraint];
static CONSTRAINT_TYPES = [AutoIncrementConstraint,IdentityConstraint,ExpressionConstraint,DefaultConstraint,NotNullConstraint,NullConstraint,OnUpdateClause,PrimaryKey,ForeignKey,UniqueKey,CheckConstraint];

@@ -141,3 +141,3 @@ /**

if (!this.isSame(typeA, typeB)) this.type(typeB);
for (const type of ['IDENTITY', 'EXPRESSION', 'NOT_NULL', 'DEFAULT', 'AUTO_INCREMENT', 'ON_UPDATE']) {
for (const type of ['IDENTITY', 'EXPRESSION', 'NOT_NULL', 'NULL', 'DEFAULT', 'AUTO_INCREMENT', 'ON_UPDATE']) {
const consA = this.constraint(type);

@@ -144,0 +144,0 @@ const consB = nodeB.constraint(type);

2

src/lang/schema/tbl/constraints/AbstractLevel1Constraint.js

@@ -15,3 +15,3 @@

*/
static get TYPE() { return _fromCamel(this.name.replace(/TABLE|COLUMN|CONSTRAINT/ig, ''), '_').toUpperCase(); }
static get TYPE() { return _fromCamel(this.name.replace(/TABLE|COLUMN|CONSTRAINT|CLAUSE/ig, ''), '_').toUpperCase(); }

@@ -18,0 +18,0 @@ /**

4

src/lang/schema/tbl/constraints/AbstractLevel2Constraint.js

@@ -18,3 +18,3 @@

let json = { type: this.TYPE, ...super.toJson(), };
if (!('name' in json)) {
if (!('name' in json) && this.params.dialect !== 'mysql') {
// Key needs to be present

@@ -31,3 +31,3 @@ json = { ...json, name: undefined };

if (json?.type !== this.TYPE) return;
if (!('name' in json)) {
if (!('name' in json) && context?.params?.dialect !== 'mysql') {
// Automatically generate a default name for PRIMARY_KEY,FOREIGN_KEY,UNIQUE_KEY,CHECK

@@ -34,0 +34,0 @@ json = { ...json, name: `auto_name_${ ( 0 | Math.random() * 9e6 ).toString( 36 ) }` };

4

src/lang/schema/tbl/constraints/TableForeignKey.js
import Lexer from '../../../Lexer.js';
import ColumnForeignKey from './ColumnForeignKey.js';
import ForeignKey from './ForeignKey.js';
import AbstractTableConstraint from './AbstractTableConstraint.js';
export default class TableForeignKey extends AbstractTableConstraint(ColumnForeignKey) {
export default class TableForeignKey extends AbstractTableConstraint(ForeignKey) {

@@ -8,0 +8,0 @@ /**

4

src/lang/schema/tbl/constraints/TablePrimaryKey.js
import ColumnPrimaryKey from "./ColumnPrimaryKey.js";
import PrimaryKey from "./PrimaryKey.js";
import AbstractTableConstraint from './AbstractTableConstraint.js';
export default class TablePrimaryKey extends AbstractTableConstraint(ColumnPrimaryKey) {}
export default class TablePrimaryKey extends AbstractTableConstraint(PrimaryKey) {}

4

src/lang/schema/tbl/constraints/TableUniqueKey.js
import AbstractTableConstraint from './AbstractTableConstraint.js';
import ColumnUniqueKey from "./ColumnUniqueKey.js";
import UniqueKey from "./UniqueKey.js";
export default class TableUniqueKey extends AbstractTableConstraint(ColumnUniqueKey) {}
export default class TableUniqueKey extends AbstractTableConstraint(UniqueKey) {}

28

src/lang/schema/tbl/TableSchema.js

@@ -10,3 +10,3 @@

import TablePrimaryKey from './constraints/TablePrimaryKey.js';
import ColumnForeignKey from './constraints/ColumnForeignKey.js';
import ForeignKey from './constraints/ForeignKey.js';
import TableForeignKey from './constraints/TableForeignKey.js';

@@ -61,3 +61,3 @@ import TableUniqueKey from './constraints/TableUniqueKey.js';

*/
primaryKey() { return this.NODES.find(node => node.TYPE === 'PRIMARY_KEY'); }
primaryKey() { return [...this.NODES].find(node => node.TYPE === 'PRIMARY_KEY'); }

@@ -73,3 +73,3 @@ /**

if (typeof column === 'string') return this.COLUMNS.find(col => this.isSame(col.name(), column, 'ci'));
return (this.build('COLUMNS', [column], Column), this);
return (this.build('COLUMNS', [column], Column), this.COLUMNS[this.COLUMNS.length - 1]);
}

@@ -85,4 +85,4 @@

constraint(constraint) {
if (typeof constraint === 'string') return this.NODES.find(node => node instanceof AbstractLevel1Constraint && this.isSame(node.name(), constraint, 'ci'));
return (this.build('CONSTRAINTS', [constraint], this.constructor.CONSTRAINT_TYPES), this);
if (typeof constraint === 'string') return this.CONSTRAINTS.find(cons => this.isSame(cons.name(), constraint, 'ci'));
return (this.build('CONSTRAINTS', [constraint], this.constructor.CONSTRAINT_TYPES), this.CONSTRAINTS[this.CONSTRAINTS.length - 1]);
}

@@ -99,3 +99,3 @@

if (typeof index === 'string') return this.INDEXES.find(idx => this.isSame(idx.name(), index, 'ci'));
return (this.build('INDEXES', [index], Index), this);
return (this.build('INDEXES', [index], Index), this.INDEXES[this.INDEXES - 1]);
}

@@ -160,3 +160,3 @@

const getNode = (reference, ifExists = false) => {
const node = this.NODES.find(node => {
const node = [...this.NODES].find(node => {
return (reference.kind === 'COLUMN' ? node instanceof Column : (reference.kind === 'CONSTRAINT' ? node instanceof AbstractLevel2Constraint : node.TYPE === reference.kind/* constraint or index */))

@@ -213,3 +213,3 @@ && (!reference.name ? reference.kind === 'PRIMARY_KEY'/* mysql only */ : this.isSame(node.NAME, reference.name, 'ci'))

node.constraint(subAction.KIND, subAction.argument());
} else if (subAction.KIND === 'NOT_NULL') {
} else if (['NOT_NULL', 'NULL', 'AUTO_INCREMENT'].includes(subAction.KIND)) {
node.constraint(subAction.KIND, true);

@@ -261,3 +261,3 @@ } else if (subAction.KIND === 'IDENTITY') {

if ((col.$TYPE && !this.isSame(col.$TYPE.toJson(), col.TYPE.toJson(), 'ci'))
|| (col.CONSTRAINTS.some(cons => ['EXPRESSION', 'NOT_NULL', 'AUTO_INCREMENT', 'ON_UPDATE'].includes(cons.TYPE) && constraintDirty(cons, true)))) {
|| (col.CONSTRAINTS.some(cons => ['EXPRESSION', 'NOT_NULL', 'NULL', 'AUTO_INCREMENT', 'ON_UPDATE'].includes(cons.TYPE) && constraintDirty(cons, true)))) {
const columnClone = col.clone();

@@ -293,3 +293,3 @@ columnClone.CONSTRAINTS = columnClone.CONSTRAINTS.filter(cons => !(cons instanceof AbstractLevel2Constraint));

else if (['DEFAULT'/*, 'ON_UPDATE'*//*useless in postgres*/].includes(cons.TYPE)) return { clause: 'SET', kind: cons.TYPE, argument: cons.expr() };
else if (cons.TYPE === 'NOT_NULL') return { clause: 'SET', kind: 'NOT_NULL' };
else if (['NOT_NULL'/*, 'NULL'*//*pretty useless in both languages*/].includes(cons.TYPE)) return { clause: 'SET', kind: cons.TYPE };
})());

@@ -368,3 +368,3 @@ }

for (const node of this.NODES) {
if (!(node instanceof ColumnForeignKey)) continue;
if (!(node instanceof ForeignKey)) continue;
if (node.targetTable().basename() !== db.NAME) continue;

@@ -382,3 +382,3 @@ if (altType === 'DOWN') node.drop();

for (const node of this.NODES) {
if (!(node instanceof ColumnForeignKey)) continue;
if (!(node instanceof ForeignKey)) continue;
if (node.targetTable().basename() && tbl.basename() && node.targetTable().basename() !== tbl.basename()) continue;

@@ -398,3 +398,3 @@ if (node.targetTable().name() === tbl.NAME) {

for (const node of this.NODES) {
if (!(node instanceof ColumnForeignKey)) continue;
if (!(node instanceof ForeignKey)) continue;
if (node.targetTable().basename() && col.$trace('get:name:database') && node.targetTable().basename() !== col.$trace('get:name:database')) continue;

@@ -447,3 +447,3 @@ if (node.targetTable().name() !== col.$trace('get:table:name')) continue;

const constraint = col.foreignKey();
if (constraint) return constraints.concat(TableForeignKey.fromJson(this, constraint.toJson()).columns([col.name()]));
if (constraint) return constraints.concat(TableForeignKey.fromJson(this, { ...constraint.toJson(), columns: [col.name()] }));
return constraints;

@@ -450,0 +450,0 @@ }, []));

102

test/foo.js

@@ -9,16 +9,28 @@

let driver, dialect = 'mysql', dbPublic;
// ---------------------------------
const maClient = await mariadb.createConnection({
host: '127.0.0.1',
user: 'root',
password: '',
port: 3306,
});
if (dialect === 'mysql') {
// JSON support: MariaDB 10.2.7, MySQL 5.7.8
// DEFAULT (uuid()) support: MariaDB 10.3, MySQL 8.0
driver = await mariadb.createConnection({
host: '127.0.0.1',
user: 'root',
password: '',
port: 3306,
// -------
database: 'test',
multipleStatements: true,
bitOneIsBoolean: true, // default
trace: true,
});
dbPublic = 'test';
} else {
driver = new pg.Client({
host: 'localhost',
port: 5432,
});
await driver.connect();
dbPublic = 'public';
}
// ---------------------------------
const pgClient = new pg.Client({
host: 'localhost',
port: 5432,
});
await pgClient.connect();
// ---------------------------------

@@ -29,31 +41,31 @@ let showQuery = false;

if (showQuery) console.log('SQL:', ...arguments);
return pgClient.query(...arguments);
return driver.query(...arguments);
}
}, { dialect: 'postgres' });
}, { dialect });
console.log('---DATABSES BEFORE:', await lqlClient.databases());
console.log('---PUBLIC TABLES BEFORE:', await lqlClient.database('public').tables());
console.log('---PUBLIC TABLES BEFORE:', await lqlClient.database(dbPublic).tables());
/*
*/
console.log('DROP 5', await lqlClient.query('DROP SCHEMA if exists obj_information_schema CASCADE', { noCreateSavepoint: true }));
console.log('DROP 5', await lqlClient.query('DROP SCHEMA if exists test_db CASCADE', { noCreateSavepoint: true }));
console.log('DROP 3', await lqlClient.query('DROP TABLE if exists public.books CASCADE', { noCreateSavepoint: true }));
console.log('DROP 2', await lqlClient.query('DROP TABLE if exists public.users CASCADE', { noCreateSavepoint: true }));
console.log('DROP 1', await lqlClient.query('DROP TABLE if exists public.roles CASCADE', { noCreateSavepoint: true }));
console.log('DROP 5', await lqlClient.query(`DROP SCHEMA if exists obj_information_schema${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 5', await lqlClient.query(`DROP SCHEMA if exists test_db${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 3', await lqlClient.query(`DROP TABLE if exists ${ dbPublic }.books${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 2', await lqlClient.query(`DROP TABLE if exists ${ dbPublic }.users${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 1', await lqlClient.query(`DROP TABLE if exists ${ dbPublic }.roles${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('....create roles......', await lqlClient.query(`CREATE TABLE roles (
id int primary key generated always as identity,
name varchar,
name varchar(100),
created_time timestamp
)`, { description: 'Created roles' }));
const savepoint1 = await lqlClient.database('public').savepoint();
const savepoint1 = await lqlClient.database(dbPublic).savepoint();
console.log('.....create users.....', await lqlClient.query(`CREATE TABLE users (
id int primary key generated always as identity,
title varchar default '...',
name varchar,
title varchar(100) default '...',
name varchar(100),
role int references roles (id),
created_time timestamp
)`, { description: 'Created users' }));
const savepoint2 = await lqlClient.database('public').savepoint();
const savepoint2 = await lqlClient.database(dbPublic).savepoint();

@@ -64,4 +76,4 @@ console.log('.....create test_db.....', await lqlClient.query(`CREATE SCHEMA test_db`));

id int primary key generated always as identity,
title varchar,
name varchar,
title varchar(100),
name varchar(100),
created_time timestamp

@@ -73,9 +85,9 @@ )`, { description: 'Created users' }));

id int primary key generated always as identity,
title varchar,
content varchar,
title varchar(100),
content varchar(100),
author int references users (id),
created_timeeee timestamp (3) without time zone
created_timeeee timestamp (3)
)`, { description: 'Created books' }));
const savepoint3 = await lqlClient.database('public').savepoint();
console.log('\n\n\n\n\n\ntables---------', await lqlClient.database('public').tables());
const savepoint3 = await lqlClient.database(dbPublic).savepoint();
console.log('\n\n\n\n\n\ntables---------', await lqlClient.database(dbPublic).tables());

@@ -92,7 +104,7 @@ console.log('rollback 3', await savepoint3.rollback());

id int primary key generated always as identity,
title varchar,
content varchar,
title varchar(100),
content varchar(100),
created_time timestamp
)`, { description: 'Created publications' }));
const savepoint4 = await lqlClient.database('public').savepoint();
const savepoint4 = await lqlClient.database(dbPublic).savepoint();
// Should see: 1,2,3,7

@@ -103,3 +115,3 @@ console.log('\n\n\n\n\n\nall savepoints-----', ...(await lqlClient.database('obj_information_schema').table('database_savepoints').select()));

for (let i = 0; i < 3; i ++) {
await (await lqlClient.database('public').savepoint({ direction: 'forward' })).rollback();
await (await lqlClient.database(dbPublic).savepoint({ direction: 'forward' })).rollback();
}

@@ -113,5 +125,5 @@ // Should see: 1,2,3

//const ww = await lqlClient.query(`SELECT title, content, author ~> name, author ~> role ~> name role_name FROM books where author ~> role ~> name = 'admin'`);
//const ww = await lqlClient.query(`SELECT name, role <~ author <~ books ~> title FROM roles`);
const ww = await lqlClient.query(`SELECT users.name, roles.name as role_name FROM users LEFT JOIN roles ON roles.id = users.role where roles.name = $1`, { values: ['admin'] });
//const ww = await lqlClient.query(`SELECT title, content, author ~> name, author ~> role ~> name role_name FROM books as BBBBB where author ~> role ~> name = 'admin'`);
const ww = await lqlClient.query(`SELECT name, role <~ author <~ books ~> title FROM roles`);
//const ww = await lqlClient.query(`SELECT users.name, roles.name as role_name FROM users LEFT JOIN roles ON roles.id = users.role where roles.name = ${ dialect === 'mysql' ? '?' : '$1' }`, { values: ['admin'] });
console.log(ww);

@@ -121,8 +133,8 @@ }

// Clean up
console.log('DROP 1', await lqlClient.query('DROP TABLE if exists public.roles CASCADE', { noCreateSavepoint: true }));
console.log('DROP 2', await lqlClient.query('DROP TABLE if exists public.users CASCADE', { noCreateSavepoint: true }));
console.log('DROP 3', await lqlClient.query('DROP TABLE if exists public.books CASCADE', { noCreateSavepoint: true }));
console.log('DROP 5', await lqlClient.query('DROP SCHEMA if exists test_db CASCADE', { noCreateSavepoint: true }));
console.log('DROP 5', await lqlClient.query('DROP SCHEMA if exists obj_information_schema CASCADE', { noCreateSavepoint: true }));
console.log('---PUBLIC TABLES AFTER:', await lqlClient.database('public').tables());
console.log('DROP 3', await lqlClient.query(`DROP TABLE if exists ${ dbPublic }.books${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 2', await lqlClient.query(`DROP TABLE if exists ${ dbPublic }.users${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 1', await lqlClient.query(`DROP TABLE if exists ${ dbPublic }.roles${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 5', await lqlClient.query(`DROP SCHEMA if exists test_db${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('DROP 5', await lqlClient.query(`DROP SCHEMA if exists obj_information_schema${ dialect === 'mysql' ? '' : ' CASCADE' }`, { noCreateSavepoint: true }));
console.log('---PUBLIC TABLES AFTER:', await lqlClient.database(dbPublic).tables());
console.log('---DATABSES AFTER:', await lqlClient.databases());

@@ -129,0 +141,0 @@

6

test/schema.pg.test.js

@@ -43,3 +43,3 @@

)`;
const tblCreateInstance1 = await Parser.parse({ name: 'some_database' }, createTableSql, null, { log: false });
const tblCreateInstance1 = await Parser.parse({ name: 'some_database', params: { inputDialect: 'postgres', dialect: 'mysql' } }, createTableSql, null, { log: false });
const tblCreateInstance2 = CreateStatement.fromJson(tblCreateInstance1.CONTEXT, tblCreateInstance1.toJson());

@@ -65,3 +65,3 @@ const sql1 = tblCreateInstance1 + '';

SET SCHEMA "newDDDDD Dbbbbbb",
RENAME constraint "constrai_ _nt_name1" TO new_constraint_name,
RENAME constraint "constrai_""_nt_name1" TO "new_cons""traint_name",

@@ -89,3 +89,3 @@ ADD constraint constraint_name2 PRIMARY KEY (col_name1),

`;
const tblAlterInstance1 = Parser.parse({ name: 'some_database' }, alterTableSql);
const tblAlterInstance1 = Parser.parse({ name: 'some_database', params: { inputDialect: 'postgres', dialect: 'mysql' } }, alterTableSql);
const tblAlterInstance2 = AlterStatement.fromJson(tblAlterInstance1.CONTEXT, tblAlterInstance1.toJson());

@@ -92,0 +92,0 @@ const sql1 = tblAlterInstance1 + '';

2

test/select.pg.test.js

@@ -42,3 +42,3 @@

it(`"Build a query with the imperative api and stringify`, async function() {
const query1 = new SelectStatement(sqlClient);
const query1 = new SelectStatement({ name: 'some_database', params: { inputDialect: 'postgres', dialect: 'mysql' } });
// JSON forms

@@ -45,0 +45,0 @@ query1.select(

src/lang/schema/tbl/constraints/AutoIncrement.js
src/lang/schema/tbl/constraints/ColumnForeignKey.js
src/lang/schema/tbl/constraints/ColumnPrimaryKey.js
src/lang/schema/tbl/constraints/ColumnUniqueKey.js
src/lang/schema/tbl/constraints/Default.js
src/lang/schema/tbl/constraints/Expression.js
src/lang/schema/tbl/constraints/Identity.js
src/lang/schema/tbl/constraints/NotNull.js
src/lang/schema/tbl/constraints/Null.js
src/lang/schema/tbl/constraints/OnUpdate.js
dist/main.js

Sorry, the diff of this file is too big to display

dist/main.js.map

Sorry, the diff of this file is not supported yet

dist/sql.js

Sorry, the diff of this file is too big to display

dist/sql.js.map

Sorry, the diff of this file is not supported yet

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc