Pacharapol Withayasakpunt

Posted on May 3, 2020

What SQLite CAN actually do

#sqlite #sql #discuss #database

Not sure about other RDBMS's.

JSON handling

This is possible via JSON1 extension. I believe the functions and syntaxes are different in PostGRES.

SELECT json_extract(col1, '$.c[2]') FROM table
WHERE json_extract(col2, '$.x') > 3;

RegExp handling

The extension must be installed first, unlike JSON1.

I am not sure how this works for Python and Node.js, though. Maybe, this in better-sqlite3, but better-sqlite3 has a problem with Electron. This is important, because LIKE in SQLite is case-insensitive (i.e. ILIKE).

SELECT field FROM table WHERE field REGEXP '123';

User-defined functions

I don't sure how this works either. I'd be happy to write and compile C myself.

Sort Null first, Null last

This is a built-in functionality, just like aggregate functions.

SORT BY field ASC NULLS LAST

Add column and rename table

This is easy, as in ALTER TABLE

ALTER TABLE table_name ADD COLUMN column_definition;

Overcoming ALTER TABLE / COLUMN limitations

This is done by

Create new table
Copy data
Drop old table
Rename new into old

So, to create a new table, you must know to get schema.

It's called PRAGMA. It generates a table. If you want to preview this in sqlite CLI, don't forget to .header on.

PRAGMA table_info(table_name);

PRAGMA index_list(table_name);

PRAGMA index_info(index_name);

So, it becomes,

-- disable foreign key constraint check
PRAGMA foreign_keys=off;

-- start a transaction
BEGIN TRANSACTION;

-- Here you can drop column or rename column
CREATE TABLE IF NOT EXISTS new_table( 
   column_definition,
   ...
);
-- copy data from the table to the new_table
INSERT INTO new_table(column_list)
SELECT column_list
FROM table;

-- drop the table
DROP TABLE table;

-- rename the new_table to the table
ALTER TABLE new_table RENAME TO table; 

-- commit the transaction
COMMIT;

-- enable foreign key constraint check
PRAGMA foreign_keys=on;

This is my implementation.

Bonus

Listing table names programmatically

There is a hidden table called sqlite_master.

SELECT [name] FROM sqlite_master
WHERE [type] = 'table' AND [name] NOT LIKE 'sqlite_%';

Safe identifier names for tables and columns

I made a function for this, which I used in Liteorm and this new project.

/**
 * https://www.sqlite.org/lang_keywords.html
 * @param s identifier
 */
export function safeColumnName (s: string) {
  const keywords = `
    ABORT
    ACTION
    ADD
    AFTER
    ALL
    ALTER
    ALWAYS
    ANALYZE
    AND
    AS
    ASC
    ATTACH
    AUTOINCREMENT
    BEFORE
    BEGIN
    BETWEEN
    BY
    CASCADE
    CASE
    CAST
    CHECK
    COLLATE
    COLUMN
    COMMIT
    CONFLICT
    CONSTRAINT
    CREATE
    CROSS
    CURRENT
    CURRENT_DATE
    CURRENT_TIME
    CURRENT_TIMESTAMP
    DATABASE
    DEFAULT
    DEFERRABLE
    DEFERRED
    DELETE
    DESC
    DETACH
    DISTINCT
    DO
    DROP
    EACH
    ELSE
    END
    ESCAPE
    EXCEPT
    EXCLUDE
    EXCLUSIVE
    EXISTS
    EXPLAIN
    FAIL
    FILTER
    FIRST
    FOLLOWING
    FOR
    FOREIGN
    FROM
    FULL
    GENERATED
    GLOB
    GROUP
    GROUPS
    HAVING
    IF
    IGNORE
    IMMEDIATE
    IN
    INDEX
    INDEXED
    INITIALLY
    INNER
    INSERT
    INSTEAD
    INTERSECT
    INTO
    IS
    ISNULL
    JOIN
    KEY
    LAST
    LEFT
    LIKE
    LIMIT
    MATCH
    NATURAL
    NO
    NOT
    NOTHING
    NOTNULL
    NULL
    NULLS
    OF
    OFFSET
    ON
    OR
    ORDER
    OTHERS
    OUTER
    OVER
    PARTITION
    PLAN
    PRAGMA
    PRECEDING
    PRIMARY
    QUERY
    RAISE
    RANGE
    RECURSIVE
    REFERENCES
    REGEXP
    REINDEX
    RELEASE
    RENAME
    REPLACE
    RESTRICT
    RIGHT
    ROLLBACK
    ROW
    ROWS
    SAVEPOINT
    SELECT
    SET
    TABLE
    TEMP
    TEMPORARY
    THEN
    TIES
    TO
    TRANSACTION
    TRIGGER
    UNBOUNDED
    UNION
    UNIQUE
    UPDATE
    USING
    VACUUM
    VALUES
    VIEW
    VIRTUAL
    WHEN
    WHERE
    WINDOW
    WITH
    WITHOUT`
    .split('\n')
    .map((el) => el.trim())
    .filter((el) => el)

  /**
   * https://stackoverflow.com/questions/31788990/sqlite-what-are-the-restricted-characters-for-identifiers
   */
  const validIdToken = 'A-Z0-9_$:'
  const kwRegex = new RegExp(`(^|[^${validIdToken}\\)])(${keywords.join('|')})($|[^${validIdToken}\\()])`, 'gi')

  return s.replace(kwRegex, (_, p1, p2, p3) => {
    return `${p1}"${p2.replace(/"/g, '["]')}"${p3}`
  })
}

So, I actually made a SQLite editor for nothing. Not sure about practicality. Originally made for NoSQL editor, but no offline NoSQL beats SQLite in terms of safety and functionality.

It is made to be somewhat similar to AirTable and Google Sheets; but is local and is optimized for a database.

patarapolw / hotcell

Yet another SQLite Editor

Top comments (1)

Michael Z • Nov 23 '20 • Edited

Something I like about sqlite is that you can just have a database in-memory or in a file, that's great for running tests against. It's possible even if you actually use a different sql flavor (thanks ORMs!)

DEV Community