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 some_keyspace 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
  ('_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 = ''

mongodb

Sample Connection String

This is a sample connection string you can use.

mongodb://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(
    'id', {
      "id": "",
      "column1": "",
      "column2": ""
    }
  )

Insert

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

Update

db.collection('table1')
  .update({
    "id": 123,
    "column1": 123,
    "column2": 123
  }, {
    $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({
    "id": "some_id"
  })

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')