supabase / postgres-meta Goto Github PK
View Code? Open in Web Editor NEWA RESTful API for managing your Postgres. Fetch tables, add roles, and run queries
Home Page: https://supabase.com
License: Apache License 2.0
A RESTful API for managing your Postgres. Fetch tables, add roles, and run queries
Home Page: https://supabase.com
License: Apache License 2.0
Lets move to using structured logging with something like Winston. We can add DB_HOST as labels, add logging levels, timestamps.
Currently the editor breaks if a user tries to update a row from a table with no primary key - wondering if there's an endpoint that I could use for this scenario?
Row updates on the editor is currently done via the supabase client
Is there an internal PG ID that we can use for grants? We need this to update the permissions when we start doing RLS
I gave a try to pg-api and requested /tables
with the right headers, got a 500, looked at the log and found this:
throwing error error: syntax error at or near "MATERIALIZED"
at Connection.parseE (/snapshot/pg-api/node_modules/pg/lib/connection.js:614:13)
at Connection.parseMessage (/snapshot/pg-api/node_modules/pg/lib/connection.js:413:19)
at Socket.<anonymous> (/snapshot/pg-api/node_modules/pg/lib/connection.js:129:22)
at Socket.emit (events.js:311:20)
at addChunk (_stream_readable.js:294:12)
at readableAddChunk (_stream_readable.js:275:11)
at Socket.Readable.push (_stream_readable.js:209:10)
at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
name: 'error',
length: 101,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '16',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'scan.l',
line: '1133',
routine: 'scanner_yyerror'
}
It seems to be related with this part
https://github.com/supabase/pg-api/blob/ed5fa837504b66e18b3278905063e0891cd29e58/src/api/tables.ts#L128-L133
Note: the database does not have any tables (both in public
and another user-created schema)
For context, ideally we'd want users to be able to create new tables with an opt-out option for a column with a primary key
-> i.e new tables will be created with one column by default
Currently pg-api supports creation of columns with name
, type
, and comment
in the payload, but we'll need a way for users to create columns with primary key, and also select if they want it to be auto_increment (?).
Additionally not too sure if its pg-api related but I'll need the swagger definitions to be updated as well to reflect if a column is set to auto-increment or not. On the FE, I'll prevent users from editing fields which are auto-incremented to prevent it messing up psql's autoincrement logic from manually set values.
Outside of this issue:
I'm not exactly familiar with psql, but I'm assuming primary key columns are limited to certain data types too - could i know what types are supported?
It would be nice to be able to specify constraints within bot a table and a column
For example:
create table profiles (
username text,
unique(username)
)
alter:
alter table profiles
add constraint check_username
check (username ~* '^[a-zA-Z0-9_]*$' );
It may need a similar treatment as the default values:
postgres-meta/test/integration/index.spec.js
Line 352 in f35c38f
Required for the applications of RLS policies
This line:
Should only run if the name is different from what the table was previously named.
At the moment it is running even if you pass it the same name. This results in an error relation already exists
.
Required for Row Level Security
A user should have the ability to GET
the /schemas
endpoint and receive back all the schemas in the Postgres database
Based on @soedirgo's research here: #41
we are JOIN
ing information_schema
views with system tables, e.g.:
-- tables.sql
...
FROM
information_schema.tables
JOIN pg_class c ON quote_ident(table_schema)::regnamespace = c.relnamespace
AND c.relname = table_name
LEFT JOIN pg_stat_user_tables ON pg_stat_user_tables.schemaname = tables.table_schema
AND pg_stat_user_tables.relname = tables.table_name
...
information_schema
views already use system tables under the hood (like below), so we could inline it, though it'd make the SQL templates pretty ugly.
-- information_schema.tables
...
FROM pg_namespace nc
JOIN pg_class c ON nc.oid = c.relnamespace
LEFT JOIN (pg_type t
JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
...
The performance difference is big though (10x):
# Not inlined
❯ hyperfine 'curl -s http://localhost:1337/tables' --warmup 5
Benchmark #1: curl -s http://localhost:1337/tables
Time (mean ± σ): 1.004 s ± 0.027 s [User: 4.3 ms, System: 5.5 ms]
Range (min … max): 0.983 s … 1.063 s 10 runs
# Inlined
❯ hyperfine 'curl -s http://localhost:1337/tables' --warmup 5
Benchmark #1: curl -s http://localhost:1337/tables
Time (mean ± σ): 97.6 ms ± 5.3 ms [User: 4.2 ms, System: 5.1 ms]
Range (min … max): 91.9 ms … 110.0 ms 27 runs
Is set this up on an example API using Fly.io
We should deploy each time we bump the version: https://fly.io/docs/app-guides/continuous-deployment-with-github-actions/
It's proving extremely slow in prod. I assume the with
joins are running sequentially and will need to be converted to regular joins
Create an action to automatically run the tests. The action needs to spin up the database (docker-compose), and then then call npm test
A user should be able to post to /tables
, which should create a new table in the database
master
branch failed. 🚨I recommend you give this issue a high priority, so other packages depending on you could benefit from your bug fixes and new features.
You can find below the list of errors reported by semantic-release. Each one of them has to be resolved in order to automatically publish your package. I’m sure you can resolve this 💪.
Errors are usually caused by a misconfiguration or an authentication problem. With each error reported below you will find explanation and guidance to help you to resolve it.
Once all the errors are resolved, semantic-release will release your package the next time you push a commit to the master
branch. You can also manually restart the failed CI job that runs semantic-release.
If you are not sure how to resolve this, here is some links that can help you:
If those don’t help, or if this issue is reporting something you think isn’t right, you can always ask the humans behind semantic-release.
An npm token must be created and set in the NPM_TOKEN
environment variable on your CI environment.
Please make sure to create an npm token and to set it in the NPM_TOKEN
environment variable on your CI environment. The token must allow to publish to the registry https://registry.npmjs.org/
.
Good luck with your project ✨
Your semantic-release bot 📦🚀
This is a bit of a pedantic (breaking) issue that I found while writing up the OpenAPI spec: due to the naming convention of Postgres (snake_case), most of the fields in the API are also snake_case. It'd be more idiomatic to use camelCase.
There are also other minor issues that I found (mostly to do with types, e.g. string
should be number
), so we can do all that plus the OpenAPI stuff in one go. Probably not gonna eliminate all of such issues, but we can at least kaizen our way there.
Looks like the sql
files aren't getting copied
There is a script npm run dist
which packs this completely into a binary.
We should implement trigger
support. This is something we will need to build into our Dashboard
A clean wrapper around select * from information_schema.triggers
foreign_key
on POST and PATCHtable_id
as an int
, PATCH returns table_id
as a string (seems to be a difference for getColumnSqlize
and getColumnByPosSqlize
?)The goal is to be able to manage subscriptions for
Possible:
/tables
routeAt some stage this should be able to be run as a CLI as well.
eg:
pg-api types > types.json
Incrementally move this to typescript - the types will be important for the table/column creation
@joshenlim mentioned that an apostrophe in sql comment caused pg-api to fail - need to verify that these are being escaped correctly else could be potential for sql injection attacks
Can we somehow automate the release notes? I tried gren
in the past but didn't really nail it
This is the code that i'm using to poke the POST endpoint for columns
curl --location --request POST 'localhost:8000/pg/columns' \
--header 'x-connection-encrypted: U2FsdGVkX19kzsnMCPnxnFX52fPxwv6VFR3s3H4gvkDpDlJgf6wut+ylfvicB902wOcbLET39WTrvh4oUmip6xshgm3x39Rq3x1uUhGNPMLdediIPwKlScqA7aqjufNxmYttetfC6h8V93E5BpaOWtmOH06ty4JKdkeUYGpPoR0=' \
--header 'apikey: eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJzdXBhYmFzZSIsImlhdCI6MTU5NTQ3Njg3MiwiZXhwIjoyNTQyMTYxNjcyLCJhdWQiOiIiLCJzdWIiOiIiLCJyb2xlIjoicG9zdGdyZXMifQ.7wwVEyU4OFEWyoezDt6ud3cDQ-dbG3PlghGTCu8czz8' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--data-raw '{
"tableId": 16625,
"name": "col_1",
"type": "int4",
"comment": "asd"
}'
A user should be able to post to /roles
which should create a new role in the database
The live_count on tables is only indicative, but we are using it on the frontend to determine the number of rows. We should see if there is a figure that is accurate always
Steps to reproduce the behavior, please provide code snippets or a repository:
/tables
live_count
Don't worry about the error he mentions, but this is the Dashboard after he has inserted 20m rows:
The result will return an array of tables ["users", "users"]
Some way to distinguish which schema the table belong to. Perhaps:
[
{ id: X, name: "users", schema: "public" },
{ id: Y, name: "users", schema: "auth" },
]
Not sure if this is intended but trying to update field type from text -> int4 throws a 500, but text -> varchar is okay
Similarly, trying to update int4 -> float8 is fine too, but int4 -> text throws 500
Are we limiting column type updates to only types that are similar to what it was originally?
In src/lib/interfaces
we have all the different types of data we are returning. All of them are using "named" IDs. For example, tables has a field like table_id
== public.users
. This is prone to breakages if the user updates the name of the table or schema.
We should add an id
field to each of these which refers to PG's internal ID
This is a breaking issue
Previously there was an enums
field on every column. This must have been removed in the refactor
Context: Whenever the editor faces an error from calls to pg-api, I'll just show the error given by the API as a toast message.
At the moment, i noticed that most of the messages just throw "Request failed with status code 500", along with a 500 status code - will be better if the API returns more specific messages for greater clarity in UX and debugging
We would like to rename this repo to make it clearer on first glance exactly what this is doing. pg-api
seems like it could be for interacting with data (like PostgREST), but the intent of the repo is to manage the database itself.
Our current proposal is postgres-meta
.
Attempting to set a defaultValue for a column of text type returns a 500
Doing it so for int type columns seem to be working fine
Via the table editor:
Column should save with provided defaultValue
In Supabase, we would like to add descriptions to tables and columns. These descriptions can be stored in PG, using COMMENTS:
comment on table table_name is 'Some comment';
comment on column table_name.column_name is 'Some comment';
At the moment all of the interactions work through an express server. However, when we start work on the CLI, we will need to use this functionality without express. My proposal is to split the API and Server functioanality into 2 areas
src
- api # contains all the TypeScript classes
- server # contains the express routes, which import from `../api`
Then, we should be able to do something this:
npm install @supabasee/pg-api
import { createClient } from 'pg-api'
const api: PostgresApiClient = createClient(process.env.connection_string)
api.tables.add({
schema: 'public',
name: 'test'
})
import { createServer } from 'pg-api'
const server: PostgresApiServer = createServer(process.env.connection_string, {})
server.start() // starts express server on port 3000
Then the route POST /tables
calls api.tables.add()
postgres-meta
doesnt double quote table name on SQL query. so the query will fails when table name includes uppercase
tableSql = `ALTER PUBLICATION ${ident(old!.name)} SET TABLE ${tables.join(',')};`
It's a PostgreSQL convention. The main drawback of using camel case is that you'll need to double quote your tables when using SQL. supabase/supabase#619 (comment)
Steps to reproduce the behavior, please provide code snippets or a repository:
{tables: ["public.Menu"], id: 16387}
{code: 400, message: "relation "public.menu" does not exist"}
postgres-meta
should double quote table name as default to support uppercase char.
The table editor on the FE will start to introduce relations between tables in V14, we'll need a set of endpoints to allow CRUD (not sure for read though) for foreign keys between tables.
No hurry on this - for V14, I'll be doing this feature manually via the POST /query
endpoint
When creating or updating columns, we can specify a column to be an identity column by passing isIdentity: false/true
to the POST payload body.
We'll need an added level of detail to isIdentity by allowing users to specify if it should be GENERATED ALWAYS
or GENERATED BY DEFAULT
as such, as they both have different implications on whether the user can specify a value for the column.
GENERATED ALWAYS
prevents user from specifying a value for that column, but GENERATED BY DEFAULT
allows so. In both cases, if no value is specified, the value will be generated automatically in a sequence.
Currently, the FE table editor does not allow user to specify a value if the column is marked as isIdentity
, but this behavior will need to be changed accordingly to above.
Add an additional parameter for the POST payload body to allow the specification of identity format as above.
And also return the parameter when getting columns.
Context: Creating a new column through the table editor (POST columns) returns a slightly different structure than GET columns, specifically tableId
is an integer from POST but string from GET, and POST columns does not return foreignKey
property. Screenshots below.
Steps to reproduce the behavior, please provide code snippets or a repository:
Trigger a POST request for pg/columns and check the response body
Trigger a GET request for pg/columns and check the response body
Compare both response bodies (have attached screenshots as below for easier reference)
POST columns response body should be the same as each column from GET request. (tableId as string, foreignKey property)
Nil
Currently we recommend creating Types from the OpenAPI spec (https://supabase.io/docs/reference/javascript/generating-types)
This will cause a few problems because the types in Open API spec are somewhat limited.
It would be great to generate these definitions directly from the database. If we build it into this library then we can use it on the Dashboard and our CLI.
selects
(which allow relationship) and insert/update
(which don't allow relationships)A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.