Query Guides:

mysql

Sample Connection String

This is a sample connection string you can use.

mysql://root:password@localhost:3306

Drop Database

DROP DATABASE database1

Create Database

CREATE DATABASE database1

Select All Columns

SELECT
  *
FROM
  table1
LIMIT
  200

Select Count

SELECT
  COUNT(*)
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Select Specific Columns

SELECT
  id,
  column1,
  column2
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Select Distinct

SELECT
  DISTINCT column1
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Insert

INSERT INTO
  table1 (id, column1, column2)
VALUES
  ('_id_', '_column1_', '_column2_')

Update

UPDATE
  table1
SET
  id = '',
  column1 = '',
  column2 = ''
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Delete

DELETE FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Create Table

CREATE TABLE table1 (
  id INT PRIMARY KEY NOT NULL,
  column1 INT NOT NULL,
  column2 VARCHAR(100) NOT NULL
)

Drop Table

DROP TABLE table1

Add Column

ALTER TABLE
  table1
ADD
  COLUMN newColumn1 varchar(200)

Drop Column

ALTER TABLE
  table1 DROP COLUMN id;
ALTER TABLE
  table1 DROP COLUMN column1;
ALTER TABLE
  table1 DROP COLUMN column2;

mariadb

Sample Connection String

This is a sample connection string you can use.

mariadb://root:password@localhost:3306

Drop Database

DROP DATABASE database1

Create Database

CREATE DATABASE database1

Select All Columns

SELECT
  *
FROM
  table1
LIMIT
  200

Select Count

SELECT
  COUNT(*)
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Select Specific Columns

SELECT
  id,
  column1,
  column2
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Select Distinct

SELECT
  DISTINCT column1
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Insert

INSERT INTO
  table1 (id, column1, column2)
VALUES
  ('_id_', '_column1_', '_column2_')

Update

UPDATE
  table1
SET
  id = '',
  column1 = '',
  column2 = ''
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Delete

DELETE FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Create Table

CREATE TABLE table1 (
  id INT PRIMARY KEY NOT NULL,
  column1 INT NOT NULL,
  column2 VARCHAR(100) NOT NULL
)

Drop Table

DROP TABLE table1

Add Column

ALTER TABLE
  table1
ADD
  COLUMN newColumn1 varchar(200)

Drop Column

ALTER TABLE
  table1 DROP COLUMN id;
ALTER TABLE
  table1 DROP COLUMN column1;
ALTER TABLE
  table1 DROP COLUMN column2;

mssql

Sample Connection String

This is a sample connection string you can use.

mssql://sa:password123!@localhost:1433

Drop Database

DROP DATABASE database1

Create Database

CREATE DATABASE database1

Select All Columns

SELECT
  TOP 200 *
FROM
  table1

Select Count

SELECT
  COUNT(*)
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Select Specific Columns

SELECT
  TOP 200 id,
  column1,
  column2
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Select Distinct

SELECT
  DISTINCT TOP 200 column1
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Insert

INSERT INTO
  table1 (id, column1, column2)
VALUES
  ('_id_', '_column1_', '_column2_')

Update

UPDATE
  table1
SET
  id = '',
  column1 = '',
  column2 = ''
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Delete

DELETE FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Create Table

CREATE TABLE table1 (
  id INT PRIMARY KEY NOT NULL,
  column1 INT NOT NULL,
  column2 VARCHAR(100) NOT NULL
)

Drop Table

DROP TABLE table1

Add Column

ALTER TABLE
  table1
ADD
  COLUMN newColumn1 NVARCHAR(200)

Drop Column

ALTER TABLE
  table1 DROP COLUMN id;
ALTER TABLE
  table1 DROP COLUMN column1;
ALTER TABLE
  table1 DROP COLUMN column2;

postgres

Sample Connection String

This is a sample connection string you can use.

postgres://postgres:password@localhost:5432

Drop Database

DROP DATABASE database1

Create Database

CREATE DATABASE database1

Select All Columns

SELECT
  *
FROM
  table1
LIMIT
  200

Select Count

SELECT
  COUNT(*)
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Select Specific Columns

SELECT
  id,
  column1,
  column2
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Select Distinct

SELECT
  DISTINCT column1
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Insert

INSERT INTO
  table1 (id, column1, column2)
VALUES
  ('_id_', '_column1_', '_column2_')

