---

title: sqlui-native —

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://cassandra:cassandra@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')

sfdc

Sample Connection String

This is a sample connection string you can use.

sfdc://{"username":"your_username","password":"your_password","securityToken":"your_token","loginUrl":"login.salesforce.com"}

Select All Columns

SELECT
  id,
  column1,
  column2
FROM
  table1
LIMIT
  200

Select Specific Columns

SELECT
  id,
  column1,
  column2
FROM
  table1
LIMIT
  200

Select By Id

SELECT
  Id,
  Name
FROM
  table1
WHERE
  Id = 'some_record_id'
LIMIT
  1

Count Records

SELECT
  COUNT()
FROM
  table1

Group By

SELECT
  Type,
  COUNT(Id) cnt
FROM
  table1
GROUP BY
  Type
ORDER BY
  COUNT(Id) DESC

Search By Name (SOQL)

SELECT
  Id,
  Name
FROM
  table1
WHERE
  Name LIKE '%keyword%'
LIMIT
  200

Recent Records

SELECT
  Id,
  Name,
  CreatedDate,
  LastModifiedDate
FROM
  table1
ORDER BY
  CreatedDate DESC
LIMIT
  200

Select By Date Range

SELECT
  Id,
  Name,
  CreatedDate
FROM
  table1
WHERE
  CreatedDate = LAST_N_DAYS: 7
LIMIT
  200

Subquery (Child Records)

SELECT
  Id,
  Name,
  (
    SELECT
      Id,
      Name
    FROM
      Contacts
  )
FROM
  table1
LIMIT
  200

Search (SOSL)

FIND { keyword } IN ALL FIELDS RETURNING table1(Id, Name)
LIMIT
  200

Insert Record (API)

conn.sobject('table1')
  .create({
    "id": "",
    "column1": "",
    "column2": ""
  })

Update Record (API)

conn.sobject('table1')
  .update({
    "Id": "record_id_here",
    "id": "",
    "column1": "",
    "column2": ""
  })

Delete Record (API)

conn.sobject('table1')
  .destroy('record_id_here')

Upsert Record (API)

conn.sobject('table1')
  .upsert({
    "External_Id__c": "external_id_value",
    "Name": "Record Name"
  }, 'External_Id__c')

Describe Object (Fields)

SELECT
  QualifiedApiName,
  DataType,
  Label
FROM
  FieldDefinition
WHERE
  EntityDefinition.QualifiedApiName = 'table1'
LIMIT
  200

Describe Object (API)

conn.sobject('table1')
  .describe()