VSCode sqlite SQL snippets

_sql.json

// create table: ct
// foreign key:  fk
// create key with type INTEGER, TEXT, BLOB, NULL, REAL respectively: cki, ckt, ckb, ckn, ckr
// create key with type TEXT + PRIMARY KEY: cktp
// create key with type REAL + NOT NULL: ckrn
// create key with type INTEGER + UNIQUE: ckiu
// PRIMARY KEY, NOT NULL, UNIQUE can be chained in any combination. E.g: ckipnu, ckrun, ckiupn
{
	"Create Table": {
		"prefix" : "ct",
		"body": [
			"CREATE TABLE IF NOT EXISTS ${1/(.)(.*)/${1:/upcase}${2}/} (",
			"\t$0",
			");"
		],
		"description": "create table"
	},
	"Foreign Key":{
		"prefix": "fk",
		"body": "FOREIGN KEY(${1:key}) REFERENCES ${2:OtherTable}(${3:OtherKey}),$0",
	},
	"Create key type: NULL": {"prefix": "ckn", "body": "${1:key} NULL,$0"}, "Create key type: NULL. Extra: PRIMARY KEY": {"prefix": "cknp", "body": "${1:key} NULL PRIMARY KEY,$0"}, "Createkey type: NULL. Extra: NOT NULL": {"prefix": "cknn", "body": "${1:key} NULL NOT NULL,$0"}, "Create key type: NULL. Extra: UNIQUE": {"prefix": "cknu", "body": "${1:key} NULL UNIQUE,$0"},"Create key type: NULL. Extra: PRIMARY KEY NOT NULL": {"prefix": "cknpn", "body": "${1:key} NULL PRIMARY KEY NOT NULL,$0"}, "Create key type: NULL. Extra: PRIMARY KEY UNIQUE": {"prefix": "cknpu", "body": "${1:key} NULL PRIMARY KEY UNIQUE,$0"}, "Create key type: NULL. Extra: NOTNULL PRIMARY KEY": {"prefix": "cknnp", "body": "${1:key} NULL NOT NULL PRIMARY KEY,$0"}, "Create key type: NULL. Extra: NOT NULL UNIQUE": {"prefix": "cknnu", "body": "${1:key} NULL NOT NULL UNIQUE,$0"}, "Create key type: NULL. Extra: UNIQUE PRIMARY KEY": {"prefix": "cknup", "body": "${1:key} NULL UNIQUE PRIMARY KEY,$0"}, "Create key type: NULL. Extra: UNIQUE NOT NULL":{"prefix": "cknun", "body": "${1:key} NULL UNIQUE NOT NULL,$0"}, "Create key type: NULL. Extra: PRIMARY KEY NOT NULL UNIQUE": {"prefix": "cknpnu", "body": "${1:key} NULL PRIMARY KEY NOTNULL UNIQUE,$0"}, "Create key type: NULL. Extra: PRIMARY KEY UNIQUE NOT NULL": {"prefix": "cknpun", "body": "${1:key} NULL PRIMARY KEY UNIQUE NOT NULL,$0"}, "Create key type: NULL. Extra: NOT NULL PRIMARY KEY UNIQUE": {"prefix": "cknnpu", "body": "${1:key} NULL NOT NULL PRIMARYKEY UNIQUE,$0"}, "Create key type: NULL. Extra: NOT NULL UNIQUE PRIMARY KEY": {"prefix": "cknnup", "body": "${1:key} NULL NOT NULL UNIQUE PRIMARY KEY,$0"}, "Create key type: NULL. Extra: UNIQUE PRIMARY KEY NOT NULL": {"prefix": "cknupn", "body": "${1:key} NULL UNIQUE PRIMARY KEY NOT NULL,$0"}, "Create key type: NULL. Extra: UNIQUE NOT NULL PRIMARY KEY": {"prefix": "cknunp", "body": "${1:key} NULL UNIQUE NOT NULL PRIMARY KEY,$0"}, "Create key type: INTEGER": {"prefix": "cki", "body": "${1:key} INTEGER,$0"}, "Create key type: INTEGER. Extra: PRIMARY KEY": {"prefix": "ckip", "body": "${1:key} INTEGER PRIMARY KEY,$0"}, "Create key type: INTEGER. Extra: NOT NULL": {"prefix": "ckin", "body": "${1:key} INTEGER NOT NULL,$0"}, "Create key type: INTEGER. Extra: UNIQUE": {"prefix": "ckiu", "body": "${1:key} INTEGER UNIQUE,$0"}, "Createkey type: INTEGER. Extra: PRIMARY KEY NOT NULL": {"prefix": "ckipn", "body": "${1:key} INTEGER PRIMARY KEY NOT NULL,$0"}, "Create key type: INTEGER. Extra: PRIMARY KEY UNIQUE": {"prefix": "ckipu", "body": "${1:key} INTEGER PRIMARY KEY UNIQUE,$0"}, "Create key type: INTEGER. Extra: NOT NULL PRIMARY KEY": {"prefix": "ckinp", "body": "${1:key} INTEGER NOT NULL PRIMARY KEY,$0"}, "Create key type: INTEGER. Extra: NOT NULL UNIQUE": {"prefix": "ckinu", "body": "${1:key} INTEGER NOT NULL UNIQUE,$0"}, "Create key type: INTEGER. Extra: UNIQUE PRIMARY KEY": {"prefix": "ckiup", "body": "${1:key} INTEGER UNIQUE PRIMARY KEY,$0"}, "Create key type: INTEGER.Extra: UNIQUE NOT NULL": {"prefix": "ckiun", "body": "${1:key} INTEGER UNIQUE NOT NULL,$0"},"Create key type: INTEGER. Extra: PRIMARY KEY NOT NULL UNIQUE": {"prefix": "ckipnu", "body":"${1:key} INTEGER PRIMARY KEY NOT NULL UNIQUE,$0"}, "Create key type: INTEGER. Extra: PRIMARY KEY UNIQUE NOT NULL": {"prefix": "ckipun", "body": "${1:key} INTEGER PRIMARY KEY UNIQUE NOTNULL,$0"}, "Create key type: INTEGER. Extra: NOT NULL PRIMARY KEY UNIQUE": {"prefix": "ckinpu", "body": "${1:key} INTEGER NOT NULL PRIMARY KEY UNIQUE,$0"}, "Create key type: INTEGER. Extra: NOT NULL UNIQUE PRIMARY KEY": {"prefix": "ckinup", "body": "${1:key} INTEGER NOT NULL UNIQUE PRIMARY KEY,$0"}, "Create key type: INTEGER. Extra: UNIQUE PRIMARY KEY NOT NULL": {"prefix": "ckiupn", "body": "${1:key} INTEGER UNIQUE PRIMARY KEY NOT NULL,$0"}, "Create key type: INTEGER. Extra: UNIQUE NOT NULL PRIMARY KEY": {"prefix": "ckiunp", "body": "${1:key} INTEGER UNIQUE NOT NULL PRIMARY KEY,$0"}, "Create key type: REAL": {"prefix": "ckr", "body": "${1:key} REAL,$0"}, "Create key type: REAL. Extra: PRIMARY KEY": {"prefix": "ckrp", "body": "${1:key} REAL PRIMARY KEY,$0"}, "Create key type: REAL. Extra: NOT NULL": {"prefix": "ckrn", "body":"${1:key} REAL NOT NULL,$0"}, "Create key type: REAL. Extra: UNIQUE": {"prefix": "ckru", "body": "${1:key} REAL UNIQUE,$0"}, "Create key type: REAL. Extra: PRIMARY KEY NOT NULL": {"prefix": "ckrpn", "body": "${1:key} REAL PRIMARY KEY NOT NULL,$0"}, "Create key type: REAL. Extra: PRIMARY KEY UNIQUE": {"prefix": "ckrpu", "body": "${1:key} REAL PRIMARY KEY UNIQUE,$0"}, "Create key type: REAL. Extra: NOT NULL PRIMARY KEY": {"prefix": "ckrnp", "body": "${1:key} REAL NOT NULL PRIMARY KEY,$0"}, "Create key type: REAL. Extra: NOT NULL UNIQUE": {"prefix": "ckrnu", "body": "${1:key} REAL NOT NULL UNIQUE,$0"}, "Create key type: REAL. Extra: UNIQUE PRIMARY KEY": {"prefix": "ckrup", "body": "${1:key} REAL UNIQUE PRIMARY KEY,$0"}, "Create key type: REAL. Extra: UNIQUE NOT NULL": {"prefix": "ckrun", "body": "${1:key} REAL UNIQUE NOT NULL,$0"}, "Create key type: REAL. Extra: PRIMARY KEY NOT NULL UNIQUE": {"prefix": "ckrpnu", "body": "${1:key} REAL PRIMARY KEY NOT NULL UNIQUE,$0"}, "Create key type: REAL. Extra: PRIMARY KEYUNIQUE NOT NULL": {"prefix": "ckrpun", "body": "${1:key} REAL PRIMARY KEY UNIQUE NOT NULL,$0"}, "Create key type: REAL. Extra: NOT NULL PRIMARY KEY UNIQUE": {"prefix": "ckrnpu", "body":"${1:key} REAL NOT NULL PRIMARY KEY UNIQUE,$0"}, "Create key type: REAL. Extra: NOT NULL UNIQUE PRIMARY KEY": {"prefix": "ckrnup", "body": "${1:key} REAL NOT NULL UNIQUE PRIMARY KEY,$0"}, "Create key type: REAL. Extra: UNIQUE PRIMARY KEY NOT NULL": {"prefix": "ckrupn", "body": "${1:key} REAL UNIQUE PRIMARY KEY NOT NULL,$0"}, "Create key type: REAL. Extra: UNIQUE NOT NULL PRIMARY KEY": {"prefix": "ckrunp", "body": "${1:key} REAL UNIQUE NOT NULL PRIMARY KEY,$0"}, "Create key type: TEXT": {"prefix": "ckt", "body": "${1:key} TEXT,$0"}, "Create key type: TEXT. Extra: PRIMARY KEY": {"prefix": "cktp", "body": "${1:key} TEXT PRIMARY KEY,$0"}, "Createkey type: TEXT. Extra: NOT NULL": {"prefix": "cktn", "body": "${1:key} TEXT NOT NULL,$0"}, "Create key type: TEXT. Extra: UNIQUE": {"prefix": "cktu", "body": "${1:key} TEXT UNIQUE,$0"},"Create key type: TEXT. Extra: PRIMARY KEY NOT NULL": {"prefix": "cktpn", "body": "${1:key} TEXT PRIMARY KEY NOT NULL,$0"}, "Create key type: TEXT. Extra: PRIMARY KEY UNIQUE": {"prefix": "cktpu", "body": "${1:key} TEXT PRIMARY KEY UNIQUE,$0"}, "Create key type: TEXT. Extra: NOTNULL PRIMARY KEY": {"prefix": "cktnp", "body": "${1:key} TEXT NOT NULL PRIMARY KEY,$0"}, "Create key type: TEXT. Extra: NOT NULL UNIQUE": {"prefix": "cktnu", "body": "${1:key} TEXT NOT NULL UNIQUE,$0"}, "Create key type: TEXT. Extra: UNIQUE PRIMARY KEY": {"prefix": "cktup", "body": "${1:key} TEXT UNIQUE PRIMARY KEY,$0"}, "Create key type: TEXT. Extra: UNIQUE NOT NULL":{"prefix": "cktun", "body": "${1:key} TEXT UNIQUE NOT NULL,$0"}, "Create key type: TEXT. Extra: PRIMARY KEY NOT NULL UNIQUE": {"prefix": "cktpnu", "body": "${1:key} TEXT PRIMARY KEY NOTNULL UNIQUE,$0"}, "Create key type: TEXT. Extra: PRIMARY KEY UNIQUE NOT NULL": {"prefix": "cktpun", "body": "${1:key} TEXT PRIMARY KEY UNIQUE NOT NULL,$0"}, "Create key type: TEXT. Extra: NOT NULL PRIMARY KEY UNIQUE": {"prefix": "cktnpu", "body": "${1:key} TEXT NOT NULL PRIMARYKEY UNIQUE,$0"}, "Create key type: TEXT. Extra: NOT NULL UNIQUE PRIMARY KEY": {"prefix": "cktnup", "body": "${1:key} TEXT NOT NULL UNIQUE PRIMARY KEY,$0"}, "Create key type: TEXT. Extra: UNIQUE PRIMARY KEY NOT NULL": {"prefix": "cktupn", "body": "${1:key} TEXT UNIQUE PRIMARY KEY NOT NULL,$0"}, "Create key type: TEXT. Extra: UNIQUE NOT NULL PRIMARY KEY": {"prefix": "cktunp", "body": "${1:key} TEXT UNIQUE NOT NULL PRIMARY KEY,$0"}, "Create key type: BLOB": {"prefix": "ckb", "body": "${1:key} BLOB,$0"}, "Create key type: BLOB. Extra: PRIMARY KEY": {"prefix": "ckbp", "body": "${1:key} BLOB PRIMARY KEY,$0"}, "Create key type: BLOB. Extra: NOT NULL": {"prefix": "ckbn", "body": "${1:key} BLOB NOT NULL,$0"}, "Create key type: BLOB. Extra: UNIQUE": {"prefix": "ckbu", "body": "${1:key} BLOB UNIQUE,$0"}, "Create key type: BLOB. Extra: PRIMARY KEY NOT NULL": {"prefix": "ckbpn", "body": "${1:key} BLOB PRIMARY KEY NOT NULL,$0"}, "Create key type: BLOB. Extra: PRIMARY KEY UNIQUE": {"prefix": "ckbpu", "body": "${1:key} BLOB PRIMARY KEY UNIQUE,$0"}, "Create key type: BLOB. Extra: NOT NULL PRIMARY KEY": {"prefix": "ckbnp", "body": "${1:key} BLOB NOT NULL PRIMARY KEY,$0"}, "Create key type: BLOB. Extra: NOT NULL UNIQUE": {"prefix": "ckbnu", "body": "${1:key} BLOB NOT NULL UNIQUE,$0"}, "Create key type: BLOB. Extra: UNIQUE PRIMARY KEY": {"prefix": "ckbup", "body": "${1:key} BLOB UNIQUE PRIMARY KEY,$0"}, "Create key type: BLOB. Extra: UNIQUE NOT NULL": {"prefix": "ckbun", "body": "${1:key} BLOB UNIQUE NOT NULL,$0"}, "Create key type: BLOB. Extra: PRIMARY KEY NOT NULL UNIQUE": {"prefix": "ckbpnu", "body": "${1:key} BLOB PRIMARY KEY NOT NULL UNIQUE,$0"}, "Create key type: BLOB. Extra: PRIMARY KEY UNIQUE NOT NULL": {"prefix": "ckbpun", "body": "${1:key} BLOB PRIMARY KEY UNIQUE NOT NULL,$0"}, "Create key type: BLOB. Extra: NOT NULL PRIMARY KEY UNIQUE":{"prefix": "ckbnpu", "body": "${1:key} BLOB NOT NULL PRIMARY KEY UNIQUE,$0"}, "Create key type: BLOB. Extra: NOT NULL UNIQUE PRIMARY KEY": {"prefix": "ckbnup", "body": "${1:key} BLOB NOT NULL UNIQUE PRIMARY KEY,$0"}, "Create key type: BLOB. Extra: UNIQUE PRIMARY KEY NOT NULL": {"prefix": "ckbupn", "body": "${1:key} BLOB UNIQUE PRIMARY KEY NOT NULL,$0"}, "Create key type: BLOB. Extra: UNIQUE NOT NULL PRIMARY KEY": {"prefix": "ckbunp", "body": "${1:key} BLOB UNIQUE NOT NULL PRIMARY KEY,$0"}
}

snippetgen.py

# the code used to generate the snippets

import json
import itertools

type_accs = {
    "n":"NULL",
    "i":"INTEGER",
    "r":"REAL",
    "t":"TEXT",
    "b":"BLOB",
}

extra_accs = {
    "p": "PRIMARY KEY",
    "n": "NOT NULL",
    "u": "UNIQUE",
}

data = {}

for acc, val in type_accs.items():
    data["Create key type: " + val] = {
        "prefix": "ck" + acc,
        "body": "${1:key} " + val +",$0",
    }

    for i in range(1,len(extra_accs)+1):
        for tup in itertools.permutations(extra_accs.keys(),i):
            extra_str = " ".join([extra_accs[k] for k in tup])
            
            data["Create key type: " + val + ". Extra: "+extra_str] = {
                "prefix": "ck" + acc + ''.join([k for k in tup]),
                "body": "${1:key} " + val + " " + extra_str + ",$0",
            }

print(json.dumps(data))