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