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 order
INSERT INTO <model_name>(?, ?, ?, ...)
-- insert using field names
INSERT 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 wildcard
SELECT * FROM <model_name> WHERE <primary_key_field> = <expression>
-- select specific columns
SELECT 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 standard
SELECT
in BlueQL is a point 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 use
SELECT ALL
. This must be accompanied by aLIMIT
clause.
- Unlike SQL, a standard
- 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 standard
UPDATE
in BlueQL is a point query and hence can only mutate a single row - Wildcard queries aren't yet permitted. NB: This will be fixed very soon!
- Unlike SQL, a standard
- 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 standard
DELETE
in BlueQL is a point 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!
- Unlike SQL, a standard
- Access control: any
- Returns: row or error
Examples
DELETE FROM apps.socialapp WHERE username = 'aboslutely mid user'