DML

DML or Data Manipulation Language is what you’ll be using for the most of your queries once you finish defining your data model. DML enables you to insert rows, manipulate the data in them, access their data or delete them.

Indexes and performance

NB: Due to performance, at this point only the primary index (aka the primary key column) can be used to run DML queries. This restriction will be eased in upcoming relases.

info

As you’ve already been told, don’t forget to parameterize. The examples directly use literals to make it easier for you to understand!

INSERT​

Syntax​

-- insert in declaration orderINSERT INTO <model_name>(?, ?, ?, ...)-- insert using field namesINSERT INTO <model_name> { field1_name: ?, field2_name: ?, ...}

Requirements and responses​

• Access control: any
• Returns: empty or error

Examples​

INSERT INTO mymodel('sayan', true)INSERT INTO mymodel { username: 'sayan', verified: true,}

SELECT​

Syntax​

-- select wildcardSELECT * FROM <model_name> WHERE <primary_key_field> = <expression>-- select specific columnsSELECT profile_pic, phone_num FROM <model_name> WHERE <primary_key_field> = <expression>-- select several records (wildcard)SELECT ALL * FROM <model_name> LIMIT <limit>-- select several records (named columns)SELECT ALL username FROM <model_name> LIMIT 100

Requirements and responses​

• Access control: any
• Operational notes:
  • Unlike SQL, a standardSELECT in BlueQL is apoint query and hence can only access a single row
  • This means that if the row does not match an error is raised
  • To select multiple rows: you need to useSELECT ALL. Thismust be accompanied by aLIMIT clause.
• Access control: any
• Returns: row or error

Examples​

SELECT * FROM apps.socialapp WHERE username = 'sayan'SELECT password, profile_url FROM apps.logins WHERE username = 'sayan'

UPDATE​

Syntax​

UPDATE <model_name> SET <field_name> <operator> <updated_field_value>, ... WHERE <primary_key_field> = <expression>

Requirements and responses​

• Access control: any
• Operational notes:
  • Unlike SQL, a standardUPDATE in BlueQL is apoint query and hence can only mutate a single row
  • Wildcard queries aren’t yet permitted.NB: This will be fixed very soon!
• Access control: any
• Returns: row or error

Examples​

UPDATE apps.socialapp SET followers += 1 WHERE username = 'sayan'UPDATE apps.socialapp SET followers += 200, following += 5 WHERE username = 'sayan'

DELETE​

Syntax​

DELETE FROM <model_name> WHERE <primary_key_field> = <expression>

Requirements and responses​

• Access control: any
• Operational notes:
  • Unlike SQL, a standardDELETE in BlueQL is apoint query and hence can only remove a single row
  • This means that if the row does not match an error is raised
  • Wildcard queries aren’t yet permitted.NB: This will be fixed very soon!
• Access control: any
• Returns: row or error

Examples​

DELETE FROM apps.socialapp WHERE username = 'aboslutely mid user'