equivalent to \d of PostgreSQL ?

[matrixise]

Hi,

with PostgreSQL, we can see the description of a table, is it possible in the REPL?

[elprans]

There's no such shortcut in the repl yet, but you can run introspection queries.

To list object types:

SELECT schema::ObjectType { name };

To list properties an links of an object type:

    WITH Object := (SELECT schema::ObjectType
                    FILTER .name = '<fully-qualified object type name>`)
    SELECT Object.pointers {name};

For example:

    WITH Object := (SELECT schema::ObjectType
                    FILTER .name = 'std::Object')
    SELECT Object.pointers {name};

[vpetrovykh]

It makes sense for us to implement a similar set of commands to PostgreSQL \d. In principle, we now have one more mechanism in addition to introspection queries that can help us - DESCRIBE. Let's consider some of the features, first.

Many PostgreSQL commands allow to specify a pattern to be matched. The pattern rules are a mix of shell file name patterns, regexp, and identifier quoting. Which incidentally means that *, ., and ? don't have their regexp meaning and require extra care if they are needed in regexp. For example, instead of the regexp .+ one should use ?+, etc. I would argue that this is one of the areas where EdgeDB should try to clean up the confusion:

• There's not a lot of value in forcing identifiers to lower-case (unless quoted) for EdgeDB simply because we have case-sensitive identifiers often of mixed case. However, we may want to make the matches case-insensitive by default.
• Rather than rely on identifier quoting mechanism to force exact case match I would suggest adding a flag to the command to toggle case-sensitivity. This will reduce the overall complexity of rules and feature interaction for patterns.
• There's no real incentive to use glob-like syntax, since we use :: as a module/name separator instead of . for an equivalent concept in PostgreSQL. So we would end up with *::* where PostgreSQL has *.*, which is neither here nor there in terms of similarity to something familiar. So it would make sense then to only use regexp patterns to provide flexibility in matches. The upside of this is that the users won't have to learn 2 flavors of regexp - one for \d commands and another for actual queries.
• We can retain the overall rule that patterns without an explicit :: in them refer to short names in the currently selected module (e.g. default). Whereas to view all modules something like .*::Foo.* should be used. It's possible to scan the pattern for presence of explicit :: and match full names or short names based on that. In theory it would be possible to write patterns that match everything by kind of abusing this (e.g. ::|.*), but this digs into fairly advanced usage of regexp and there's no real harm.

To summarize, I would propose to use patterns that are case-insensitive regexps where the presence of a literal :: is interpreted that fully-qualified name must be matched, while its absence indicates that only short names in the current module will be matched. Case-sensitivity should be a command-level flag. I'm tempted to suggest C, but that may clash with something like \dC for displaying casts. So maybe M (for "match") or P (for "pattern") could work.

[1st1]

However, we may want to make the matches case-insensitive by default.

+1.

But how to make it case-sensitive? Upper-case I as in \dI foo|bar?

So it would make sense then to only use regexp patterns to provide flexibility in matches.

+1.

We can retain the overall rule that patterns without an explicit :: in them refer to short names in the currently selected module (e.g. default).

-1.

I wouldn't do any of that (implicit :: and current module scope) that -- seems overly complex UX. I'd simply match the pattern on fullnames in all modules.

. I'm tempted to suggest C, but that may clash with something like \dC for displaying casts. So maybe M (for "match") or P (for "pattern") could work.

The classic flag for case-insensitive regexp is i. I propose to do the reverse of that flag -- I.

[vpetrovykh]

We can retain the overall rule that patterns without an explicit :: in them refer to short names in the currently selected module (e.g. default).

-1.

I wouldn't do any of that (implicit :: and current module scope) that -- seems overly complex UX. I'd simply match the pattern on fullnames in all modules.

I think I see your point. I didn't take into account that in PostgreSQL the tendency is to have flat structure where everything is basically in the same "module". Whereas in EdgeDB we expect potentially a fair bit of module fragmentation (depending on coding style, of course). So treating :: as special makes less sense. With clear naming it won't matter and with multiple similar names across modules it's a benefit if the pattern picks them all up.

. I'm tempted to suggest C, but that may clash with something like \dC for displaying casts. So maybe M (for "match") or P (for "pattern") could work.

The classic flag for case-insensitive regexp is i. I propose to do the reverse of that flag -- I.

I like I just fine. +1

[1st1]

I think we should support the following commands:

  \d      NAME           describe type / function / view / etc
  \df     [PATTERN]      list functions
  \dA     [PATTERN]      list access methods
  \dC     [PATTERN]      list casts  
  \dg     [PATTERN]      list roles
  \di     [PATTERN]      list indexes
  \do     [PATTERN]      list operators
  \dt     [PATTERN]      list object types
  \dT     [PATTERN]      list scalar types
  \du     [PATTERN]      list roles
  \dv     [PATTERN]      list views
  \l      [PATTERN]      list databases

Open question:

• Should \dt and \dT show all types including the ones defined in modules std::,sys::, and cfg::? Or should we have an S modifier as in \dtS, \dTS, \dfS, etc to show system/standard types?

[vpetrovykh]

Another common feature of \d commands is that they often have an S variant that includes system/built-in items, whereas by default the results include only user-created items. We currently don't have a natural divide like that, or rather, it's inconsistent.

For example, SELECT Object{__type__:{name}} will ignore schema objects (ostensibly every TYPE is implicitly extending Object, though), and will include primarily user-created objects, but will also include arguably system objects like stdgraphql::Query and stdgraphql::Mutation singletons.

On the other hand, SELECT schema::ObjectType{name} or SELECT schema::Function{name} will dutifully list all such items present in the schema regardless of provenance.

Should \dt and \dT show all types including the ones defined in modules std::,sys::, and cfg::? Or should we have an S modifier as in \dtS, \dTS, \dfS, etc to show system/standard types?

Yes, I think we should.
Overall it appears that we tend to ignore the divide between system entities and user-created entities and simply rely on module names to filter them as needed. So we can use this in a form of an S flag for some commands (like listing types, scalars, or functions), where we would introduce an additional filter that omits all things that have names starting with any of the "system" modules by default and remove that restriction when the S flag is used.

[vpetrovykh]

To the commands I'd add:

\dm [PATTERN]      list modules

Otherwise I agree with the list.

One question, though: what are "access methods"?

[vpetrovykh]

Oh, and we want a \dc [PATTERN] list constraints, too.

[vpetrovykh]

Additionaly, for functions and scalar types it may be useful to add extra flags (similar to function flags in PostgreSQL):

  \df[an]     [PATTERN]      list functions [only aggregate/normal] 

Where aggregate functions are any functions with at least one SET OF arg.

  \dT[sen]     [PATTERN]      list scalar types [only sequences/enums/normal]

[1st1]

Oh, and we want a \dc [PATTERN] list constraints, too.

I'm not sure if it's going to be useful. Not sure we should have a command to list all indexes either. I'd skip them for now. @elprans?

Additionaly, for functions and scalar types it may be useful to add extra flags (similar to function flags in PostgreSQL):

I don't like the idea of separating functions and aggregates. We don't really separate them in our documentation; aggregate is a function with a SET OF parameter. So I'd just stick to \df listing both.

[vpetrovykh]

Listing what constraints are available is useful when you know you need one, but forgot the exact name.

It's also useful to know which custom constraints have already been created so you don't blindly duplicate them.

Regarding indexes, knowing which things are indexed could be useful when trying to get a sense of what may be impacting a large complex query. Like trying to narrow down where to look first for optimizations. This bit is coming from ORM experience where a priory I don't always know which indexes will be created automatically and which I need to specify manually, so this kind of overview is useful.

Regarding aggregates, I can see why now it's not terribly relevant distinction, but it may be useful when we finally have GROUP working because that's where you may specifically want to only consider SET OF functions when constructing a query. So we can have a plain \df for now and maybe revisit this later in context of having GROUP.

[1st1]

OK on indexes & constraints; just need to tweak the output to make sure it's clear if a constraint is abstract or not. Need to also display clear type names / link/property names for non-abstract constraints and indexes.

[elprans]

Do we really have to follow the \dX command for listing things? It seems like using \lX makes a bit more sense. The X in \dX can then denote the schema class, which is directly related to DESCRIBE <SCHEMACLASS> syntax.

[1st1]

@elprans

Do we really have to follow the \dX command for listing things?

The only point of using \dX is to make Postgres users feel at home. I feel it's important. Although since you're a heavy Postgres user yourself I think you should make the call here. So \lX or \dX?

[elprans]

I use psql quite often and I have to look them up most of the time, so I don’t think there’s much muscle memory there other than \dt. We can always put a hint into an error message about unrecognized command. I’m for \l

[1st1]

Then \l it is :) I like that it's consistent now.

[tailhook]

This seems to be done. Feel free to open more specific issues in CLI repository.