Update

UPDATE
  table1
SET
  id = '',
  column1 = '',
  column2 = ''
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Delete

DELETE FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Create Table

CREATE TABLE table1 (
  id BIGSERIAL PRIMARY KEY,
  column1 INT NOT NULL,
  column2 VARCHAR(100) NOT NULL
)

Drop Table

DROP TABLE table1

Add Column

ALTER TABLE
  table1
ADD
  COLUMN newColumn1 CHAR(200)

Drop Column

ALTER TABLE
  table1 DROP COLUMN id;
ALTER TABLE
  table1 DROP COLUMN column1;
ALTER TABLE
  table1 DROP COLUMN column2;

sqlite

Sample Connection String

This is a sample connection string you can use.

sqlite://test-db.sqlite

Drop Database

DROP DATABASE database1

Create Database

CREATE DATABASE database1

Select All Columns

SELECT
  *
FROM
  table1
LIMIT
  200

Select Count

SELECT
  COUNT(*)
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Select Specific Columns

SELECT
  id,
  column1,
  column2
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Select Distinct

SELECT
  DISTINCT column1
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Insert

INSERT INTO
  table1 (id, column1, column2)
VALUES
  ('_id_', '_column1_', '_column2_')

Update

UPDATE
  table1
SET
  id = '',
  column1 = '',
  column2 = ''
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Delete

DELETE FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Create Table

CREATE TABLE table1 (
  id INTEGER PRIMARY KEY NOT NULL,
  column1 INTEGER NOT NULL,
  column2 VARCHAR(100) NOT NULL
)

Drop Table

DROP TABLE table1

Add Column

ALTER TABLE
  table1
ADD
  COLUMN newColumn1 TEXT

Drop Column

ALTER TABLE
  table1 DROP COLUMN id;
ALTER TABLE
  table1 DROP COLUMN column1;
ALTER TABLE
  table1 DROP COLUMN column2;

cassandra

Sample Connection String

This is a sample connection string you can use.

cassandra://username:password@localhost:9042

Create Keyspace

CREATE KEYSPACE IF NOT EXISTS database1 WITH replication = { 'class': 'SimpleStrategy',
'replication_factor': 3 };

Drop Keyspace

DROP KEYSPACE IF EXISTS database1;

Select All Columns

SELECT
  *
FROM
  table1
LIMIT
  200

Select Specific Columns

SELECT
  id,
  column1,
  column2
FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''
LIMIT
  200

Insert

INSERT INTO
  table1 (id, column1, column2)
VALUES
  (123, 123, '_column2_')

Update

UPDATE
  table1
SET
  id = 123,
  column1 = 123,
  column2 = '_column2_'
WHERE
  id = 123
  AND column1 = 123
  AND column2 = '_column2_'

Delete

DELETE FROM
  table1
WHERE
  id = ''
  AND column1 = ''
  AND column2 = ''

Create Table

CREATE TABLE table1 (id INT PRIMARY KEY, column1 INT, column2 VARCHAR(100))

Drop Table

DROP TABLE table1

Add Column

ALTER TABLE
  table1
ADD
  new_column1 TEXT

Drop Column

ALTER TABLE
  table1 DROP id;
ALTER TABLE
  table1 DROP column1;
ALTER TABLE
  table1 DROP column2;

mongodb

Sample Connection String

This is a sample connection string you can use.

mongodb://username:password@localhost:27017

Create Database

db.createDatabase("database1");

Drop Database

db.dropDatabase();

Select All Columns

db.collection("table1").find().limit(200).toArray();

Select Specific Columns

db.collection("table1")
  .find({
    id: 123,
    column1: 123,
    column2: 123,
  })
  .limit(200)
  .toArray();

Select Distinct

db.collection("table1").distinct("column1", {
  id: "",
  column1: "",
  column2: "",
});

Select One Record

db.collection("table1").findOne({
  _id: ObjectId("some_id"),
});

Insert

db.collection("table1").insertMany([
  {
    id: 123,
    column1: 123,
    column2: 123,
  },
]);

Update

db.collection("table1").update(
  {
    id: 123,
    column1: 123,
    column2: 123,
    _id: ObjectId("some_id"),
  },
  {
    $set: {
      id: 123,
      column1: 123,
      column2: 123,
    },
  },
);

Delete

