Feature requests.

[Hisham-Pak]

• Add a way to return row description for the query results in http api. Column(name, type_code, display_size, internal_size, precision, scale, null_ok) with name and type_code mandatory and others NULL if no meaningful values can be provided.

Data Definition Language:

• TRUNCATE ...,... RESTART IDENTITY CASCADE
• Check Constraints.
• ALTER TABLE ... ADD CONSTRAINT ... (CHECK/PRIMARY KEY)
• ALTER TABLE ... DROP CONSTRAINT
• ALTER COLUMN ... (TYPE/SET NOT NULL/DROP NOT NULL) example.
• Add binary/bytea/blob datatype
• Foreign Key support (REFERENCE, ADD/DROP CONSTRAINT FOREIGN KEY REFERENCES ... ON DELETE ... ON UPDATE...)
• CREATE/ALTER/DROP DATABASE? Support connection to different databases?

[pmwkaa]

Hey Hisham,

I believe you can specify a set of used columns with using columns= argument. It will set to DEFAULT other column values. To return a column description, it is possible to set format, like this json-obj-pretty. It will wrap all result column expressions along side with names into json object.

Also note, that the current documentation is outdated, I am going to update it closer to the major release soon.
(Sorry for that)

Thank you for the feature requests, I will add them to my list.

TRUNCATE ...,... RESTART IDENTITY CASCADE

✔️ makes sense for IDENTITY, not sure about FOREIGN KEYS, will see

CHECK Constraint
ALTER TABLE ... ADD CONSTRAINT ... (CHECK/PRIMARY KEY)
ALTER TABLE ... DROP CONSTRAINT

✔️

Makes sense for the CHECK constraint, not sure about the PRIMARY KEY though. Currently it is mandatory and used as a partition key. I was thinking to separate them in the future, but this will heavily affect scans, will see.

ALTER COLUMN ... (TYPE/SET NOT NULL/DROP NOT NULL)

✔️ Type change would be tricky though, might be easier to create/drop column for now.

Add binary/bytea/blob datatype

✔️ It is not difficult to add it, just curious how to use them with JSON, same as normal strings?

Foreign Key support (REFERENCE, ADD/DROP CONSTRAINT FOREIGN KEY REFERENCES ... ON DELETE ... ON UPDATE...)

I had some thoughts, but this one might be very tricky.

CREATE/ALTER/DROP DATABASE?

Can this be replaced by separate SCHEMA, or there are some additional settings needed?

Support connection to different databases?

Do you have specific use case in mind?

[Hisham-Pak]

ALTER TABLE ... DROP CONSTRAINT ... (CHECK/PRIMARY KEY)

But Amelie allows creating multiple pk constraints in columns which does not align with other databases (or is it a composite pk under the hood maybe clarification in the documentation?). Would it be possible to add a separate UNIQUE instead then? The DROP CONSTRAINT PRIMARY KEY was intended for UNIQUE and not PRIMARY KEY.

CREATE/ALTER/DROP DATABASE? Can this be replaced by separate SCHEMA, or there are some additional settings needed?

Yes, while unusual, MySQL already has DATABASE and SCHEMA as synonyms.

Support connection to different databases? Do you have specific use case in mind?

It would make life a lot easier if we can have DATABASE/SCHEMA name in connection string for database drivers and orms. If this can not be done at least a USE DATABASE/SCHEMA will help since lots of drivers/orms might not support SCHEMA.TABLE syntax.

Seems like docs are version 0.3.0, please update thanks.

[Hisham-Pak]

I believe you can specify a set of used columns with using columns= argument. It will set to DEFAULT other column values. To return a column description, it is possible to set format, like this json-obj-pretty. It will wrap all result column expressions along side with names into json object.

I found this example in tests:

select system.table('test').columns;
[[{
  "name": "id",
  "type": 2,
  "type_size": 4,
  "constraints": [["not_null", true], ["as_identity", 1], ["default", null]]
}]]

The format json-obj-pretty also works as expected. But is there a way to do this in one HTTP round trip? and possibly return column data only for the returned set from the executed query.

[pmwkaa]

ALTER TABLE ... DROP CONSTRAINT ... (CHECK/PRIMARY KEY)

But Amelie allows creating multiple pk constraints in columns which does not align with other databases (or is it a composite pk under the hood maybe clarification in the documentation?). Would it be possible to add a separate UNIQUE instead then? The DROP CONSTRAINT PRIMARY KEY was intended for UNIQUE and not PRIMARY KEY.

I think there is a bit of confusion here. Usually you allowed to create only one PRIMARY KEY per table (which creates primary unique index), but you can have many UNIQUE indexes. Some databases do not require PRIMARY KEY, some do. In Amelie:

1. PRIMARY KEY is required per table. The key is used as a partition key.

2. Amelie supports secondary indexes. NON-UNIQUE indexes will have composite key (second part is a primary key copy). You can also have UNIQUE, but currently only for tables with 1 PARTITION (you can specify the number of partitions during CREATE TABLE).

I might ease both of those requirement in the future, but there always will be some form of a partition/shard key.

It would make life a lot easier if we can have DATABASE/SCHEMA name in connection string for database drivers and orms. If this can not be done at least a USE DATABASE/SCHEMA will help since lots of drivers/orms might not support SCHEMA.TABLE syntax.

Interesting, let me think about it. Ideally Amelie should be used by using functions instead of sending plain-text SQL. This would be much more faster and secure. Each function call is transactional and supports HTTP API.

But is there a way to do this in one HTTP round trip? and possibly return column data only for the returned set from the executed query.

Currently format can be changed in the db config, passed as an argument on start as --format= or it can be set in the HTTP Prefer header per each http request (Prefer return=format).

if you change format to json-obj, it will wrap all returning column names into object, like this:

format json-array

select 1, 2, 3;
[[1, 2, 3]]

format json-obj-pretty

select 1 as a, 2 as b, 3 as c;
[{
  "a": 1,
  "b": 2,
  "c": 3
}]

The same should apply for the HTTP API results.

Seems like docs are version 0.3.0, please update thanks.

Yes, sorry about that. A lot of going on and a lot changed since then and I want to make it right without introducing a lot of breaking changes in the future. I will update everything in the upcoming major release.

I am currently working on improving UI/UX for the next release. The console/cli experience will be more alike to sqlite/duckdb, it would be easier to experiment and play around. I will also switch to plain/text content-type for result on console for simplicity.

I am also happy to suggestions, thoughts and constructive critique.

[pmwkaa]

The next release will have full support for databases with updated api.