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.
Top comments (1)
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!)