db.collection("table1").deleteMany({
  id: 123,
  column1: 123,
  column2: 123,
});

Create Collection

db.createCollection("table1");

Drop Collection

db.collection("table1").drop();

redis

Sample Connection String

This is a sample connection string you can use.

redis://localhost:6379

Set Value

db.set("key", "value123");

Get Value by Key

db.get("key");

Scan for keys

db.keys("*");

Hashset > Set Value

db.hSet("hash_key1", "field1", "value1");

Hashset > Get Value By Key

db.hGetAll("hash_key1");

Hashset > Values

db.hVals("hash_key1");

Hashset > Check if key exists

db.hExists("hash_key1", "field1");

List > Get Items

db.lRange("list_key1", 0, -1);

List > Push item to the front

db.lPush("list_key1", "value");

List > Push item to the back

db.rPush("list_key1", "value");

List > Delete item from the front

db.lPop("list_key1");

List > Delete item from the back

db.rPop("list_key1");

Set > Get Items

db.sMembers("set_key1");

Set > Add Item

db.sAdd("set_key1", "value1");

Set > Is a member of set

db.sIsMember("set_key1", "value1");

Set > Total Size

db.sCard("set_key1");

Set > Remove last item

db.sPop("set_key1");

Sorted Set > Get Items

db.zRange("sorted_set_key1", 0, -1);

Sorted Set > Add Item

db.zAdd("sorted_set_key1", [
  {
    score: 1,
    value: "some_value",
  },
]);

Publish a message

db.publish("pubsub_channel_key1", "some_message");

cosmosdb

Sample Connection String

This is a sample connection string you can use.

cosmosdb://AccountEndpoint=some_cosmos_endpoint;AccountKey=some_cosmos_account_key

Create Database

client.databases.create({
  id: "database1",
});

Create Database Container

client.database("database1").containers.create({
  id: "some_container_name",
});

Drop Database

client.database("database1").delete();

Select All Columns

client
  .database("database1")
  .container("table1")
  .items.query({
    query: `
  SELECT *
  FROM c
  OFFSET 0 LIMIT 200`,
  })
  .fetchAll();

Select Specific Columns

client
  .database("database1")
  .container("table1")
  .items.query({
    query: `
  SELECT c.id,
  c.column1,
  c.column2
  FROM c
  WHERE c.id = ''
  AND c.column1 = ''
  AND c.column2 = ''
  OFFSET 0 LIMIT 200`,
  })
  .fetchAll();

Select By Id

client
  .database("database1")
  .container("table1")
  .items.query({
    query: `
  SELECT *
  FROM c
  WHERE c.id = '123'`,
  })
  .fetchAll();

Read

client.database("database1").container("table1").item("some_id", "some_partition_key").read();

Insert

client.database("database1").container("table1").items.create({});

Update

client.database("database1").container("table1").item("some_id", "some_partition_key").replace({
  id: "some_id",
});

Delete

client.database("database1").container("table1").item("some_id", "some_partition_key").delete();

Raw Select All Columns SQL

SELECT
  *
FROM
  c

Create Container

client.database("database1").containers.create({
  id: "table1",
});

Drop Container

client.database("database1").container("table1").delete();

aztable

Sample Connection String

This is a sample connection string you can use.

aztable://DefaultEndpointsProtocol=https;AccountName=<your_account_name>;AccountKey=<your_account_key>;EndpointSuffix=core.windows.net

Create Table

serviceClient.createTable("somenewtablename");

Select All Columns

tableClient.listEntities({
  queryOptions: {
    filter: ``,
  },
});

Select Specific Columns

tableClient.listEntities({
  queryOptions: {
    filter: `PartitionKey eq 'some_partition_key'`,
    select: ["id", "column1", "column2"],
  },
});

Insert

tableClient.createEntity({
  rowKey: "some_row_key",
  partitionKey: "some_partition_key",
});

Update

tableClient.updateEntity({
  rowKey: "some_row_key",
  partitionKey: "some_partition_key",
});

Upsert

tableClient.upsertEntity(
  {
    rowKey: "some_row_key",
    partitionKey: "some_partition_key",
  },
  "Replace",
);

Delete

tableClient.deleteEntity("some_partition_key", "some_row_key");

Create Table

serviceClient.createTable("table1");

Drop Table

serviceClient.deleteTable("table1");